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.
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!
AMAZING! Thanks Guy, will watch it tomorrow and let you know how I get on.
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.
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"]
Set Variable [$$Existing; Value: "<EMPTY>"]
If [$$Current = ""]
Set Field [Table::Log;
"Field 1 Deleted, Previously '"&$$Existing&"'¶on " &Get ( CurrentTimestamp )&" by "&Get ( AccountName )&"¶¶"&Table::Log
Set Field [Table::Log;
"Field 1 changed from: '"&$$Existing&"' to: '"&$$Current&"'¶on " &Get ( CurrentTimestamp )&" by "&Get ( AccountName )&"¶¶"&Table::Log
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.
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!