That is a very nice example of using LIKE.
As you embrace using SQL more and more in your workflow, you will also want to take care to guard against your queries breaking when you change the names of fields or tables.
While ROLE::partySearch in your calculation will automatically change when you rename it, you can get the same insurance for "name" and "party" by doing something like the following.
sqlField_name = Substitute(GetFieldName(party::name);"::";".");
sqlTable_party = GetValue(Substitute(GetFieldName(party::name);"::";"¶");1)
ExecuteSQL("SELECT " & sqlField_name & " FROM " & sqlTable_party &" WHERE " & sqlField_name & " LIKE ? " ; "" ; "" ; "%" & ROLE::partySearch_g & "%" )
The value for sqlField_name becomes "party.name" which FileMaker's SQL is happy with.
You could also use the more elaborate
sqlTable_name = GetValue(Substitute(GetFieldName(party::name);"::";"¶");2)
to just yield "name" in the same way sqlTable_party gets just "party".
Note that the choice of field in the table "party" used to calculate sqlTable_party is arbitrary. Only the left hand side of "party::name" is used.
Thanks Tom. Great advice! I often use GFN when passing script parameters but it never occurred to me to use it with the ExecuteSQL function. Thanks for taking the time to provide examples.