Currency and numeric fields are displayed without the decimal part of their valueSymptoms
All values for numeric fields (decimal fields, currency, price) are displaying a truncated value for the decimal part. Though the values are stored in the database correctly with decimal values, on the UI (anywhere in the ServiceNow application), the decimal part is not displayed for decimal fields. For currency and price fields, the decimal part shows as ,00. The values are not rounded (based on the decimal value in the database); the decimal value is just truncated from the value.
When using show XML on a record to see the values retrieved from the database, the decimal values are also not there.
This issue is likely to occur on self-hosted instances only, using an Operating System installed in non-US English.
In the database, the values are stored correctly with decimal values, using the (.) as the decimal point:
However, the application servers (nodes) are started with a user that has the $LANG environment variable set to a locale that uses a comma (,) for the decimal point.
When the application nodes are started by that user (using /glide/bin/glide.sh or /glide/nodes/<instance_port>/startup.sh), it expects comma values for decimal points, which it does not receive from the database (as that sends '.'). As a result, the part after the decimal point is ignored and the value is displayed as truncated for decimal fields, with zeros for the decimal part of currency and price fields.
$ echo $LANG
Before starting the application nodes, ensure the language variable (LANG) is set to a value that uses (.) for decimal points.
For example, for an instance called labinst, running on port 16000:
$ set LANG='en_US.UTF-8'
----- or (depending on OS) -----
$ export LANG='en_US.UTF-8'
$ echo $LANG
$ /glide/bin/glide.sh start
Starting glide software:
Cleaning local tmp: /glide/nodes/labinst_16000/tmp
Starting Glide (labinst_16000)...
The locale settings used for displaying data in the application are determined by the glide.system.locale system property. If that is set to a locale that uses a decimal point as comma (For example, fr.FR), then the users see a comma instead of a decimal point, even though the database stores a decimal point.
At user level, the system property can be overridden by the country (and language) in the sys_user record. That determines how numeric values are displayed.
Starting the nodes with the modified LANG variable displays new and existing values for numeric fields containing decimal values correctly, as they were stored in the database with decimal values.
When re-queried, newly entered records display correctly:
|Note: For currency and price fields, the display value for existing records (entered when the application nodes were started with the invalid LANG setting) still show as ,00:|
For displaying values in the application for currency and price fields, the values are not read from the base table, but from tables fx_currency_instance (for currency fields) and fx_price (for price fields) (which reference the base table, record and field). That allows the application to deal with multiple currencies. The values are stored without decimal places (saved when they were created when the application servers were started in french). New records created after the variable change are stored there with decimal values.
Updating the values manually in the form updates them in the database and they display correctly when queried. Alternatively, a datafix script is required to modify existing values in fx_currency and fx_price. They have a reference to the base table, record and field which has the correct value in the reference currency. In a single currency instance, this is the same as the currency used in fx_currency_instance and fx_price. In a multi currency instance, conversion would be required.