ExecuteSQL, SQL

FM 12 ExecuteSQL “Unconference” Session

Kevin Frank

11 Sep 2012: The ConditionalVL_SQL demo has been updated to fix a minor bug.

Yesterday I had the pleasure of co-presenting a DevCon “Unconference” session with John Ahn on the topic of ExecuteSQL. I wasn’t sure if there would be much interest in this somewhat geeky subject, but the room was packed, and the audience ran the gamut from SQL newbies to SQL power users.

We started out with a basic intro, and showed a few simple demos. Next we moved on to a discussion of some of the issues, nuances, gotchas, etc. Then John showed a couple ExecuteSQL-powered demos which I strongly commend to your attention: a) Multi-Column Dynamic Portal Sorting…

…and b) Dynamic Value Lists…

…which, incidentally, caused every jaw in the room to drop, due to the fact that John’s method of constructing these conditional value lists seems to violate at least one fundamental law of FileMaker physics — more on this in an upcoming posting.

Here are links to everything we referred to during the presentation, in roughly chronological order.

FileMaker Hacks postings:

John Ahn’s demos:

Other links:

Published by Kevin Frank

11 thoughts on “FM 12 ExecuteSQL “Unconference” Session”

  1. Thanks for the examples and for furthering the cause.

    I’ve been using SQL for dwindling value lists with a variant on the technique he uses for conditional value lists. I needed to implement a conflict-checker to determine if a value was available (this was for a scheduling application) base on the day, start time, and end time. Using SQL to perform the join and select the available resources allowed me to do this with a much less complex graph.

    There’s one minor difference in my technique: I use a “pass-thru” script to exit with the script parameter. Here’s the body of that script:

    Exit Script[Get ( ScriptParameter )]

    This allows me to have the script trigger succeed or fail based on the evaluation of the script parameter. I can pass static values (i.e. True or False) or I can use the return results from execution of the SQL expression.

    Reply

  2. Thanks for doing the unconference conference session. ExecuteSQL was all anybody was talking about but there weren’t any sessions on how to actually use it so I really appreciate the class and this website! Keep up the good work!

    Reply

  3. I was a little surprised that there was no official ExecuteSQL track at the FileMaker DevCon 2012 conference. After Andy LeCates raved about how Vince Menano had used it to massively improve his new Inspector tool, I thought for sure it would be a major focus of the conference, if not a whole track, then at least a few solid sessions. I didn’t see anything about your Unconference session until it was too late.

    P.S. I also see a Youtube video called “FileMaker 12 – ExecutesSQL Function – 12 Days of FileMaker 12 Series” (http://www.youtube.com/watch?v=y-j-msi3ij0)

    So much more to learn…I thought the new information deluge was done for a while. Thanks for posting this info here. Better to find out about it late than never.

    Reply

  4. Kevin, these are wonderful techniques and thanks for sharing them. Was the presentation recorded so those of us who were unable to can have the pleasure of viewing.
    Thanks
    patricia

    Reply

    1. No the presentation was not recorded as this was not a scheduled session. It was the Wed 10:45, “Unconference”.

      Reply

    2. FYI:

      The only sessions recorded were in Glimmer 1-4 , Glimmer 5-7 — both upstairs, and the Fontaine room — near the check-in Lobby.

      Best Regards,
      – – Scott

      Reply

  5. Thank you for posting this. I just took a look at the demo file for the dynamic value lists, and I think that it is brilliant. Thank you so very much for sharing.

    Reply

  7. Thanks for posting the Dynamic Value Lists (ConditionalVL_SQL). I use it all the time.

    However, I have a question regarding value lists of this type. I have hit a limit on the number of values displaying in a the list.

    Is there a limit to the number of values displayed in a value list created with this method?

    Or am I missing something?

    All the best, thanks again

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.