How to sum multiple price fields in records on one table and push value to another field on another table.

[Andrew Linenber]

Hi all.

We have a custom app that contains a table "Accommodations" with a field "equip_cost" and a table extended off of accommodations called "accommodation tasks" that also has equip_cost..

There will be multiple acc. task records associated with 1 accommodation record.

I am trying to come up with a business rule that will sum all of the cost information from the tasks and set the equip_cost field on the parent accommodation with the total.

The business rule is set up on the Accommodation Task table, and is set to run after update.

This is what we currently have, which I tried to develop based upon the information here (https://docs.servicenow.com/bundle/jakarta-platform-administration/page/administer/currency/concept/currency-values-scripts.html):

function executeRule(current, previous /*null when async*/) {
	//after close then we need to update the parent ticket with the cost information
	
	var total = 0;
	var parent = new GlideRecord('x_unofk_ada_rcea_accom');

	parent.addQuery('sys_id', current.parent);
	parent.query();

	while(parent.next()) {
	total = parseFloat(total) + parseFloat(current.equip_cost);	
	total = parseFloat(total) + parseFloat(parent.equip_cost);
	
	parent.equip_cost = total;
	}


}

In a previous version, we were using parent.equip_cost = current.equip_cost; and this did push the appropriate information, but it would not sum the totals.

I am sure that I'm missing something simple, but for the life of me it isn't clicking.  Has anyone done something like this prior and can point me in the right direction?

Thanks in advance.

[Bhagyashree8]

Hi,

You have created business rule on Accomodation task table that is right.

i had almost same requirement. so, try some code changes as below.

(function executeRule(current, previous /*null when async*/) {

var SysId = current.getValue('<parentreferencefield>');
 var total=0;
 
 var accomodationTask = new GlideRecord('<accomodationtasktable>');
 accomodationTask.addQuery('<referencefield>',SysId);
 accomodationTask.query();

while(accomodationTask.next()){

if(!isNaN(parseInt(accomodationTask.getValue('equip_cost'),10)))
 total += parseInt(accomodationTask.getValue('equip_cost'),10);

}

var accomodation = new GlideRecord('<parenttable>');
 accomodation.get(SysId);
 accomodation.equip_cost = total;
 story.update();


})(current, previous);

let me know if you still face issue.

[Bhagyashree8]

i forgot to change in below line

use

accomodation.get(SysId);

insted of

accomodation.get(storySysId);

[Bhagyashree8]

Hi,

Just wanted to check if it is working for you now.

In that case can you please mark response correct/helpful so that it will help someone in future.

Thanks,

Bhagyashree

[Andrew Linenber · Accepted solution]

This is what finally ended up meeting our requirements for this issue.

var parent = current.parent.getRefRecord(); //Gets parent record as full object
		if (parent.isValidRecord()){
			// Recalculate the parent cost on the parent
			var taskCost = new GlideAggregate('x_unofk_ada_rcea_acc_task');
			taskCost.addQuery('parent', current.parent); //limits query to those with the same parent record of the current record
			taskCost.setGroup(false);
			//taskCost.addQuery('state', "Closed Approved" );limits query to those with the state of closed approved
			taskCost.addAggregate('SUM', 'equip_cost');
			taskCost.query();
			if (taskCost.next()){
				parent.setValue('equip_cost', taskCost.getAggregate('SUM', 'equip_cost')); //sets Equipment Cost on the parent record
				parent.update(); //updates parent record
			}
		} else {
			// This means the task has a parent value, but the parent doesn't actually exist, this would be unexpected.
		}
		
		
		
	})(current, previous);