audit trail

[bigandfat]

I have a request to document the updates to a database. Perhaps a dialog box asking for user name and reason for changing the data, saving this to a text file along with previous field value and current field value?
Has anyone programed anything like this?

Thanks in advance.

 

[Kliot]

Sure, you just have to set up a procedure and call it at some point when the user is editing the record. I have a procedure that is called when the changeValue event is called. In the procedure I capture the value before the value is changed and write it to a log table along with a time stamp and the user name from getNerUserName(). You can easily add a dialog box asking for a reason if you want. It's also just as easy to write this data to a text file using the textstream function.

Hope this helps
Perrin

 

[footpad]

bigandfat,

I typically override the dataPostRecord() action to handle this and copy the audit values to either a different table or a log file, as Perrin suggests.

I'll try to mock up an example later today...

-- Lance

 

[jartman2]

We've been doing exactly what you're describing in our application, for about 4 months now. We use it to track changes to customer purchase orders. It works great. One suggestion I have is that you use a drop down list for the REASON field if you can - making summaries or reports of the reasons would be a lot easier than dealing with individual's random entries. We haven't yet made this change ourselves, but I'll be glad when we do.

We use changeValue on each relevant field, and save the results to a table rather than a text log. The table is mighty handy when you have to re-create a history, because you can filter, sort, etc.

It's interesting that footpad brought up dataPostRecord - we rarely use this because we've had some problems with it. For example, if the user edits a field and then closes the form without hitting F9, the record in the table gets changed but you never get a dataPostRecord. There are several other ways to make this happen. I never understood why... maybe footpad could comment?

- John