FileMaker SQL: Why Use It? (Context Independent)

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.

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:

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:

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

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

2 Comments

  • Mardi Kennedy

    Hi John,

    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,

    Regards,
    Mardi Kennedy

Leave a Reply

Your email address will not be published. Required fields are marked *

Check out some of our other posts ...

Suggesting Appointment Slots

Show Available Slots that Match Multiple Criteria Schedulers often look for gaps in their schedules to find the open resources for each opportunity. But sometimes,

Introducing Draft Settings Mode

Following up on this idea that people stretch themselves when they feel a little safer, we’ve been very focused on the customization experience for DayBack

New Longer Timescales for DayBack

Resource Scheduling Swimlanes You can now extend the pivoted scheduling view in DayBack to show items by week instead of solely by day. This lets

FileMaker Summer Camp – Recap

Unconference Sessions If you missed Pause in October, here’s a look at the sessions that attendees hosted. All the sessions are listed in this post

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2024 SeedCode, Inc.