Take a look at the example in Figure 11-2 to see how FileMaker knows when to recalculate fields. It’s very common for calculation fields to use other calculation fields in complex arrangements, as this hierarchy of field dependencies illustrates.

First Name and Last Name are the only editable fields in Figure 11-2. When someone edits one of these fields, FileMaker looks at the field dependencies to see if it needs to do any extra work. The program sees that it needs to recalculate Full Name and then store that Full Name value, which in turn triggers more work. FileMaker must recalculate and store the Full Address value as well. In a sense, that one change trickles down through all the dependent fields as FileMaker calculates and stores, calculates and stores. All this recalculating happens as soon as the user exits the First Name field after making the change.

Figure 11-2. This picture shows a series of interdependent fields. The Full Name field is a calculation field that uses First Name and Last Name. The Full Address field uses Full Name (and presumably some others). The fields in gray are unstored calculation fields. Collection Letter uses Account Balance and Full Address. Account Balance in turn uses Balance Due. Since an account can have several invoices, each with a balance due, Account Balance actually uses several balance due values—one from each related invoice.

By contrast, since Collection Letter is an unstored field, FileMaker doesn’t recalculate it right away. (If it did, it would have no place to store the result.) Instead, the program waits until someone brings up the field onscreen, then it runs the calculation on the current data and displays the result.

Again, more things change. To calculate the Collection Letter value, FileMaker grabs the stored value for the Full Address field. But it also needs the Account Balance, which is not stored. As such, it has to first calculate that field. Doing so requires calculating the Balance Due on each invoice in turn, then adding them up to get an Account Balance. Finally, it has the values it needs to show you the Collection Letter. Thus, when you use unstored calculations, FileMaker pulls the data it needs down through the hierarchy of dependencies on an as-needed basis.

When you first create a calculation field, FileMaker makes it a stored field automatically, if possible. Some field values aren’t eligible for storage. These are the situations when a calculation field must be unstored:

If your calculation meets any of these criteria, FileMaker automatically turns on the “Do not store calculation results–recalculate when needed” option on for you, and it doesn’t let you turn it off. Otherwise, FileMaker automatically stores the field.

Even if FileMaker can store a certain value, you might not always want it to. Here are some reasons you might turn on that “Do not store” box:

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.

	"TEXT" = "text"

	Exact ( "TEXT" ; "text" )

	If ( Exact ( First Name ; "Dominique" ) ;
	"Free" ; "$299.00" )

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:

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 result—the one FileMaker uses if none of the conditions were true.

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

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.

	Get(CurrentTime) > Time(4;0;0) and
	Get(CurrentTime) < Time(12;0;0)

	Get(CurrentTime) > Time(4;0;0)

	Get(CurrentTime) < Time(12;0;0)

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

	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"
	)

Most people’s first thought would be the If function, since the calculation needs to check if one condition is true:

The calculation then takes the result of the If function and returns one of two possible results:

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 red—the 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.

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 Amount–Total Paid ; RGB ( 255 ; 0 ; 0 ) ) ;

	Invoice Amount–Total Paid

	)

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 Amount–Total 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 read—and quicker for FileMaker to work through.

Your Total Due calculation can use the Let function to solve all its problems. Just put the Amount Due in a variable and use it throughout the calculation:

	Let ( Amount Due = Invoice Amount Total Paid ;
       If ( Get(CurrentDate) > Date Due and Amount Due > 0;
          TextColor ( Amount Due ; RGB(255;0;0) );
		  Amount Due
       )
	)

This version of the calculation is simpler, easier to change, and more efficient. You can’t beat that.

The variables you’ve written so far have all been local variables. Now it’s time to learn that local variables having shockingly short memories.

Local variables can lose their values even before a calculation is finished. If you write:

	Let ( Amount Due = Invoice Amount Total Paid;
       If (
          Get ( CurrentDate ) > Date Due and Amount Due > 0;
		  TextColor ( Amount Due ; RGB ( 255 ; 0 ; 0 ) );
		  Amount Due
		  )
	) & If ( Amount Due < 0 ; "CR" ; "" )

The calculation tries to use the Amount Due variable after the end parenthesis in the Let function. Anything that happens after that in the calculation is outside the Let function’s scope, so when you try to close the Specify Calculation dialog box on this calculation, FileMaker complains that it doesn’t know what that last Amount Due is supposed to be. One way to rewrite that calculation using a local variable is:

	Let ( Amount Due = Invoice Amount Total Paid;
       Case (
          Get ( CurrentDate ) > Date Due and Amount Due > 0;
		  TextColor ( Amount Due ; RGB ( 255 ; 0 ; 0 ) ;
		  Amount Due < 0 ; "CR" ; ""
		  )
	)

In this example, you’re including the last test condition within the scope of the Let function and you’ve switched to a Case function, so that you don’t have to read a set of nested If functions.

If you want the local variables you set inside calculations to follow the same naming conventions as variables you set in scripts (see Chapter 15), prefix their names with “$.” In that case, you’d write the calculation you just saw like this:

	Let ( $Amount Due = Invoice Amount Total Paid;
       Case (
          Get ( CurrentDate ) > Date Due and $Amount Due > 0;
		  TextColor ( $Amount Due ; RGB ( 255 ; 0 ; 0 ) ;
		  $Amount Due < 0 ; "CR" ; ""
		  )
	)

Notice that you have to include the prefix in the Let function and in the formula that follows it.

But just as FileMaker gives you global fields, it also gives you global variables. Unlike local variables, global variables hold their results after the Let function is finished. To create a global variable, add a “$$” prefix to its name. Here’s the same calculation rewritten with a global variable:

	Let ( $$Amount Due = Invoice Amount Total Paid;
       Case (
          Get ( CurrentDate ) > Date Due and $$Amount Due > 0;
		  TextColor ( $$Amount Due ; RGB ( 255 ; 0 ; 0 ) ;
		  $$Amount Due < 0 ; "CR" ; ""
		  )
	)

The only difference you can see in the calculation is the $$ prefix. But the practical difference is vast: Global variable values remain until you change them (through another calculation or through a script), or until you close the file.

	Let(
       clean phone = Filter(Phone Number;
    "0123456789");
       Case(
          Length(clean phone) = 10;

		  "(" & Left(clean phone; 3) & ") " &
		  Middle(clean phone; 4; 3) & "-" &
		  Right(clean phone; 4);

		  Phone Number
       )
	)

You could run a script that checks to see if a Payment was made within 10 days of the Invoice Date and if it was, apply a 1% discount to the $$Amount Due field. Sure, you can do something similar with a straightforward calculation field, but in that case, it gets a little trickier to apply the discount to some of the records, but not to others. With a script, you can find the records you want to give a spur-of-the-moment discount, run the script on that found set, and you’re done.