Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.
Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).
The first two demos explore a basic (v1), and a more elaborate (v2), SQL approach to dealing with found set awareness, and the third demo shows a pure FileMaker approach… which tends to be faster, though the v2 demo does have a trick up its sleeve.
Some Basics
Here’s the Relationships Graph for demos 1 & 2:
And here are field defs for the transactions table:
The SQL Found Set Issue In A Nutshell
The concept of a “found set” does not exist in FM/SQL, so when you perform a SQL query in FileMaker, the query ignores your current found set. Fortunately, FM/SQL can be tricked into querying only the records within the current found set, by using a SQL “IN” clause, and feeding that IN clause a comma-delimited set of primary keys corresponding to the records in the found set.
More Information
If the primary key field is a number field, then the IN clause will look like this (to keep the example simple, let’s assume a found set of three records)…
IN ( 52641,46787,87246 )
…but if the primary key field is a text field, then each element must be wrapped in single quotes, like so:
IN ( '52641','46787','87246' )
Note that it is the field type that determines whether the FM/SQL parser considers the primary key to be a number or text, and not the data inside the field.
However, having drawn your attention to this distinction, I will now invite you not to worry about it — provided you use the FoundSetToSQL custom function employed by demos 1 & 2, this will be taken care of for you automatically.
This is my third version of this custom function, and I’m happy to report that by relying on a summary list field rather than recursion to build the key list, it no longer chokes on found set sizes greater than 9,999 records. [Actually, the CF doesn’t “rely” on a summary list field — it expects a return-delimited list, and how you generate that list is your decision.]
Demo #1: SQL Found Set Awareness, v1
In each demo, within the current found set we have the option of a) counting instances of the active payee, or b) summing transactions for the active payee.
Script #1: sql count payee in found set
Let’s take a look at the “count payee” script first. It’s a two step script, and the first step loads up a variable, $theCount…
…and the second step displays the dialog.
If you’re wondering about the use of the NumToJText function, it’s a simple way to ensure that thousands separators appear in the numbers (if necessary) when the dialog displays.
Script #2: sql sum amount for payee in found set
The methodology is similar in the “sum amount” script. First load up a variable ($theSum)…
…and then massage and display it in a dialog. In addition to the NumToJText function mentioned above, a custom function (FormatNumberAsText) is used to format $theSum.
And here’s what the user sees.
Demo #2: SQL Found Set Awareness, v2
[Note: since the two scripts are so similar, for the remainder of this article we will only be looking at the “count payee” script.]
A potential problem with the v1 demo is that the script takes a one-size-fits-all approach, so as the found set size increases, the script takes longer to run. In v2, however, the script is a little smarter.
Explanation
a) If we’re not in a found set (i.e., we’re looking at all records), then we can lose the “IN” clause and simplify the SQL query to this:
b) If our found set size is less than half the total record count, then do what we did in v1:
c) Otherwise we’re in a situation where our found set size is 50% or more of the total record count. In this case, and this is the “trick up the sleeve” I alluded to at the outset, we’re going to spawn a new temporary window (which will inherit the current found set), do an Omit Records to flip the found set to the inverse of the records we actually care about, grab a list of those primary keys, have our SQL query use “NOT IN” rather than “IN”…
…and then close the temporary window and return to our main window.
To see the difference this can make performance-wise, open both v1 and v2, show all records, omit one record in both demos, and then click the Count or the Sum button.
Demo #3: FM Found Set Awareness
In this demo we’re going with a plain FileMaker approach rather than SQL, and we’re going to do some of the heavy lifting at the table schema level…
…and some via the Relationships Graph.
Here’s the script:
As you can see, we’re stealing a page from the demo #2 playbook, and if all records are visible (i.e., we’re not in a found set), we simply count related records via a self-join relationship on the payee foreign key.
However, if we are in a found set, then we count related records across this relationship:
And it is considerably faster than the SQL approach under most circumstances, but bear in mind that all of these techniques will tend to slow down as found set sizes increase, and that they will perform better locally than across a LAN or WAN.
Note: if your starting found set is large, you may need to add a “Refresh Window [Flush cached join results]” step before the Set Variable step.