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

By April 29, 2016 September 5th, 2019 5 Comments
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.

Download the Session Files

Person holding a bag

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

Wim Decorte

Wim Decorte

Wim is a Senior Technical Solution Architect at Soliant. He is a FileMaker 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 and 18 Certified FileMaker Developer and the author of numerous Tech Briefs and articles on FileMaker Server. Wim is one of the very few multiple FileMaker Excellence Award winners and was most recently awarded the FileMaker Community Leader of the Year award at the 2015 FileMaker Developer Conference. He is also a frequent speaker at the FileMaker Developer Conference and at FileMaker Developer groups throughout the world. In addition to being a renowned expert on FileMaker Server, Wim also specializes in integrating FileMaker with other applications and systems. His pet project is the open source fmDotNet connector class that he created.


Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant