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.