Core FileMaker Blog

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

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved