A READER ASKS: struggling with FileMaker "Case" and "If" calculations

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts


I have been struggling with "Case" and "if" calculations.

I am at a business where we schedule 200 people. We want our schedule to
show the breaks that the people will need to take.

I am trying to do something like the following (where "Shift Hours" is
already a successful calculation itself)...

If Shift Hours is less than 4 return 0.

If Shift Hours is greater than or equal to 4 AND less than or equal to 6
return 15.

If Shift Hours is greater than 6 AND less than or equal to 8.5 return 15,30.

If Shift Hours is none of the above, return ?

One thing that might help is that a CASE function will evaluate each case step from the top going down and the first condition it comes across that has a TRUE result, will return the associated matching value. In your description, you are going from the bottom to the top (meaning you have to look for a minimum and maximum range). If you flip it, going from top to bottom, you only need to have greater than comparisons.

In in top down method, you simply have to say ...
are you at least this ,
or are you at least this,
or are you at least this,
ok then you are that obviously this.

So going from top down, the calculation could look something like ...

shift hours > 8.5, "greatest value", (which is greater than 8.5)
shift hours > 6, "next greatest value", (which is between 6 and 8.5)
shift hours > 4, "next greatest value", (which is between 4 and 6)
shift hours > 0, "next greatest value", (which is between 0 and 4)

By using this method, your upper limit is automatically handled because your case statement would never get to that line, if that is TRUE.

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com