ExecuteSQL – Taming the Beast
Posted: November 19, 2013
I’ve been getting to grips with the ExecuteSQL function. To say that I’ve been enjoying it would be partly true – but it can be a little tricky, not without its frustrations, and disappointing in its performance. BUT, I’m beginning to see just how powerful and useful it could be. There are a couple of excellent resources that I’ve found very useful: one is Filemaker Hacks – Kevin Frank’s articles about ExecuteSQL on his blog are excellent. Kevin writes so clearly, and presents the material so well, that even when he’s repeating stuff that I already know (which isn’t often!), it’s a delight to read his articles. Of particular relevance here are:
Also, Beverley Voth’s “The Missing Filemaker 12 ExcecuteSQL Reference” – there’s a link to this on FilemakerHacks too – here.
Here are some examples of expressions using the function:
Basic List of Field Contents
ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE nameLast = ‘Smith’ ” ; “” ; “” )
This will give a list of the first names of people whose last name is Smith. A couple of things that tripped me up, even at this basic stage:
- “PPL” is a table occurrence, not a base table name.
- ‘Smith’ is case sensitive.
To get around the case sensitivity issue, I’ve started changing all text to lower case within the expression, e.g.
ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE LOWER(nameLast) = ‘smith’ ” ; “” ; “” )
ExecuteSQL – Taming the Beast | A Filemaker Miscellany.