Forensic change information? How can I save a list of old values = new values on save

[perry99]

I want to save each and every change made to a record on web application v11..

Ideally, this is the result of forensic table:



Who Field What it was What it is When
------------------------------------------------------------------------
Joe Address 1234 Apple St 1234 Peach St 2013-03-14 08:00AM
Joe Name Smith, Steve Doe, John 2013-03-14 08:01AM


It would be pretty easy to open Forensic table, and add the changes, that part I know..

But is there an easy way to get list of old values and new values on DIRTY Fields?

[Keith Hubert]

Re: Forensic change information? How can I save a list of old values = new values on

Hi Perry,

There is a server side event called, AfterUpdateRecord. This will have the variable, e.OldDataSubmitted - Original data in the row that was updated.

[perry99]

Re: Forensic change information? How can I save a list of old values = new values on

Thanks,
It drives me crazy when I have read about these certain commands, but then cannot find them again when I need them! I searched for an hour using the help wiki, and on these forums..

I will give it a try after I sleep for a couple hours.. I know right now, I will ruin anything I touch.

[workaholic06]

Re: Forensic change information? How can I save a list of old values = new values on

Hi Perry,

We do this a little differently to keep a forensic audit trail of everything modified/added.

In the OnBeforeSQLCommandExecute, we run the sql statement that is created by Alpha to insert or update the record into tablename. Then, we modify the statement to insert a record into our tablename_audit table. this has all the fields in tablename, plus additional fields for auditID, auditDateTime, auditUser.

This allows us to see exactly what information was changed at what time by each user.

Thanks,

Earl

[perry99]

Re: Forensic change information? How can I save a list of old values = new values on

Here is what I did for now.. This is temporary, that just creates one record showing all the changes.. I plan to save each field change record by record in mysql vs current dbf memo field..


'create changestemp variable:
delete changestemp
changestemp="Old Data="

DirtyFieldList=stringscanner.Create(e.DirtyFields)
DirtyFieldName=stringscanner.Create(e.DirtyFields)
DirtyFieldCount=line_count(DirtyFieldList)

for x=1 to DirtyFieldCount

DirtyFieldList.GotoLineStart(x)
changestemp=changestemp+crlf()+ DirtyFieldList.GetLineText(x)+":"+crlf()
changestemp=changestemp+"Changed From: "+eval("e.olddatasubmitted."+DirtyFieldList.GetLineText(x))+crlf()
changestemp=changestemp+"Changed To: "+eval("e.datasubmitted."+DirtyFieldList.GetLineText(x))+crlf()

next x

' variable 'changestemp' now contains list of changes for this record.. Now I am going to save it to Messagessent table.

tblchange=table.open("[PathAlias.ADB_Path]\Messagessent")
tblchange.enter_begin()
tblchange.dateandtimecreated=now()+ " by "+e.olddatasubmitted.technician
tblchange.jobid=e.datasubmitted.job_number
tblchange.apptid=e.datasubmitted.apptid
tblchange.techid= e.olddatasubmitted.technician
tblchange.type="TechWebUpdate"
tblchange.message=changestemp ' This is Memo field that all the changes are listed in.
tblchange.enter_end(.t.)
tblchange.close()