7 Replies Latest reply on Jan 20, 2014 4:19 PM by ngarnier

    CREATING AN AUDIT/MODIFICATION LOG

    RACHAELRIGG

      Title

      CREATING AN AUDIT/MODIFICATION LOG

      Post

            

           Hi there
            
           I've been trawling through forums trying to find a way to write a script that captures changes to fields as they are committed, capturing the following components:
           - timestame
           - account name of the active user
           - the field name of the modified field
           - its original value before the modification and
           - the new value
            
           Currently i can only get the first two auto-enter values to log in the database. Really need to capture "last visited record" and it's contents. 
            
           I've also tried to run the script with a related table in the same file AND related table in a separate database (thinking the latter may be less messy) but i've had limited success. 
            
           Any help would be SO appreciated.
            
           Thank you so much for your time.
            
           Rachael

        • 1. Re: CREATING AN AUDIT/MODIFICATION LOG
          philmodjunk

               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."

          • 2. Re: CREATING AN AUDIT/MODIFICATION LOG
            RACHAELRIGG

                 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 sales@excelisys.com 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.

               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 ; "¶" ; "•" ) ] ;
            

            Case (

            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 & "\"¶" )

            )
                      & AuditLog1

            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.

                  

            • 3. Re: CREATING AN AUDIT/MODIFICATION LOG
              philmodjunk

                   FileMaker 7 doesn't have all the tools available in current versions--espicially the use of script triggers.

              • 4. Re: CREATING AN AUDIT/MODIFICATION LOG
                bumper

                     try UltraLog, a bit more up to date:

                     http://www.nightwingenterprises.com/demosX/demoX01.html

                • 5. Re: CREATING AN AUDIT/MODIFICATION LOG
                  RACHAELRIGG

                       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 :)

                        

                  • 6. Re: CREATING AN AUDIT/MODIFICATION LOG
                    bumper

                         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.

                    • 7. Re: CREATING AN AUDIT/MODIFICATION LOG
                      ngarnier

                           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 sad