Pesky Decimal Place Digits in Salesforce Currency Fields

[fusion_text]If you have experience with Salesforce Currency and Number fields, you have certainly had the need to customize the number of decimal places displayed. Salesforce does a reasonably good job of handling Currency and Number fields seamlessly for you with a few exceptions related to the precision of the data.

Test Setup

In order to better understand the issues at hand and see it for yourself, follow these instructions to setup test fields and test data in a Salesforce Developer Org or Sandbox:

Create a new field on OpportunityLineItem with these properties
1. Name = Test_Currency
2. Type = Currency
3. Decimal Places = 15, 3
Create a new Rollup Summary field on Opportunity with these properties
1. Name = Test_Currency_Rollup
2. Type = Roll-up Summary
3. Summarized Object = Opportunity Product
4. Roll-Up Type = SUM
Create a Trigger on Opportunity Line Item with the following code
1. Events – before insert and before update
2. Code – simplified for testing purposes
  [code language=”javascript”]
  trigger OpportunityLineItemTrigger on OpportunityLineItem (before insert, before update) {
  if (trigger.isBefore amp;amp;amp;amp; (trigger.isInsert || trigger.isUpdate)) {
  for (OpportunityLineItem oli : trigger.new) {
  oli.Test_Currency__c = ((oli.UnitPrice*oli.Quantity) * (21.999/100.0));
  }
  }
  }
  [/code]
Create a new Opportunity with 3 Opportunity Line Items with these values for their Unit Price (Sales Price) and Quantity. The Test Currency value in parenthesis is what will be calculated.

After our OLIs are created and the before insert trigger has fired, we can expect our Opportunity Test Currency rollup summary field to have a value of $60,497.202 (the sum of our above three Test Currency value rounded to 3 decimal places) as is shown below.

Our Currency fields display with only 3 decimal places but in the backend, the precision is different, as the trigger code does not perform any truncation or rolling up/down on the value. You can see this in the screenshot below.

It is also easily done by running some Anonymous Apex code as follows:

[code language=”javascript”]
Opportunity opp = [select Test_Currency__c from Opportunity where Id=’006o000000B2NMuAAN’ limit 1];
System.debug(‘======= opp.Test_Currency__c = ‘ + opp.Test_Currency__c);
[/code]

Now that we have our test objects and data setup, lets take a look at how changes to a Currency field’s decimal place specification can quietly wreck havoc.

Problem Description

Lets say we have an Opportunity Line Item Trigger that calculates an additional value for each Opportunity Line Item, saving it into a custom Currency Field (called Test Currency for our purposes). The field is defined to have decimal place digits as follows:

15 digits to the left of the decimal place
3 digits to the right of the decimal place

This field was rolled up into a Rollup Summary field on the Opportunity. Sounds good so far right? So the Trigger calculates a value for this field based on a number of different fields. For simplicity and demonstration purposes, we will only use these two fields in our calculation:

UnitPrice
Quantity

So whenever an OLI is created or updated, the value of our field is recalculated using this calculation inside of our OLI Trigger

[code language=”javascript”]
oil.Test_Currency_c=((oli.UnitPrice*oli.Quantity)*(21.999/100.0));
[/code]

Then let’s say that we actually only want 2 decimal places for this field. So we modify the definition of the custom field to be 14, 2 (14 digits to the left and 2 to the right) from 15,3. Salesforce warns us “Making this change may cause data loss by truncating values that do not meet the new restrictions.” and we say OK which changes the field definition. Now lets see what has happened to our existing records.

At this point, if I refresh my OLI View Page, I see that the Test Currency value displayed now only shows 2 decimal places the value with the as expected. This makes sense to me. However, looking at the records in the database, I notice that their values have not changed (they are still un-truncated). This also seems OK to me as the database is preserving the precision for you but is only displaying the requested digit to the end-user. Since Salesforce end-users are not likely to be running SOQL queries on the backend, they are not likely to notice the discrepancy.

But what about our Rollup Summary field value – is it still correct? The UI is only displaying the value of the field rounding up or down to the specified of number decimal place digits but the database is storing all the decimal place digits so which values is the Rollup Summary value aggregating since the display and database values are in a different state.

As you see above, the Rollup Summary is based on database values, NOT the values displayed in the UI which also makes sense as the calculations are sourced from the database. Salesforce is simply truncating the extraneous digits for display purposes. So summing up the values from the UI, the Rollup Summary is off by 1/10 of a cent. The value should be $60497.21 but in fact it is $60497.20.

