12

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.

This is the formula (OK, it is called Revenue, but it doesn't matter in this example

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).

The Revenue(Lost) is 0, that is ok from a summary point of view. The 5 decimal places are a problem

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:

Entered as return value just a random integer. There is NO obvious relation to any other field.

The result:

Shouldn't be there at least this time no decimal places?!

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).

  • I've repeated steps from your question and the formula shows NO decimals in my dev org (used ROUND(rollupField__c, 0) expression). So from $ 12,25000 i am getting $ 12 – Sergej Utko Feb 6 '15 at 13:40
  • Thanks for trying! Do you have a multi currency org? Could you please change the decimals in your currency management for USD to 5 decimal places? What now? – Nisse Knudsen Feb 6 '15 at 14:08
  • No multi currency activated yet on my org. – Sergej Utko Feb 6 '15 at 15:40
  • 2
    I don't know if it is related, but I have seen formatting problems when working in a multi-currencty org and fields with "excessive" precision being stored. Our experience was when trying to format the value into an email template for an approval workflow. And like what you are describing, the formatting was ignored, and the full precision was displayed/un-formatted. We ended up having to create a formula field on the same object, and then reference that formula field. (e.g. field1__c = 5,00001 field2__c = round(field1__c, 2)) – Tezyn Feb 6 '15 at 18:30
0

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

TEXT(CEILING(YOUR_CURRENCY_FEILD))
0

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

0

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 :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.