FileMaker Pro succeeds as a database system because of its many layers. As a rapid development database you can create a robust and elegant solution fairly quickly. Dig deeper, invest some time and effort, and you expose powerful tools and options that extend your solution. One such hidden tool is FileMaker’s internal SQL engine, which intrepid developers expose via plugins.
You might ask why bother, since SQL is a complex language with specific syntax we must learn outside FileMaker. Also, almost everything that SQL does within FileMaker can be natively accomplished without adding plugins. However, I believe there are times when the power of SQL combines nicely with FileMaker to produce better, smarter solutions, and this makes learning and using SQL worthwhile.
Some of the reasons to look at SQL in FileMaker include context-free queries, a simplified the relationship graph (fewer TOs in the graph), avoiding complex finds in scripts, and flexibility in building reports. In FileMaker, context is king. When creating or finding records through scripts, for instance, failing to start on the right layout could lead to drastic consequences. SQL removes these context constraints.
Several plugins enable access to FileMaker Pro 11’s internal SQL via functions. These include:
- MMQuery from CNS
- DoSQL from myFMButler
- 2empowerFM SQL Runner from Dracoventions
- ScriptMaster from 360Works
For my testing I used MMQuery, and primarily its ExecuteSQL( query ) function.
My task required comparing two tables with ca. 1 million records each and finding the records from Table A that were missing in Table B, and then make some adjustments to these records. Although I probably could have accomplished this in FileMaker, I like the elegance of SQL as it allows you to select data from various tables, and return only the results that you need. Never before having tried SQL in FileMaker, I started small, testing a basic query to discover constraints or differences in the SQL language from my experience with MySQL.
While testing the SQL queries I discovered that the tablename in each SQL statement refers to FileMaker’s table occurrence name, not the base table name. In my project database, one of the tables I queried lacked a corresponding table occurrence with the same name. The first query resulted in an error that the table did not exist, and when I switched to a table occurrence name, the query worked. Table names are not case-sensitive, so if you have a “Contacts” table occurrence name, you can use “select * from contacts” and the query works.
The Select statement returns single or multiple columns (fields) from single or multiple tables. The syntax follows a certain sequence. FileMaker, Inc’s ODBC/JDBC Developer Guide shows some SQL examples. The initially daunting syntax can be broken down into just the pieces you need from the options:
All text inside the square brackets are optional. I created a script to set two variables – a $query with “select * from contacts” and a $result with ExecuteSQL( $query ) so I could check these in the Script Debugger and use $result in subsequent steps in my script. The “*” in SQL is the same as “all” and gathers all the fields from all the records into the result. To narrow down the query you need to specify the field, or in SQL terms, the column. Querying across a single table requires just the column name, such as “select nameFirst from contacts”, but when querying multiple tables you need to include the tablename or table alias as a prefix to the column name, such as “select contacts.nameFirst from etc.”.
Now, returning to the problem at hand: how to get values from Table A that are not in Table B? Imagine two tables: author and book. In the author table we have an author ID field and the author name. In the book table we have a book ID field, an author ID field (foreign key), and the book title. I need to find authors with no titles in the book table. Starting with a simple join, I can see all the records that match (for these examples I have capitalized SQL commands, and added returns for the sake of clarity):
SELECT *
FROM author, book
WHERE author.id = book.author_id
But this query returns authors and titles that are in both tables, and I need the reverse. Using a LEFT JOIN produces the records that matches and those that do not match.
SELECT * FROM author
LEFT JOIN book
ON author.id = book.author_id
Since this returns all the data from both tables, I need to isolate the records that do not match. In SQL terms these would be any records with NULL values in the related field, ie. the book.author_id. By adding a WHERE clause to limit the found set, the result is what I need.
SELECT author.* FROM author
LEFT JOIN book
ON author.id = book.author_id
WHERE book.author_id IS NULL
Using “author.*” instead of “*” also limits my result set to just the records from the author table. No joins are required in the relationships graph, and my script can execute the query from any context. The excitement of seeing values populated into $result from the SQL query–even the most basic of queries–told me that using SQL as part of FileMaker opens up new vistas in developing solutions. In another post I’ll cover another powerful feature of SQL called sub-selects, and how this can simplify complex reports.
Thanks for sharing this great article Anders. I have been a bit daunted by SQL in FileMaker and never got stuck in and had a crack at it, but after reading this article I’m keen to try it out and see what is possible, thanks!
Thanks for the comments, Daniel. While running into some date formatting issues in my research on sub-selects and reports, I came across Kevin Frank’s custom functions for FileMaker and SQL among his posts on the subject. My background is more MySQL than SQL, and there are a few quirks in FileMaker’s SQL that require slight changes in query syntax. Kevin Frank’s date format custom function is invaluable.
The other benefit is that SQL runs much faster than doing it natively in FileMaker script steps. So while it takes more time to figure out your SQL statement, if you have a really big FileMaker script taking a really long time because of relationships, calculations and updates, then doing it in SQL can usually speed things up a lot. I just find it interesting that SQL instructions to a FileMaker database are faster than FileMaker native instructions to the same database. Anyway, just remember that SQL is one of those tools to turn to when you have a poorly performing database and need some optimization.
SQL definitely speeds up some actions, and despite requiring a plugin to work, I think SQL capabilities now have become a valuable part of my FileMaker process. I just need to adjust all my ingrained MySQL commands over to the ANSI SQL92 that FileMaker uses.
Thanks for the SQL article Anders, I’m now interested in learning more. Do you recommend any beginner books?
Damon, my of the SQL I’ve encountered came from MySQL a few years ago, though I’ve done a little bit of work with Oracle and MS SQL. I’ve found that FileMaker’s SQL has certain nuances and quirks (likely standard SQL92, but different to me), that reading the ODBC documentation is very useful. Also, I’m looking at more SQL specific books, such as the following: SQL in a Nutshell, Learning SQL, and SQL Bible
Damon, I am a fan of SQL Queries for Mere Mortals.
How do you do variables in filemaker query: This is what i need:
declare @id int = 1
Select * from filemakertable where id=@id
but I can’t seem to do that.
You need to use the arguments parameter:
Let([
@id = 1;
sql = ExecuteSQL ( “select * from filemakertable where id = ? ” ; “” ; “” ; @id)
];
sql
)
so if I need to enter a varchar as parameter instead would the query you showed me be any different?
i.e declare @id varchar(50)=’5-8765′;
select * from filemakertable where id = @id
I’m not sure you need to specify the type, ie. varchar
When you declare the variables in the Let statement you don’t specify the type. That possibly would come into effect if you used an ODBC/JDBC query, but with internal FileMaker SQL in a calculation, I don’t believe it matters.