I'm off for some (almost) spring skiing starting today, so no more posts until next Monday. Until then...
The FileMaker Let Function
A good exposition on the Let Function from Geist Interactive:
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.
I've studied multiple posts on the Let Function over the years and have always learned something of value in each. This post is no exception: Using the List function to show multiple variable values at once will save in troubleshooting time in the future. Simple but powerful..
For more information on using the Let function, click here.
Execute SQL() – the Good, the Bad, and the Ugly
Another very helpful post from Wim Decorte of Soliant Consulting on the vagaries of the ExecuteSQL() command. Many developers use it heavily; others not so much. I tend to fall in the middle, but only because I never took the time to figure out what caused it to be slow when I needed fast. That has changed with what I learned here:
For now, I want to focus on performance. While participating in the online FileMaker forum, it struck me that ExecuteSQL() seems to be getting a bad reputation, and I do not think it deserves it. I certainly wouldn't want to see people avoid it. So here is the good, the bad and the ugly:
- The Good: ExecuteSQL() is blazingly fast.
- The Bad: But not always.
- The Ugly: When it is slow it can be extremely slow.
Using ExecuteSQL() effectively all comes down to understanding the circumstances around what makes it fast and what makes it slow.
In our experience, there are two broad areas where it will slow down:
- Complex queries that include JOINS, GROUP BY or SQL functions
- Running queries (even very simple ones) against a table where the user has an open record in
That second behavior where ExecuteSQL() will slow down when the user has an open record in the target table is discussed and demoed in my 2014 Devcon presentation. Get the demo file here.
The demo file linked above contains more than ExecuteSQL() help; it also contains a proof of concept audit log and other goodies. And the original post is full of links to help with ExecuteSQL() queries and problems. And here's a helpful link of past posts on FileMaker Pro Gurus.