Hi Everyone,

I’m having some difficulty with a calculation. I’m keeping track of donations by individuals and each individual has a status. I’m looking for a group of individuals who donated $500+ in 2015, and also donated $500+ in any other past year. Their status should read “Donated in 2015, has donated in the past”.

This is the calculation I’m using but it doesn’t seem to work. It’s part of a larger case, and the case has been working perfectly so far. This is my first glitch.

Case (

Donation 2015 ≥ 500

and

Donation 2014 ≥ 500

or

Donation 2013 ≥ 500

or

Donation 2012 ≥ 500

or

Donation 2011 ≥ 500

or

Donation 2010 ≥ 500

or

Donation 2009 ≥ 500

or

Donation 2008 ≥ 500

or

Donation 2007 ≥ 500 ;

"Donated in 2015, has donated in the past" ;

and then the case continues on from there. It’s giving the “Donated in 2015, has donated in the past” status to people who haven’t donated in 2015. I just can’t quite wrap my mind around what I've done incorrectly.

I’m pretty new to Filemaker, so any help would be very much appreciated!

Thanks!!

-Rachael

AND binds more closely than OR (has a higher precedence), so your calculation actually is evaluated like this:

( Donation 2015 ≥ 500

AND

Donation 2014 ≥ 500 )

or

Donation 2013 ≥ 500 or

Donation 2012 ≥ 500 or

Donation 2011 ≥ 500 or

Donation 2010 ≥ 500 or

Donation 2009 ≥ 500 or

Donation 2008 ≥ 500 or

Donation 2007 ≥ 500

You need to override the default precedence using parentheses:

Donation 2015 ≥ 500

AND

(

Donation 2014 ≥ 500 or

Donation 2013 ≥ 500 or

Donation 2012 ≥ 500 or

Donation 2011 ≥ 500 or

Donation 2010 ≥ 500 or

Donation 2009 ≥ 500 or

Donation 2008 ≥ 500 or

Donation 2007 ≥ 500

)

If this part of something larger, I suggest you get familiar with the Let() function (unless you're already using it); then you can do stuff like this:

Let ( [

theAmount = 500 ;

donatedIn2015 = Donation 2015 ≥ theAmount ;

donatedBefore =

Donation 2014 ≥ theAmount or

Donation 2013 ≥ theAmount or

Donation 2012 ≥ theAmount or

Donation 2011 ≥ theAmount or

Donation 2010 ≥ theAmount or

Donation 2009 ≥ theAmount or

Donation 2008 ≥ theAmount or

Donation 2007 ≥ theAmount ;

qualifiedDonor = donatedIn2015 AND donatedBefore ;

{

optionally calculate more variables ]] ;

use variables here, e.g. qualifiedDonor)One advantage is the improved legibility; another thing is that you could modify the qualifying amount simply in one place to change the calculation.

Finally, note that if you'd store the annual amounts in a related table, you could calculate the qualification for each record (as a Boolean value 1 or 0), then do:

Sum ( annualDonationsWithoutCurrentYear::amountQualifies) > 0

without ever changing the calculation (which you will have to do annually with your existing structure).