FileMaker Pro Audit Trail “Revisited”

By Doug West

Nearly four years have passed since our last Easy-Bake FileMaker Pro Audit Trail made its Internet debut. The same principles are still going strong, and now we’ve added some bells and whistles based on user feedback.

The steps to implement this revised FileMaker Pro audit trail technique in your own solution is as easy as:

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

Our primary goals in revisiting this FileMaker audit trail technique were to:

  • Remove the need for the New Record script included in the prior tip file
  • Audit all repetitions of repeating fields
  • Capture changes made through the use of the Replace Field Contents command

The first of these goals provides two benefits. Besides the direct effects of the simplification and reduced effort to implement, we also gained support for auditing changes in portal rows without scripting.

Performance is always a concern when it comes to background processing and we’ve done what we could to minimize the overhead. The impact may not be noticed when editing a single record. However, when using the Replace Field Contents command, the performance impact can quickly become apparent with even relatively small found sets. The Get(ModifiedFields) function, unfortunately, doesn’t report the field modified with a Replace. Instead, we’re forced to evaluate every field on the record and compare the current field contents to the latest corresponding value in the audit history. If a future version of FileMaker Pro provides us with this useful detail, performance will improve automatically without modifying these custom functions.

Of course, 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, why not simply capture the history to a background table through a scripted process before deleting the record?

The _audit field in this technique is also no longer dependent on a modification timestamp field in each table. If you know of an easier way to add an audit log to a FileMaker solution, we’d love to hear about it.

Enjoy using this revised version of the FileMaker Pro audit trail technique!

•• Download AuditTrailRevisited.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.