FileMaker Pro Audit Trail “Revisited”

By Doug West

Nearly ten years have passed since our Easy-Bake Audit Trail made its Internet debut! The same principles are still going strong, and now we’ve refined the approach based on user feedback.

The steps to implement this revised technique in your solution are as easy as:

1. Copy the one custom function from this demo file and paste it into each file of your app.
2. Copy the _audit field and paste it into each table of your app.
3. (Optional) Add excluded field names in a script that runs when the first window opens.

This audit mechanism relies on FileMaker’s Get(ModifiedFields) function. Unfortunately, that function has suffered a bit of bugginess over the years. Recent FileMaker Pro release notes call out the following issues and enhancements:

FileMaker Pro 2023 (20.1.1):

The Get(ModifiedFields) function now returns fields containing data when a new record is created.

The Get(ModifiedFields) function incorrectly returned an empty result for fields modified with replace field contents, import, relookup, or drag and drop operations.

FileMaker Pro 2023 (20.1.2):

The Get ( ModifiedFields ) function failed to return all field modifications when a field repetition other than the first repetition was modified.

The enhancement to include the appropriate fields when creating a new record, as well as the fix for repeating fields, allows us to remove the more time-consuming elements of this tip file’s previous iteration. It is no longer necessary to check every field in the table for changes when auditing new records and we no longer have to check every repetition of a repeating field. These improvements greatly improve performance as long as you have already updated to FileMaker Pro 2024 (21.0.1) or later.

Using the While() function instead of recursion in the custom function doesn’t have much of an impact on performance but could affect memory usage in some situations.

As was noted in prior revisions of our audit trail tip, this type of audit log stores the history on the same record as the current values. So what happens to the history when you delete the record? The history gets deleted along with it. A variety of solutions have been proposed to address this situation. If you’re concerned about this dilemma, the history can be copied to a background table through a scripted process before deleting the record.

•• Download AuditTrailRefined.zip ••

**This article is provided for free and as-is, use, enjoy, learn, and experiment at your own risk – but have fun! eXcelisys does not offer any free support or free assistance with any of the contents of this blog post. If you would like help or assistance, please consider retaining eXcelisys’ FileMaker Pro consulting & development services.

About eXcelisys, Inc.: Founded in 2001, eXcelisys (www.excelisys.com)is an FBA Platinum Partner and FileMaker Certified developer organization. eXcelisys specializes in designing, developing, customizing, supporting, consulting, migrating, upgrading, fixing, and integrating of database solutions for Desktop, Mobile, and Web applications. Our core technology competencies are FileMaker Pro, FileMaker Go, and MySQL for database frameworks, along with FileMaker WebDirect, WordPress, MySQL, PHP, CodeIgniter, PostgreSQL, Joomla, Drupal, Magento, CSS, HTML5, and Javascript for web sites and web applications. Aside from providing eXcellent customer service, our goals are to use these technologies to intuitively automate your organization’s data solution needs seamlessly and flawlessly across the web, mobile, and desktop platforms. Contact eXcelisys today for a free estimate and consultation about making your business more efficient through intuitive and effective software automation. 866-592-9235.

eXcelisys, Inc. is an independent entity and this web site/information/blog post has not been authorized, sponsored, or otherwise affiliated with FileMaker, Inc. FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries.