Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Audit log - where to implement?

Post Reply
israelwebdev
Posts: 21
Joined: Thu May 22, 2014 6:08 pm

Audit log - where to implement?

Unread post by israelwebdev »

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) ?
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Audit log - where to implement?

Unread post by admin »

israelwebdev,

Use PHP after Save.

Steven
israelwebdev
Posts: 21
Joined: Thu May 22, 2014 6:08 pm

Re: Audit log - where to implement?

Unread post by israelwebdev »

So how do I access the fields/values that have changed from there?

Thanks
admin wrote:israelwebdev,

Use PHP after Save.

Steven
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Audit log - where to implement?

Unread post by admin »

israelwebdev,

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);
Steven
israelwebdev
Posts: 21
Joined: Thu May 22, 2014 6:08 pm

Re: Audit log - where to implement?

Unread post by israelwebdev »

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
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Audit log - where to implement?

Unread post by admin »

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
israelwebdev
Posts: 21
Joined: Thu May 22, 2014 6:08 pm

Re: Audit log - where to implement?

Unread post by israelwebdev »

I was able to refactor some of your code to track the changes from the client side.

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;

}	
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"
Rolf
Posts: 28
Joined: Fri May 20, 2016 9:24 am

Re: Audit log - where to implement?

Unread post by Rolf »

I found this discussion to be very informative.
Rolf
Posts: 28
Joined: Fri May 20, 2016 9:24 am

Re: Audit log - where to implement?

Unread post by Rolf »

And with nuBuilder Forte the awesome folks at nuSoftware have even created a wiki page how to do this and more.
Post Reply