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.