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