Tip: Add a Wayback Machine to your FileMaker Audit Logs

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:

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.

[ba-box background=”#778899″ border=”#708090″ textcolor=”#F7F7F7″][jetpack_subscription_form][/ba-box]