I spent some time this weekend doing some old fashioned FileMaker hacking that I’ve always enjoyed (viva La WorkAround Pro).  Definitely inspired by Todd Geist’s very cool hyper-list stuff, but he had also reminded me of the problem of ExecuteSQL on the found set, and I’ve heard this as a common feature request.  My big take away from Todd’s comments was that the found set is a uniquely powerful thing, so if it could somehow be incorporated into FMSQL, then that would certainly be a potentially powerful tool.  Let me say right now that I did get this to work, but the results are such that it’s really not a practical solution except in maybe some edge cases.  I can proudly say that it is neither as fast (edge exception) or as well-abstracted as hyper-list, however…

When I was in Woodworking school we were given a demo on creating an initial flat edge using a table saw.  It was an elaborate jig set up, safety was a bit questionable and took a little practice to get right.  Some of us were scratching our heads as the joiner is a dedicated machine for doing just this task.  We would all have access to joiners and they work about 10 times better that this elaborate table saw “hack.”   The demonstrator acknowledged this and made two important points:  First, obviously, you indeed may not have access to a joiner at all times.  And second, and a more important point, is that it gives us a deeper understanding of how the table saw works, what it can do and what it’s limitations are, i.e. fruitless experiments (like mine) can lead to a deeper understanding of your tools.

I had remembered, from some elaborate work that Lisette Wilson had done with Snapshots and dynamic sorts, that Snapshots are very light and fast.  They also, if you look at them as text, give a logical reading of Record IDs representing the found set in this syntax:

So, could I capture this info and somehow incorporate it into a SQL Query?  This would depend on the answers to two questions.

The first question to answer is:  “Does FMSQL give access to the Record ID?”   I ask this question because that’s the only info I’ll be able to get from the snapshot (If I can even do that).  Looking at the reserved words in the ODBC guide, I didn’t see RECORDID, but I did see ROWID, and sure enough that will get you the Record ID!  You can use both in SELECT and WHERE.  Record IDs, of course, have their limitations as actual key fields, but they can be relied upon to be unique within a table at a given time.  This does seem like a somewhat significant discovery (for me), and worth the effort spent to pursue this already!

The next question is: “Can I capture information written to a snapshot in a relatively “hack free” way?”  The answer, of course, is no.  After that, we have to ask: “Can I just do this?” and the answer there is invariably yes.  I first tried to “read” a snapshot using InsertFromURL into a global field (so much for pure abstraction), but got a 507 validation error.  I did remember hearing that you could insert the contents of a text file.  After playing around for a bit I realized that I could export a snapshot using a variable path that specified it as a text file, and to my pleasant surprise I realized that I could Insert the contents of this file into the global immediately after exporting it.  It is useless as a snapshot, but that’s not really the point here.  So, the second cool thing I learned is that InsertFromURL from a file, particularly a hacked Snapshot works and could have some potentially powerful uses.  I had used a web viewer for this in the past, and scraped the contents for similar results, but it required some hard refreshing after exporting the file.  The InsertFromURL doesn’t require this and works right after the Snapshot creation step is completed, which seems significant as well.  Although I initially “poo-poo’d” the idea of reconstructing the sort of a Found Set, the snapshot does provide this info to you in a logical way as well, so dynamically reconstructing the sort from here into the query is actually relatively doable, with the sort by value list being the snag.

From here it’s relatively easy and fast to parse out the record id info and convert it to a combination of a series of BETWEEN clauses and a single IN clause.  This leads to the third thing I learned:  The performance is so variable from a practical perspective that it’s not really viable.

For example, when run on the full found set of 25,000 records it takes less than as second…pretty darn good.  However, when you run it on a found set of 1437, defined by first_name = S*, it takes 6 seconds.  When you have a found set of 16277, defined by last_name = A…O, then it takes a ridiculous 2:26!!  These results are actually not that surprising.  I’m more surprised that it works at all and actually resolves.  If you look at the queries themselves, the results make perfect sense.  For the full Found Set we get something nice and lean:

