In Filemaker, we have often leveraged the ability to have a checkbox field containing a list of parameters linked to a second table, and then displaying related records from the second table for all parameters chosen. In SQL, this is usually handled as follows:
SELECT a.name, a.address
FROM addresses a
WHERE a.city in ('Austin','Houston')
It turns out that ExecuteSQL can handle exactly the above query. However, I cannot find any way to get it to accept the "in" statement passed in as a parameter.
I have tried everything I can think of:
- Send it in natively (which delimits the list with paragraph markers) - NOPE
- Send it in after using a "substitute" function, just replacing paragraph with comma (Substitute ( chklistField; ¶; ",")) - NADA
- Make the "substitute" a little fancier by enclosing all items in single quotes ("'" & Substitute ( chklistField; ¶; "','") & "'") - NOTHING
So, thinking slightly outside the SQL box, I did come up with the following, which is ugly, but does work (and works well):
$cityList= "'" & Substitute ( chkListField; ¶; "','") & "'";
$$query = "SELECT a.name, a.address
FROM addresses a
WHERE a.city in (" & $runIDList & ")"
(I globalized the query in order to see it in the Data Viewer, since this runs as part of a script)
Does anybody have a better way? Has anyone found a way to use the parameter passing to simply send a list in? Or should we bring this up to Filemaker as "Feedback"? It seems like lists should be modified appropriately to be incorporated into ExecuteSQL statements, or at least re-handled properly when they're seen a parameter to an "In" statement.