Does anyone have experience creating an audit log to track granular data changes by user (beyond modified_at/by)?
Any suggestions for implementing in JavaScript (nuOnSave), PHP (Before Save?), or MySQL (trigger) ?
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Audit log - where to implement?
-
- Posts: 21
- Joined: Thu May 22, 2014 6:08 pm
Re: Audit log - where to implement?
So how do I access the fields/values that have changed from there?
Thanks
Thanks
admin wrote:israelwebdev,
Use PHP after Save.
Steven
-
- Site Admin
- Posts: 2786
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 3 times
Re: Audit log - where to implement?
israelwebdev,
If you are updating a record you might do something like this..
Steven
If you are updating a record you might do something like this..
Code: Select all
$s = "UPDATE employee SET emp_edited = NOW() WHERE employee_id = '#RECORD_ID#'";
nuRunQuery($s);
-
- Posts: 21
- Joined: Thu May 22, 2014 6:08 pm
Re: Audit log - where to implement?
That doesn't answer my question.
I'm looking for the best state where I can access OLD_VALUE and NEW_VALUE.
Wouldn't Before Save be more appropriate, so I can access the form data and the stored data, similar to your example
http://wiki.nubuilder.net/nubuilderv3/i ... efore_Save
I'm looking for the best state where I can access OLD_VALUE and NEW_VALUE.
Wouldn't Before Save be more appropriate, so I can access the form data and the stored data, similar to your example
http://wiki.nubuilder.net/nubuilderv3/i ... efore_Save
-
- Site Admin
- Posts: 2786
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 3 times
Re: Audit log - where to implement?
israelwebdev,
Yes before save will allow you to access the yet unchanged record with #RECORD_ID# and the new values via the hash variables on the Edit Form.
Steven
Yes before save will allow you to access the yet unchanged record with #RECORD_ID# and the new values via the hash variables on the Edit Form.
Steven
-
- Posts: 21
- Joined: Thu May 22, 2014 6:08 pm
Re: Audit log - where to implement?
I was able to refactor some of your code to track the changes from the client side.
Returns an array of objects with a "human-readable" field name, value, and startval.
(index) field value startval
0 "CustBanks 0000 BankID" "7acb35b2-f410-43df-afb0-542076f1a895" ""
1 "CustBanks 0000 AcctNo" "000000000" "-=noValue=-"
2 "CustBanks 0000 Memo" "" "-=noValue=-"
3 "CustBanks 0000 Active" "1" "-=noValue=-"
4 "CustBanks 0000 isDefault" "1" "-=noValue=-"
5 "CustomerMI" "P" ""
6 "CustomerCountry" "USA" "CANADA"
Code: Select all
function custGetChangedData(prefix){
var fields = [];
for(var i = 0 ; i < nuSession.subformName.length ; i++){ //-- loop through form and subforms
var isMainForm = nuSession.subformName[i] == 'nuBuilderEditForm';
var name = nuSession.subformName[i];
var recs = nuSession.subformRowNumber[i]+1;
var cols = nuSession.subformColumns[i];
for(var R = 0 ; R < recs ; R++){ //-- loop through records
del = false;
if(isMainForm){
var row = '';
name = '';
}else{
var row = ("000" + String(R)).slice (-4);
}
if($('#'+name+row+'_nuPrimaryKey').length == 0){
var PK = $('#nuFormPrimaryKey').val();
}else{
var PK = $('#'+name+row+'_nuPrimaryKey').val();
}
if($('#'+name+row+'_nuDelete').is(':checked')){
if(!PK)
continue;
fields.push({field : (name.replace(prefix,'').replace('_subform','')+' '+row).trim(), value : '-=Delete=-', startval: '', delete_record : 'yes'});
del = true;
}
for(var C = 1 ; C < cols.length ; C++){ //-- loop through fields
var id = name + row + cols[C].field;
var v = $('#' + id).val();
var match = v;
if($('#'+id).attr('data-saveable')=='1'){ //-- is this saveable data
if(cols[C].type == 'listbox'){ //-- if listbox then create a string
if(v == null){
v = '';
}else{
v = v.join(nuSession.nuBuilderSeparator);
}
match = v;
}else{
//v = formatter.formatField($('#' + id).attr('data-nuformat') ,v,true); //-- format value for sql string
}
var startval = nuCheckStartValue(id);
if(parseFloat(startval)==0) startval=0;
if(parseFloat(match)==0) match=0;
//The "falsey" values are: false, null, undefined, 0, "" ( empty string ), NaN ( Not a Number )
if((startval || match) && (startval != match || del )){
fields.push({field : (name.replace(prefix,'').replace('_subform','')+' '+row+' '+cols[C].field).trim(), value : v, startval : startval});
}
}
}
}
}
return fields;
}
(index) field value startval
0 "CustBanks 0000 BankID" "7acb35b2-f410-43df-afb0-542076f1a895" ""
1 "CustBanks 0000 AcctNo" "000000000" "-=noValue=-"
2 "CustBanks 0000 Memo" "" "-=noValue=-"
3 "CustBanks 0000 Active" "1" "-=noValue=-"
4 "CustBanks 0000 isDefault" "1" "-=noValue=-"
5 "CustomerMI" "P" ""
6 "CustomerCountry" "USA" "CANADA"
-
- Posts: 28
- Joined: Fri May 20, 2016 9:24 am
-
- Posts: 28
- Joined: Fri May 20, 2016 9:24 am
Re: Audit log - where to implement?
And with nuBuilder Forte the awesome folks at nuSoftware have even created a wiki page how to do this and more.