Executing SQL on the Current Found Set

[comment]

We often need to collect data from the current found set, for example for interfacing with an API. Over the years several methods have been developed for such purpose, with differing levels of complexity.

On many occasions the most convenient method to gather such data would be to use the ExecuteSQL() function. However, since SQL queries operate independently from the found set, this presents two obstacles:

• reconstructing a complex find in SQL may be difficult and/or inefficient;
• in some cases it is not possible to recreate the find at all since it was performed by the user - possibly even by omitting some records manually.

The introduction of the GetRecordIDsFromFoundSet() function in version 22 opens up a new way to limit a SQL query to any arbitrary found set very simply. All we need to do is add a WHERE clause to our query and use the IN operator to enumerate the RecordIDs collected by this function. Note that since the IN operator cannot accept a placeholder (or more precisely, a single placeholder cannot be used for multiple values), we must inject this list directly into the text of the query.

Here is a bare-bones example:

Let ( [
query = "SELECT SomeField FROM YourTable WHERE ROWID IN («listIDs»)" ;
foundSetIDs = Substitute ( GetRecordIDsFromFoundSet ( 0 ) ; ¶ ; "," )
] ;
ExecuteSQL ( Substitute ( query ; "«listIDs»" ; foundSetIDs ) ; "" ; "" )
)

And that's all it takes in version 22.

 

If you have not yet upgraded to version 22, you can still utilize a very similar method, albeit with a few more resources:

At a minimum you will need to define a summary field that lists the values from your primary key field. In the following examples we are using a table named Items, where ItemID is the primary key field and sListIDs is the summary field:

Let ( [
query = "SELECT SomeField FROM Items WHERE ItemID IN («listIDs»)" ;
foundSetIDs = Substitute ( Items::sListIDs ; ¶ ; "," )
] ;
ExecuteSQL ( Substitute ( query ; "«listIDs»" ; foundSetIDs ) ; "" ; "" )
)

This method is shown in the attached demo file. Note that it assumes the ItemID field is of a Number type. No problem if you are using serial numbers, but if you are populating your primary key field with UUIDs, they must be generated using the Get(UUIDNumber) function. Otherwise you will have to enclose each individual value in single quotes before passing the list to the query:

Let ( [
query = "SELECT SomeField FROM Items WHERE ItemID IN («listIDs»)" ;
foundSetIDs = "'" & Substitute ( Items::sListIDs ; ¶ ; "','" ) & "'"
] ;
ExecuteSQL ( Substitute ( query ; "«listIDs»" ; foundSetIDs ) ; "" ; "" )
)

Both of these approaches also allow for executing the query server-side, after passing the user found set's IDs as the script parameter. In fact, version 22 opens up a whole new range of possibilities for processing the user's found set server-side, since now it is dead simple to reconstruct the same found set (including record order!) on the server using the new Go to List of Records[] script step.

 

FoundSetSQL.fmp12