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

[qz8437]

       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 · Accepted solution]

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);

[qz8437]

Hi Bonnie,

I wanted to use the calculated field instead of a business rule to keep it cleaner (we already have so many business rules in our system) and Problem is a very low volume table. But I take your point - especially regarding list-view. I'll use a before biz rule. I'm curious why you prefer to use the condition field over the conditions builder. I tend to lean the same way, but I don't know if there are any pros/cons when something simple is all that needs to be checked. I have seen once or twice where both were used. Do you know what happens then? I could experiment to find out, but thought you might know. Do both get applied, or does one get ignored?

                 Thanks, Reed

[Bonnie Cresswe2]

Hi Reed

My preference for using the condition field over the condition builder partly comes from having worked with ServiceNow for longer than the condition builder has existed (so am in the habit of using the field), and partly because it makes it easier to search for conditions if they are all in the same place.  Although that is now not really possible because ServiceNow out of the box business rules are starting to use the condition builder, so all systems are starting to have a mix!

As to your question over which is used if you have both defined? Dunno! Would love to hear the results of your test on that 😄

Apart from that, did the script I suggested work for calculating the duration?

Cheers

Bonnie

[qz8437]

FYI - I did a quick Biz Rule test using both the condition field and the conditions builder. The results showed that both were used - they were connected by a logical AND (both had to be true). I checked for "Active is true" in the conditions builder and current.priority==2 in the condition field. And only records that were Active and Priority "2" were processed by the Rule.