Logical Functions

The logical function group is a diverse and powerful lot. You get functions for making decisions (called conditional functions), learning about field values, and even evaluating calculations inside other calculations. This section covers all those possibilities. Along the way, you learn how to define and use variables, which act as placeholders while complex calculations go through their many steps.

11.2.1. Conditional Functions

This chapter began by posing an interesting challenge: You have a calculation field in the Invoices table called Total Due. It calculates the total amount due on an invoice by subtracting the sum of all payments from the total amount of the invoice. Can you modify the Total Due calculation to turn red when an invoice is past due?

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 expressionsthat 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.

11.2.1.1. The If function

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 evaluates the condition looking for a Boolean result (true or false). If the condition has a true value, the function returns its second parameter (true result). If the condition is false, though, it returns the False Result instead. Here's an example:

If ( First Name = "Dominique" ; "Free" ; "$299.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.

11.2.1.2. The Case function

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:

• Good Morning
• Good Afternoon
• Good Evening
• 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.

Note: Since the Case function accepts several conditions, it's entirely possible that more than one condition is true at the same time. If so, FileMaker chooses the first true condition when it picks a result.

To implement the greeting message described on Section 11.2.1.2, 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 user sees "Good Evening."

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 11.3.2.2. 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.)

11.2.1.3. The Choose function

The Choose function is sort of the forgotten third member of the conditional trio. People don't immediately grasp how to use it…so they don't. But if you think of it as a value list with the choices coded into a calculation, you see how Choose can turn an awfully ugly Case function into a specimen of neatness.

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 number. The number tells it which of the results to choose: If Condition is zero, the function returns Result Zero; if it's one, it returns Result One; and so on.

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 11.2.2.1.)

Note: This calculation uses the Round function, which you haven't seen before. Round takes two numbers as parameters. It rounds the first value to the number of decimal places specified in the second parameter.

11.2.2. Constructing a Conditional Calculation

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.

11.2.2.1. Total Due calculation #1: using the If function

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 smarter Total Due field changes to red.

Note: Since the Total Due field already calculates the due balance, you may be tempted to take a shortcut: Create a new field that uses the If function to change the existing Total Due field to red, then plop that new field on the Invoices layout instead. But that would clutter your database with a superfluous field. And in a relational database especially, that kind of sloppy field swapping is only asking for trouble.

11.2.2.2. Total Due calculation #2: using the Case function

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 )

Tip: With a single condition and default result, the syntax for If and Case are the same. So if you do need to change an If statement to Case later, simply change the word "If" to "Case" and add the conditions.

This calculation works as advertised, but it has a couple of weak points. First, it has to calculate the total amount due three times. That makes for three times as many chances to introduce typos and three times as many places to edit the "Invoice AmountTotal Paid" expression if you change the calculation later.

Second, Total Paid is an unstored calculation based on the sum of related records. That's one of the slowest things you can ask a calculation to do. It may not matter much in this example, but in a more complicated situation, a calculation like this could slow FileMaker to a crawl.

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.