Running SQL Queries in FileMaker

Running SQL Queries in FileMaker

Keep up to date with CoreSolutions

Running SQL Queries in FileMaker


I’ve spent the last several years working on solutions using a variety of databases – primarily FileMaker, but I’ve also used MySql, SQL Server and even Oracle a time or two. One of the things I like about using SQL databases is that you can join tables together pretty much on the fly. In FileMaker, if I have a calculation that requires a relationship that I haven’t defined, it means that I need to go to the relationship graph, create a new table occurrence and a new relationship. Not necessarily a big deal, but if you’ve got a lot of these calculations, your relationship graph can get complicated fairly quickly.

I recently came across a thread in the FM Forums discussing running SQL commands in FileMaker (click here to view it). It is possible to run SQL commands in FileMaker – there’s just nothing in the FileMaker interface that allows you to do so. It is possible using plugins though. There are several plugins that allow you to do this – among them are ScriptMaster Advanced (360Works), DoSQL (SH Partners / MyFMButler) and MMQuery (CNS). I did some testing using DoSQL, but the article above discusses the other plugins as well. It’s actually fairly easy to use – below I have a simple calculation that I created:

mFMb_DoSQL( “select name, province from company where country=’CA’” )

If you aren’t familiar with SQL, this statement simply retrieves the contents of the name and province field from the company table (note – you need to use a table occurrence name here, not the table name), where the value of the country field is CA. This statement is then run through function mFMb_DoSQL – below is a sample result:

CoreSolutions Software|ON¤Test Company|ON

By default, fields are separated by the ‘|’ character, and records are separated by ‘¤’ ( although there are functions that allow to change those values). So in this case two records have been returned – one for CoreSolutions Software in Ontario, and Test Company in Ontario.

While I haven’t had a chance to use this in a full solution, it’s something I’d definitely like to use in the future. I can definitely see it being useful, and a good way to cut down some of the complication in the relationship graph.


Leave a Comment