The logical function
The answer lies in the three conditional functions. Each one lets you specify more than one possible result. The function requires one or more parameterscalled conditions or conditional expressions that tell it which result to pick. The conditional functionsIf, Case, and Choosediffer in how many possible results they support and what kind of condition they expect.
The first and most common conditional function is simply called If. The If function is the basic unit of decision making in FileMaker calculations. It's the ticket when you have to decide between two choices, based on some criteria.
It looks like this:
If ( Condition ; True Result ; False Result )
When you use the If function, FileMaker
If ( First Name = "Dominique" ; "Free" ; "9.00" )
For example, this calculation returns Free if the First Name field matches "Dominique." If it doesn't match, then it returns $299.00 instead.
FREQUENTLY ASKED QUESTION
Matching Text Values
What do you mean by "First Name field matches 'Dominique'?" What
When you use the = operator with text values, FileMaker
"TEXT" = "text"
If this function is too
Exact takes two text parameters, compares them, and returns true if they match exactlyincluding case. This expression has a False result:
Exact ( "TEXT" ; "text" )
If ( Exact ( First Name ; "Dominique" ) ; "Free" ; "9.00" )
This version of the calculation would return "$299.00" if the First Name field contained "dominique," since the case on the letter D doesn't match.
Sometimes you need to pick from more than just two choices. Luckily, If has a cousin named Case that simply excels at such problems. For example, suppose you want to show one of these four messages on the top of your layout:
Go To Bed
You obviously need to choose between these messages based on the time of day. The If function doesn't work very well for this problem because If only allows one condition and two possible results. You can nest If statements one inside the other, so that the false result is really another If statement. But nested If functions are really hard to read and even harder to tweak, so if you find that your business rules require a change in your calculation, you may rue the day you decided to use 12 nested Ifs to decide which discount your customers should get.
The Case function has this form:
Case ( Condition 1 ; Result 1 ; Condition 2 ; Result 2 … )
You can add as many parameters as you want, in pairs, to represent a condition and the result to be returned if that condition is true. Because the conditions and results are sequential, and not nested, you can easily read a Case statement, no matter how many conditions you pile on. You can even add an optional parameter after the last result. This parameter represents the default resultthe one FileMaker uses if none of the conditions were true.
To implement the greeting message described on Section 18.104.22.168, a calculation using the Case function might look like this:
Case ( Get(CurrentTime) > Time(4;0;0) and Get(CurrentTime) < Time(12;0;0); "Good Morning"; Get(CurrentTime) > Time(12;0;0) and Get(CurrentTime) < Time(18;0;0); "Good Afternoon"; Get(CurrentTime) > Time(18;0;0) and Get(CurrentTime) < Time(22;0;0); "Good Evening"; "Go To Bed" )
In this calculation, the Case function checks first to see if the current time is between 4:00 a.m. and 12:00 p.m. If it is, the "Good Morning" value is returned. If not, it then checks whether the time is between 12:00 p.m. and 6:00 p.m., which would produce the "Good Afternoon" message. Finally, it checks to see if it's between 6:00 p.m. and 10:00 p.m. If so, the
You don't need to specify a condition for the last result"Go To Bed"because if all the previous conditions are false, it must be time for bed. In other words, if it isn't the morning, and it isn't the afternoon, and it isn't the evening, then it must be late at night. (If you need further help deciphering the above calculation, see the box on Section 22.214.171.124. On the other hand, if you're so far ahead that you can see a better way to do it, see the box on Section 11.2.2.)
The Choose function is
UP TO SPEED
A Complex Case
The Case function expresses a familiar conceptdo thing #1 in one case, do thing #2 in a different case, and so on. But you might not immediately know how you get from that simple idea to the more complicated calculations shown int his chapter. Here's how it breaks down.
Remember that semicolons separate the parameters you pass to a function. So the first parameter is all of this:
Get(CurrentTime) > Time(4;0;0) and Get(CurrentTime) < Time(12;0;0)
That whole expression forms the first condition. Remember from Chapter 9 that the and operator works on two Boolean values. It returns a true result if the values on each side are both true .
So really, you can split this condition in two. First, this must be true:
Get(CurrentTime) > Time(4;0;0)
If that's true, FileMaker checks to see if this expression is true too:
Get(CurrentTime) < Time(12;0;0)
These sub-expressions are much simpler. Each has the same form, comparing the current time to a time you construct with the Time function. The first makes sure it's after 4:00 a.m. The second makes sure it's before 12:00 p.m. The other two conditions in the calculation are exactly the sameexcept that they look at different times.
It looks like this:
Choose ( Condition ; Result Zero ; Result One ; Result Two … )
Unlike the other conditional functions, Choose doesn't expect a Boolean expression for its condition. Instead, it looks for a
. The number
Imagine you have a Student table, and one of its fields is called GPA. This field holds the student's current grade point average, as a number. You'd like to turn this number into a letter grade on the printed report.
Many FileMaker developers would immediately jump to the Case function to solve this problem. They'd do something like this:
Case ( GPA < 0.5; "F"; GPA < 1.5; "D"; GPA < 2.5; "C"; GPA < 3.5; "B"; "A" )
While this calculation gets the job done, you can do it more succinctly with the Choose function:
Choose ( Round(GPA; 0); "F"; "D"; "C"; "B"; "A" )
When you turn the GPA value into an integer (using Round), it becomes a candidate for the Choose function. When the GPA is 3.2, FileMaker rounds it to three, and selects result number three: "B." (Remember that the first result is for zero , so number three is actually the fourth result parameter. For more detail, see the box on Section 126.96.36.199.)
POWER USERS' CLINIC
Clever Case Conditions
If you're a logic-minded person with a
Case ( Get(CurrentTime) <= Time(4;0;0) or Get(CurrentTime) > Time(22;0;0); "Go To Bed"; Get(CurrentTime) < Time(12;0;0); "Good Morning"; Get(CurrentTime) < Time(18;0;0); "Good Afternoon"; "Good Evening" )
This version takes advantage of the fact that the Case function returns the result associated with the first true condition. FileMaker looks at the first condition, which checks to see if it's before 4:00 a.m. or after 10:00 p.m. If either's true (note the "or" operator), the function returns Go To Bed .
If both aren't true, FileMaker moves on to the second condition, which asks if it's earlier than 12:00 p.m. If so, it returns Good Morning . (What if it's three in the morning? That is earlier than 12:00 p.m., but you don't see "Good Morning" because FileMaker never gets this far. If it's 3:00 a.m., the search for truth stops after the first condition.)
If it still hasn't found a true condition, FileMaker moves on to the
If you're comfortable with this kind of logic, you can save yourself some clicks and a little typing. (Technically you also make a more efficient calculation, but unless you're using the abacus version of FileMaker, that doesn't matter much.)
Many people, on the other hand, find a calculation like this one utterly confusing. In that case, just use the longer version and find something else in your life to brag about.
Now that you've seen the three conditional functions, it's time to take a stab at that calculation way back from the beginning of this chapter: Make the Total Due turn red when the due date has passed.
When you're trying to come up with a logical calculation, think about what information FileMaker needs to make the decision, and what action you want FileMaker to take after it decides. Then consider how best to do that using your database's existing fields and structure. Your first decision is which conditional function to use.
FREQUENTLY ASKED QUESTION
The Choose function insists that the first parameter should be for a zero condition. What if I don't want zero? My condition values start with 1 .
This is a common question. Luckily you don't really have a problem at all. There are two equally easy ways to get what you want from Choose. Perhaps the most obvious is to simply add a
Choose ( Door ; "" ; "European Vacation" ; "New Car" ; "[Wah Wah Wah]" )
In this calculation, there is no Door number zero, so you just stick "" in the spot where the zero result belongs. You could just as well put "Henry Kissinger" there for all you care, since it never gets
If you just don't like having that dummy result in your calculation, you can take this approach instead:
Choose ( Door 1 ; "European Vacation" ; "New Car" ; "[Wah Wah Wah]" )
This version simply subtracts one from the Door number. Now Door number one gets the zero result, and Door number two gets the one result. This approach becomes more appealing when your choices begin with an even higher number:
Choose ( Year 2000 ; "Dragon" ; "Snake" ; "Horse" ; "Sheep" )
Since this calculation uses the year as the condition, it would be a real drag to enter 2000 dummy values. Instead, you just subtract enough to get your sequence to start with zero.
Most people's first thought would be the If function, since the calculation needs to check if one condition is true:
Is the value of the Date Due field earlier than today's date?
The calculation then takes the result of the If function and returns one of two possible results:
If it's true that the due date has passed, display the total due in red text.
If it's not true that the due date has passed, display the total due in black text.
In plainer English, the If condition checks to see if the due date is passed. If so, it returns a red result; if not, it returns a black result. In addition, the calculation needs to find the value to display in black or redthe total due. The full calculation might look like the following:
If ( // Condition Get(CurrentDate) > Date Due and // Calculate the total due here to make sure it's not zero Invoice Amount > Total Paid; // True Result TextColor ( // Calculate the total due here for the red result Invoice Amount Total Paid; RGB(255;0;0) ); // False Result // Calculate the black total due here Invoice Amount Total Paid )
To put this calculation to work in your database, delete the calculation currently in the field definition for Total Due and type in this one. When the due date is passed, the value in your newly
Lots of people like the Case function so much that they always use it, even in places where the If function is perfectly competent. You might choose to use Case if there's any chance you'll want to add some conditions to the statement later on. Instead of editing an If expression later, you can save time by using Case from the start (just in…case).
The same calculation using Case (and minus the helpful comments above) would look like this:
Case ( Get ( CurrentDate ) > Date Due and Invoice Amount > Total Paid ; TextColor ( Invoice AmountTotal Paid ; RGB ( 255 ; 0 ; 0 ) ) ; Invoice AmountTotal Paid )
This calculation works as advertised, but it has a couple of weak points. First, it has to calculate the total amount due
. That makes for three times as many
Second, Total Paid is an unstored calculation based on the sum of
In the next section, you'll learn how FileMaker helps you write leaner calculations that are easier for you to readand quicker for FileMaker to work through.