formula for records that have either or both

Erica Esson 2

Searching for help with formula that will list donor with {CnSmryGft_1.CnSmryGft_1_Total_Gift_amount} >= $1000.00 or {CnSmryGft_2.CnSmryGft_2_Total_Gift_amount} >=  $1000.00. Looking for records that have either or both



 

Chester Bammel

Hi, it would be pretty much just like you have it listed, but in parenthesis.  In the selection criteria filter include this statement:

({CnSmryGft_1.CnSmryGft_1_Total_Gift_amount} >= $1000.00 OR {CnSmryGft_2.CnSmryGft_2_Total_Gift_amount} >=  $1000.00)



By including it in parenthesis, it treats the whole thing as a single condition.  Even if both are true, it still satisfies the condition.

Hope this helps.  If not, please reply.

 

Erica Esson 2

That's what I had but some donors didn't show on the report.

Chester Bammel

Then it could be a couple of other things.

1.  Maybe one of the other filters is excluding the donors.  Check all other filters and make sure that one of them is not preventing them from showing up.  You can test this by creating another copy of the report that you use for testing only.  Remove all filters EXCEPT for this one.  See if they show up now.  If they do, then it is one of the other filters.

2.  Joins.  If you are using "equal" joins, and if they do not exist in one of the tables you are joining, then they will be excluded from the report.  If this is the case, then you would need to use an outer join (such as a left outer join) on the appropriate table, so even if they do not exist in the second table, they will still be in the report.



I'm betting if it is not the filter, then it is a join.

 

Erica Esson 2

I’m using left outer Join on all tables. Records that are not showing on the report. A B $3,000.00 $0.00 $200.00 $1,500.00 $0.00 $1,500.00

Joshua Bekerman bCLO bCRE

Looks like you might have to test for nulls, at least for the first and last examples you listed.  I'm in the middle of a report and can elaborate later.  Or hopefully this gets you on the right track in the meantime.

Chester Bammel

Hi Erica,

Obviously, we are at a disadvantage here, not having your report and your data in front of us.  However, if I were approaching this issue, I would continue to dissassemble the report.  As mentioned before, I make a copy of the report and rename it reportname.RIP.rpt.  (RIP for Ripped Apart, not Rest in Reace  )  I still believe, assuming your data is good, that it may be a join issue.  You said you had outer joins, but one may be going the wrong direction.  Take your copy and start by removing irrelevant fields, one field at a time.  Rerun the report after each.  Sometimes the most obscure - seemingly meaningless field can be the problem. 

 Also, if I have removed all fields from the report that are on a table, I will remove that table from the report.  Keep doing that one field at a time and eventually, your missing data will appear.   When it appears, you will then know what the offending field or table is.  Then set out to figure out why.  Fix whatever that problem is, whether it be a join or anything else and then apply that same fix to the real report and it should work.



If you can find anything else specific to give me, please send it my way.  I have not worked that much with RE specificaly, but I have worked in Crystal for many years.



Chester

 

John Heizer

Before I give suggestions I want to make sure I'm understanding correctly.  By "formula" you're talking about what shows in the Formula Workshop - Record Selection Forumula Editor window?  If so, which do you have, "Exceptions for Nulls" or "Default Values for Nulls" selected?

Chester Bammel

Hi John.  The reason I did not go down the null path (though I thought of it), is one of her examples did not have zeroes, which could have been from nulls.  That example was: $200.00 $1,500.00