This site uses tracking cookies. By using this site, you agree to our Privacy Policy. If you don't opt in, some parts of the site might not function.
Blog
Home / Blog / FileMaker / SQL FileMaker Query Builder
11Nov 2013

SQL FileMaker Query Builder

About the Author

Mislav Kos Mislav Kos

Mislav is a FileMaker developer and a Senior Technical Project Lead at Soliant Consulting.

Comments (14)

Shin Ninagawa - December 14, 2013

Thank you sharing great idea!

Reply
Derek - January 4, 2014

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.

Reply
Tony - January 6, 2014

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!

Reply
Mislav Kos
Mislav Kos - January 7, 2014

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.

Reply
Lee Smith - February 6, 2014

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?

Reply
Mislav Kos
Mislav Kos - February 11, 2014

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.

Reply
John Freberg - September 2, 2015

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

Reply
    Mislav Kos
    Mislav Kos - September 2, 2015

    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.

John Freberg - September 2, 2015

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

Reply
    Mislav Kos
    Mislav Kos - September 2, 2015

    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.

Tom McDougal - January 4, 2016

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

Reply
    Mislav Kos
    Mislav Kos - January 5, 2016

    Tom, the installation instructions are included in the comments of the “README for Query Builder” script.

Naomi Fujimoto - February 13, 2016

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

Reply

Leave a Reply