However, for the Found Set defined by First_Name = S* we get something significantly more complex:

Here’s the whole thing…

(scroll that to the right to see the whole query)

The found set defined by the more complex criteria of First_Name = A…O generates a query that’s 134062 characters long and takes just under 2 seconds to create itself, so the fact that it takes 2:26 to complete could be seen as relatively good news!

I am as infatuated by ExecuteSQL as anyone. Todd points out that native FileMaker provides us uniquely powerful tools, and he could not be more right.  For me, and many of us, the temptation to take these tools for granted and forsake them for the newest thing without objective evidence is a disservice to our code.  This seems like a truism in retrospect, but always good to go through the process and see it validated.

Although I had a strong sense this was a fools errand as far as getting a practical solution to the SQL vs Found Set question, I am pleased I went through the process for another reason.  I don’t think my method for “re-engineering” a found set this way was flawed.  From a query stand point, I’m not sure there’s a more efficient way to reconstruct the available info into something more compact for FMSQL, so the idea of not being able to have a native way to do this seems more reasonable.  Having said that, FileMaker can generate a new window with the same found set from snapshot link in less than a second.  It would be nice to be able to tap into that kind of speed!

Tagged with →  

18 Responses to ExecuteSQL using the Found Set?!?…Yes!…well maybe not.

  1. Russ DeMoss says:

    I like the idea of testing what it will do as well what it doesn’t do ( or do well)! Keep up the good work.

  2. brianschick says:


    This is a fantastic line of inquiry, and kudos for blazing a trail.

    I’d guess that this might evaluate much quicker if you could use a simple expression like.

    …WHERE id IN (x, y, z)…

    Assuming I have this right, then I’d wonder about creating a light native FileMaker structure that would allow us to use List() to harvest a list of either record id’s, or maybe a list of ID’s (whether we’re using UUID’s, serial ID’s, or some other method of generating our primary keys).

    There are, of course, lots of ways this can be done, and with some tinkering, we could probably find a few painless ways to do this quickly create a list of unique found set ID’s on demand, ready to be fed to our SQL query. I’d suspect this would have the advantage of creating a more readable query, avoiding the need to invoke InsertFromURL(), and potentially evaluating much more quickly.

    Thanks again for starting this discussion. Looking forward to hearing others’ ideas and seeing where this leads.

    PS – I love your woodworking example. I have a very similar memory involving a router table and a plank to be leveled. I wasn’t successful, but I still have both my hands, so I’ll call it a win ;-)

    • Jason Young says:

      Thanks Brian, I will have a follow up post along those lines. Basically using a Hyperlist type method to build a single lN clause rather than the combination with the BETWEENS. That does seem to be generally faster, although it still seems to bog down on large sets even with the single IN clause.

      It would be nice if FileMaker had an InsertIntoVariableFromURL (base 64 encoding as needed to boot) so we didn’t need the global. For that reason, if you really wanted to have an SQL Query based on the found set, I think using HyperList to build your id list and using that rather than the snapshot method would be a better overall method than this, both for portability and predictability of results…but in that case, why not just use Hyperlist to build the ultimate results that you’d get from the SQL. My guess is that doing that will almost always be faster, maybe having a really large number of columns being the exception.

      • brianschick says:

        Hey Jason,

        Yes, I agree. I’ve been lamenting the lack of the missing InsertFromURLIntoVariable function for the past few months. And as you say, I keep wishing we didn’t need that annoying global field. And agreed also that something like Hyperlist to assemble these id’s will probably be a better route to take than using InsertFromURL.

        I think even in this case, there are likely cases when SQL can do things that are either more powerful or require less overhead than doing them natively. We may want to grab columns from unrelated TO’s, concatenate fields on the fly, do aggregations, or other things SQL excels at.

        I still think you’re onto something interesting here. One way or another, linking the current found set directly to SQL feels like a powerful concept. Maybe we’ll just need some time to get a handle on what we can do with it…

  3. […] have one thing (at least) to follow up with on my ExecuteSQL using the Found Set post from a little bit back.  This was to see if I could get the RecordID parsing optimized (or at […]

  4. Bill Barman says:

    So what does the ” Insert the contents of this file into the global immediately after exporting it.” entail? What does the URL look like? Does it refer to the path the txt document was saved, or something more simple than that?

    • Jason Young says:

      Hi Bill,
      The export path for the snapshot looks like:
      “file:” & Get ( TemporaryPath ) & “snapshot.txt”

      and the InsertFromURL path looks like:
      “file://” & Get ( TemporaryPath ) & “snapshot.txt”
      On Mac I’m substituting out the “Macintosh HD/” so the actual calc looks like:
      Substitute ( “file://” & Get ( TemporaryPath ) & “snapshot.txt” ; “Macintosh HD/” ; “” )

      There’s going to be a few more posts on this theme and I should have a sample file link on one of the upcoming ones as well.

      • Bill Barman says:

        Thanks Jason.

        I was playing around with it on my FMPro 12 desktop version and finally got it to work when I exported and imported to the Web folder within the FMP application folder.
        Now I know about “TemporaryPath” too!

        Thanks again.

      • Jason Young says:

        Right, Temp Path is a great tool as it cleans itself up when you close FileMaker!

  5. I’ve been thinking we could use a similar technique to allow FMRPC to work with the user’s found set (and not just the current record or all records), based on some previous tinkering I’d done with Snapshot links. Thanks for fleshing out the non-trivial mechanical details. I’d use a similar approach, but not to drive a WHERE statement for ExecuteSQL, but to drive a subscript that performs a native FileMaker queries. That may or may not scale so well, but it’s worth some additional exploration. Great post!

  6. Greg Lane says:

    FWIW, FileMaker Pro 12 seems to have a hard limit on the number of OR operators that can be used in the ExecuteSQL function. I get a consistent crash if I use more than 4,629. There’s also a limit for AND operators, but it seems to be somewhere above 22,000. That’s plenty for typical usage, but be careful when working with larger snapshot files.

    • Jason Young says:

      Hi Greg,
      I didn’t run into any of those limits with these tests. With 25,000 records it was basically one Big IN clause with a few thousand OR BETWEENS. When I ran the straight Single IN clause with 25,000 here: http://www.seedcode.com/sql-and-the-found-set-part-2-recordid-list-and-hyperlist/, it was slow but did complete. Do you know what the limit of literals in an IN clause is? Been meaning to test someday, but seems like something you might know =)

      • Greg Lane says:

        The limit on literals with an IN operator seems to be somewhere around 500,000, at least with integer values. I’ve seen both ? results and crashes beyond that. The results are inconsistent, so there may be other factors, such as the length of the values or perhaps other things competing for FileMaker Pro’s memory.

  7. Vincent says:

    Did you experiment with FM13 ListOF(RecordID) where RecordID is a calc filed that does Get(RecordID) ?

    • Jason Young says:

      Hi Vincent,
      Right, that might help a little, but getting the list of IDs for the WHERE clause is not the slow part. It’s the execution of the SQL itself that’s so slow.

  8. Ramon Richie says:

    Copy All Records script step -> into a variable (will need a plugin for that) then use list in IN clause.

    • Jason Young says:

      Hi Ramon,
      Thanks Ramon,
      Right, this is very fast! and you can paste into a global field and reference that without a plug-in, although it’s not very portable. Unfortunately, getting the ids for the WHERE clause is not the problem it’s the execution of the SQL itself that is so slow once that list of IDs gets long. Maybe chunking the list of ids into smaller groups and making multiple ExecuteSQL calls would be a way to make this faster? I’ll have to try that some day!

      • Ramon Richie says:

        Yep that might be a good idea :-) In some solutions I just “replicate” the SQL to match the Filemaker found set, but that is high maintenance. Must say I’m getting the urge to click on that loop step and give a try.

Leave a Reply

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

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

Share →
DayBack Calendar
DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these