FileMaker’s Internal SQL Engine, part 1

Recently a client asked me if I could help her produce a somewhat unusual report. Normally reports are created to summarize, aggregate or otherwise analyze data; in this case she wanted to report on the database itself. Specifically, she wanted to see all the tables in her database, along with current record count and most recent modification timestamp per table. Ultimately we decided that a simple table view — rather than a formal report — was all she actually needed.

So, I created a new table, “tables”, and populated the table_name field manually, by typing in the table names. You might think that FileMaker’s “TableNames” function could help here, but the function actually returns table occurrence names, rather than table names. Once that was done, how to populate the other two fields?

The thought of creating 50 cartesian join relationships from my “tables” table to the other tables in the system wasn’t particularly appealing. Equally unappealing was the thought of the scripted convolutions I would need to go through to populate the table, though perhaps I would be able to use Evaluate() or GetField() to simplify things.

But fortunately we were using the MonkeyBread plug-in on this project, so another option readily presented itself. Why not use SQL instead? This would allow us to access data in any table without needing to create any additional relationships. So we did. To populate the record_count field was simply a matter of issuing this replace command:

MBS ( "FM.ExecuteSQL" ;
"SELECT COUNT ( * )
FROM " & Tables::table_name
)

In other words, count the records for each table. What about the most recent modification timestamp? That turned out to be a piece of cake also. Here’s the replace calculation for most_recent_ts:

GetValue (
MBS ( "FM.ExecuteSQL" ;
"SELECT zl_modification_ts
FROM " & Tables::table_name &
" ORDER BY zl_modification_ts DESC"
)
; 1 )

In plain English, for each table sort all the records by timestamp in descending order, and then grab just the first one (i.e., the most recent). The above syntax works, incidentally, because the modification timestamp field in every table is named zl_modification_ts. Consistency. It’s a good thing.

Unfortunately, FileMaker’s SQL implementation does not include the SQL “Limit” function, hence the use of GetValue above. But this illustrates a useful lesson — there’s nothing wrong with combining FileMaker syntax and SQL syntax… use whichever one makes the most sense at the moment.

Incidentally, the portions of code shown in grey above are specific to the MonkeyBread plug-in. If we used a different plug-in, the grey portion would be different, but the remainder of the code would not change. That’s because the numerous plug-ins that allow you to tap into FileMaker’s internal SQL engine are really just providing a conduit for your SQL code.

I’ll have more to say about FileMaker Internal SQL in upcoming postings. (For that matter, I also wrote about it the other day.) In the mean time…

Suggested reading:
1. FileMaker 11 OBDC and JDBC Guide (chapter 7: “Supported Standards”)
2. Wikipedia SQL Entry

Links to some of the SQL plug-in vendors (to the best of my knowledge, these are the only ones that are fully FM11-compliant):
1. MyFMButler (doSQL)
2. CNS (MMQuery)
3. Dracoventions (SQL Runner)