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.