How to calculate the total amount of story points for a completed PI

[TerryC03]

Hello,

I'm trying to calculate the total completed story points within a completed PI. This is what I have, but my logged value is returning zero; I'm aware my return value is empty at the moment. This is also within a PDI.

(function calculatedFieldValue(current) {

var totalStoryPoints = 0;

var grPI = new GlideRecord('sn_safe_program_increment');

// Add a query to find the completed PIs
grPI.addQuery('state', 3);
grPI.query();

// Loop through each completed PI
while (grPI.next()) {
    var grStory = new GlideRecord('sn_safe_story');

    grStory.addQuery('story_points', grPI.sys_id);
	grStory.addQuery('state', 3);
    grStory.query();

    while (grStory.next()) {
        totalStoryPoints += grStory.story_points;
    }
}

gs.info('Total completed story points: ' + totalStoryPoints);
	return '';  // return the calculated value

})(current);

[Gurpreet07]

Logic should be something like below

var totalStoryPoints = 0;

 var grStory = new GlideRecord('sn_safe_story');

    grStory.addQuery('parent', current.sys_id +'');     //parent may not be the correct field name.  It should be backend name of field 'Program Increment' from 'sn_safe_story' table

    grStory.addQuery('state', 3);

    grStory.query();

    while (grStory.next()) {

        totalStoryPoints += grStory.story_points;

    }

return totalStoryPoints;

Also servicenow advices using GlideAggregate in these type of scenarios. 

https://docs.servicenow.com/bundle/vancouver-api-reference/page/app-store/dev_portal/API_reference/G...

[Harish KM · Accepted solution]

Hi @TerryC03 I see few errors in your script. Correct bold lines as per below

var totalStoryPoints = 0;

var grPI = new GlideRecord('sn_safe_program_increment');

// Add a query to find the completed PIs
grPI.addQuery('state', 3);
grPI.query();

// Loop through each completed PI
while (grPI.next()) {
gs.info("inside while"+grPI.number);
var grStory = new GlideRecord('sn_safe_story');

grStory.addQuery('sn_safe_program_increment', grPI.sys_id); // here you need to check sysid of parent i.e program increment but your passing story_points here which is wrong, once you fix this it should work
grStory.addQuery('state', 3);
grStory.query();

while (grStory.next()) {
totalStoryPoints += grStory.story_points;
}
}

gs.info('Total completed story points: ' + totalStoryPoints);