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.

Before SQL

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:

Before SQL (click for a larger version)

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.

After SQL

Now take a look at the same script in FileMaker 12:

SQL Version (click for a larger image)

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.)

Learning SQL

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 you’re 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.

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.

Source