Created a formula to Group Records - records missing from report

Darcene Thomas

I created the formula below to group on Solicitor Gifts and Community Engagement Appeal.  Line 3 is not producing any records in the report. What am I doing wrong?


This is what I am looking for:

If appeal like *CE then Community Engagement Appeal (doesn't matter what solicitor is listed)

If solicitor like *Harrison, but appeal is blank or appeal is not like *CE then Solicitor Gifts


Formula:

Line1:  IF IsNULL({CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name}) and {CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}like "*CE" then "Community Engagement Appeal" else

Line 2:  IF {CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}like "*CE" then "Community Engagement Appeal" else

Line 3: IF {CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}<> "*CE" and {CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name} like "*Harrison*" then "Solicitor Gifts"


Thanks,

DThomas

 

David Tang

Hello Darcene,


Maybe in Line 3, instead of <>, replace it with " not like" or " not (<string> like "*CE") " ?


Just a thought...


:-DTang.

Darcene Thomas

Hi David,


I so wanted this to work!  I even changed the line order and Solicitor Gifts is still missing.

It's probably something really simple.


Thanks :-)

 

Marie Stark

What is your Appeal ID?

David Tang

Hello Darcene,


Have you attempted just the inner condition to see if you pull "Solicitor Gifts"?


IF (IsNull({CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}) or Not ({CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID} like "*CE")) and

    ({CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name} like "*Harrison*") then "Solicitor Gifts"


 

Darcene Thomas

My appeal ID is anything containing "CE" (i.e., 2020 CE, 2021 CE, etc.).

Marie Stark

Maybe something like this?


Line 1:  IF {CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}like "*CE" then "Community Engagement Appeal" else

Line 2 IF  {CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name} like "*Harrison*" then "Solicitor Gifts"

else "Solicitor Gifts"

Darcene Thomas

Good Morning Marie,

That doesn't work either.  Below are the possible combinations.
Appeal - Solicitor

*CE - Harrison

*CE - Other solicitors

*CE - blank

Solicitor - Appeal

Harrison - blank

Harrison - <> *CE

 

Marie Stark

So if the solicitor is not Harrison and the appeal is not like CE,  then what should happen?

Darcene Thomas

They should not list in the report. 

My record selection is:  {@Appeal} in ["Community Engagement Appeal", "Solicitor Gifts"]

James Andrews

Have you tried writing a formula and adding it to your report to see if it's returning the correct results?  You could do it in stages

If (not isnull({appeal}) and {appeal} like *CE* )
    then "Community engagement" 

Run that, are they showing up correctly?  If so, then add another line, something like:

If ((not isnull({appeal})) and not ({appeal} like *CE*)) and
   (not isnull({solicitor}) and solicitor like "*harrison")
   then "solicitor"
else "whatever"

make sense? testing for nulls each step of the way and gradually adding conditions. Also, I know I use a lot of parentheses and sometimes some are unnecessary, but they don't hurt and help me organize things.


Edit: once you get it figured out so your formula shows up correctly for each record, then you can move the formula over and group by it. You can also use a modified version of it as a record selection formula by taking out the if then else and just having the two statements there.

Darcene Thomas

Hi James,


The community engagement formula works great on its own, but when I add the solicitor formula only records for community engagement show up.

The solicitor formula on its own doesn't produce any records.  


The parentheses do make it easier to read.  I will play with the solicitor formula.

Thanks

Joshua Bekerman bCLO bCRE

Try changing your Report Options: Convert Database NULL values to Default and Convert Other NULL Values to Default.  No idea if this will help but mentioning it anyway.  You might also need to test for blank values ("") in addition to NULL.  

James Andrews

Darcene Thomas:


The community engagement formula works great on its own, but when I add the solicitor formula only records for community engagement show up.

The solicitor formula on its own doesn't produce any records.  

 

OK besides testing for blank values as Josh suggested, do you have more than one solicitor on a gift? I see you're looking not at the solicitors field in CnGf, but actually at the solicitor constituent record a couple of tables away in CnGf_1Sol_1Cn. So if you have more than one solicitor on a gift that's going to be a one-to-many relationship and Crystal is just going to look at the first one and move on if it doesn't apply. You could test for this by making a subreport with just that table in it and running that formula through it, taking a  look at the links back to CnGf_1Sol_1 and CnGf_1. Then maybe you could integrate the subreport into your report.


Otherwise -- if that's the case and you have multiple solicitors -- you're looking at working with variables (or custom SQL statements) to get the one you want.

Steven Cianciarulo

Are your joins set up as left outer join? I can never remember the technical bits of why, but doing this has an effect on which records show up.

Darcene Thomas

James, with a minor tweak your formula worked!  


If (not isnull({CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name}) and {CnGf_1Sol_1Cn.CnGf_1Sol_1Cn_Last_Name} like "*Harrison*" )

    then "Solicitor Gifts" else


If (not isnull({CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID}) and {CnGf_1Apls_1.CnGf_1Apls_1_Appeal_ID} like "*CE*" )

    then "Community Engagement Appeal" 

 

Thank you all for your help!

James Andrews

I'm glad it helped! My Crystal formula syntax is rusty because we don't use it as much anymore, but good to hear it worked.