So why did this happen and what can we do to correct it?

Underlying Problem

By default, Currency fields created in Salesforce have the following properties

18 places to the left of the decimal place and 0 decimal places to the right
If you change the value of a Currency or Number field via the UI
1. Salesforce will truncate the value if necessary (rounding up or down as defined here: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_decimal.htm)
2. It is stored in the database with this new truncated value
3. It is shown to the user with this new truncated value
However, if you change the value of the Currency or Number field via Apex.
1. Salesforce does NOT truncate the value when saving to the database. It saves it with all of the digits after the decimal places regardless of the field definition.
2. No truncation or rounding up is performed unless explicitly done so.
3. When editing this record in the UI, all decimal places are shown (mirroring what it is in the database and ignoring the field specification). NOTE: If you subsequently edit and save this same record in the UI, the rules for #2 above apply assuming other Apex triggers or classes are not executed.

In some ways, this behavior makes sense right? The UI takes care of the underlying formatting issues for you (similar to making TimeZone adjustments to DateTime fields). But when you are updating data on a SObject from within Apex, then Salesforce assumes you know what you are doing and leaves the formatting to you. On the other hand, this behavior is very confusing and disconcerting, leaving your data in an inconsistent state.

The most confusing part of this is when you have altered the field definition to have less decimal places (ie from 15.3 to 16.2) but you have not yet updated existing data to reflect the new precision. For instance, in our example above, we have an Opportunity Line Item record with a Test Currency field value of $32,998.4978001 (database value). As seen here, it is displayed in the View Page as $32,998.50.

Then it is shown as $32,998.498 in the Edit Page.

Why is this? The records created prior to the field change still have 3 digits to the right of the decimal place UNTIL such time as you update the record, not the field, just the record. In fact, you do not even need to change any fields – all you need to do is open the record up for Edit, hit Save and SF will auto-magically perform its truncation and field will have correct format in the display AND in the database going forward. This is really important as it is confusing. You open the record for edit, and the field shows 3 decimal places, you change a different field and save the record. Next time you go to edit the record, the field shows a different value that was changed without any user interaction or custom code.

Recommended Solution

Finally – what can you do if you find yourself in the situation described above where the field definition has changed and you have existing records that need to be updated to reflect the new precision. Here are two suggestions, one for the immediate correction of inconsistent data, the other a best practice to minimize discrepancies from the start.

Mass Data Update

To ensure that this does not happen to you, if you are changing a Currency field definition to have less decimal places, consider doing a bulk data exercise to have Salesforce automatically truncate and round (where necessary) the value of that field on all existing records. If there is special handling you wish to do in terms of truncation and/or rounding rules, then you will need to write an Apex trigger to handle this. Otherwise, there is no need to actually change the Currency field – you just need to apply an update to the record, instructing That will instruct Salesforce to update the record, doing its work behind the scenes. There are several ways to do this depending on the number of records.

With the use of a tool such as DataLoader, or Apex DataLoader.
Manually through the UI. For each record in question, open the record for Edit and hit Save. You do not even need to change any actual data values, Salesforce saves the record and will apply any truncation/rounding based on field definitions.
Through Apex anonymous code or a trigger. (This is required if you have special processing to do and do not want Salesforce default truncation and rounding rules to apply).

Truncate and Round Values in Apex Code or Trigger

To ensure accurate currency values and to prevent discrepancies, you should round Currency values (and Number values where applicable) to your Apex code so its accuracy will match that of the Salesforce UI. NOTE: This does introduce a dependency between your code and the Currency field definition. If and when the field definition is changed to have a different number of decimal places, the code needs to be updated. The simplest way to do this is with the following Apex code:

[code language=”javascript”]
oli.Test_Currency__c = oli.Test_Currency__c.setScale(2, RoundingMode.HALF_UP);
[/code]

where

2 is the number of digits to the right of the decimal place
HALF_UP means that Salesforce will round the value of the digit being discarded up when its greater than 0.5 and down when its less than 0.5.

Once this code is in place, the behavior for updating a Currency Field will behave similarly for the UI and Apex code and triggers.

That’s it! Hopefully this helped shed some light on Salesforce’s behavior when it comes to Currency fields and changing the decimal place definition. Happy Coding![/fusion_text]