5 Replies Latest reply on Nov 6, 2011 7:59 AM by DanielM

    Audit trail in Filemaker pro 11

    DanielM

      Title

      Audit trail in Filemaker pro 11

      Post

      Is it possible to set up an audit trail in FMP 11?  That is a file recording all changes being made to other files by user name, date/time and change made.  Does this require FMP server? 

        • 1. Re: Audit trail in Filemaker pro 11
          mgores

          Maybe a table to log changes and a script that creates a new record in that table with modification date time stamp, user, etc.  Trigger that script to run on modification of any fields of concern.  You could even capture the field modified and pass that through as a variable or script parameter.

          • 2. Re: Audit trail in Filemaker pro 11
            philmodjunk

            I've played with this idea using a set of global variables to capture the original values and field names of any field that is modified. Then I used the OnRecordCommit trigger to run a script that logged all the field names and changes in a change history table.

            I found that this worked well for simple forms, but if you have portals for creating and editing data, things got more complex as you have more tables and more edit actions (such as creating and deleting a portal record) to track.

            It was still doable and I could even set up a system to roll back the record to previous values and selectively choose which edits to keep and which to discard.

            • 3. Re: Audit trail in Filemaker pro 11
              DanielM

               Hmmm...  Those are interesting suggestions.  Do you have any examples you would share with me, such as how you used the OnRecordCommit trigger?  Thanks.

              • 4. Re: Audit trail in Filemaker pro 11
                philmodjunk

                First, I set a pglobal variabls to the current field's value with the onObjectEnter trigger. OnObjectSave was then used to trigger a script that compared the current value to the value in the global variable. If it was different, it appended the field name and previous values to a pair of global variables so that they stored a return separated list of field names and their previous values since the last commit record step.

                This resulted in 3 global variables: $$OldValue, $$FieldNames; $$FieldValues

                To append the current field name to $$FIeldNames I used:

                Set Variable [$$FieldNames ; Value: List ($$FieldNames ; Get ( activeFieldtableName ) & "::" & Get ( ActiveFieldname ) ]

                The OnCommitRecord triggered script then:

                Used Set Variable to capture the current record's primary key value in a variable.

                Changed to a layout based on the change log record created one new record for each value in the two global variables holding the field names and their matching previous values, entering the field names and their previous values into text fields created for this purpose in the change log table. The script also logged the Pimary Key from the variable, A common "changeID" value so that an "Undo" script could pull up all the records from one such change log, the current timestamp and the current account name. These last two fields made it possible to see when the change was made and by whom. It then returned to the original layout.

                To revert a change, a script pulls up all the change log records with the current record's Primary Key and most recent ChangeID, then loops through them using Set Field By Name to reset the fields to their previous values.

                Note: by adding layout and/or table name fields to the change log. One such table can track changes on multiple tables and with multiple layouts.

                • 5. Re: Audit trail in Filemaker pro 11
                  DanielM

                   Wow, quite an answer.  I'll work on that.  Thanks for the in depth instructions.