Select * From FileMaker

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.