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.

FREQUENTLY ASKED QUESTION
Matching Text Values

What do you mean by "First Name field matches 'Dominique'?" What constitutes a match?

When you use the = operator with text values, FileMaker compares the two values on each side letter-by-letter. If every letter, number, space, punctuation and so on matches, you get a True result. But the comparison isn't case sensitive. In other words, this expression has a True result:

	"TEXT" = "text"

 

If this function is too forgiving for your needs, you can use the Exact function instead.

Exact takes two text parameters, compares them, and returns true if they match exactlyincluding case. This expression has a False result:

	Exact ( "TEXT" ; "text" )

 

It's perfectly legal to use the Exact function (or any other function, field, or expression) as the first parameter of the If function, like this:

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

 

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.

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

---

POWER USERS' CLINIC
Clever Case Conditions

If you're a logic-minded person with a healthy dose of schizophrenia, you might be jumping up and down in your chair right now, waving your hand in the air. What you're dying to say is that you "can make that Case function simpler!" Well, you're probably right. In fact, this calculation does the same job:

	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 next: Is it before 6:00 p.m.? Again, the structure of the case statement implies that it must be after noon at this point since any time before noon would've been caught in the previous conditions. So this condition is really looking for a time between noon and six, even though it doesn't say so in so many words.

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.

 

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.

FREQUENTLY ASKED QUESTION
No Zero

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 dummy zero result:

	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 chosen anyway. Just make sure you put something in there, so your first real result is in the number-one spot.

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.

 

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.