FileMaker SQL: Simplify and Stay on Screen
FileMaker 12 lets us run SQL queries against our own FileMaker tables. This has been possible with plugins for a while and a number of serious devs like Mike Lee, Greg Lane, and Kevin Frank have been talking about what a great technique this is. I completely agree and think the new ExecuteSQL calc function is the killer feature of FileMaker 12.
There are a few reasons for this and I hope to get to write about more of them. But for now, consider the following simple script that checks to see if any records match a given ID:
There is nothing really wrong with this script and in fact it comes from our FileMaker 10 calendar. But look at all the work we have to do to make sure we’re in the right context to perform the find. We have to:
- Record our current context and find criteria
- Draw a new off-screen window
- Navigate to the new layout / context
- Execute our find, recording the outcome
- Close the off-screen window and return to our original context with the results
Kind of complicated. And frustrating to debug as the action is happening off-screen.
Now take a look at the same script in FileMaker 12:
Because the new ExecuteSQL calc function lets us search for records independent of context, we don’t need to manage layouts and windows. We can just ask FileMaker if the records exist. Fewer moving parts means simpler scripts, which means more stable solutions: no question about it.
(For what it’s worth, we could have written that as just one line, putting the ExecuteSQL calc in the ExitScript result, but I find these things are easier to debug if you can look in the script debugger for $sc_Result before the script exits. That way, I can alter it right here if I need to.)
FileMaker’s new ExecuteSQL function only accepts versions of the SELECT statement so there isn’t that much SQL to learn But it does support joins, union, and groupby, so if you already know your way around this stuff you’ll be rewarded. If you don’t, SeedCode will soon be releasing a free tool to help. (You’ll love it.) If you’re not getting our newsletter, sign up here to be notified as soon as we ship.
(Note: the free SQL Explorer is now available.)
And if you’re wondering what you can possibly do with just the SELECT statement (which returns lists of matching data), think Virtual List and stay tuned.
(17:20) – SQL API – Filemaker has to interpret your commands – It emulates SQL
I’m thrilled that you’re doing this series because it certainly strikes me that this is the next ‘skill set-du-jour’.
My own SQL experience is rudimentary but I started doing some experimenting a few months back, using a plug in and v11, on a served database with roughly 120k records. ***I have not yet done ANY experimenting with the v12 ExecuteSQL() function.
Back then, the purpose was to just develop a bit of SQL dexterity. (Eventually, I want to get to the point of stripping back the RG but for now, I just want to know that my queries are returning correct results.)
One observation was that sometimes (mostly), especially with largish found sets, the SQL approach took considerably longer than the conventional FM alternative.
I’ll be very curious to find out what others discover in this regard, so we can all better know which is the best tool choice in the various scenarios.
Looking forward to the next instalment,