When creating a calculation in FileMaker, things can get quite complicated. You may need to use a range of sub-expressions with nested FileMaker functions. The resulting calculation expression may work but it could be hard to understand and even harder to troubleshoot.

Enter the Let function. This is not so much a function as a way to set out the logic and structure of a calculation. It can lead to easily understood calculation expressions with clear logic. 

What is Let?

The syntax of the Let function appears difficult at first. The Claris Help topic shows the format as:

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

So let’s simplify. The curly braces denote parts that are optional. Without the optional parts, the minimum format becomes:

  Let ( var1 = expression1 ; calculation )

var1 is any variable name. Most commonly this is a calculation variable (as mentioned in a previous article). This can be written as a plain word. 

expression1 is any calculation expression, field, or constant. This is used to set a value for the variable. 

calculation is the result of the Let function. This is what gets returned. 

Can we use this in an example?

One variable

With just one variable, we can imagine a calculation such as:

  Let ( degreesF = reading::degreesC * 1.8 + 32 ; degreesF )

The degreesF variable is set with the calculation using the value from the degreesC field multiplied by 1.8 and adding 32. The resulting ‘calculation’ is simply the variable which now contains the degrees Fahrenheit result. 

This is a very simple, somewhat inane, example. You could easily have written the calculation as:

  reading::degreesC * 1.8 + 32

With the Let statement, there is some explanation included about what is being calculated – degreesF is calculated using degreesC. But to be honest, this is not a great use case for Let. The Let function comes into its own when there are more variables. 

Multiple variables

To use multiple variables, we need to include the optional parts of the Let format. Look at the following:

  Let ( [ first    =  person::firstName ;
          last     =  person::lastName  ;
          initial  =  Left ( first; 1 ) ;
          rollname =  last & ", " & initial & "."
  ];
          rollname
  )

If the person::firstName field contained Jeremiah and the person::lastName field contained Bullfrog, this calculation would return:

    Bullfrog, J.

The Let function has been written on separate lines and spaced out for ease of reading. There are two parts to the function inside the brackets. Think of it like this:

    Let ( [ variables ] ; calculation )

All the variable definitions are grouped as an array inside square brackets. With multiple variables, each “variable = expression” is separated by a semi-colon. The variables are first, last, initial and rollname. The calculation is separated from the variable array (after the ] ) by another semi-colon. 

An important feature of the Let format is that once a variable has been defined, it can be used in a subsequent variable expression. You will see that the first variable has been used in the calculation of the initial variable; and the last and initial variables have been used for the rollname. 

Another feature of the use of Let is to document the calculation. We could have written this calculation as:

 person::lastName & ", " & Left ( person::firstName ; 1 ) & "."

It may be simple enough to understand by reading it. However, the Let function variables make it very clear what is being done with the data, step by step:

1. The fields are simplified to single words
2. An initial is created from the first name
3. The roll name is a combination of last name, comma, space, initial and period
4. The result is clear about what has been calculated – the roll name

More complex name calculation 

Let’s take a name calculation one step further with more names and more rules. This time, we want to calculate a person’s initialised full name using their first, last and middle names. The first and middle names will be initials and the last name will be in full. So three examples might be:

Meryl Louise Streep = M. L. Streep
Taylor Alison Swift = T. A. Swift
Abraham Lincoln     = A. Lincoln (no middle name)
Joanne Rowling      = J. Rowling (no middle name*)
Winston Leonard Spencer Churchill = W. L. S. Churchill 

* Some trivia for you – J.K. Rowling has no middle name – she got the K for her pen name from her grandmother Katherine. 

So you can see two complexities here:

• people with no middle name
• people with more than one middle name (assumed all in the same field)

Start simple and build

So we will start with a simple calculation and build in extra parts later:

  Let ( [ first    =  person::firstName ;
          middle   =  person::middleNames ;
          last     =  person::lastName ;
          namelist =  List ( first; middle; last );
          fullname =  Substitute ( namelist; ¶; ". " )
  ];
          fullname
  )

