A Bag of Goodies: ExecuteSQL(), Named Buckets, RelationInfo…

Back in 2014, I did a DevCon session on “Deep-Dive Scripting, an Audit log…”. The end-result of getting an audit log was not as important as the journey among the new and old scripting and function features. On the various FileMaker forums I see a lot of questions that are related to some of the features that I used in that presentation so we are offering the slide deck and the demo files here for people to take apart and discover.

Get the Session Files

A quick overview of what you will find in there:

1. A demonstration of when to be careful with ExecuteSQL() calls when you have an open record in the target table (it forces the FileMaker client to download all the data in the table from FileMaker Server). That’s in the ‘COR011_WimDecorte_ExecuteSQL_PerformanceTest.fmp12’ file. If you have not seen this in action, it is a real eye-opener.
2. Since the purpose of the demo was to create a proof-of-concept for an audit log, I had to solve the issue of collecting both the old data and the new. But I did not want to do that ahead of time, I wanted to only do that when the user commits a record. So how do you get the data? There’s a flow-chart in the demo that shows it all but the short version is:
   • just before the commit (and the OnCommit trigger is a pre-event trigger meaning that the commit has not happened yet), the client has all the new data, the server still has the old data. So we ask the client for the new data and the server for the old.
   • obviously we want to ask for just the data that was changed (which is where the Get( ModifiedFields) function comes into play).
   • knowing the behavior of #1 above we can’t very well use ExecuteSQL() to ask the client for the values of the changed fields. So we have to find other ways of doing that.  But we CAN use ExecuteSQL() through a ‘Perform Script on Server” script step to get the old data.
   • since we want the audit-log to be generic and just work on whatever layout with whatever portals on it. So one of the interesting challenges was how to use the FileMaker meta-data tables (FileMaker_Fields and FileMaker_Tables) and the design functions to interrogate a layout and create a generic SQL query from it. Features used: RelationInfo(), Get(RecordOpenState) and Get(RecordOpenCount), FieldNames() and FieldType()
   • oh, and also used the Base64Encode and Decode functions to work with container data.
   • and of course one of my favorites: ‘Named Buckets’.  That’s when you specify a variable’s repetition by calling it by name:

$myVariable[ Code( “Wim” ) ] = “something”

is the same as:

$myVariable[ 1090010500087 ] = “something”

Enjoy the demo files and post here with any questions or find me on the FileMaker Community forum or FMforums.com