Scott Howard Consulting»Tapping into FileMaker’s Internal SQL
Scott Howard Consulting
Designing software that fits
« Elements of a Database
Tapping into FileMaker’s Internal SQL
This post started as a place to collect links to good articles about using FileMaker’s internal SQL functionality. Many things can be accomplished more efficiently with SQL than with native FileMaker script steps and calculations. Until FileMaker 12 came out in April 2012 you needed a third-party plug-in to access FileMaker’s internal SQL. At the bottom of this post are some good plugins available for free in case you haven’t yet upgraded your solutions to 12.
A recent update to a complex routine illustrated the power of using FileMaker’s internal SQL. My routine processed several thousand records from an audit log then pushed them to a hosted file. Using various methods I had gotten the process down to 15 to 20 minutes. The new routine gathers the same amount of data using SQL, uploads it to the server where it is parsed and pushed to the correct hosted tables. The process now takes 30 to 60 seconds! The overall speed gains involved more than just SQL, but from the user’s perspective it was like going from rush hour standstill to warp speed!
Handling table and field names with spaces
Ideally the fields and tables you query should have no spaces in their names. Frankly, on some solutions I still use plain field names because they are easy to understand. I may change that now that FileMaker 12 gives us access to their SQL. It is not difficult to accommodate these spaces; it’s just one more thing to remember. Basically you use “escaped” double quotes around your table or field names. Let’s say I have a Registrations table. I want to know how many registrations have come in for classes this year. This query gets a count of all the registrations for 2012:
ExecuteSQL ( “
SELECT Count(*)
FROM registrations
WHERE \”School Year\” = 2012
” ; “,” ; “¶” )
via Scott Howard Consulting» Blog Archive » Tapping into FileMaker’s Internal SQL.