Exluding records from other Formula results

Erin Hunt

I am using formulas to get summary results. So far I have got the results I want but there are a few more summaries that the record could have the criteria to match two formulas.

Is there a way to easily exclude a record if it is already included in a formula?

Joshua Bekerman bCLO bCRE

Erin Hunt:

I am using formulas to get summary results. So far I have got the results I want but there are a few more summaries that the record could have the criteria to match two formulas.

Is there a way to easily exclude a record if it is already included in a formula?

 Could you provide some more details?  What fields are you summarizing, what formula are you using, etc.?

Thanks.

Erin Hunt

Josh Bekerman:

 Could you provide some more details?  What fields are you summarizing, what formula are you using, etc.?

Thanks.

Hi Josh,

Here is a sample of a formula I am using

if {NUCLEAR_.Constituency Code} = "Estate" then {NUCLEAR_.Fund Split Amount} else 0

 I am then inserting a summary of this into the footer.

Here is another formula where I would want to exclude any results that are already in the "Estate" formula as well as a few more formula's.

if {NUCLEAR_.Appeal Description} like (["golf 2014*","gala 2014*","phys*","Third Party Fundraising/Special Events", "Vendor Revenue","lgh craft*","PR_CT*","*FS0913", "*RM0813","Film Revenue"])

and {NUCLEAR_.Tribute Gift} = False then {NUCLEAR_.Fund Split Amount}  else 0

I am not very proficient with Crystal Reports so this may be a really bad way to do what I am wanting the report to do in the first place.

 Thanks.

Joshua Bekerman bCLO bCRE

Erin Hunt:

Hi Josh,

Here is a sample of a formula I am using

if {NUCLEAR_.Constituency Code} = "Estate" then {NUCLEAR_.Fund Split Amount} else 0

 I am then inserting a summary of this into the footer.

Here is another formula where I would want to exclude any results that are already in the "Estate" formula as well as a few more formula's.

if {NUCLEAR_.Appeal Description} like (["golf 2014*","gala 2014*","phys*","Third Party Fundraising/Special Events", "Vendor Revenue","lgh craft*","PR_CT*","*FS0913", "*RM0813","Film Revenue"])

and {NUCLEAR_.Tribute Gift} = False then {NUCLEAR_.Fund Split Amount}  else 0

I am not very proficient with Crystal Reports so this may be a really bad way to do what I am wanting the report to do in the first place.

 Thanks.

Exclude estates from your second formula, something like this:

if {NUCLEAR_.Constituency Code} <> "Estate"

AND

{NUCLEAR_.Appeal Description} like (["golf 2014*","gala 2014*","phys*","Third Party Fundraising/Special Events", "Vendor Revenue","lgh craft*","PR_CT*","*FS0913", "*RM0813","Film Revenue"])

AND

{NUCLEAR_.Tribute Gift} = False then {NUCLEAR_.Fund Split Amount}  else 0

You might also have to check for null Constituency Codes (depending on your RE field settings) and hopefully someone else will chime in on the proper way to do that (James? Patrick?).

 

James Andrews

Josh Bekerman:

Exclude estates from your second formula, something like this:

if {NUCLEAR_.Constituency Code} <> "Estate"

AND

{NUCLEAR_.Appeal Description} like (["golf 2014*","gala 2014*","phys*","Third Party Fundraising/Special Events", "Vendor Revenue","lgh craft*","PR_CT*","*FS0913", "*RM0813","Film Revenue"])

AND

{NUCLEAR_.Tribute Gift} = False then {NUCLEAR_.Fund Split Amount}  else 0

You might also have to check for null Constituency Codes (depending on your RE field settings) and hopefully someone else will chime in on the proper way to do that (James? Patrick?).

 

Agreed - the only way I can see to do this is to basically build your first formula and then start including a negative version of it in every subsequent formula. And do that again with your second formula, and all others.

And yes, test for nulls if you're looking at fields that potentially have null values. For an example of why that's important and how to do it, take a look at this thread.

Erin Hunt

James Andrews:
Agreed - the only way I can see to do this is to basically build your first formula and then start including a negative version of it in every subsequent formula. And do that again with your second formula, and all others.

And yes, test for nulls if you're looking at fields that potentially have null values. For an example of why that's important and how to do it, take a look at this thread.

 Thanks for the replies, I was hoping I didn't need to put negative conditions in all the formulas to exclude things but I guess I have more work ahead.

 

James Andrews

Erin Hunt:

 Thanks for the replies, I was hoping I didn't need to put negative conditions in all the formulas to exclude things but I guess I have more work ahead.

 

It might be easier than it would appear at first. Note that this might work and might not, but it might just be a matter of copying your earlier formulas and adding a 'not' to them.

So if you have the first formula as

IF (not (isnull ({conscode})) AND {conscode}= 'estate')<br><br/>THEN {giftamt}

you could theoretically copy that into the next formula and negate it with NOT, like this:

IF {appeal} IN [list of appeals]<br><br/>AND NOT (not (isnull ({conscode})) and {conscode}='estate')<br><br/>THEN {giftamt}

and continue doing that with each piece:

IF {tribute gift} = true<br><br/>AND NOT (not (isnull ({conscode})) and {conscode}='estate')<br><br/>AND NOT ({appeal} in [list of appeals])<br><br/>THEN {giftamt}

That might move things along rather than rewriting the criteria in the negative.