ExecuteSQL: Using IN with Faux Dynamic Variables

Editor’s note: Today we have a guest article written by John Weinshel, whose knowledge of SQL in general, and FileMaker+SQL in particular, runs deep. John’s contributions in various online forums are always worth reading, and it’s a privilege to present his thoughts on this topic here.

The new ExecuteSQL() function in Filemaker 12 does not work dynamically (with the question mark) as expected with the IN function (nor with BETWEEN). For example, we might expect the following statement to return all the ID’s for contacts whose first name is either John, Mary, or Renee:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( ? )
";
""; ""; " 'John','Mary','Renee' "
)

…but it doesn’t. The following does work, but it’s not dynamic:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( 'John','Mary','Renee' )
";
""; ""
)

It occurred to me that we instead could:

• Build the desired group using conventional Filemaker methods (that’s the dynamic part)
• Pass those results to a variable
• Hand-write the SELECT statement with that variable inside it, and assign it to another variable
• Use ExecuteSQL with that variable as the argument to return the expected results. It works.

In today’s demo file, Using IN with faux dynamic variables, v2, the script following this idea first assembles a list of ID’s for invoices from the previous 100 non-weekend dates, passes those ID’s to a global field, and then GTRR’s (goes to related records) to those invoices using a TO from the global to the ID’s.

Here’s the first half of the “Gather Last 100 Non-Weekend Days” script:

At this point the “argument” for the IN function has been assembled as a SQL-friendly comma-delimited $DateList variable, like so:

And here’s the remainder of the script.