debug g_scratchpad guidance and best practices?

[woodyfairley]

I am trying to improve.

I wrote an app that uses a business rule to save a table's sum aggregates for related records to a scratchpad array with named index; in the client script i retrieve the specific aggregated sum using a variable that contains a value used to select the correct indexed value in the scratchpad variable. It worked when I developed the app in the Global scope context, but now the scratchpad is show undefined: part of my debugging has been to write the variables to a log message and/or alerts. Any ideas? If I find the solution, I will add it to this thread.

Business Rule:

(function executeRule(current, previous /*null when async*/) {
	
	// Add your code here
	//this code captured from global app on 6-4-19 to a text file;
	var sums = 0; // initialize variable 1;
	var purch_req = {}; // initialize array 2;
	var req_num = []; // initialize array 3;
	var tot = new GlideAggregate('x_197547_ocio_purc_x_197547_ocio_ordering_table'); // 4;
	tot.addAggregate('SUM','u_line_item_total'); // 5;
	tot.groupBy('u_request_number'); // 6;
	tot.query(); // 7;
	var tots = []; // initialize array for capturing the aggregate SUM 8;
	var i = 0; // initialize loop counter used for array index value 9;
	var treq_num = ''; // initialize temp request number variable for loop 10;
	while(tot.next()) {
		req_num.push(tot.getDisplayValue('u_request_number')); // capture record's request number to key array 12;
		treq_num = req_num[i]; // assign to temp variable 13;
		sums = tot.getAggregate('SUM','u_line_item_total'); // get sum value 14;
		sums = parseFloat(sums, 10); // convert to number data type 15;
		tots.push(sums); // add value to values array 16;
		purch_req[treq_num] = sums; // assign the key and its sum value to the scratchpad array 17;
		i++; //this ends the loop one higher than the index value, equals the total count; // 18;
	}
	g_scratchpad.pr_total = purch_req; // assign scratchpad array 20;
	//alert("g_scratchpad.pr_total= " + purch_req) 21;
})(current, previous);

onChange client script:

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
	if (isLoading || newValue === '') {
		return;
	}
	
	//Type appropriate comment here, and begin script below
	// declaring all variables so they are available across script layers;
	// onChange script based on u_line_item_total field in Ordering Table;
	var req_num = '';// null value;
	var req_total = 0;
	var new_req_tot = 0;
	var clean_tot = 0;
	var new_li_tot = 0;
	// eliminating the comma: convert value to string, replace comma with '', join then parsefloat;
	var oldValStr = oldValue.toString();// assign oldValue as a string;
	if (oldValStr.indexOf(',') > -1) {
		var newchar = '';// new variable with null value;
		oldValStr = oldValStr.split(',').join(newchar);//this removes the comma;
	}
	var oldVal = parseFloat(oldValStr, 10);//now converting to number;
	var newVal = parseFloat(newValue, 10);//now converting to number;
	req_num = g_form.getDisplayValue('u_request_number');//display value is the key, not the sysid;
	req_total = g_scratchpad.pr_total[req_num];// works just fine with the displayvalue;
	req_total = parseFloat(req_total, 10);//convert to number type;
	new_req_tot = req_total - oldVal;//perform reset maths;
	clean_tot = new_req_tot + newVal;//perform reset maths 2nd stage;
	g_form.setValue('u_request_total', clean_tot); //display the updated request total on the form;
}

I liked using scratchpad array variables since it enables the collection of the aggregate data across the table grouped by the m2m values in a column and with the named index I can simply reference the m2m value across any number of client scripts using the same scratchpad array variable; I am not sure why it isn't working as a Scoped application..

I wish you well.

Woody

[DirkRedeker]

Hi

I do not know, which type of business rule you implement, a before or after, a insert or update, display, etc.

Anyhow, I am not sure, if that should be the desired option to store the data to a scratchpad. I think, there may be situations, when that approach will not be the best and most effective and efficient way. 

Think about having lots of records in your table. It will take longer and longer to calculate all the aggregates each time. 

And even to have the scratchpad available, needs to run the calculations of the business rule each time. I think there will be situations, when the business rule will not run, and you do not have access to the aggregate. 

Instead, I recommend to create a separate table, holding your aggregate values. Thus table needs to be updated each time, a value changes, that is the colum your aggregate depends on. 

Then, you only need to update the aggregate values in your table for those records, where the aggregate is affected (current and previous values),

In your client, you the query the aggregate table, which is always up to date, by business rule. 

Does that make sense to you. 

Let me know if that answered your question and mark my answer as correct and helpful, please. 

BR 

Dirk 

[woodyfairley]

Hi dirk, in my research I learned that aggregates are automatically calculated on the server and stored as part of the table, in real time. When a field value is saved, the aggregates are re-calculated and their fields are also updated, and since this uses some background mysql algorithms the resource utilization on the server is nominal. This is why I want to perform the aggregate calculations as it is really fast; see this document for more detail, it is quite informative:

https://docs.servicenow.com/bundle/london-application-development/page/app-store/dev_portal/API_refe... 

I am only capturing a single GlideAggregate value for each group (SUM) and it is already calculated and presented on the screen when I perform the column function of sum in the list view. I have a different post thread on the challenge of getting that value off the web page, here:

https://community.servicenow.com/community?id=community_question&sys_id=6f09165e1b1b7300fff162c4bd4b...

As to the challenge of triggering the business rule every time, it is a matter of configuring the trigger properly.

Creating a table to manage the aggregate values is a bit overkill for my current goal of simply capturing the grouped SUM values of one specific column, and the primary goal is to use the SUM value as part of a client script calculation, so the use of a g_scratchpad to transfer  the table's grouped sum values to the client script is ideal and worked in the Global Scoped version of my app.

In this morning's continued search for answers, I found this article that differentiates arrays and objects; what I am trying to create in scratchpad is an object, where the element name is assigned by a variable to identify the group, and the element value is the SUM value of the group. I've been trying to do this as an array not an object and arrays only use numbered index elements. I need to learn more about JSON.

https://www.w3schools.com/js/js_arrays.asp 

https://www.w3schools.com/js/js_object_definition.asp 

Now I think I am on the right track, the goal is to construct the scratchpad object to look like this:

g_scratchpad.pr_total

"PR0001087" : 955.00,

"PR0001088" : 1370.65,

"PR0001089" : 43.99,

(and so on) with the full listing from the table, then I can pull the appropriate value from the scratchpad object when the new record producer is opened by the user.

This dialog is helping, thank you, and I hope that some of the links will also help you.

Kind Regards,

Woody

[Jim Coyne]

Although not a solution to your issue, this may help your debugging - "Show Contents of g_scratchpad" Tool.

I'm curious to know how it works for you.

[woodyfairley]

Thanks Jim, I was considering the idea of creating just such a tool, well done!

Kind Regards,

Woody