Using found set in SQL queries

Document created by monkeybreadsoftware on Oct 24, 2018
Version 1Show Document
  • View in full screen mode

There is currently no direct way to tell SQL to use the current found set as condition for a query. But with the GetNthRecord function from FileMaker and the FM.Loop function in MBS FileMaker Plugin, we can build a list of IDs and generate automatically the SQL condition to use the found set.

 

The following line queries a list of IDs from current record:

 

MBS("FM.Loop"; "index"; 1; Get ( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )")

 

As you see we define variable and count it from 1 up to the number of records in found set. For each loop iteration, GetNthRecord is called to query field named Contacts::ID and we get back the list of IDs like this:

 

2

3

5

6

7

8

 

We can change the calls to produce the right list for SQL with IN operator. We add some text before and after and replace the new line with the comma going between values. The new loop call looks like this:

 

"(ID in (" & substitute(MBS("FM.Loop"; "index"; 1; Get ( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )"); ¶; ",") & "))"

 

The text returned may look like this: (ID in (2,3,5,6,7,8))

Please note that for UUIDs you may need to add quotes to get them in quotes, e.g. like this:

 

"(ID in '" & substitute(MBS("FM.Loop"; "index"; 1; Get ( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )"); ¶; "', '") & "')"

 

The new list has quotes for the ID values: (ID in ('2', '3', '5', '6', '7', '8'))

 

Next we can build a SQL line using the IDs and query another field like the last name:

 

MBS("FM.ExecuteFileSQL"; ""; "SELECT \"Last\" FROM Contacts WHERE (\"ID\" in (2,3,5,6,7,8))")

 

As you see we put Last in extra quotes as last is a reserved value in SQL. In general it doesn't hurt to put all field and tablee names in quotes, so the SQL works for all names including those with spaces. The field used for ID may also need to be quoted and if you use UUIDs, please put values in single quotes.

 

One little check is required: Please make sure your found set is not empty before using this. Otherwise you end with an error message from FM.Loop or invalid SQL.

 

 

PS: With Command-Option-C you can copy expressions with color from the calculation dialog.

5 people found this helpful

Attachments

    Outcomes