How do I set a duration field as a calculated field?

qz8437 · ‎05-29-2018

I have added a new date/time field on the Problem table that gets set to the current date & time when our Root Cause field gets populated (u_root_cause_entered). I also have a duration field that I want to be calculated using the Calculation Dictionary field - so that it reflects the amount of time from the creation of the Problem until the root cause is filled in. Here is what I put in the Calculation field:

(function calculatedFieldValue(current) {
if(current.u_root_cause_entered.nil()) return '';

var dur = new DurationCalculator();
return dur.calcScheduleDuration(current.sys_created_on.getDisplayValue( ), current.u_root_cause_entered.getDisplayValue( ));
})(current);

But it doesn't work. the duration field remains blank. I also tried this:

(function calculatedFieldValue(current) {
	if(current.u_root_cause_entered.nil()) return '';

	var dur = GlideDateTime.subtract(current.sys_created_on, current.u_root_cause_entered);

	return dur;
})(current);

But that didn't work either. I tried several tweaks to both methods and none of them ever gave a value to the duration field. Can anyone point me in the right direction?

Thanks,

Reed

Bonnie Cresswe2 · ‎05-29-2018

First of all, for what you want to do you SHOULD NOT USE the calculated field option in the dictionary - any field that has a calculation done in this way can affect system performance, because the calculation is done every time the record is loaded. Including in a list view. So if you view a list of say 5 records that calculation is done 5 times. 100 records and it is done 100 times. Imagine if multiple users are loading lists of these records!

But fear not - there is an easy solution. Because you want to work out the difference between two datetime fields, and you want to do that when one of those fields is updated, a Before update business rule is the way to go.

So something like this:

Business rule set to run Before Update (check the order, this should be run after the rule that updates the u_root_cause_entered runs)

Condition to check for when the Root Cause Entered datetime field changes (so that this BR doesn't run on every update), my preference is to use the Condition field on the Advanced tab, but you can use the condition builder on the When to run tab if you prefer, if using the Condition field on the Advanced tab something like: current.u_root_cause_entered.changes() && !current.sys_created_on.nil()

Advanced script field will be something like this (assuming your new duration field is called u_root_cause_duration):

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

if (current.u_root_cause_entered.isNil()) { // to make sure we clear the duration if the end date is cleared
current.u_root_cause_duration = '';
}
else {
current.u_root_cause_duration = gs.dateDiff(current.sys_created_on.getDisplayValue(), current.u_root_cause_entered.getDisplayValue(), false );
}

})(current, previous);

View solution in original post

michaelwatts · ‎08-29-2018

Hi Bonnie,

Just checking back to see if you had time to revisit my last post?

Thanks.

Bonnie Cresswe2 · ‎09-09-2018

Hi

Apologies, haven't logged in for a while - the business rule should be a Before Update, and the condition needs to look for when the closed_at field changes.

The screenshot you sent shows that the Closed field is empty, what happens if you put a date in there and save?

Kind regards

Bonnie

samwallace88 · ‎02-18-2020

The way I got it to work was this:

var start_date = new GlideDateTime(current.start);
var end_date = new GlideDateTime(current.end);

var calculation = new GlideDateTime.subtract(start_date, end_date);

current.u_outage_duration = calculation;

Shambhu5 · ‎05-29-2018

Hi,

As suggested by Bonnie Cresswell, you should not use/practice Dictionary level changes.

I had a simple requirement that, I needed to calculate the difference in duration of Stat date and the End Date. A before Business Rule was written for that.

My both Start Date and End Date were having type as "Date/Time", whereas Duration named field had a type as "Duration" itself. Please find below BR on Before Update;

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

// Add your code here

var start = current.getValue('u_start_date').GlideDateObject();
var end = current.getValue('u_end_date').GlideDateObject();

// gs.addInfoMessage("test "+start+end);

current.setValue('u_duration',gs.dateDiff(start,end));

})(current, previous);

You can make some changes in the above code and that will work.

Please mark my answer as Correct/Helpful, if it somehow helped.

Regards,

Vishrut

www.DxSherpa.com

Bonnie Cresswe2 · ‎05-29-2018

Hi Vishrut - I don't think your script will work as intended. If you want to use dateDiff you should use getDisplayValue() to convert the strings to the expected format. If you want to use GlideDateTime objects use the GlideDateTime subtract() method instead of dateDiff(), see this article for info on dateDiff https://docs.servicenow.com/bundle/kingston-application-development/page/app-store/dev_portal/API_re...

and this one for info on using GlideDateTime objects (including the subtract() method):

https://docs.servicenow.com/bundle/kingston-application-development/page/app-store/dev_portal/API_re...

cheers

Bonnie