For purposes of this example, we're working with the proposal records and the proposal solicitors - if the report is working with actions, simply substitute the proposal fields with the corresponding action field. Also, this example uses a list of 6 different solicitors, named here as Solicitor Name1, Solicitor Name2, Solicitor Name3, Solicitor Name4, Solicitor Name5, and Solicitor Name 6 - adjust for desired list of solicitors as needed.

1. Go to Insert > Group

2. Select to group on the field CnPrProp_1.CnPrProp_1_IDKEY, then click OK

Note: Make sure that it's the Prop_1 IDKEY and not the Prop IDKEY

3. Right-click and select to suppress all sections except the Report Header

4. Create a formula called: Solicitor Name1

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name1")>0 then 1

5. Create a formula called: Solicitor Name2

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name2")>0 then 1

6. Create a formula called: Solicitor Name3

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name3")>0 then 1

7. Create a formula called: Solicitor Name4

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name4")>0 then 1

8. Create a formula called: Solicitor Name5

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name5")>0 then 1

9. Create a formula called: Solicitor Name6

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name6")>0 then 1

10. Create a formula called Solicitor Name as follows, including all parenthesis - be careful to get it all, this is a long one:

if instr({CnPrProp_1.CnPrProp_1_Solicitors},";")=0 then {CnPrProp_1.CnPrProp_1_Solicitors} else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},";")>0 then

(

if sum({@Solicitor Name1},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name2},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name3},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name4},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name5},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name6},{CnPrProp_1.CnPrProp_1_IDKEY}) >1 and

left({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")-1) in ["Solicitor Name1", "Solicitor Name2", "Solicitor Name3", "Solicitor Name4", "Solicitor Name5", "Solicitor Name6"]

then

left({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")-1)

else

if sum({@Solicitor Name1},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name2},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name3},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name4},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name5},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name6},{CnPrProp_1.CnPrProp_1_IDKEY}) >1

and

not(left({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")-1) in ["Solicitor Name1", "Solicitor Name2", "Solicitor Name3", "Solicitor Name4", "Solicitor Name5", "Solicitor Name6"])

and

instr({CnPrProp_1.CnPrProp_1_Solicitors},";")+instr(strreverse({CnPrProp_1.CnPrProp_1_Solicitors}),";")-1=len({CnPrProp_1.CnPrProp_1_Solicitors})

then

right({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")-1)

else

if sum({@Solicitor Name1},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name2},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name3},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name4},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name5},{CnPrProp_1.CnPrProp_1_IDKEY})+

sum({@Solicitor Name6},{CnPrProp_1.CnPrProp_1_IDKEY}) >1

and

not(left({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")-1) in ["Solicitor Name1", "Solicitor Name2", "Solicitor Name3", "Solicitor Name4", "Solicitor Name5", "Solicitor Name6"])

and

instr({CnPrProp_1.CnPrProp_1_Solicitors},";")+instr(strreverse({CnPrProp_1.CnPrProp_1_Solicitors}),";")-1<>len({CnPrProp_1.CnPrProp_1_Solicitors})

then

mid({CnPrProp_1.CnPrProp_1_Solicitors}, (instr({CnPrProp_1.CnPrProp_1_Solicitors},";"))+2, (len({CnPrProp_1.CnPrProp_1_Solicitors})-len(left({CnPrProp_1.CnPrProp_1_Solicitors},instr({CnPrProp_1.CnPrProp_1_Solicitors},";")))-instr(strreverse({CnPrProp_1.CnPrProp_1_Solicitors}),";"))-1)

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name1")>0 then "Solicitor Name1"

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name2")>0 then "Solicitor Name2"

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name3")>0 then "Solicitor Name3"

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name4")>0 then "Solicitor Name4"

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name5")>0 then "Solicitor Name5"

else

if instr({CnPrProp_1.CnPrProp_1_Solicitors},"Solicitor Name6")>0 then "Solicitor Name6"

)

11. Group on formula from Step 10 as needed

# Blackbaud Community

Connect and collaborate with fellow Blackbaud users.