Calculate total 'on hold' duration and add it as a custom field

[MaryJohn]

Hi Everyone,

We have a requirement to display the total time spent in 'on hold' state of an incident and display its value as a custom field in the incident form. I tried creating a 'duration' type field with calculated value using below script, but it does not seem to display the total duration calculated. However, when i use the same script on a 'string' type field, it populates the value. Can someone please help in identifying why 'duration' type field isn't displaying the value or where I am going wrong and how can I fix this.  The duration is ideally required to be populated in day/hour/min format so its easily understandable by users. 

Script :

(function calculatedFieldValue(current) {

var dur=0;

var gr = new GlideRecord('metric_instance');

gr.addQuery('id',current.sys_id);

gr.addEncodedQuery('definition.name=On Hold Duration^calculation_complete=true');

gr.query();

while(gr.next())

{

dur=dur+gr.duration.dateNumericValue()/(60*60*1000); //duration converted from millisecs to hours

}

return dur;

})(current);

[Martin Friedel]

Hello MaryJohn,

try this script:

(function calculatedFieldValue(current) {

    var durationSeconds = 0;

    var metricGR = new GlideRecord('metric_instance');
    metricGR.addQuery('id', current.getUniqueValue());
    metricGR.addEncodedQuery('definition.name=On Hold Duration^calculation_complete=true');
    metricGR.query();

    while (metricGR.next()) {
        durationSeconds = metricGR.duration.dateNumericValue();
    }

	var gt = new GlideTime(durationSeconds);

    return gt;

})(current);

Then populate your custom Duration field on incident with that GlideTime object. Just example, you have to modify your code accordingly:

incidentGR.setValue('duration', gt);

If my answer helped you, please mark it as correct and helpful, thank you 👍
Martin

[Anushiya Preeth]

Thank you for the solution @Martin Friedel. This is really helpful! 

[Martin Friedel]

I'm glad I could help 🙂