Hidden Error Messages in ExecuteSQL
One of the most frustrating issues when dealing with ExecuteSQL is the dreaded question mark (“?”) FileMaker returns as the result of the malformed query. Your query does not work for some reason, but you have no idea what the problem is. Daniel Wood, of Digital Fusion, stumbled upon a method to coerce a useful error message by putting the query into a Let statement, and directing FileMaker to return nothing instead of the question mark:
While testing queries in the data viewer, I happened to enter a particular query in a Let statement. The idea was that if “?” was returned, I wanted the result to be nothing instead of the question mark, eg:IF ( Query = "?" ; "" ; Query )
When I put this into the data viewer and pressed ‘Evaluate’ what I saw was quite interesting:
Where did that come from!! The evaluated result of the calculation – which was meant to be nothing – instead was showing as a nice descriptive error message telling me exactly what was wrong in my query.
When I went back into the data viewer calculation, the message was still showing. However as soon as I hit “Evaluate” it disappeared and the expected blank result was shown.
So when does the message appear?
After (not so) extensive testing, I have been able to only reproduce showing the error message in the data viewer, after the “Monitor” button is pressed. Once you go back into the calculation the message remains, but using the “Evaluate” button will cause it to disappear.
It turns out there is a way to coax an error code from FileMaker!
And, in an example of the power of the internet at it’s finest, after posting his blog post to several sources online, it turns out someone else, Andries Heylen, had discovered this a couple of years ago and had helpfully written a function to extract the error codes.
I’ll be using this custom function the next time I use ExecuteSQ–it will be quite the time saver.
Check back to Daniel’s post soon: He will be posting a list of all 52 possible error messages (hat tip to Nick Lightbody for that list).
Unlocking Hidden Error Messages in the ExecuteSQL Function.