BlogFileMaker

SQL FileMaker Query Builder

By November 11, 2013 14 Comments

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.

FileMaker SQL Query Builder Screenshot

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.

Mislav Kos

Mislav Kos

Mislav is a FileMaker developer and a Technical Solution Architect at Soliant Consulting.

14 Comments

  • Avatar Shin Ninagawa says:

    Thank you sharing great idea!

  • Avatar Derek says:

    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.

  • Avatar Tony says:

    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!

  • Avatar Mislav Kos says:

    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.

  • Avatar Lee Smith says:

    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?

  • Avatar Mislav Kos says:

    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.

  • Avatar John Freberg says:

    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

    • Mislav Kos Mislav Kos says:

      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.

  • Avatar John Freberg says:

    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

    • Mislav Kos Mislav Kos says:

      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.

  • Avatar Tom McDougal says:

    Sorry, I’m a bit of a newbie, but how do I install this into one of my own solutions?

  • 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/ .

  • […] (σàâΦ¿ÿΣ║ïπü»πüôπüíπéë) Mislav Kos 2013/11/11 […]

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant