Create a currency formula field with no decimal places for Standard Page Layout

Question

We have the following challenge with currency fields:

Scenario: On child record(s) (in a multi-currency org) we have pricing information which is stored as a currency value with 5 decimal numbers in the database. The user needs to be allowed to enter such fine/small values. On the parent record we want to summarize all of its children's prices into one number. Because it is at a higher level view, we are not interested in 5 decimal places.

Initial Solution: We created Roll-Up Summary fields, which has no Decimal Number setting or possibility to round. So, additionally for "view reasons" in the standard page layout, we created a formula field. This formula has Return Type: CURRENCY and Decimal Places: 0.

Problem: But when we look now at the page layout, we see that still 5 decimal places are returned (We also tried adding ROUND(x,0) and FLOOR(x), it didn't help).

My thought was that maybe it is due to the roll-up from a 5 decimal places currency field, but I tried this and the result was even more strange:

The result:

Can anyone explain what is happening and maybe also how to solve this?

My guess so far was that Salesforce takes compares all existing currency fields on a custom object and select the finest/most detailed decimal places setting and applies it to all other fields. But that calculations are simpler and there is a consistent view for the user (on my object are other currency fields, besides the roll-up summary that I described earlier).

Answer 1

I tested this before and after enabling multi-currency on a Developer Edition, and can confirm that that's when the behavior changes.

Fortunately angry_at_russia's solution still works: create a TEXT formula and format it as you like. You may need to amend the format and/or rounding though, e.g.

"USD " + TEXT(ROUND(Your_Rollup_Field__c, 0))

So if the rollup value is 43.94501, this formula reads "USD 44".

You may run into more problems with that assuming it isn't always going to be "USD" as in my example. I'm not aware of a formula function that can read currency though :(

Answer 2

You can create a text formula for displaying amounts to the users like this:

TEXT(CEILING(YOUR_CURRENCY_FEILD))

Answer 3

I found this example and to round 3 places to 2 places. Instead of the actual digits "100,2", you would enter your field/variable. I did not test this!! Also http://resources.docs.salesforce.com/208/15/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf#customize_functions_i_z__ROUND

ROUND(100,0) = 100

ROUND(100+0.5,0) = 101 which is probably not the answer people would want.

One way around this is to add just less than half a digit. Typically you would want to do this to one decimal place greater than the possible decimals in your number. For example:

ROUND(100+0.499,0) = ROUND(100.499,0) = 100 but anything greater than 100.001 would round up to 101: ROUND(100.001+0.499,0) = ROUND(100.5,0) = 101