7 Replies Latest reply on Aug 6, 2015 9:09 AM by tetrasync

    Recording changes to certain fields

    tetrasync

      Title

      Recording changes to certain fields

      Post

      Is there any specific way to log or record any changes made in the Filemaker?

       

      I'd like to be able to see a history of changes to certain fields, even if its the only last few entries.

      What's the best way to approach this apart from archiving my database every so often?

      Thanks in advance!

        • 1. Re: Recording changes to certain fields
          philmodjunk

          Regardless of this issue, you should archive your database on a regular basis and retain the copies.

          There are third party produced tools that provide audit tracking options.

          You can also create your own using script triggers and an added table to log the changes, thought it can be a bit of work to set up and make sure all eventualities are covered.

          Here's a basic outline:

          On each field where you want to add this audit tracking capability, set up the OnObjectEnter trigger to capture the previous value of the field in a global variable. The same exact variable and the same script can be used for each such field. The script is very simple:

          Set Variable [$$OldValue: value: Get ( ScriptParameter ) ]

          You then include the TableOccurrence::FieldName reference to this field in the optional script parameter box. This method works for all possible field formats--including popup menus where the menu deploys and a value is selected before the OnObjectEnter performed script can execute.

          Then you set up the onObjectSave trigger to perform a script that copies the value of $$OldValue and the results of Get ( ActiveFieldTableName ) &"::" & Get ( ActiveFieldName ) to a new record in your added ChangeLog table. A relationship linking the current record's primary key to a foreign key field in this change log then links this log record to the correct record in your original table. An "on Creation TimeStamp Field" in the ChangeLog table will automatically log the date and time when this change was logged. An OnCreationAccountName field can automatically log the account name of the user that made the change.

          • 2. Re: Recording changes to certain fields
            tetrasync

            Will this record more than one change though? I'd want an entire history of changes ideally.

             

             

            Also can OnObjectEnter triggers and OnObjectSave be set globally somehow? So that no matter what changes are made they are added to my archive table?

            Just to be clear can I set the triggers to the entire field as opposed to just a field on a layout? Will the trigger apply to every occurrence of this field on every layout I have?

            I'm assuming I'll need a field in the archive table for every field I want to archive?

            Apologies for the thousands of questions!

            • 3. Re: Recording changes to certain fields
              GuyStevens

              Hi Ben, I once made a video about this on my youtube channel. You can see it here.

              Greetings

              Guy Stevens

              • 4. Re: Recording changes to certain fields
                tetrasync

                AMAZING! Thanks Guy, will watch it tomorrow and let you know how I get on.

                • 5. Re: Recording changes to certain fields
                  philmodjunk

                  And to answer your question. Yes this will produce an entire history of the changes made to the fields you set up with this audit tracking. You'll be able to see what the previous value was, when it was changed and by whom. This can be set up in a portal listing each logged change to the record.

                  • 6. Re: Recording changes to certain fields
                    MarkBanin

                    Hi Ben

                    I have one running on my system.  Here ya go....

                    1) Create A field on the record called "Log"  or call it whatever you like

                    2) You need Two script triggers to run on each field: On Object Enter and On Object Save 

                    On Object Enter

                    Set Variable [$$Existing; Value: "Field 1"]  #This stores the contents of "Field 1" into the Variable "Existing"

                    On Object Save

                    Commit Records[Skip data entry validation; No dialog]

                    Set Variable [$$Current; Value: "Field 1"]

                    If [$$Existing=""]

                        Set Variable [$$Existing; Value: "<EMPTY>"]

                    End If

                    If [$$Current = ""]

                       Set Field [Table::Log;

                       "Field 1 Deleted, Previously '"&$$Existing&"'¶on " &Get ( CurrentTimestamp )&" by "&Get ( AccountName )&"¶¶"&Table::Log

                    Else   

                        Set Field [Table::Log;

                         "Field 1 changed from: '"&$$Existing&"' to: '"&$$Current&"'¶on " &Get ( CurrentTimestamp )&" by "&Get ( AccountName )&"¶¶"&Table::Log

                    End If

                    Set Variable [$$Existing; Value: ""]

                    Set Variable [$$Current; Value: ""]

                     

                    This will keep an updated log each time something changes in Field 1 or will record "<EMPTY>" if the contents are deleted.  It will store everything with the latest change always being at the top of the log so that you don't have to scroll down to eternity.

                     

                    Enjoy!

                     

                    • 7. Re: Recording changes to certain fields
                      tetrasync

                      Guy's video was really helpful!

                      All I had to do on top of his tutorial was to figure out how to have the audit sit in a different layout as opposed to being a portal within the layout you want to audit.

                      Super easy and clear to understand though! Thanks!