FileMaker Pro 13 Tip-n-nTrick: Easy-Bake FileMaker Pro 13 Audit Trail
There are many ways to implement audit logs in FileMaker Pro 13, but I’m always on the lookout for the method with the least overhead. If you don’t need separate records in another table for every field change, then you can keep track of field changes using FileMaker 13’s new Get(ModifiedFields) command and the following:
- A custom function to handle the heavy-lifting
- A text field to call the custom function and store our change log
- A script step to set a list of ignored field names in a script that runs when the file opens
- A modification timestamp field to trigger the log
There’s a good chance that you already have a modification timestamp in your solutions, and the field to store the log itself would be required with any approach. So that means our “overhead” is limited to one custom function and one script step. That makes it quite easy to implement this FileMaker Pro 13 audit trail in any solution.
OK, so maybe that was a little too easy! The only problem I’ve found with this approach is that the initial values entered on a new record won’t get tracked unless we commit the record right after it’s created. This file has a custom menu set to override the New Record menu command with a script to take care of this nuance for us. If you already script the new record creation process in your solution, then you can incorporate this additional step into your existing script(s).
What do you think about our FileMaker Pro audit trail solution? Can we make this any easier while maintaining the simplicity, performance, and flexibility?
About eXcelisys, Inc.: Founded in 2001, eXcelisys (www.excelisys.com) is an FBA Platinum Partner and FileMaker Certified developer organization specializing in the designing, developing, customizing, supporting, consulting, migrating, upgrading, tweaking, fixing, and integrating of FileMaker Pro solutions, FileMaker Go solutions, MySQL, PostgreSQL, QuickBooks-FileMaker Pro Integration, Excel and MS Access FileMaker Pro conversions/migrations, iPhone and iPad business solutions, and other various database technologies and frameworks that automate your organization’s data solution needs for use on the web, mobile, and desktop platforms. Contact eXcelisys today for a free estimate and consultation about your business software automation needs @ 866-592-9235.
Great simple solution – exactly what I was looking for!
Thank you!
Thank you
The only big issue with your solution is that there is a big confusion when the log try to write the older value (from)
(it takes the LAST older value AND unfortunately not the old value from the modified field)
see below with the old value “peinture 4r” (that I did primary put on the field Description)
20/11/2015 20:51:33: Doug changed ˂TVAProdFacturee˃ from “200” to “20”
20/11/2015 20:51:11: Doug changed ˂MontantProdTTC˃ from “peinture 4r” to “200”
20/11/2015 20:51:11: Doug changed ˂TVAProd˃ from “peinture 4r” to “20”
20/11/2015 20:51:02: Doug changed ˂Description˃ from “” to “peinture 4r”
Any idea to resolve this bug ?
many thanks for your precious help
Guillaume (Paris)
Hello
I downloaded your last version, and it seams that the bug was corrected on the custom function (case) !! THANK YOU !!!!
you are the best 🙂
We aim to please, thank you!
Hi,
I just downloaded your demo file and incorporate it to my file but Im getting this result.
3/8/2016 9:19:36 PM: Admin changed ˂Material Name˃ from “” to “Pigment blue”
3/8/2016 9:19:36 PM: Admin changed ˂Material Code˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Expiry Date˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Retest Date˃ from “” to “3/10/2016”
3/8/2016 9:19:36 PM: Admin changed ˂Material Supplier˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Received by˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Quantity Received˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Unit˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Manufacturer˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Location˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material Manufacturing Date˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂Material PO Number˃ from “” to “”
3/8/2016 9:19:36 PM: Admin changed ˂ModStamp˃ from “” to “3/8/2016 9:19:36 PM”
3/8/2016 9:19:36 PM: Admin changed ˂Modification Timestamp˃ from “” to “3/8/2016 9:19:36 PM”
If I change my material name im getting this
3/8/2016 9:22:33 PM: Admin changed ˂Material Name˃ from “Pigment blue” to “Pigment pink”
3/8/2016 9:22:33 PM: Admin changed ˂ModStamp˃ from “3/8/2016 9:19:36 PM” to “3/8/2016 9:22:33 PM”
3/8/2016 9:22:33 PM: Admin changed ˂Modification Timestamp˃ from “3/8/2016 9:19:36 PM” to “3/8/2016 9:22:33 PM”
Can you please help me.
Thanks.
thong
Thong,
We provide these tips and tricks as a freebie and as-is, and we are very glad you are trying to incorporate this into your solution. In order for us to help you, you would need to engage our services. And we would be more than happy to assist you.
Thank you for visiting our blog and downloading the demo.
Hi!
Christo,
Thanks, love your solution, I did a small modification, included “exclusions” as parameter, that way don’t need Startup Script, works great…
Thanks again…
Fernando A.
Hi Fernando,
Could you please share your modified file.
Thank you.
Thong
Hi Thong!
Apologies for delay! We are experiencing a daily 4 to 8 hours energy cut-off for the next 40 days here in Venezuela! Hours vary!, I will prepare a sample and try to upload sometime tomorrow.
Fernando A
Hi Fernando,
Congrats
Could you please share your modified file by posting the link (dropbox or wetransfer)
Thank you.
Hi Thong!
Sample is ready, need your email to send it, is that possible?
Fernando A.
Hi Fernando,
I don’t know how can I send you a private message for my email.
Thanks.
thong
If you have a Dropbox account (or similar online file storage), you can simply provide a public link to the file without the need to exchange personal contact info.
Hi –
I am a Filemaker beginner and would like to ask where you have set variable Excelisys_ChangeLog as I couldn’t find it.
Excelisys_ChangeLog ( Get ( ModifiedFields ) ; Self ) ;
Appreciate your advise.
M&B
Hello Fernando,
Would mind sharing the modified file?
Appreciated.
Than You
M&B –
Excelisys_ChangeLog is not a variable. In the tip file, the Change Log field uses the Auto-Enter option to call the Excelisys_ChangeLog custom function, which returns the list of changes.
Hi Doug, Thanks for the tip.
Hi Thong,
Sorry for delay,
things here are quite complex here with Electricity, I created a dropbox account and shared a folder, hope this link takes you to the file. https://www.dropbox.com/s/l2rjsshwx0wf5jj/Audit%20Track%20%28mod%29.fmp12.zip?dl=0, if not please walk me through, first time using this service.
Fernando,
Hi Fernando,
Thank you so much. I really appreciate it.
Hi Doug –
thank you for your reply, appreciate it.
I am not using FMPro Advanced and would like to ask you if you kindly could share the ‘content’ of the custom function with me?
Thank you.
M&B
The formatting may be a bit hard to follow, but here’s the content of the custom function:
Let ( [
~thisField = GetValue ( fieldList ; 1 ) ;
~thisFieldType = FieldType ( Get ( FileName ) ; ~thisField ) ;
~changelog = changelog & ¶ ;
// Parse previous value for this field from the existing Change Log text
// This next step uses special “arrowhead” characters, not less than and greater than, to reduce the likelihood of finding a match within field contents
~prevValueStart = Position ( ~changelog ; “˂” & ~thisField & “˃” ; 1 ; 1 ) ;
~prevValueStart = Case ( ~prevValueStart > 0 ; Position ( ~changelog ; “\” to \”” ; ~prevValueStart ; 1 ) + 6 ) ;
~prevValue = Case ( ~prevValueStart > 0 ; Middle ( ~changelog ; ~prevValueStart ; Position ( ~changelog ; ¶ ; ~prevValueStart ; 1 ) – ~prevValueStart – 1 ) )
] ;
Case ( not IsEmpty ( ~thisField ) ;
List ( // Record a change for this field value if it’s not a calculation field, and it’s not in our ignored list
Case ( IsEmpty ( FilterValues ( ~thisField ; $$Excelisys_ChangeLogExclusions ) ) and Right ( LeftWords ( ~thisFieldType ; 1 ) ; 4 ) ≠ “Calc” ;
Get ( CurrentHostTimestamp ) & “: ” & Get ( AccountName ) & ” changed ˂” & ~thisField & “˃” & // Wrapping with “arrowhead” characters
Case ( MiddleWords ( ~thisFieldType ; 2 ; 1 ) ≠ “Container” ; ” from ” & Quote ( ~prevValue ) & ” to ” & Quote ( GetField ( ~thisField ) ) )
) ;
Excelisys_ChangeLog ( MiddleValues ( fieldList ; 2 ; ValueCount ( fieldList ) ) ; changelog ) // Call the custom function again for all remaining fields in the list
)
)
)
Nice and simple – thanks for sharing!
For security, you can set the Change Log field to be read only (the auto-enter calc will still be able to modify it), but there is an additional vulnerability… Any user with FileMaker Advanced who knows about the existence of $$Excelisys_ChangeLogExclusions can use the data viewer to change its value (to exclude additional fields). Therefore it would be better to hard-code this in the custom function or use a parameter to pass them in.
Great suggestions, Jason! The common argument against this approach to an audit trail is that the log goes away when the record is deleted. So if security is a concern, you’ll probably also want to script the record deletion process and capture the contents of the change log to another table before deleting the original record.
Does anyone know how to make this custom function include fields that received a calculated result?
As long as you’ve selected the setting to store the calculated results in the storage options of the calculation fields, then they should behave the same as any other data entry fields with this approach.
Brilliant, works great! Thanks for sharing.
Thank you for sharing this. Is there a license for incorporating the custom function to my solution?
No licensing is required. You’re free to use it as-is, or modify to fit your needs.
Of course, we won’t mind if you note that your approach was inspired by a tip you found at eXcelisys.com!
Thanks.
Hi Doug, thank you very much for sharing. Sorry for my bad english first of all…
Your system works like a charm…. only a request….with this brilliant method i can’t produce logs from related tables edited in portals…
Can you suggest me a modification? Or just a theorical approch for my needs?
Thank you. i’m learning a lot by you.
You’re welcome! The same concept can be applied to related tables by simply including the changelog field in each table. This requires a change to the custom function to properly capture the first modification to a related record. We’re working on an updated tip file that will likely be ready next week to address that issue.
Hi there,
This is a great tool, and it works well if you are mainly interested in knowing when fields were changed and what they were changed to, but it doesn’t work well with determining what that field was changed FROM, and I don’t know how to fix it. Often, if a record has not been changed recently, the “prevValue” is incorrect – it ends up being an old value from a totally unrelated field (maybe the last field that was modified?”, not the field currently being modified. However, if you then make another change to that exact field, it seems to fix the problem. See below
10/18/2017 11:26:50 AM: User1 changed ˂Status˃ from “Active” to “Inactive”
10/18/2017 11:26:45 AM: User1 changed ˂Status˃ from “584” to “Active”
The 545 was from an unrelated “Number of Units” field.
Any help with this would be greatly appreciated.
Thank you!
Thanks Caroline. Have you had this logging in your solution for a few years? I think that problem was fixed with an update in August 2015. I’d recommend pasting in the contents of the “Excelisys_ChangeLog” custom function from a new download to see if the accuracy improves.
Hey Doug,
Great tool, that’s exactly what I was looking for! And really glad you still answer questions after 2 years.
I have two questions, I’m sure it would help others as well :
1. In a previous post, you said you would probably update the demo file so we can use related records with the change log. The problem is when creating a portal record without the use of a script (adding info to the last portal row), the change log is not updated for the related record. I am assuming it’s because the related record is not committed right away, unlike a parent record via the custom menu. Did you find a way to fix this?
2. Is there any way to “save” the current info on each record to the change log? The records created prior to the change log only register “” when modified.Maybe finding a solution to #2 might also help with #1!
Thank you for your help!
I have a Data Entry layouts that captures the values to globals. Then a script does the new record script step and then sets the fields to the globals. Somehow this does not trigger the audit log feature and it’s not capturing the initial changes to any of the fields.
Do you a work around solutions to this issue?
Rudy –
Try adding a Commit Record script step immediately after the New Record step, then set the fields. This will “initialize” the record before you start modifying it.
Doug: This worked perfectly. Thanks
I really like your audit log. I don’t know if needed but any plans to upgrade to FileMaker 16?
No changes should be needed for this technique to work as intended in FileMaker 16. However, we’ve had an enhanced version in the works for a while now that should be ready soon.
What’s the best way to get notified when your new version becomes available?
Subscribe to our RSS feed. 🙂
Hi Doug
Thank you for posting this solution. It is very helpful. I use a “pay per solution” auditing log in another solution which is good in that you do not have to set up the new record commit but if you are making a solution for a client who cannot afford to purchase that product then this is a great alternative (if you can control the record create).
I have just implemented this into an existing system. How can I capture the starting point for existing records?
Replacing the contents of the Change Log field with the following calculated result (after you have copied in our custom function) should initialize the log for you, as long as you run it from a layout with all of the fields you want to audit:
Excelisys_ChangeLog ( FieldNames ( “” ; Get ( LayoutName ) ) ; Change Log )
This passes the names of all fields on the current layout into our custom function for capturing the current field contents. “Change Log” at the end is a reference to the log field in your solution (which may need to change based on your field naming), but the rest of the calculation can be left as-is.
I, too, am singing my praises… Thank you 🙂
Mike
Hello, I’m encountering the same problem as guesquiere mentioned above in 2016 (“The only big issue with your solution is that there is a big confusion when the log try to write the older value (from) (it takes the LAST older value AND unfortunately not the old value from the modified field)”), but he mentioned that the problem was fixed in a more recent version of the file, which I can’t seem to find. Where could I find this newer version (when I click on the link “Download demo file”, it seems to be the older version…) ?
Thank you in advance for your reply
sorry for my previous message, it was me who was doing something wrong apparently. Thank you very much for this wonderful tool !
Mathilde
This is really neat, thanks a lot for sharing! I see it discussed in a few comments above, but did you have any luck getting the solution to work with portal records where you add new records via the last row in the portal?
You’re welcome! Support for editing in portals was added along with a few other enhancements in our new version posted at:
https://excelisys.com/fmp-tip-n-trick-filemaker-pro-audit-trail-revisited/
This audit log is fantastic. It automatically ignores calculation fields, but is it possible for it to log when an unstored calculation changes?
Glad you like it! Unstored calculations can be a bit tricky and the answer to your question isn’t a simple yes or no. This audit feature will trigger anytime a field in the table is changed but unstored calculations could be defined to reference only related values. In that situation, values could change on the related/child record when no changes were made to the local/parent record and the audit log would not get updated. There are at least a few ways to approach this challenge and the one that comes to mind first would involve a script trigger when editing any of the data entry fields that could affect the unstored calculation. This script could insert the desired text directly into the audit log field as needed.