I need to calculate the time between sys_created_on and the first status change

Jerome MAISETTI · ‎07-28-2022

Hello All
I need your precious help again 🙂
I've created a field on the task table called "Reaction Time".
I need this field to be a calculation between the ticket creation and the first status change.
I already created a field that does the calculation between sys_created_on and requested date but I have to admit that I don't know how to achieve this new one.

Here is my first script

runCalculateTime();

function runCalculateTime() {

var laterDate = new GlideDateTime(''+current.sys_created_on);
var earlierDate = new GlideDateTime(''+current.requested_by_date);

var duration = new GlideDuration();
duration = GlideDateTime.subtract(earlierDate, laterDate);

current.u_ticket_creation_delay = duration;

How can I catch up the first status change ?

Thanks

Jérôme.

SumanthDosapati · ‎07-28-2022

Hi @Jerome MAISETTI ,

You can try an AFTER UPDATE business rule as below

Condition : 'Status' Changes

AND

'ReactionTime' IS NOT EMPTY

Script:

var created = new GlideDateTime(''+current.sys_created_on);
    var updated = new GlideDateTime(''+current.sys_updated_on);

    var duration = new GlideDuration();
    duration = GlideDateTime.subtract(created, updated);

    current.u_reaction_time = duration; //update your reaction time field name
	current.setWorkflow(false);
	current.update();

Mark as correct and helpful if it solved your query.

Regards,
Sumanth

View solution in original post

Vipul Sethi · ‎07-28-2022

I will suggest you to use metric and create metric defination for state field, its an oob functionality which can capture the duration for each state and u can use gliderecord to query to fetch metric instance records and get the duration for the particular state that you are looking for. PFB the link on how to create metric instance.

https://docs.servicenow.com/bundle/sandiego-platform-administration/page/use/reporting/concept/c_MetricInstance.html

Jerome MAISETTI · ‎07-28-2022

@Vipul Sethi : Thanks.
But how can I script that state ? Do I have to do a glide record in task and fetch state = x ?

Jerome MAISETTI · ‎07-28-2022

I used the business rule suggested by Sumanth and it works fine 🙂

SumanthDosapati · ‎07-28-2022