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

Andrew Linenber · ‎07-19-2018

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.

Andrew Linenber · ‎08-09-2018

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);

View solution in original post

verda · ‎08-29-2022

Just use Number() to convert price field to number.

 

total += Number(accomodationTask.getValue('equip_cost'))