FileMaker SQL Options
There are several excellent utilities available for constructing SQL queries for use with FileMaker's ExecuteSQL function. I like the SeedCode SQL Explorer developed by Jason Young. Unfortunately, as I create new queries for current projects, I find myself inclined to type them out manually instead of using a wizard (which is how SQL Explorer works). It just seems faster that way, especially now that I've gotten some SQL experience under my belt.
This process involves opening up the Data Viewer and writing out the query there. If the query has some moderate degree of complexity, I usually end up having to wrap it in Andries Heylen's sql.debug custom function at least once along the way to troubleshoot what I did wrong. Finally, I abstract the field and table names so that the query doesn't break if the names end up changing later.
These last two steps – troubleshooting and name abstraction – end up being a bit of a nuisance, so I built a tool to make this easier and faster to do. My Query Builder tool also does some SQL formatting to make queries easier to read.
Download Our Complimentary FileMaker SQL Builder
I followed Todd Geist's Modular FileMaker approach and submitted the tool on his site.
Complete this form to get the Query Builder tool.
You can use the file in your solution in one of two ways: either add table occurrences from your solution to this file or install it as a module into your solution, (which takes about a minute to do).
I also created a video to demonstrate how to make the most of this utility. You can check it out here.
Let me know what you think, or if you find a bug (not that that would ever happen), or if you have a suggestion on how to improve it.
Get More FileMaker SQL Insights
Have questions about how to use leverage SQL in FileMaker? Contact our team of certified experts. As a Platinum Partner, we can provide direction and development support to help your team drive innovation in your FileMaker application.