The Let Function and Variables

2017-11-03 09:05:03

The Let function creates a temporary holder for a value, called a variable, which can be plugged into a calculation over and over again. You'll do a little more work upfront to set up a variable, but that effort pays off with faster calculations that are easier to read, edit, and troubleshoot.

11.3.1. Defining Calculation Variables

In your Let function, you define a value and give it a name, and then use that name as often as you need throughout the calculation. In this case, you can calculate the amount due once, and store the result in a variable called Amount Due.

The Let function is unique among functions because it controls the way you write your calculation, not the result. Here's an example:

Let ( [ L = 5 ; W = 10 ; H = 3 ] ; L * W * H )

Like the Substitute function described on Section 10.3.3.4, Let uses bracketed notation. It really takes just two parameters. The first is a list of variable definitions. Each variable gets a name and a value using this format:

Name = Value

Tip: FileMaker uses the terms "var" for Name and "expression" for Value in its manual and help files. The terms mean the same things, but "var" and "expression" sound much more impressive.

If you have more than one variable to define (as in the example above), put a semicolon between each one, and put them all in-between a pair of square brackets. You can use any calculation expression as the value.

In fact, the expression that determines the value of a variable can even use other variables that were defined earlier.

For example, the next calculation is perfectly legal. Its hours variable has a value of 240: 24 times the value of the Days variable:

Let ( [ Days = 10 ; Hours = 24 * Days ; Minutes = 60 * Hours ]; Minutes & " Minutes" )

The second parameter can be any calculation expression. This parameter is special because you can use any of the variables you've defined inside the expression, just like fields. In the first example above, there are three defined variables (L, W, and H); the expression then multiplies them together.

When FileMaker evaluates the Let function, it determines the value of each variable just once, and then plugs this value into the expression every time that variable is used. The result of a Let function is simply the result of its expression.

11.3.1.1. Total Due calculation #3: using Let

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.

11.3.2. The Life of a Variable

Most variables last only as long as it takes FileMaker to work through the calculation, then they're gone. This type of variable is called a local variable because they aren't valid outside the Let function that calls them into existence. But you can also create a special variable, called a global variable, that lives beyond your calculation. Read on to see when to use each type.

11.3.2.1. Local variables

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.

11.3.2.2. Global variables

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.

POWER USERS' CLINIC

Field Formatting Calculations

Now that you understand most of FileMaker's calculation power features, you're ready to see something really neat. Sometimes you have a field that lets you have free entry, but you want to be sure the end result is formatted properly.

The trick is to attach an Auto-Enter calculation to a field, and make the calculation depend on the field itself. Whenever the user changes the field, FileMaker recalculates the auto-enter calculation and updates the field. For example, imagine you want all customer phone numbers entered in this form:

(555) 555-1212

Since Phone Number is a text field, there's nothing keeping the user from typing it this way instead:

555/555/1212

Or like this:

555-555-1212

Or like this:

55 55 55 12 12

To clean up the data when it's entered, just add an auto-enter calculation to the Phone Number field. This calculation does the trick:

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

First the calculation uses the Filter function to remove any non-numeric characters from the entered phone number, and puts the result in a variable called "clean phone." Then, if the clean phone has exactly 10 digits, the calculation breaks it apart according to the format you want. Otherwise it just returns the phone number the way the person entered it.

To make the calculation work properly, be sure you turn off "Do not replace existing value (if any)" in the Field Options dialog box.

Tip: FileMaker's Advanced Data Viewer (Section 19.1.1.4) eliminates guesswork by letting you check the contents of local and global variables.

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.

11.3.3. Nesting Let Functions

As with other functions, you can nest Let functions inside one another. In fact, you can define a variable once, and then redefine it inside a nested Let function. The variable's value changes while inside the nested Let function, then changes back when it ends. By the same token, you can define a variable with the same name as a field, and FileMaker uses the variable's value while inside the Let function.

Here's a very simple example of a Let function inside another Let function:

Let ( X = 3 ; // only X is defined here Let ( Y = 4 ; // X and Y are both defined here X * Y ) // Only X is defined here too )

You can also use Let more than once in a single calculation without nesting:

Let ( [X = 3; Y = 4] ; X * Y ) & Let ( units = "inches"; " " & units )

Категории