{"id":6438,"date":"2014-01-21T12:39:56","date_gmt":"2014-01-21T20:39:56","guid":{"rendered":"http:\/\/www.seedcode.com\/?p=6438"},"modified":"2014-01-21T12:39:56","modified_gmt":"2014-01-21T20:39:56","slug":"filemaker-audit-log","status":"publish","type":"post","link":"https:\/\/seedcode.com\/filemaker-audit-log\/","title":{"rendered":"Tip: Add a Wayback Machine to your FileMaker Audit Logs"},"content":{"rendered":"

Leverage your FileMaker Audit Logs<\/h2>\n

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.<\/p>\n

In our case we had been running a version of\u00a0Ray Cologon’s Ultra Log<\/a>\u00a0and it had been tracking changes to this status field. \u00a0If 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:<\/p>\n

\"FileMaker<\/a>
Stock results from Ultra Log<\/figcaption><\/figure>\n

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\u00a0audit log<\/a>\u00a0is\u00a0awesome<\/em> and you’ll find a great video walkthrough here<\/a>.)<\/p>\n

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:<\/p>\n

SeedCode_WaybackMachine ( ProjectID, FieldName ; Date )<\/strong><\/p>\n

In action, this looks like:<\/p>\n

SeedCode_WaybackMachine ( “AC99215A-9506-485F-A14E-36339E2AEB2D” ; GetFieldName ( Project::Status ) ; Date ( 9 ; 1 ; 2013 ) )<\/p>\n

(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<\/a>. It’s a big help. Credit to Jason Young and Lisette Wilson, both for explorer and for Wayback itself.)<\/p>\n

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:<\/p>\n

\u2022 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.<\/p>\n

\u2022 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.<\/p>\n

\u2022 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.<\/p>\n

\u2022 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.<\/p>\n

[ba-box background=”#778899″ border=”#708090″ textcolor=”#F7F7F7″][jetpack_subscription_form][\/ba-box]<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u00a0Ray Cologon’s Ultra Log\u00a0and it had been tracking changes to this status field. \u00a0If 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\u00a0audit log\u00a0is\u00a0awesome 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: \u2022 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. \u2022 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. \u2022 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. \u2022 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]<\/p>\n","protected":false},"author":1,"featured_media":6440,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[7],"class_list":["post-6438","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-filemaker-13"],"yoast_head":"\nLeverage FileMaker Audit Logs: add a Wayback Machine for your Data<\/title>\n<meta name=\"description\" content=\"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/seedcode.com\/filemaker-audit-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Leverage FileMaker Audit Logs: add a Wayback Machine for your Data\" \/>\n<meta property=\"og:description\" content=\"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/seedcode.com\/filemaker-audit-log\/\" \/>\n<meta property=\"og:site_name\" content=\"SeedCode\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/seedcoder\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-21T20:39:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png\" \/>\n\t<meta property=\"og:image:width\" content=\"109\" \/>\n\t<meta property=\"og:image:height\" content=\"110\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"seedcode\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dayback\" \/>\n<meta name=\"twitter:site\" content=\"@dayback\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"seedcode\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/\"},\"author\":{\"name\":\"seedcode\",\"@id\":\"https:\/\/seedcode.com\/#\/schema\/person\/af42f3f8cbfc8652fb0ffbb3f46f260f\"},\"headline\":\"Tip: Add a Wayback Machine to your FileMaker Audit Logs\",\"datePublished\":\"2014-01-21T20:39:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/\"},\"wordCount\":573,\"commentCount\":10,\"publisher\":{\"@id\":\"https:\/\/seedcode.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png\",\"keywords\":[\"FileMaker 13\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/seedcode.com\/filemaker-audit-log\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/\",\"url\":\"https:\/\/seedcode.com\/filemaker-audit-log\/\",\"name\":\"Leverage FileMaker Audit Logs: add a Wayback Machine for your Data\",\"isPartOf\":{\"@id\":\"https:\/\/seedcode.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png\",\"datePublished\":\"2014-01-21T20:39:56+00:00\",\"description\":\"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/seedcode.com\/filemaker-audit-log\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage\",\"url\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png\",\"contentUrl\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png\",\"width\":109,\"height\":110},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/seedcode.com\/filemaker-audit-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/seedcode.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tip: Add a Wayback Machine to your FileMaker Audit Logs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/seedcode.com\/#website\",\"url\":\"https:\/\/seedcode.com\/\",\"name\":\"SeedCode\",\"description\":\"Build the Software You've Always Wanted\",\"publisher\":{\"@id\":\"https:\/\/seedcode.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/seedcode.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/seedcode.com\/#organization\",\"name\":\"SeedCode\",\"url\":\"https:\/\/seedcode.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/seedcode.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2022\/12\/SeedCodeLogo.png\",\"contentUrl\":\"https:\/\/seedcode.com\/wp-content\/uploads\/2022\/12\/SeedCodeLogo.png\",\"width\":595,\"height\":189,\"caption\":\"SeedCode\"},\"image\":{\"@id\":\"https:\/\/seedcode.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/seedcoder\",\"https:\/\/x.com\/dayback\",\"https:\/\/www.linkedin.com\/company\/seedcode\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/seedcode.com\/#\/schema\/person\/af42f3f8cbfc8652fb0ffbb3f46f260f\",\"name\":\"seedcode\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/seedcode.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f0c386c5b845ee7fe111b36b2d87e983811f1f549682de9597a934b9dfd45469?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f0c386c5b845ee7fe111b36b2d87e983811f1f549682de9597a934b9dfd45469?s=96&d=mm&r=g\",\"caption\":\"seedcode\"},\"sameAs\":[\"https:\/\/seedcode.com\"],\"url\":\"https:\/\/seedcode.com\/author\/seedcode\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Leverage FileMaker Audit Logs: add a Wayback Machine for your Data","description":"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/seedcode.com\/filemaker-audit-log\/","og_locale":"en_US","og_type":"article","og_title":"Leverage FileMaker Audit Logs: add a Wayback Machine for your Data","og_description":"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.","og_url":"https:\/\/seedcode.com\/filemaker-audit-log\/","og_site_name":"SeedCode","article_publisher":"https:\/\/www.facebook.com\/seedcoder","article_published_time":"2014-01-21T20:39:56+00:00","og_image":[{"width":109,"height":110,"url":"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png","type":"image\/png"}],"author":"seedcode","twitter_card":"summary_large_image","twitter_creator":"@dayback","twitter_site":"@dayback","twitter_misc":{"Written by":"seedcode","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#article","isPartOf":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/"},"author":{"name":"seedcode","@id":"https:\/\/seedcode.com\/#\/schema\/person\/af42f3f8cbfc8652fb0ffbb3f46f260f"},"headline":"Tip: Add a Wayback Machine to your FileMaker Audit Logs","datePublished":"2014-01-21T20:39:56+00:00","mainEntityOfPage":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/"},"wordCount":573,"commentCount":10,"publisher":{"@id":"https:\/\/seedcode.com\/#organization"},"image":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage"},"thumbnailUrl":"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png","keywords":["FileMaker 13"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/seedcode.com\/filemaker-audit-log\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/seedcode.com\/filemaker-audit-log\/","url":"https:\/\/seedcode.com\/filemaker-audit-log\/","name":"Leverage FileMaker Audit Logs: add a Wayback Machine for your Data","isPartOf":{"@id":"https:\/\/seedcode.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage"},"image":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage"},"thumbnailUrl":"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png","datePublished":"2014-01-21T20:39:56+00:00","description":"This simple technique lets you query your FileMaker audit log to retrieve the value of any field for any date in the past. Uses FileMaker SQL.","breadcrumb":{"@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/seedcode.com\/filemaker-audit-log\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#primaryimage","url":"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png","contentUrl":"https:\/\/seedcode.com\/wp-content\/uploads\/2014\/01\/CAR.png","width":109,"height":110},{"@type":"BreadcrumbList","@id":"https:\/\/seedcode.com\/filemaker-audit-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/seedcode.com\/"},{"@type":"ListItem","position":2,"name":"Tip: Add a Wayback Machine to your FileMaker Audit Logs"}]},{"@type":"WebSite","@id":"https:\/\/seedcode.com\/#website","url":"https:\/\/seedcode.com\/","name":"SeedCode","description":"Build the Software You've Always Wanted","publisher":{"@id":"https:\/\/seedcode.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/seedcode.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/seedcode.com\/#organization","name":"SeedCode","url":"https:\/\/seedcode.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/seedcode.com\/#\/schema\/logo\/image\/","url":"https:\/\/seedcode.com\/wp-content\/uploads\/2022\/12\/SeedCodeLogo.png","contentUrl":"https:\/\/seedcode.com\/wp-content\/uploads\/2022\/12\/SeedCodeLogo.png","width":595,"height":189,"caption":"SeedCode"},"image":{"@id":"https:\/\/seedcode.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/seedcoder","https:\/\/x.com\/dayback","https:\/\/www.linkedin.com\/company\/seedcode\/"]},{"@type":"Person","@id":"https:\/\/seedcode.com\/#\/schema\/person\/af42f3f8cbfc8652fb0ffbb3f46f260f","name":"seedcode","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/seedcode.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/f0c386c5b845ee7fe111b36b2d87e983811f1f549682de9597a934b9dfd45469?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f0c386c5b845ee7fe111b36b2d87e983811f1f549682de9597a934b9dfd45469?s=96&d=mm&r=g","caption":"seedcode"},"sameAs":["https:\/\/seedcode.com"],"url":"https:\/\/seedcode.com\/author\/seedcode\/"}]}},"_links":{"self":[{"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/posts\/6438","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/comments?post=6438"}],"version-history":[{"count":0,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/posts\/6438\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/media\/6440"}],"wp:attachment":[{"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/media?parent=6438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/categories?post=6438"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/seedcode.com\/wp-json\/wp\/v2\/tags?post=6438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}