This will take the first, middle and last names and string them together with a period and space. For example, Meryl. Louise. Streep or Abraham. Lincoln would be returned.

There is an interesting technique that first creates a return-separated list of names (namelist) and then substitutes the returns (¶) with a period and a space (fullname). The advantage here is that when there is no middle name, there is no item in the list, and therefore no middle name or extra period and space in fullname. You could do this in one calculation by nesting functions, but the Let variables allow us to do it in two steps for greater clarity. 

Now we need to initialise the first and middle names. We make some changes (highlighted in bold):

  Let ( [ first    =  person::firstName ;
          middle   =  person::middleNames ;
          last     =  person::lastName ;
          firstinitial = Left ( first; 1 ) ;
          middleinitial = Left ( middle; 1 ) ;
          namelist =  List ( firstinitial; middleinitial; last ) ;
          fullname =  Substitute ( namelist; ¶; ". " )
  ];
          fullname
  )

We have added two variables and used them in namelist. Note that the order of the variables is only important if you subsequently want to use a variable again. In the above example, first, middle and last could be in any order. But first must be before firstinitial and firstinitial must be before namelist. 

With this update, M. L. Streep and A. Lincoln would be returned.

So we are almost there. One case that is not accounted for is that of multiple middle names as in Winston Leonard Spencer Churchill. Currently, this would only return W. L. Churchill. It is assumed that Leonard Spencer is entered in the middleNames field. 

Here is a suggested expansion to account for up to three middle names:

  Let ( [ first    =  person::firstName ;
          middle   =  person::middleNames ;
          last     =  person::lastName ;
          firstinitial = Left ( first; 1 ) ;
          middle1  =  MiddleWords ( middle; 1; 1 ) ;
          middle2  =  MiddleWords ( middle; 2; 1 ) ;
          middle3  =  MiddleWords ( middle; 3; 1 ) ;
          middleinitialslist = 
            List ( Left(middle1;1); Left(middle2;1); Left(middle3;1) ) ;
          middleinitials = Substitute ( middleinitialslist; ¶; ". " ) ;
          namelist  = List ( firstinitial; middleinitials; last ) ;
          fullname  = Substitute ( namelist; ¶; ". " )
  ];
          fullname
  )

This will separate out the first three middle names from the field. Then middleinitialslist takes all the initials and makes them into a list. The Substitute technique is used as for namelist to make middleinititals. Then it is put together as before. 

Does it work?

When creating calculations, it is very important to test them. The above final Let calculation works for all five names listed at the start.

However, you will find it can break. For example, if someone has a double-barrelled middle name such as Mary-Louise, FileMaker will see two words and initialise them both. This may be OK, maybe not. 

Scope matters

In a previous article, we discussed the scope of variables – where and when they are available for use. The variables we have been using here in Let functions are known as calculation variables. Their scope is strictly within the Let function. This means that they are only available while the Let function is being evaluated. Once the evaluation is complete and a result is returned, all calculation variables cease to exist. 

While evaluating a Let function, it is possible to use script and global variables when they are available (in the same way as using field data). For example, if a Let function is used in an If statement in a script, you can call on any script variables already defined in that script. You can even update variable data in the calculation – be very careful doing this as it can create obscure troubleshooting issues. 

Conclusions

This article shows the power of the Let function to set out the logic and structure of a calculation expression. It allows for clear expression and explanation of the workings of a calculation. While you do not need to use Let for every calculation, you will be surprised how often it is useful now you know how to use it. 

Another subtle advantage of Let is that it can be more efficient. In the last example, the data from the middleName field is called once (into a variable). If you were to write this calculation out in full, you would be calling that same field multiple times. 

What to do next:

• Create a simple Let calculation in your own database. 
• Try rewriting one of your more complex calculations using Let.
• Update the Let calculation above to account for double-barrelled first and middle names e.g. Mary-Louise initialises to M. rather than M. L.
• Search around in published FileMaker blogs for more examples of Let. 
• If there are functions used in the examples that you have not seen before, look them up in the Claris Help Functions reference and educate yourself about their use.