Metric Defnition and its limitation with 1-many relation - Help

[Amith Makkuva]

I my current project, Agile process owners want to capture the overall time of all the tasks spent in a user story. 

I am working a metric definition in service-now and here is the requirement. I want the metric instance OOB function and use startDuration to start when the first task in story is draft state and the endDuration to hit only when the 4th task in the current story is marked as complete. I want this to trigger only when all these tasks are part of the same user story. 1) Development 2) Code Review 3) Testing 4) Sprint Review.

I am trying to go with the name of the tasks as they are auto created with the flow of user story states. but my code never behaves nor triggers or captures based on script include or business rule when I refer to multi tables. can you help with achieving this or is is there a limitation to metric definition as it by design it does not react outside the selected tables ?

Metric Def configuration:

table: scrum_task

field: state

script calculation

Code:

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

// Check if the current task is part of a user story

var story = 'STRY2000003';

if (!story) {

return;

}

// Get all tasks related to the user story

var taskGR = new GlideRecord('scrum_task');

taskGR.addQuery('story.number', story); //story.number=STRY2000003

taskGR.query();

var tasks = {};

while (taskGR.next()) {

tasks[taskGR.short_description] = taskGR;

}

// Initialise the MetricInstance

var mi = new MetricInstance(definition, current);

var ss = tasks['Sprint Review'].state;

var sd = tasks['Development'].state;

// Check if the metric instance exists

if (mi.metricExists()) {

// Check if Sprint Review task is Complete

if (ss == 3) {

mi.endDuration();

}

} else {

// Check if Development task is in Draft state

if (sd == -6) {

// Check if a metric instance already exists for this story

var metricInstance = new GlideRecord('metric_instance');

metricInstance.addQuery('story', story);

metricInstance.addQuery('metric_definition', definition);

metricInstance.query();

if (!metricInstance.next()) {

// Start the metric duration

mi.startDuration();

}

}

}

})(current, previous);