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 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.

About the Author:

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 and WooCommerce for websites 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 anytime for a free estimate and consultation about making your business more efficient through intuitive and effective software automation. 866-592-9235.

12 Comments

  1. Stephen April 27, 2018 at 4:53 pm - Reply

    Doug that is fantastic… I am looking at this and trying to determine if it is possible to tweak the CF to actually output the audit log in to a JSON array?

    {
    [
    {
    “acct” : “Stephen Dolenski”,
    “field” : “test”,
    “from” : “1”,
    “to” : “0”,
    “ts” : “4/27/2018 3:35:19 PM”
    },
    {
    “acct” : “John Doe”,
    “field” : “test”,
    “from” : null,
    “to” : “1”,
    “ts” : “4/27/2018 3:34:10 PM”
    }
    ]
    }

    • Doug West April 28, 2018 at 9:06 pm - Reply

      Yes, it’s absolutely possible to modify the eX_Audit custom function to output a JSON result. That would be done in these two lines of the calculation:

      Get ( CurrentHostTimestamp ) & “: ” & Get ( AccountName ) & ” changed ˂” & ~thisFieldName & “˃” & // Using “arrowhead” characters
      Case ( not ~isContainer ; ” from ” & Quote ( $prevValue ) & ” to ” & Quote ( $thisFieldValue ) )

      Since we’re just stringing text together, we could string the text together in the JSON tagging format or use the JSONSetElement() function introduced in FileMaker 16. I considered the same idea myself while developing this round of changes. However, if you’re not planning to parse the data into a records in a separate table, the JSON structure doesn’t seem to provide much of an advantage when trying to find the most recent revision to a specific field.

  2. Pau Lavender May 2, 2018 at 7:13 pm - Reply

    Doug, this is brilliant! I dropped this into a hosted file of about 23,000 records in the table I tried it on. Got about a 5 minute delay in regaining control. Dialog said updating…

    Looks like it updates mods going back aways. After that, worked perfectly. Needed an audit log this brilliant anlight weight forever. Thank you so much! The developer community is always so giving.

    • Doug West May 3, 2018 at 8:22 am - Reply

      You’re welcome! I’m glad you were able to put it to good use.

  3. Jared Kuvent May 3, 2018 at 4:53 am - Reply

    Doug, thank you so much for this exemplary audit trail and your generous donation to the community.

    In the project I am developing, which is a Melbourne University social enterprise research project, I am looking to write the details of the audit (timestamp, username, original value, modified value) into seperate fields. I’m sure by analysing your solution further, I could figure it out, or find a less efficient way of parsing the data that is being generated by the _audit auto enter field. Do you have any ideas or pointers on where I could look to doing this? In some of the custom functions that exist? Or perhaps by creating multiple auto-enter _audit fields?

    Perhaps I should learn how to wield more of the Execute SQL function… Any suggestions or pointers would be greatly appreciated.

    We will be listing your company in our list of sponsors and supporters on our research project website. Cheers.

    • Doug West May 3, 2018 at 8:38 am - Reply

      The eX_Audit custom function writes the new information to the audit/changelog history field. These two lines of the calculation are where you would want to start:

      Get ( CurrentHostTimestamp ) & “: ” & Get ( AccountName ) & ” changed ˂” & ~thisFieldName & “˃” & // Using “arrowhead” characters
      Case ( not ~isContainer ; ” from ” & Quote ( $prevValue ) & ” to ” & Quote ( $thisFieldValue ) )

      FileMaker’s ExecuteSQL() function supports only reading information with a SELECT statement. Maybe you could extend this a bit to ALSO write the information to another table at the same time by using the BE_FileMakerSQL function with the free BaseElements plugin from Goya:

      https://baseelementsplugin.zendesk.com/hc/en-us

      The custom functions could continue to operate as-is with a single audit field serving as an internal reference for the “from” values when a field is modified. By simply adding a bit of instruction to this custom function, you could also write to another table every time a field modification is logged with something that looks like:

      BE_FileMakerSQL ( “INSERT INTO MyAuditTable (FieldX,FieldY,FieldZ) VALUES (‘ValueX’,’ValueY’,’ValueZ’)” )

      You can wrap everything with FileMaker’s GetAsText() command to get the readable values you’re expecting.

      • Jared Kuvent May 3, 2018 at 8:59 pm - Reply

        Awesome tips and pointers, Doug. I hadn’t taken advantage of the Base Elements plugin, and I definitely will now. Gives me a lot to look into and tinker with. I’ll be sure to share what I come up with on your forum as well. Cheers.

  4. Roman July 22, 2018 at 1:14 pm - Reply

    Hello, Community,

    I have implemented this brilliant Audit log to my app and is working smooth,

    When My self imports record which is done weekly, now it takes too long to import them, record by record,

    Is therey anything we can do in order to improve the import process as before which was done very fast before implenting this audit log?

    • Doug West July 23, 2018 at 10:05 am - Reply

      The import is also logged in the audit trail if you leave the box checked to “Perform auto-enter options while importing”. Clearing that checkbox will suppress the logging during import but may have other unintended effects if you’re using auto-enter and lookup options elsewhere.

      You could also suppress the logging by setting a global variable before importing and modifying the auditing custom function to only evaluate when the global variable is not set.

      Of course, both of these approaches require you to disable the auditing to improve performance.

  5. Sam July 27, 2018 at 10:37 am - Reply

    Brilliant! I was reading the older version earlier today and only seeing this updated version now. For some reason my portal row changes are still not getting captured. Any idea what may cause this?

  6. Sam July 27, 2018 at 10:41 am - Reply

    Forgive me, all I needed to do was create a new record after copying the functions and now it works great! This is simply amazing, thanks so much for sharing with the community!

    • Doug West July 27, 2018 at 10:45 am - Reply

      Great! I was in the process of responding that the details are logged within the record where the changes were made. So changes to related records only show in the audit field on the related record and not the parent record. You clearly got past that point, since you’re now seeing the expected results.

Leave A Comment

Website Content and Design

Website Content and Design

Copyright © eXcelisys, Inc., all rights reserved. No unauthorized use permitted; all derivative works must have prior approval. All content – including design and copy within the eXcelisys website is protected under international copyright laws. eXcelisys will prosecute ANYONE who borrows or steals any derivative or facsimile of the content and design of eXcelisys’ website without written approval or permission. Read More

Trademarks

Trademarks

FileMaker®, FileMaker Pro® and the referencing FileMaker® logos are trademarks of FileMaker, Inc., in the United States, the European Union and other countries. MySQL® and MySQL® Logos are registered trademarks of MySQL AB in the United States, the European Union and other countries. The PostgreSQL elephant logo “Slonik” is a registered trademark of the PostgreSQL Global Development Group. Read More

Privacy Policy

Privacy Policy

We will never give or sell your email address or contact information to anybody, for any reason. There are no eXceptions. We do not make our customer list available to anyone outside of eXcelisys at all, ever.

There is no way that submitting your information to eXcelisys will result in you receiving “spam” or other junk mail from eXcelisys if you have requested us not to do so. Read More