Getting/updating duration of current metric instance

[candrade1]

I'm relatively new to ServiceNow, and I'm trying to help set up a custom request task/form. One of the requirements is that it keeps track of how long the request has been in a specific status ("Approved", "For Review", etc.) cumulatively in days and displays it in the form and list layout.

I set up a Metric Definition (field value duration) that runs each time the request status changes and it works like a charm, but I noticed that the last metric instance doesn't have a duration because it hasn't changed or ended yet. My strategy was to try either automatically populating the End Date and/or Duration with the live "now" date/time as long as Calculation Complete is false (so that if the status is changed, the normal metric rules kick in), or to try adding together all the other metric instance durations in the request form field, and then just doing an "if" check like before to calculate the last duration on the fly. And by "automatically", I figure the script fires before displaying the list view of all the requests (similarly to if you click on "Open Incidents").

My questions are: is this a good approach of trying to solve this? And if so, how would this best be implemented? (I've tried messing around with Business Rules, Client Scripts, and Calculated Field Value but my form fields don't get any values; I'm not sure if my scripts aren't firing or if they're not producing any output. The closest I've gotten this to work is with a display business rule, but that requires opening every request form to refresh its value).

Thanks,

Chris

[simonw]

Hi Chris,

I wouldn't suggest changing the values of the metrics instances unless you are using scripted metrics - and even then with caution.   I'm not sure this would work, but you could extend the metrics table with a calculated field which calculates the duration from start until now if the instance is open, otherwise display the duration already calculated.

I suspect you're viewing data from a list view so an on display business rule may not work in this case?

Simon

[simonw]

As a basic example, add a new field Current Duration of type duration, mark it as a calculated field and the following script

if (current.calculation_complete) {

  current.u_current_duration = current.duration;

}

else {

  current.u_current_duration = gs.dateDiff(current.start.getDisplayValue(), gs.nowDateTime(), false);

}

This will work on both the list view and form views

[candrade1]

Simon,

Thanks for the reply. So for specifics, I have the custom table called u_clinical_request that extends the task table (with some additional custom fields). I also created a database view that joins u_clinical_request with metric_definition and metric_instance (called u_clinical_request_metric). Am I missing anything else? And do I create the Current Duration field in u_clinical_request?

Thanks!

[simonw]

Hi Chris,

You'll need to add the new field to your metric_instances table as all of the calculations depend on this table.   Then add this new calculated field to you your u_clinical_request_metric view so that it is available in context of your Clinical Requests table.

Simon