Roll Up OR Calculated Value

[Andrew Bettcher]

Happy New Year Everyone.

I was able to amend a roll up calculation in PPM with Financials and then have a client script perform a calculation to convert hours to days but I'm stuck with a similar financial requirement.

Everything is OOTB in our Project and Project Financials area so we have a standard relationship between portfolio, project and project task. We don't use stories of programs at all.

I need to be able to sum values in project sub tasks into a field on the parent. 

Each sub-task has a field called u_task_cost. This will take a manual entry that will be the cost of the individual task. When the task is marked as complete, I need that value to go into a sum that is displayed on the parent project into a field called u_delivered_value. As each task is completed, the delivered value will continue to sum each value from the sub task until the delivered value matches the original u_total-project_revenue (or exceeds it or comes under budget which would be OK).

I had a go at a simple business rule that could sum fields but I'm not sure how to get the values from sub-tasks (i.e. I can only do it if all of the fields are on the same form). I also looked into "calculated value" for the parent field but documentation on this is sparse. 

I have some awareness of "cost" in terms of form performance but we can take a small performance hit if it means that the calculation works.

I would also need to do something similar for "number of days delivered" but I think that once I've got a general method then I can work out how to do this. I've tried to reverse engineer some of the OOTB scripts that do similar things but my scripting skills are less than they should be despite having been on the course....

Can someone help please or point me in the right direction?

[Andrew Bettcher]

I've cobbled together the following scripts using bits and pieces found on numerous Google searches etc:

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

var agg = new GlideAggregate('pm_project_task');
agg.addQuery('state', 3);
agg.query();
agg.addAggregate('SUM', 'u_task_cost');
agg.groupBy (current.parent, 'u_delivered_value');

if (agg.next()) {
var delivered = 0;
delivered = agg ('SUM', 'u_task_cost');
allDelivered = agg.getAggregate('SUM', 'u_task_cost');

g_form.setValue ('u_delivered_value', allDelivered);}
gs.info ('SUM = ' + allDelivered);

})(current, previous);

It's in a before business rule set to run on the pm_project table. 

My guess is that some of you script kiddies will find it an hilarious mish-mash of techniques that clearly won't work in it's current state. Any clues as to how I could adapt this to actually do something? 

All the rest is just copied and pasted from here - https://docs.servicenow.com/bundle/jakarta-application-development/page/script/glide-server-apis/concept/c_GlideAggregate.html?title=GlideAggregate

with some tweaks to make it relevant.

State, 3 is "Closed Complete" on the pm_project_task state field.

The logic is this: I get the sub-tasks using the glide aggregate, define and then run the query. Then I sum the values from that field and group them into the parent field but I haven't switched to the parent form (current.parent???)

It doesn't do anything at all as far as I can see. For all I know it's reprogramming our instance to invade Moscow but there are no noticeable or desirable effects.

[Andrew Bettcher]

Update:

Using information taken from 50 plus web pages I have managed to tell ServiceNow to only find sub-tasks of the current parent project. That doesn't seem like much but, believe me, it's a big deal for me. 

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

var totalCostSum = new GlideRecord('pm_project_task');
totalCostSum.addQuery('parent', current.sys_id);
totalCostSum.query ();
var result = totalCostSum.getRowCount();
gs.addInfoMessage(result);

That took me one whole working day but the info message says 18 which is the number of tasks on my test project.

I'll update this further once I've done the summing calculation and got it all working.

They covered GlideRecords on the course I went on but I didn't really fully appreciate what it was all about until now. This piece of work has opened up all sorts of neural pathways in my head and I'm wondering what I can solve next..... 

[Andrew Bettcher · Accepted solution]

I noted a distinct lack of responses on this post and so I guess it's completely feasible that no one had read it and that whatever I type here will never be read but, just in case someone else gets stuck with something similar I'd thought I'd post the solution that works.

It's likely that there are lines in the code below that don't do anything or that put gigantic overheads on the performance of our instance but, because it works, I'm not going to touch it. However, if any code monkeys do read this and have any comments that might help me improve then I would be very grateful. 

For what it's worth, here is my code:

(function executeRule(current, previous /*null when async*/) {
	
	var totalCostSum = new GlideRecord('pm_project_task'); //gets records from
	var cal = 0;
	totalCostSum.addQuery('parent', current.sys_id);
	var qc = totalCostSum.addQuery('state', 3);
	qc.addQuery('state', 3);
	qc.addOrCondition('state', 7);
	qc.addOrCondition('state', 4);
	totalCostSum.query ();
	totalCostSum.getValue('u_task_cost');
	while (totalCostSum.next())
		cal = parseFloat(cal) + parseFloat(totalCostSum.u_task_cost);
	var result = new GlideRecord ('pm_project');
	result.addQuery('sys_id', current.sys_id);
	result.query();
	if(result.next());
		result.u_delivered_value = cal;
	result.update();


	//result.setDisplay (u_delivered_value = result);
	
}
		)(current, previous);

In summary, it finds the sub tasks of parent project, gets the manually entered value of those tasks (but only if they are in one of the defined states), sums them and then returns the value into a field on the parent. Another script then calculates the difference between the summed value and the originally entered value of the entire project and puts the remainder into another field.

The hardest part was the parseFloat business and then the addOrQuery but all of the information is available on a variety of Googles. For info, I have 122 versions of this script listed under the business rule!!

I was also able to use the basic premise of this script and apply it to get and then calculate days spent and the remaining in exactly the same way except I didn't need the parseFloat because I was already dealing with integers.

This has been an excellent and worthwhile introduction to the New Year and to coding. Prior to this, the most coding I ever did was going into Curry's as a small boy and typing:

10 PRINT "Andrew is ACE!!!";

20 GOTO 10

RUN

Into the Commodore 64s.

I'd recommend trying it if you can find a Commodore 64 although I suspect that these days you can get a C64 on your phone.....Just change the message between the inverted commas to whatever you like.....;-)

Happy and Prosperous 2019 Everyone.