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.
Thank you sharing great idea!
Mislav,
This is an EXCELLENT tool. I cannot tell you how much I am appreciating this module. Thank you for your effort, especially making it into a filemaker module so I can use it in my own solutions.
One question: Does filemaker have have fully-enabled SQL engine? It seems like I’m putting in some fairly standard queries and they are coming back with syntax errors, when I know they are correct.
This is a fantastic tool, especially for people (like myself) just learning how to utilize Execute SQL!
One thing to note:
After installing it and trying to use it, I spent over 2 hours and couldn’t get a single query to work right. I swore I had it right, but I couldn’t get it to work. After much deliberation, I finally figured out that the problem was that I had “Smart Quotes” turned ON in the File Options. I never turned it on, it’s just on by Default.
Oh man… such a needle in the haystack problem! If you’re using Smart Quotes then the “regular” apostrophes get converted to “curly apostrophes”, causing the SQL query to fail. This is made all the more complicated by the fact that when using Courier New, the difference between the two apostrophe types are so minimal it’s hard to tell.
‘ vs. ‘
Anyway, just thought I’d post this for any newbies! Fix it by going to File > File Options > Text and un-check the “Use Smart Quotes” box.
Great little helper tool – thank you!
Thanks for the nice words, Derek.
Regarding FileMaker's implementation of SQL, as far as I know FileMaker hasn't published any documentation detailing exactly which aspects of SQL have and have not been implemented, so the best we have is trial and error. The most comprehensive documentation I've found thus far is Beverly Voth's writeup on Kevin Frank's blog: http://www.filemakerhacks.com/filemakerhacks/6406.
Thank you for your file.
I tried to use the link to Kevin FrankΓÇÖs site, but it doesnΓÇÖt work either the one here or on the file. I was able to find the article and here is the link
http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/
I can not duplicate your second Query, but IΓÇÖm not sure if it is me or what.
Do you have a white paper showing your queries so I can compare?
Lee, thank you for the new link. I'll make sure to update the file.
I unfortunately don't have an accompanying white paper, but here's the second query that I use in the video: select s.name, w.name from state s join coastline c on s.id = c.id_state join bodyofwater w on w.id = c.id_bodyofwater where order by s.name. I hope that helps.
Mkos:
I have installed Query Builder into one of my solutions, but I can’t seem to get it running.
The Query Builder Layout completes the “trigger: OnLayoutEnter: Query Builder” script without error. But when I run the “Test QueryBuilder Module Installation” script, it fails with 3 ‘table missing errors’ and then the dialog from the script indicating that the Query filed has not been set up correctly on the layout.
I have triple checked my installation steps, but I must be missing something. Can you shed any light?
Thanks,
John Freberg
John, the problem might have something to do with field repetitions. Make sure that the field you’re using is configured as a global field with 5 repetitions. This is done in the Storage tab of the field Options dialog.
Another possibility is that the field name wasn’t correctly specified in the “Configure QueryBuilder Module” script. Look for the Set Variable [$qbField; Value: GetFieldName ( QueryBuilder::QueryBuilder_g )] step.
You’ll also need to make sure that the field is properly set up on the layout. There should be five instances of the field ΓÇô one for each repetition. Four instances of the field are on the layout, and the fifth one is to the right of the layout boundary.
Good luck.
Thanks Mkos,
I closed all of Filemaker down, started a fresh install and everything worked fine.
Now I have a question. Can you insert global variables into the Query Builder and have the query run without error?
In the query below, I want to use a global variable, $$ScannedCode, which I read from a barcode scanner to find a record that matches either on the AssetTag or Serial number field. So far, I have not been able to make this work. I’ve tried a variety of approaches, but all I get is SQL syntax errors, i.e. “?”.
Let ( [
query = ”
SELECT AssetTag
, SerialNumber
FROM Assets
WHERE AssetTAg = $$ScannedCode
OR SerialNumber = $$ScannedCode
” ] ;
ExecuteSQL ( query ; “” ; “” )
)
Thanks,
John Freberg
John,
Try this: “SELECT AssetTag , SerialNumber FROM Assets WHERE AssetTag = ” & $$ScannedCode & ” OR SerialNumber = ” & $$ScannedCode
If AssetTag and SerialNumber are text fields (instad of number), then the values will need to be enclosed inside of single quotes like this:
“SELECT AssetTag , SerialNumber FROM Assets WHERE AssetTag = ‘” & $$ScannedCode & “‘ OR SerialNumber = ‘” & $$ScannedCode & “‘”
Or you could do it like this, using question marks:
Let ( [
query = “SELECT AssetTag , SerialNumber FROM Assets WHERE AssetTag = ? OR SerialNumber = ?”
] ;
ExecuteSQL ( query ; “” ; “” ; $$ScannedCode ; $$ScannedCode )
)
In this approach, you don’t need to worry about enclosing the values inside of single quotes; FileMaker takes care of it for you.
Sorry, I’m a bit of a newbie, but how do I install this into one of my own solutions?
Tom, the installation instructions are included in the comments of the “README for Query Builder” script.
Hello Kos,
I am also a newbie, I have read the readme for Query Builder, but still don’t understand exactly how to install this.
1. copy these custom functions to your solution: #T, #F, sql.debug
Where exactly should this be copied and what should it look like?
2. copy the QueryBuilder_g global field (it has 5 repetitions) into one of your tables
No matter which table, can it also be a separate one? How can this be copied?
3. create a blank layout called “Query Builder” and base it off of the same table that you placed the field into. Suggestions: no header, no footer, layout height 600, width 968, theme Contrast – Blue.
a I don’t understand anything.
4. copy the “QueryBuilder” script folder into your Modules folder. (If you don’t have a Modules folder, create that first.)
I can’t copy it, script import is available, but I can’t export it. How do I do this?
5. set the OnLayoutEnter trigger of the “Query Builder” layout to run script “trigger: OnLayoutEnter: Query Builder”
Where should this be set?
I know, too many questions… is there perhaps a more detailed documentation for the installation?
Thank you
Translated with DeepL.com (free version)
Hi Bruno,
It sounds like you’re just getting started with FileMaker. Welcome to the FileMaker community!
A good way to start learning the basics is to watch the videos here: https://community.claris.com/en/s/learning-trac-detail-standard?ltui__urlRecordId=a5G0H0000019XO4UAM<ui__urlRedirect=learning-trac-detail-standard
Watching those videos will show you how to copy/create custom functions, fields, layouts, etc.
Mkos,
Thank you for sharing a great tool.
I have translated your blog article into Japanese, which you can read at:
http://notonlyfilemaker.com/2016/02/sql-query-builder/ .
Pingback: SQLクエリービルダー | Not Only FileMaker
I believe I have installed correctly, however I get errors on the OnLayoutEnter: Query Builder. I can see that the Configure QueryBuilder module has succeeded and in data viewer I can see they are correct. When it gets to line 17 Set Field By Name [$$Field_Query; “”] I get the error that the Field is missing. It does the same for the subsequent fields as well. I have replaced the quotes, etc. and still it does not work. Any suggestions?
Hello Ann,
What value does $$Field_Query have for you?
$$Field_Query gets set in “Configure QueryBuilder Module” on line 68. In the out-of-the-box configuration, it gets set to the QueryBuilder::QueryBuilder_g field name (more specifically, the first repetition of the QueryBuilder_g field).
If you haven’t already, I suggest double-checking that you’ve followed the installation instructions documented in the “README for Query Builder” script.
Just typing my solution here in case this happens to someone else. I had everything there and it looked right to me. Decided to just copy and past the one line of code again, change the table name. Works perfectly now. I have no idea what the problems was. This is really a magnificent tool – thank you so very much for making and sharing. I love this kind of professional generosity.
I’m glad you got it working.
And, thanks for the thanks.