kmtenor

Using ExecuteSQL and FM checkbox lists

Discussion created by kmtenor on Jul 12, 2012
Latest reply on Jul 14, 2012 by Malcolm

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:

 

  1. Send it in natively (which delimits the list with paragraph markers) - NOPE
  2. Send it in after using a "substitute" function, just replacing paragraph with comma (Substitute ( chklistField; ¶; ",")) - NADA
  3. 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):

 

Let ([

$cityList= "'" & Substitute ( chkListField; ¶; "','") & "'";

 

$$query = "SELECT a.name, a.address

FROM addresses a

WHERE a.city in (" & $runIDList & ")"

];

 

ExecuteSQL($$query;"";"")

)

 

(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.

 

Thanks.

 

-Kevin

Outcomes