A Tip A Day #18 – Rollup Summary Decimals in Database

This post is a part of the daily blog series 

A Tip A Day, daily dosage of learning!

Day #18 – Rollup Summary Decimals in Database

Lets say you have a rollup summary field of decimal on your master object record which is a SUM of currency field of your child records.

Scenario: For a child record if we entered the value of 1000.  In master object, the rollup summary will give value as USD 17.90 (INR 1000.00) This is because Org value is USD and we have entered value in INR

In the rollup field value in your page layout or report or list views is 17.90. (precision: 2) However, if we query the value using SOQL it will be like 17.8571428571428571428571428571 !

Explanation: Don’t be surprised looking at that long number.   The reason for this behaviour is the way values are stored in the database.  The entire number is stored in the database of Salesforce.

The UI precision does not affect the precision of the database, which is floating point. This is very useful for industries like financial institutions (banks) which do not want to round off fractions of money because these values add up over time to a larger number.

Workaround: 

In Apex, we can use setScale() method to set the precision, similar to the precision in UI.

Decimal rollup = 17.8571428571428571428571428571;
Decimal rounded = rollup.setScale(2);
system.debug(rounded); //17.85

Read all other tips of the blog series here – A Tip A Day, daily dosage of learning!