Leverage your FileMaker Audit Logs

Here is a common reporting request: the customer has a project with a status field that changes over time. They’d like a report that shows what the status of the project was on a given date in the past. This can be pretty hard to do if you hadn’t anticipated this request to begin with. Fortunately, running a FileMaker audit log isn’t that tough and makes answering questions like this much simpler.

In our case we had been running a version of Ray Cologon’s Ultra Log and it had been tracking changes to this status field.  If you’ve never used it before, Ray’s routine stores changes as a paragraph of text in a new field within the edited record. It looks like this:

FileMaker Audit Log by Ray Cologon

Stock results from Ultra Log

We added a server-side script that explodes each of these entries into it’s own record in a new table (along with a couple other enhancements like capturing the name of the script running during any changes). We then clear out the original audit-log field. (Credit to Fabrice Nordman for inspiring this technique of turning entries into their own records. His FileMaker audit log is awesome and you’ll find a great video walkthrough here.)

With this data now stored as records, we could construct a SQL query to return the value of any field for a given date or time. By bundling this SQL into a custom function, we now have a very tidy way of grabbing the value of any field for any date in the past:

SeedCode_WaybackMachine ( ProjectID, FieldName ; Date )

In action, this looks like:

SeedCode_WaybackMachine ( “AC99215A-9506-485F-A14E-36339E2AEB2D” ; GetFieldName ( Project::Status ) ; Date ( 9 ; 1 ; 2013 ) )

(The actual SQL is specific to our solution but if you need help making queries like this, be sure to check out our free SQL Explorer. It’s a big help. Credit to Jason Young and Lisette Wilson, both for explorer and for Wayback itself.)

We’ve been finding a lot of uses for this since we first created it for some financial reports. A couple of not-so obvious things about this are pretty cool:

• Our audit table is pretty big, so we split it up into one table per year: we’ll be able to alter the SQL query inside the function to search the correct table based on the date passed in.

• Since we’re only interested in the facts about “projects” (as opposed to other entities in the solution like contacts) we also have a parameter for the primary key of the project, this let’s us know which record’s status we’re interested in. In the future we may add an “entity name” parameter so we could query facts about contacts, etc. as well.

• The server side script which explodes these audit-entries into records runs hourly, a “refresh” button in the log’s interface will also run this on demand using Perform Script on Server so our log browser will effectively be real-time.

• Having these entries as their own records means they’d survive the deletion of project records, though records like that aren’t able to be deleted in this solution, just moved to an archived status.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Tagged with →  

10 Responses to Tip: Add a Wayback Machine to your FileMaker Audit Logs

  1. John, very compelling as is Matt’s video. FYI I tried to look into Fabrice’s software on his website and it appears it is no longer available. I will look at Ray Collogans and WorldSync’s FMDataGuard software.

    Thank you,

    Bradford Mc Mullin

  2. Dorian Cougias says:

    How do I get ahold of that server-side script?

  3. Doug Staubach says:

    Thanks for posting – I’ love to see the server-side implementation also.

  4. seedcode says:

    Doug, Dorian,
    Thanks for the comments. This is a little hard to abstract away from what we’ve done for this one client so I just haven’t had the time to dive in. When I do I’ll post notes here. For the adventurous, it’s really just a bunch of text parsing to run through the log and create individual records from the entries. Note: you do want to make sure those records have been committed before you blow out the original entry. This is where FileMaker transactions come in: https://www.geistinteractive.com/filemaker-transactions/ (get started with the videos on the right side of the page).

Leave a Reply

Your email address will not be published. Required fields are marked *

Share →
DOWNLOAD
DayBack Calendar
DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
DOWNLOAD
TimeZync
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these