by Glenn Weinstein, Appirio
We learned recently that Salesforce enforces decimal points specified in field definitions only at the user interface (UI) level, not at the database or API level.
For example, define a custom currency field, say, “Salary.” Give it length = 16, and decimal places = 2.
You might think that you’re restricting the precision of the field to two decimal places.
At first blush, your assumption seems to prove correct. On the UI level (on a standard edit page), if you try to type in, say, $123.456, it’ll round off to $123.46 when you click “Save,” and store it in the database as $123.46.
But if you then set a value via API, say $123.456, it’ll be stored in the database – and returned via API (e.g. SOQL query) – as $123.456.
Surprised? We were.
In fact, SFDC will let you store up to 24 decimal points via API, regardless of the decimal places provided in the field definition. So you could save $123.123456789012345678901234.
You might think “who cares?” But it matters when you have code that implicitly assumes currency amounts will have no more than 2 decimal places. Your next thought might be “let’s write a validation rule to throw an error if there are more than 2 decimal places provided.” But this issue arises precisely in situations where we don’t have a user simply trying to type in a value. So a validation rule could cause unexpected, and probably undesirable, errors in Apex triggers and methods, as well as integrations with external systems.
In our case, this situation proved problematic when a field value is used later in an outbound integration. We use FinancialForce PSA, a native AppExchange app, to track billable timecard data, and we’ve written a custom integration to pass PSA data to our Workday Financials system via a web services call. Workday Financials, in turn, expects dollar amounts to have no more than 2 decimal places. This worked fine for several years, as our bill rates were always in round dollars. But our integration unexpectedly failed when we had a project with an unusual bill rate that, when multiplied by a fractional number of hours, resulted in a billable amount with more than 2 decimal places. Workday replied to our web services call with an error, which took awhile to resolve to its root cause.
We have also seen this issue manifest itself in “incorrect” totals displayed via UI. Imagine a screen that displays a series of line items with prices, each rounded to two decimal places, followed by a sum total, also rounded to two decimal places. The total may not actually match the sums of the rounded-off values! Good luck troubleshooting that one – each line item will appear correct on standard UIs, so you’ll only detect the issue if you query the line items’ price values via SOQL.
The remedies we could think of were –
- Fix callouts to APIs that expect 2 decimal places, by rounding the value before calling the outbound web service.
- Fix UI display logic to round values to 2 decimal places.
- Write an “after insert / after update” trigger to round the value to 2 decimal places.
- Create a formula field based on the actual field, but round the value to 2 decimal places.