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-21-2018

Hi Bonnie,

I tried using your script to populate an OOB field (calendar_duration) but to no avail. Is it possible you could assist me in this matter? Thanks in advance. here is my script.

Condition:

current.calendar.duration.changes() && !current.sys_created_on.nil()

Advanced script:

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

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

})(current, previous);

Bonnie Cresswe2 · ‎08-21-2018

Hi Michael

What do you want the calendar_duration field to show? i.e. Which columns do you want to use to calculate the duration, can you give me the column names of what the start date/time field is, and what the end date/time field is?

Kind regards

Bonnie

michaelwatts · ‎08-22-2018

Hi.

I would like the calendar_duration field to show the amount of time it took to close the request from start to finish. Column names would be (sys_created_on) for start date time and (closed_at) for the end date time.

Thanks.

Bonnie Cresswe2 · ‎08-23-2018

Hi Michael

Ok so let's assume you have those two fields available on the sc_request table and you want to calculate the duration if the closed_at field changes... The business rule would be on the sc_request table.

Your condition could be:

current.closed_at.changes()

your advanced script could be:

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

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

})(current, previous);

Give that a try 🙂

michaelwatts · ‎08-24-2018

Hi Bonnie,

I added the script to the business rule. Question. Should this be an after business rule? I'm not getting the field to populate. Here is a screenshot.