Calculated Duration field not sorting

[Kyle Wiley]

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...

[Kyle Wiley]

Not sure if it matters or not but the display value of the field might show "34 Minutes" in the ticket but once you check the actual value, it displays "1970-01-01 00:34:00"

[Shahed Shah1 · Accepted solution]

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.

[Kyle Wiley]

Yes, the calculated field is checked in the dictionary.

The old tickets shouldn't be affecting the calculation anymore though I wouldn't think because if the ticket has been resolved, we are clearing that duration field out and the filters and reports that we have setup are not looking at the Resolved/Closed incident states, only those that are active.  

[Kyle Wiley]

So essentially, this is a platform limitation that we will not be able to get around if we wish to sort on a calculated field?   And there is no other way to calculate a current duration other than using the calculated field or a business rule that mass updates all of the open records so it can be sorted?