The Let Function

Among FileMaker Pro’s many functions, the Let function stands out as one of, if not the most, powerful among them. This article attempts to demystify the Let function and demonstrate examples of its practical use.

Purpose of the Let function

Sets a variable(s) to the result of an expression for the duration of a calculation, script or in the case of global variables, until the file is closed.

Why use the Let function?

Allows the user to assign the results of an expression to a variable and then return a result all within a single function.

Aids in the reduction or elimination of nested and/or repeated expressions and functions which improves readability and performance.

Format

FileMaker Pro 10’s help defines the format of the Let function as such.

Let({[}var1=expression1{;var2=expression2…]};calculation)

From FileMaker’s help documentation the parameters of the Let function are defined as such:

var – any variable name, local variable name or global variable name.expression – any calculation expression, field or constant.calculation – any calculation expression, field or constant.

Note: In order to make it more readable I have separated the format into multiple lines. This convention is not required but makes it easier to discuss in this article. Also please note that you can include comments using the “//” characters. I should also note that though FileMaker includes the curly braces in their description of the format “{}” they are not required and are present to denote the option of making multiple variable assignments. If you only have a single variable you can omit the brackets. See the code samples below.>

Variable Types

It’s important to remember that Let supports three variable types: calculation, script and global.

calculation scope a – valid for the duration of the Let statement.script scope $a – valid for the duration of the calculation in which it is defined or the script in which the calculation is evaluated.global scope $$a – valid until the file is closed.

The Let statement below illustrates the assignment of each variable type. It is also important to note that even though they all share the name “a” they are NOT the same variable. The absence or inclusion of the “$” or “$$” denote separate variable spaces. So be sure you do not confuse them when you are writing a calculation.

Basic Use

Here are some examples of the Let statement in use.

Here I assign values to variables a = 1 and b = 2. Then I use the defined variable in a calculation expression a + b. The result of the calculation is returned as the result of the expression. If a = 1 and b = 2 then a + b =3. So the value returned from the Let statement is 3.

Let (
[
// assign variables
a = 1;
b = 2;
];
// return the result or use the variable in an expression
a + b
)

For the next example we will assume the following script steps preceded our Let statement.

Set Variable [$a;1]
Set Variable [$b;1]

I am using variables that were assigned in the above script steps. $a and $b are set and then used in the Let statement.

Set Variable [$result;Let (
$c=$a+$b;
// return the result or use the variable in an expression
$c
)
]

Use Let to Avoid Nesting

In this example we try a more real world scenario to demonstrate how Let helps us avoid nesting and repetition. First let’s try parsing some text without using the Let function. In this case, we are trying to get everything after the first sentence using the position of the first period (“.”).

This calculation statement is difficult to read due to the nested Position and Length functions. It might make sense 6 minutes after it’s written but what about when you come back in 6 days, 6 months or even 6 years later. You’ll have to take a close look at it to understand what’s going on.

In this example the statement is longer but is more readable. Each line handles only one function. It can easily be read from top to bottom without the confusion of using nested functions. When you revisit this calculation you won’t be scratching your head trying to remember how it works. You’ll be able to read it.

Use Let to Avoid Repetition

In this example I am creating an expression, part of which I will need to use more than once. Here’s how it works without the aid of the Let statement.

Again, this statement is hard to read due to nesting. Also notice the reuse of the expression “Sample Data::Quantity * Sample Data::Amount” which appears twice and causes the statement to be longer than it needs to be and contributes to the statement’s cluttered appearance. Another problem is that if we need to change the “Sample Data::Quantity * Sample Data::Amount” expression we’ll need to do so in multiple places.

Like the previous example this is much easier to read since each line performs a single function. If you want to change an expression you can do it in one place without having to hunt though the code for duplicate expressions.

Nesting Let Functions

FileMaker Pro will allow you to nest Let statements. However, if you make a calculation scope variable assignment in a nested Let statement it will only be available within the Let statement in which it was defined. If you want to make variable assignments that are available to the outer Let statement then use a script level variable ($var) or a global variable ($$var).

Let (
[
a = 1;
b = Let ([a=0]; a)
];
a
)

In the above example the returned result a is equal to 1 even though the assignment was made in the nested Let. The reason is that the “a” used in the nested Let only has calculation level scope and therefore the assignment is only valid within the nested Let statement.

If we change the a to be a script level variable by the use of a single “$” then the returned result will be different.

Let (
[
$a = 1;
b = Let ([$a=0]; $a)
];
$a
)

The above example now returns $a as 0. This is because the scope of $a is now at the script level. Any assignment made to that variable will be true until the enclosing script has completed.

Dynamic Variable Creation

When combined with the Evaluate function Let can be used to create variables dynamically.

Imagine you have a table that contains a list of things that you need to reference regularly. These items may not change on a regular basis and might be considered constants. For example you might have a list of strings used for localization within a solution used in multiple regions.

In this case you might want to have constants that are defined at launch time that correspond to the users region.

Imagine you have a table called Strings which contains 3 fields: “Name”, “String” and “Region”. Now imagine that upon launch the system could do a find on the Strings table to get all the strings for a given region.

You could then loop over the records and use a Let statement to dynamically create a global variable to store the constant for each record.

After this calculation is run on a given record a variable corresponding to the record’s Name field variable will be created. if the record’s name field contains “Welcome” then the variable name would be “$$Welcome” the contents of the variable would be whatever was in the String field.

Given a record with the following data…

Name: Welcome
String: Welcome to XYZ solution!
Region: United States

Would result in the variable “$$Welcome” with an assigned value of “Welcome to XYZ solution!”. The $$Welcome variable could then be used anywhere within the file in which it was set without any regard to context. That is just one example of using dynamic variables. There are many more things you can do, but I’ll save that for a future article.

So go forth, take your new found knowledge and untangle the mess of repetitious, nested calculations lying around in your FileMaker solutions. You better hurry before you forget what they do.