Calculated Duration field not sorting

Kyle Wiley · ‎09-01-2017

We have a Calculated Duration field that we have created in our instance to track how long a ticket has been opened but it does not sort correctly in a list view of the tickets.

Here is the script that is used on the field:

var res = current.resolved_at.getDisplayValue();
if (res == '') {
var cdt = gs.nowDateTime();
current.u_current_duration = gs.dateDiff(current.opened_at.getDisplayValue(),cdt,false);
} else {
current.u_current_duration = '';
}

It seems to only work once the actual record is loaded and updated that it will sort correctly, for the time being. Tickets that have not had some sort of update to them, do not sort properly in the list view. I guess this is something to do with the field checking against the current time to get it's value.

Does anyone know a way around this? Right now, the field is handy on a ticket to ticket basis but looking at a report or list of tickets, it is not helpful because you cannot sort on oldest to newest. I know that you can always check the Opened field for when it was opened but management is wanting the display of how long it has been opened in a separate, sort-able field...

Shahed Shah1 · ‎09-01-2017

So this is an actual calculated field, where you have Calculated checked and this script is in the Calculation for the field in the Dictionary?

There is a weird behaviour where the calculated value is not saved into the DB until the respective record has been updated via a user operation or script. It's not a defect as such, but just how it has worked in the platform for a long time.

When the list is being sorted on a calculated field, it is using the stored value from the DB. As the script is using values from the same record I don't see this being a problem for newly created records, since the calculation will take place on the new form and the value saved when the record is submitted.

But, this would be an issue for records created prior to the creation of this field. A hack of a solution would be to run a script in the background that would go through all the old records and do an gr.update() on them, to save the calculated value to the DB.

EDIT: Took a while to find it, but the behaviour for the calculated value is documented on the Dictionary entry form article.

View solution in original post

Mussie · ‎05-20-2018

Hi Kyllewiley,

I am trying to add a duration field to a form using your code and it is working but the only problem is, it isn't displayed properly. Any idea how I could fix this?

(function calculatedFieldValue(current) {

	var res = current.resolved_at.getDisplayValue();

if (gs.nil(res)){

//var cdt = gs.nowDateTime().getDisplayValue();
	var cdt = gs.nowDateTime();

//res = gs.dateDiff(current.opened_at.getDisplayValue(),cdt,false);
	res = gs.dateDiff(current.opened_at,cdt,false);

} else {

res = null;

}
	return res;

})(current);

Mussie

Mussie · ‎05-20-2018

Thanks guys, I was able to resolve this. I was using a string field instead of Duration and also I used an attribute

format=glide_duration

dgr1 · ‎01-13-2020

I'm seeing similar behavior, in the New York build, messing with the Needit app from github, when adding a calculated column called Hs:

(function calculatedFieldValue(current) {

var now = GlideDateTime().getNumericValue();
var openTime = new GlideDateTime(current.opened_at.toString()).getNumericValue();
return Number( now - openTime);

})(current);

gives me a column that sorts like: