There are third party produced products for generating an "audit trail" on a table. While they cost you money, they also save you time creating and debugging your own solution.
If you are interested in "rolling your own", you can set the OnObjectEnter script trigger on each of your fields where the script parameter directly references the current value of the field. (If the field is for Invoice::Date, the script parameter would be Invoice::Date.)
Then you can set up a script like this:
Set Variable [$$OldValue ; Get ( ScriptParameter ) ]
This will work even for pop up menus where other methods to capture the original value will fail due to the triggered script not performing until after the user makes a menu pick.
Then you set up the OnObjectSave trigger to perform a script that creates a record in a Log Table if the value of the field is changed.. Get ( ActiveFieldTableName ) will return the name of the field. And you can set a field in the log table to the value of $$OldValue to preserve the original value of the field. Other set field steps in this script can log the:
Primary key of the record being edited
Account name of current user
Current Date, time or TImeStamp
You could also log the new value, but you don't actually need to, since the new value is stored in the current record you can just refer back to the value of the field for this record using the save dPrimary Key to access the new value.
You might want to set up additional triggers to trap for and log events such as deleting a record or creating a new one and Related records in a portal form their own special challenge here.
And instead of deleting a record, you may want to prevent users from actually deleting it. Have them mark the record "deleted" and then use scripts, relationships, portal filters etc to omit the "deleted" record from view and calculations. That way you can "undo" a delete just by finding the record and clearing the field that marked it as "deleted."
Hey Phil - thanks so much for your reply.
Overnight I found this site - http://www.excelisys.com/filemaker-tips-tricks-demos-downloads.php - that changed my world! Their guide to audit logs was so comprehensive and i've just successfully set up a log in my database. Very happy.
Thought i'd share their PDF doc with you. You can also download their Audit Log demo fp7 file (which really helped me in setting up the fields)
Creating an Audit Log in FileMaker Pro 7
by Andrew Persons
South Bend, IN 46637 firstname.lastname@example.org Temecula, CA 92592 www.excelisys.com
An oft-requested feature for FileMaker Pro solutions is an audit log – the ability to track relevant changes to a record. This document, along with the demo file, outlines how to do just that. This method will automatically track each change to the fields the developer specifies, including who made the change, when, what the old value was, and what the new one is.
This paper assumes that you are familiar with FileMaker Developer 7’s custom function feature, and with the concept of recursive functions. For more information on recursive functions, see the white paper Recursive Calculations in FileMaker Developer 7, available at www.excelisys.com.
We’ll start with a straightforward version of this method before moving to another approach that greatly simplifies the implementation for each table in a solution.
Example 1: Audit Log Basics
Our audit log system takes advantage of FileMaker Pro 7’s new ability to have an auto- enter calculation replace the contents of its field. We’ll be tracking five fields: Name, Address, City, State, and ZIP.
The first thing to do is define our audit log field, AuditLog1. Make it a text field and leave it at that for now.
Next, we need to create a field to hold the old values of the fields we’re tracking, so that we can report to the user what the previous value was, and what the new value is when a field is changed. Let’s call this one AuditFields1.
Substitute ( Name & "|" & Address & "|" & City & "|" & State & "|" & ZIP ;
Page 1 of 6 © 2004 Andrew Persons. All rights reserved.
[ "¶" ; "•" ] ; [ "|" ; "¶" ] ) &
Left ( AuditLog1 ; 0 )
This field will produce a return-delimited list of each field’s contents. It will replace any returns in any of the fields with a bullet ("•"). This is necessary since each modification to the record is stored on a separate line in the audit log, and any additional returns would greatly reduce the log’s clarity.
You probably also noticed the last line...
Left ( AuditLog1 ; 0 )
...and are wondering at its purpose. This ensures that the field is updated when the AuditLog1 field is updated. This is key.
Next, go into the field options for the AuditLog1 field and define an auto-enter calculation. Be sure to uncheck the “Do not replace existing value of field (If any)” option.
Let ( [
NamePrev = Substitute ( MiddleValues ( AuditFields1 ; 1 ; 1 ) ; "¶" ; "" ) ; AddressPrev = Substitute ( MiddleValues ( AuditFields1 ; 2 ; 1 ) ; "¶" ; "" ) ;
CityPrev = Substitute ( MiddleValues ( AuditFields1 ; 3 ; 1 ) ; "¶" ; "" ) ; StatePrev = Substitute ( MiddleValues ( AuditFields1 ; 4 ; 1 ) ; "¶" ; "" ) ;
ZIPPrev = Substitute ( MiddleValues ( AuditFields1 ; 5 ; 1 ) ; "¶" ; "" ) ;
NameCur = Substitute ( Name ; "¶" ; "•" ) ; AddressCur = Substitute ( Address ; "¶" ; "•" ) ; CityCur = Substitute ( City ; "¶" ; "•" ) ; StateCur = Substitute ( State ; "¶" ; "•" ) ; ZIPCur = Substitute ( ZIP ; "¶" ; "•" ) ] ;
NameCur ≠ NamePrev ; Get ( CurrentHostTimeStamp ) & ": " & Get ( AccountName ) & " changed " & Get ( ActiveFieldName ) & " from \"" & NamePrev & "\" to \"" & NameCur & "\"¶" ;
AddressCur ≠ AddressPrev ; Get ( CurrentHostTimeStamp ) & ": " & Get ( AccountName ) & " changed " & Get ( ActiveFieldName ) & " from \"" & AddressPrev & "\" to \"" & AddressCur & "\"¶" ;
Page 2 of 6 © 2004 Andrew Persons. All rights reserved.
CityCur ≠ CityPrev ; Get ( CurrentHostTimeStamp ) & ": " & Get ( AccountName ) & " changed " & Get ( ActiveFieldName ) & " from \"" & CityPrev & "\" to \"" & CityCur & "\"¶" ;
StateCur ≠ StatePrev ; Get ( CurrentHostTimeStamp ) & ": " & Get ( AccountName ) & " changed " & Get ( ActiveFieldName ) & " from \"" & StatePrev & "\" to \"" & StateCur & "\"¶" ;
ZIPCur ≠ ZIPPrev ; Get ( CurrentHostTimeStamp ) & ": " & Get ( AccountName ) & " changed " & Get ( ActiveFieldName ) & " from \"" & ZIPPrev & "\" to \"" & ZIPCur & "\"¶" )
First, we define some variables using the Let() function. Each one in the first set of five is designed to hold the appropriate value from the AuditFields1 field. This will be the value prior to the current modification.
The second set of five variables is the current value of each field. We substitute a bullet (“•”) for returns to be consistent with AuditFields1, so that each field’s contents can be accurately compared with any modifications.
Next, we simply have a case statement that compares each old value with each new value, and reports the change if they don’t match. Then it appends AuditLog1 to the end, so that each change gets added to the top, building a list of changes over the life of the record.
The key to this method is defining AuditLog1 first and then putting AuditLog1 in the definition for AuditFields1. When one of the tracked fields is modified, AuditLog1’s auto-enter calculation is evaluated. Because AuditLog1 was created first, this happens before AuditFields1 has calculated. Since AuditFields1 still has the old values, they can be compared with the new values and reported if different. Then, AuditFields1 evaluates and stores the new values, ready for comparison at the next modification.
FileMaker 7 doesn't have all the tools available in current versions--espicially the use of script triggers.
Yeah wanted to avoid getting a 3rd party program in unless I really had to. The above is working perfectly for me - thanks for your help though :)
UltraLog is a update on what you are using. It is free and an improvement that works much better in current versions of FileMaker. It does use custom functions so you have to have FMPA to install.
Regardless, one weakness of all "roll your own" audit logs is tracking deletions. This can be handled with custom menus and scripting all deletions to trigger the logging.
I'm using the same PDF from excelisys to make my audit log in FM13 and I've run into the problem of my calculation being too long, way over 30,000 characters. Any suggestions? I'm at the end of my rope. I have a few tables that have about 100 data fields and I don't want to keep having to split them. With the manual audit log I have to enter every single field name into the calculation and it's a nightmare. Is there anyway to do a manual audit log that works on the table level like CNS audit?, which I can't use because they haven't upgraded to FM13 yet