2 Replies Latest reply on Oct 15, 2012 9:50 AM by philmodjunk

    Audit Trail

    eibcga

      Title

      Audit Trail

      Post

           Hi, I have looked on this forum for various audit-trail or change-tracking type of subjects and I think I have an idea how to do this by using a combination of script triggers, variables and set fields in a script.  What I'm having difficulty with is applying those tips to my financial database.

           I have created an Audit table which will track any records in the Ledger or Journal tables that were MODIFIED or DELETED.  I will not require a dialogue box to input the reason for the change or deletion.

           My screen shot shows the grey object tables to store Contacts, Subaccounts, Accounts, Account Groups, and Account Types. I have green event tables to store Transactions (aka Journal table) and Transaction Line Items (aka Ledger table).  Red tables are used for special queries.

           Essentially, I would like to be able to track in the Audit table whenever ANY field in the Journal or Ledger table is modified or deleted.  Thus, I have setup the pertinent fields in the new Audit table that I would like tracked.  It also includes fields of other tables that will not change, but are included for information purposes, such as the transaction ID or line item ID.

           Since any given transaction or transaction line item record could potentially be changed many times, or deleted, what relationship would I establish with the Audit table?  Would it be the Journal table or the Ledger table that links to the Audit table?  I plan to add related fields to the Audit table after the relationship has been established.

           Thank you in advance for any guidance.

            

      Screen_Shot_2012-10-13_at_12.44.15_PM.png

        • 1. Re: Audit Trail
          eibcga

               Ok, I've taken a shot at this and almost got it working.

               As shown in the new screenshot below, I made a one-to-many relationship Ledger::id ----< Audit::id_ledger (a transaction line item could potentially have many changes, but each audit trail record can only relate to one and only one transaction line item record).   Also shown in the screenshot below are the script steps to the new AuditTrail script.

               The problems I'm having are:

               1)  I have the OnRecordCommit script trigger set on the Journal (transaction) layout, and also on the Ledger (transaction line items) layout.  The script that is triggered is the AuditTrail script.  When I change the value in any field on the Ledger layout, the change gets properly recorded as a new record in the Audit table.  Similarly, when I'm on the Journal layout, and I change the value in any field belonging to the Journal table itself (fields of the parent record), such as the transaction date, contact name, reference number, or transaction memo -- again, the change gets properly recorded as a new record in the Audit table.  The Journal layout has a portal on it to the Ledger table.  While on the Journal layout, if I change the value of any field in the portal ON THE FIRST CHILD RECORD, such as the account ID, quantity, amount, line item memo, or subaccount, again, the change gets properly recorded as a new record in the Audit table when I commit the change. (btw - If I change a transaction on the Journal layout, and also have a separate window open to the Audit table, it's fun to watch the new audit record appear automatically!)

               However, if I change the value of any field in the portal belonging to OTHER THAN THE FIRST CHILD RECORD, say the 2nd child record, it will record the parent record information in the Audit table, but it WON'T record the change in the 2nd child record field values to the Audit table.  Not sure why the script is ignoring changes I make to child records when I'm on the Journal layout, but it doesn't ignore the changes when I'm on the Ledger layout?

               2) If I delete any parent record from the Journal layout (and the related child records), the records prior to committing the deletion does NOT get recorded in the Audit table.  Also, if I'm on the Ledger layout and I delete any records, the deletions does NOT get recorded in the Audit table.

               3) When I'm on the Journal layout to add a new transaction record and child records in the portal, the parent record gets recorded as a new record in the Audit table (Note: the child records of the new parent record are NOT recorded in the Audit table).  The Audit table should only track CHANGED or DELETED records, not new records.

               PhilModJunk mentioned in the related posts that audit trails or change tracking can be complex, and that depending on what one wants to get tracked when records are changed or deleted, may require some more scripting (whch I'm now trying to figure out).

               Thanks again in advance for any guidance.

                

          • 2. Re: Audit Trail
            philmodjunk
                 

                      However, if I change the value of any field in the portal belonging to OTHER THAN THE FIRST CHILD RECORD, say the 2nd child record, it will record the parent record information in the Audit table, but it WON'T record the change in the 2nd child record field values to the Audit table.

                 You would appear to have a context problem there. Your script is executing from the context of the parent record. Thus, any references to a child record will always refer to the first such related child record.

                 I've played around with a slightly different approach. I've set up OnObjectEnter triggers that load a global variable with a field's current value. This I can set up using the same variable and script for every editable field on my layout that I want to include in the audit trail. I pass the value of each such field to this script as a script parameter so that I successfully capture original values even for fields formatted as pop up menus.

                 Then I'd  use the OnObjectSave trigger to log the name of the field, the original value, any relevant id number values to global variables by building lists of values in them, appending the original values to these lists.

                 I'd then use the OnCommitRecord trigger to perform a script that pulls the data from these global list variables, storing them into the new audit trail record and then it'd clear the global fields for the next edit session.

                 Note that your audit trail table need not have a matching field for every editable field you want to track. You can use a pair of text fields, one to log the name of the field and one to log the previous value.