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

James Andrews

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

Action solicitor is available in export -- it's in the Actions area of export, not the Relationships area.

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

James Andrews:
Action solicitor is available in export -- it's in the Actions area of export, not the Relationships area.

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.

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.

James Andrews

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.

Testing as I've lost two replies already.

Joshua Bekerman bCLO bCRE

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.

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}

James Andrews

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}

Could you do something like,

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

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

Joshua Bekerman bCLO bCRE

James Andrews:
Could you do something like,

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

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

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.

James Andrews

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.

True. I'm picturing pulling your name no matter who else is on there.

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...

Joshua Bekerman bCLO bCRE

James Andrews:
True. I'm picturing pulling your name no matter who else is on there.

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...

Hi Becky, any luck with this?

Mike Furlong

Just a quick note as Josh and James seem to be providing the needed answers.  I've developed a couple of reports that work off of picking single solicitors off of an Action where multiple solicitors are listed and you want to keep in mind that you will be counting the same action multiple times then.  Once for each solicitor if you are tallying them up.  Depending on the purpose of the report, you might be ok with this.  If not, it creates a larger issue.  Just wanted to throw that out.