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]
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).
Thanks for posting – I’ love to see the server-side implementation also.
How do I get ahold of that server-side script?
Ah! Sorry about that–reading too fast. That script is pretty specific to our client’s log so it’s not something we’ve thought about posting. I’d like to make a more generic version, though, and *that’s* something we could post. =) I’ll see how feasible that is and post back next week. Thanks, Dorian.
Hello John. Is it “next week” yet? Just kidding. I’m really intrigued by this post and would love to see more about your server-side implementation. Anything further that you’re willing and able to take the time to share is greatly appreciated by me and many others I’m sure.
Try this, Dorian… http://help.filemaker.com/app/answers/detail/a_id/12083
I was looking for the *particular* script mentioned in this post.
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.
Bradford Mc Mullin
I hope you don’t mind me replying 3 years later, I only noticed this post today 🙂
Here is the link to version 2.0: https://www.1-more-thing.com/shop/logiciels/fm-auditlog-pro-2-0/