Symptoms
There can be cases where sorting of "Calculated" decimal fields won't sort the right way in list view.
Let's consider the below example:
1. Open any supported environment.
2. Let's consider "Weight" and "Max Weight" field defined in asmt_metric table.
3. Open the "Max Weight" dictionary field and switch to "Advanced view".
4. Under "Calculated Value" form section, set "Calculated" checkbox to true.
5. "Calculation" field shows up. Kindly add the following:
(function calculatedFieldValue(current) {
// Add the "Weight" field's value twice and reflect it on "Max Weight" field.
var result = current.weight + current.weight;
return result; // return the calculated value
})(current);
6. Go to "asmt_metric" table list view. (asmt_metric.list)
7. Bring "Weight" and "Max Weight" fields in the list view (via Personalize List Columns).
8. Change any record's "Weight" field to a small/large value, let's say 1.5 or 10.5
9. The "Max Weight" field will show the double value (3 or 21). Sort the "Max Weight" field in ascending/descending order.
10. You will notice the sorting order breaks and the calculated values (like above) will be in between the list.
This also breaks if we use modify the above script like this: var result = current.weight + 12.54;
Release
Jakarta, Kingston and London.
Cause
- The sort order of records is based on the value in the database and not what is displayed on the form/list.
- The Calculation will manipulate the value displayed in a list or a form, but the actual value in the database is not changed until the record is updated.
- So based on above example, the display of the "Max Weight" field was manipulated and from database perspective, it was never updated at all.
Resolution
- Technically, the calculated field is not updated at the database.
- Hence in order to do that, our recommendation is to perform GlideRecord update() via business rules or script includes.
Additional Information
Business Rules: https://docs.servicenow.com/bundle/london-application-development/page/script/business-rules/concept/c_BusinessRules.html
Script includes: https://docs.servicenow.com/bundle/london-application-development/page/script/server-scripting/concept/c_ScriptIncludes.html