Thoughts about OnWindowTransaction

[MonkeybreadSoftware]

Recently in the FM Training Livestream, we talked about the new OnWindowTransaction trigger in FileMaker 2023 version. 

We looked into the Creating audit logs with the OnWindowTransaction script trigger article on the Claris Engineering blog. You may also want to check the blog article from Soliant here: Transaction Logging in FileMaker 2023.

 

We got a few questions and thoughts and we look forward to have someone make a session about the trigger for next Claris Engage to talk about all the little details, but let me start here with a few thoughts and possible pitfalls:

• OnWindowTransaction trigger is defined per file, so one script for all tables in that file and your script may need to look for which table it was called.
• The field named in the OnWindowTransaction trigger can have any content. It is usually JSON, but it doesn't need to be JSON, but could also be a list of values or XML.
• There is a possibility to have a typo in the field named for OnWindowTransaction trigger. Future tools to process DDRs may check this. If a field is defined there, it should exist in all tables and always be spelled the same way.
• You may want to avoid logging to a table in the same file. If you would do so, you need to avoid recursion as you could easily audit the changes to the audit log table. e.g. check the table name early in the script and exit if it is a table, you don't want to monitor.
• The Audit Log gets huge quickly. Preferable keep it separate from your normal database. Whether you use a text file, push changes to a FileMaker database or some external one, you may need to find a strategy as you may easily get several GB of log data. e.g. rotate the audit file by using a new blank file every week/month and move the old one to backup place or regularly delete old entries via script. 
• When you have audit log data somewhere, you can show it in a portal next to a record to show changes like our audit example file we have included with the MBS FileMaker Plugin. More advanced may be an undo button next to the old value.
• Make sure nobody can alter or delete audit log entries or prevent the audit script from running. Audit is to find mistakes made by staff, so they should not be able to change a record and delete logs.
• If you do an import, your trigger is called once after the import. Be prepared to get a huge JSON with details to all the imported records. Importing 10000 records? Your JSON may be 5 MB big!
• You can have your script trigger uses PSOS to delegate the work to the server. But there you may run into problems if you exceed the number of simultaneous PSOS sessions. And if you pass the JSON and it exceeds 1 MB, your script parameter can be truncated!
• With MongoDB functions, you can push changes to the MongoDB directly very efficiently. Fine tune the credentials, so you use an insert-only account for pushing audit data to server and avoid anyone in the company editing/deleting these records. This makes the audit log over time a great offsite backup.
  See OnWindowTransaction and MongoDB
• Calling "Halt Script" in the script editing a record seems to prevent the OnWindowTransaction trigger from running.
• If a script does multiple commits, you may get multiple times the script triggered. This includes multiple triggers for the same record changed multiple times. Since the triggers are queued, the queue can get longer and it may take some time later to process them.
• You can use Open Transaction script step to collect multiple changes and get one trigger call later with Commit Transaction script step.
• The performance impact for logging depends heavily on how quick your calculation for the audit field is and how quick your trigger script performes. Sometimes the script has more steps than the scripts causing the changes.
• The example custom function from Claris Engineering blog uses SQL to fetch the list of fields from the schema. That query can be slow or fast depending on whether the field list is queried from the server.
• If FileMaker Pro crashes for some reason, the window gets closed or you just loose connection to the server, all pending OnWindowTransaction trigger calls are lost.
• Your OnWindowTransaction script may run in any context. Please use New Window script step to create a new card window to make your own context, if you need to go to a layout to create a new record. Please make sure you return to same context (by closing card window) to avoid the user's current state. The card window used may have all fields out of sight and have a message text saying "Please wait", if it gets to be seen.
• As Clay Maeckel (Chief Software Architect at Claris) shared on the Going under the hood with Claris engineering webinar for FileMaker 2023, the ideas leading to this feature came together at the Pause On Error event last year and they got a prototype shown there. One more good reason to attend events: You can tell Claris staff about the problems you have and they listen and provide a solution.
• On FileMaker Pro, all scripts perform one after the other, but on the server, they may run in parallel. Your OnWindowTransaction trigger may run in parallel for multiple PSOS sessions as well as in Web Direct and Data API processes. Using a simple text file is not recommended in that case. But writing to a FileMaker database, SQL database or MongoDB server gets coordinated by them.
• If you like to query old values of a record for the trigger, you could do this: In the OnRecordLoad trigger, you query the Audit Field to get the same JSON as for the trigger later and put it in a global variable.
• If you reference a table from another file, the trigger runs in the current file for the external table. So your trigger can be responsible for changes in other files and thus the trigger in the other file doesn't run for the changes as only one trigger runs.
• The Get(RecordOpenState) function can be used in the calculation and be checked for whether this is a new record, a modified one or a deleted one.
• You can prevent the audit logging to run by just opening the database with an older FileMaker Pro version. Use the file options dialog to require version 20 or check in the startup script.

Most of this changes are similar to the issues we saw with Audit functions in MBS FileMaker Plugin, which still can be useful for you.

All as of FileMaker version 20.1.2. Future versions may change the behavior. Please let us know if you have additions or comments to this list.