Using Action Solicitor instead of Primary Solicitor(s) on record

Becky Griswold:

I'm wanting to organize an action report for our moves management by Action Solicitor.  There isn't a way (that I have found) to export the solicitor assigned to the action, only the primary solicitor on the record.  That said, is there a way within Crystal (or Export for that matter) to only get the solicitor linked to the action instead of the solicitors assigned in the relationship tab?

Looking forward to your answers! 

 Thanks so much, Becky

The problem with it is that it will export solicitor names as a semicolon delimited field, so if you have more than one solicitor on an action, you have to accommodate for that in the report. Also it exports them in the format lastname, firstname. Why they can't make action and proposal solicitors export in their own table for reporting purposes is beyond me.

Becky Griswold:
Thanks James! So explanation as to why this was brought on. We have a report that was created years ago that was exported through Query as an Access Database file and used in a Crystal report. In the query it was using only the last name of the solicitor so we were having only one solicitor show up on the report even when there may have been two. I am attempting to redo the report as an .MDB export so it can be used in custom reports but am bumping up against this problem. There are two solicitors on some of the actions (I am pulling out of the action solicitor not relationship tab), but I was confused because of how the report pulled previously. I'm supposing the only way to have one solicitor listed is through formulas in Crystal. Anyway, any further words of wisdom would be super appreciated.

Josh Bekerman:

Hi Becky!  Correct, you'll need a formula that accounts for every combination of solicitors in your data file.  You can find this in the formula editor by right-clicking the solicitor field and then Browse Data.  Obviously you'll need to decide which solicitor to return for each combination (or clean up the solicitors on the action record in RE if there should only be one solicitor anyway).  The formula could be something like this:

If {solicitor} = "Bekerman, Josh; Griswold, Becky" then "Josh Bekerman" else

If {solicitor} = "Bekerman, Josh; Andrews, James" then "Josh Bekerman" else ...(for all other combinations, and then finish with just the solicitor field)

{solicitor}

If {solicitor} like "*Bekerman, Josh*" then "Josh Bekerman"

I think that might work. Let's see if this posts...

Josh Bekerman:

A quick note for Becky on the differences between the two.  James' example could be used if Josh should always be the returned value no matter who the other solicitors are.  My example could be used if it does matter which solicitors are listed, maybe Josh should be listed if it's James and Josh, but Becky should be listed if it's Becky and Josh.  Then again I think you could use both examples in the same formula, you would just have to be careful of the order the if/then statements are in.

Edit: actually not entirely true. You could still run through the if/thens using my formula, if you order them from most relevant solicitor to least relevant.

Of course, if that changes per project or per constituency, you have a whole other level of conditions to meet...