In this series, “Learn FileMaker Like A Boss“, we’re exploring those techniques, mindsets, and tools that make us a better developer. I’m starting the series with topics based on my own discoveries during my FileMaker formative years. In this post, we will take a look at the FileMaker Let function.
I consider the day I started using the FileMaker Let function a huge milestone in my development. Its use opened new development doors for me and made me a better developer. I produced cleaner code: better organized and easier to read. Though a small, innocent-looking function, Let is a powerful technique for developers. So let’s discuss it.
FileMaker Let: A Multi-Purpose Function
The Let function has a lot of uses. It allows you to:
- Store a value into a variable (be it a function, a field’s value, or something hard-coded in)
- Break complex calculations into smaller steps
- Assign multiple variables to values for this function, for the entire script, or for the file in general.
- Return the result of a calculation using the variables declared inside.
These purposes give us a chance to make code that is cleaner and allows us to debug a complex calculation easier.
Let’s Start at the Very Beginning
Here’s an example of a Let statement.
The top of the function, in between the square brackets [] is the declaration area, and the section after is the calculation area. Whatever is declared and calculated in the declaration area can be in turn calculated and returned. In this example, I’m declaring the variables _a and _b and assigning them to 1 and 2 respectively. In the calculated area I’m adding these two together, and my result is returned.
The Syntax
The syntax is important and there are some rules around it:
- You don’t need the [] unless you plan on assigning multiple variables. I always place the brackets in the statement to be consistent. I’ve a shortcut key to which I’ve assigned this structure, and the brackets are always there.
- The last variable declared in the declaration area cannot have a semi-colon after it. Notice in the picture, the variable _end has no semi colon. This actually kept tripping me up until I decided to always end my declaration variable with an _end variable.
- Something needs to be in the calculation area, even if it is “” or a 1.
Let functions can be used anywhere there is a calculation dialog. And they are useful everywhere. They can be simple, like shown above, or very complex.
In fact, the more complex a calculation needs to be, the more useful a let function will prove in two ways:
- A variable can be a smaller part of a calculation
- A variable can get the value from a field or function that is meant used many times throughout.
Consider this example
A company has an invoice system. It contains three tables (for simplicity sake): Parts, Invoices and Invoice line items. As invoice lines are entered, some parts may have a discount on them. So that needs to get applied to the total cost.
But sometimes a part has an extra sale going on. The business logic says we should apply the larger of the two discounts to the total cost.
Here are the fields:
Without a let statement, the calculation to determine the total cost would be something like:
It’s a mess and hard to read. It produces the correct result ($37.50), but is worthless to my future self. I don’t know what’s happening here. Note. There is another way to do this calculation. For this post, I chose an extreme calculation.
Here’s the same calculation using the FileMaker Let function:
Nicer looking, huh?
Let’s look at the individual parts of the function.
In the first part of the Let declaration area, I’m creating some variables and assigning them values from the current record and will use these throughout the rest of the statement. I’m doing this simply because “_q” is shorter and easier to read than “InvoiceLineItems::Quantity”. As I put together the calculation, I can use “_q” anywhere the value from that field is needed. I have 5 variables that each have a value from a field.
The next part of the Let declaration area performs simple math using the variables above. In this example, I am first finding _discToUse: which discount is more will be used. That’s simple. Then I find the total cost sans any discount in _totalCost. Next, I’m figuring out the discount’s multiplier. Finally I’m finding the _result.
Notice each step here is a simple calculation, and the next one relies on some variables above. This makes the let statement invaluable. Since each calculation part is in a variable, I can view each part (or many parts) by returning those variables when using the data viewer.
All the work is done in the cascading variables, and so it leaves only one simple calculation to perform, the Round (), in the calculated area.
This is much more simple to read. Sure it’s longer, but it is easy to read. And that’s a great help to my future self.
Debugging
Each value and small calculation is stored in a variable. As I debug it in the Data Viewer, I can return one or all of the variables listed above with a simple extra statement. I might do this:
I’ve commented out the final result, the Round function, and instead returned a List of my small calculations variables. I can now see how they are calculated.
One time is a Charm
Look at this calculation, which returns my correct age.
Notice anything? (Look at the highlighted text) Besides the lack of formatting, the Get(CurrentDate) function is used ten times. That means each time FileMaker comes to that function it has to evaluate it. It basically is calculating the current date ten times. That is unnecessary.
Instead we’d use the Let function to save the current date in a variable. _curr = Get(CurrentDate). That’s one time and now we can use the variable throughout the rest of the calculation. It might save milliseconds, but it still saves time.
Other Thoughts
Prepare
When putting this together for a script or for a calculated field, I always use the data viewer to generate the calculation. That way I can test every part of it before committing it to the script or field calculation.
How Low Will You Go?
You can get very far into the weeds in using the FileMaker Let function; each calculation can be broken down further and further. There’s no problem with that. But you can decide at any time to combine calculations into one variable declaration. It is up to you, but remember to think about your future self. What would he or she like you to do now?
What’s in a Name?
There’s nothing special about the variables that I chose except for two things:
- I always start mine with an underscore “_”. It’s just a convention I use. Some folks start with “~” and some do not include a prefix.
- The names are important. Since one of the points of the let statement is to use “_q” instead of “InvoiceLineItems::Quantity” in multiple places, I want to make sure the variable means something. I might, in this example, change “_q” to “_quan” to remind myself it is the value in the Quantity field.
Doing More
You can also assign a local variable ($var) or a global variable ($$var, which is actually global to this file only) in the Let statement’s declaration area. This is used for highly specialized reasons, ones that you’re not likely to come across very often. In my daily development I never do this. When I’m debugging a script, I might want to temporarily change the value of a local variable in order to see different logic performed.
The FileMaker Let Function
The FileMaker Let function is powerful and is an essential tool for all FileMaker developers. The function is essential for making complex calculations easy to debug in the now and to review in the future.
Trackbacks/Pingbacks