Add Object/Field name to SQL table - UX

[paulyp]

Hi all
I want to audit any and all changes on a table (staff_info) into another table (staff_audit). I am using a SQL insert statement and that is working well, but rather than have a specific Insert command for every object or field, I want to be able to insert the object name into one of the columns in the audit table. There are five columns in the audit table, user, date/time, object name, old details and new details.
Is there a way of referencing the objects name where the data is being entered into ?

Cheers
Paul

[mumfie]

Re: Add Object/Field name to SQL table - UX

Hi Paul,
Not sure I understand exactly what you mean but some possible methods are
-the UX server side event dialogValidate has access to the new and also original values. It also has access to the field names amended via user in e.dirtyColumns
-You might be able to setup an SQL trigger to automatically update staff_audit when records are inserted into staff_info

[paulyp]

Re: Add Object/Field name to SQL table - UX

HI Mumfie
I want to actually add the the field or objects name that is presently being edited - so if I change the object labelled as staff first name from Mickey Mouse to Donald Duck then I want to add this plus the name of the object that the name was changed in and in this way, I can have one xbasic structure that can be applied to all objects in the UX if that makes sense (I have a habit of not explaining well :(
Cheers
Paul

[GGandhi]

Re: Add Object/Field name to SQL table - UX

the changed fields or the control names will be available in e.dirtyColumns, probably as a crlf limited string. you may be able to use it. take a look.

[mumfie]

Re: Add Object/Field name to SQL table - UX

HI Mumfie
I want to actually add the the field or objects name that is presently being edited - so if I change the object labelled as staff first name from Mickey Mouse to Donald Duck then I want to add this plus the name of the object that the name was changed in and in this way, I can have one xbasic structure that can be applied to all objects in the UX if that makes sense (I have a habit of not explaining well :(
Cheers
Paul

please see http://msgboard.alphasoftware.com/al...=e.dirtyFields

This should get you started

for each field in e.DirtyColumns
	oldData = eval("e.OldDataSubmitted." + field)
	newData = eval("e.dataSubmitted." + field)
'todo assign SQL args and insert details in table staff_audit
next

[JimDK]

Re: Add Object/Field name to SQL table - UX

I am using same function in all my applications, where I am updating an sql table with the old and the new value and at the same time sending a mail with the changed values to the administartor of the system. the function is liek this:

FUNCTION InsertChangelog AS C (e AS P, tabname AS C )
dim cn as sql::connection
dim args as sql::Arguments
dim arg1 as sql::arguments
dim sql as c
dim fname as c
dim getdata as c
dim newdata as c
dim x1 as c
dim x2 as c
dim errortext as c
dim Username as c
dim UserID as c
dim pUser as p
dim message as p
dim body as c
dim filter as c
dim res_exp as c

on error resume next
if eval_valid("session.fullname") = .f.
Username = "Testuser"
else
Username = Session.fullname
end if
cn.Open("::Name::ticdb")
cn.PortableSQLEnabled = .t.
body = "<h1>Changelog fields " + "</h1><br>"
body = body + "<table border=" + "3" + "width=" + "800" + ">"
body = body + "<caption><strong>Task change log fields</strong></caption>"
body = body + "<tr bgcolor=" + "#CCFFCC" + " border>"
body = body + "<td>Fieldname</td><td>Changed from</td><td>Changed to</td>"
body = body + "<tr bgcolor=" + "#CCFFFF" + " border=" + "1" + "></tr>"
FOR each foo in e.dirtyColumns
if foo.value <> "CHGBY" .and. foo.value <> "CHGDAT" then
newval = "e.DataSubmitted." + foo
prevval = "e.OldDatasubmitted." + foo
Newvalue = eval(newval)
Oldvalue = eval(prevval)
args.add("logtime",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
args.add("new_creby",username)
args.add("new_tabname",tabname)
args.add("new_fieldname",foo.value)
args.add("new_oldvalue",Oldvalue)
args.add("new_newvalue",Newvalue)
args.add("new_recid",e.DataSubmitted.taskid)
body = body + "<td>" + foo.value + "</td><td>" + Oldvalue + "</td><td>" + Newvalue + "</td></tr>" + crlf()
sql = "INSERT into changelog (logtime, Creby, Tabname, Fieldname, oldvalue, newvalue, Recid) values (:logtime, :new_creby, :new_tabname, :new_fieldname, :new_oldvalue, :new_newvalue, :new_recid)"
cn.execute(sql,args)
end if
NEXT
body = body + "</table>"
cn.close()
message.Subject = tabname + " Changed for recid: " + e.DataSubmitted.taskid
message.to = "[email protected]"
message.Html_Message = body
email_send2(message, "",.t.,.t.)
END FUNCTION

[paulyp]

Re: Add Object/Field name to SQL table - UX

Hi Guys

thanks for the reply. I tried what Jim put down and it is EXACTLY what I was looking for, works like a charm. The code I finally used is:

dim cn as sql::connection
dim args as sql::Arguments
dim sql as c
cn.Open("::Name::CCS")
cn.PortableSQLEnabled = .t.
FOR each foo in e.dirtyColumns
if foo.value <> "CHGBY" .and. foo.value <> "CHGDAT" then
newval = "e.DataSubmitted." + foo
prevval = "e.OldDatasubmitted." + foo
Newvalue = eval(newval)
Oldvalue = eval(prevval)
args.add("logtime",convert_type(a5_eval_expression("=Now()",local_variables()),"T"))
args.add("new_logged_in_user",session.userid)
args.add("new_fieldname",foo.value)
args.add("new_oldvalue",Oldvalue)
args.add("new_newvalue",Newvalue)
args.add("new_user_edited",e.dataSubmitted.cont_name+ "_" + e.DataSubmitted.cont_contact_first + "_" + e.DataSubmitted.cont_contact_last)
sql = "INSERT into z_staff_audit (zsa_date_added, zsa_user_name, zsa_column_updated, zsa_old_data, zsa_new_data, zsa_record_edited) values (:logtime, :new_logged_in_user, :new_fieldname, :new_oldvalue, :new_newvalue, :new_user_edited)"
cn.execute(sql,args)
end if
NEXT
cn.close()

Cheers
Paul

[Medic1196]

Re: Add Object/Field name to SQL table - UX

Paul or Jim, where and how did you call the function?
Could use this myself, but kind of lost on where to put what and calling the function.

[JimDK]

Re: Add Object/Field name to SQL table - UX

I am using the server-side: AfterDialogValidate for the UX xcomponent Udklip.PNG, and on the grid it is normally the server-side event CanUpdateRecord I am using

[Medic1196]

Re: Add Object/Field name to SQL table - UX

Well that worked like a charm!! Thank you. I tried to create something like this quite a while back but finally gave up on it before I lost all my marbles.
This is great Jim, thank you very much.

[Medic1196]

Re: Add Object/Field name to SQL table - UX

It saves even new values, so it shows blank on the oldvalue, is there an easy way to only record values which had an old value and not ones that are receiving an initial value?
I even got the email, so even that portion is working...so cool!