Opportunity Amount - Formula field not displaying currency

Question

We have a custom object and in that custom object, we have a formula field, which will display the opportunity amount... (Formula field return type is Currency) Opportunity__r.Amount

In the opportunity record the Amount is displayed in the below format:

Oppty currency Amount (User currency)
e.g: CAD xxx (USD xxxx)

Whereas in this formula field in the custom object, it just displays the user currency..in the below format

User Currency Amount
e.g:USD xxxxx

This is the case when opportunity owner view this record. If someone else views this custom object record, then displays the formula field in the below format:

OpptyOwnerCurrency Amount (Logged in user currency)

It is NOT considering the opportunity currency.

I want to show this formula field in the same format as the opportunity amount field:

CAD xxx (USD xxxx)

please suggest any workarounds for this issue..

Advanced Currency Management is not enabled.

Answer 1

Once multi-currency is enabled in your org, most objects in the database now have a CurrencyIsoCode field on them. Just because objects like the Opportunity object and your custom object are related by a lookup or master-detail, does not mean that the CurrencyIsoCode codes will be the same. These are separate records in the database, with separate CurrencyIsoCode fields. It sounds like your custom object record has a CurrencyIsoCode in USD. If you change it to CAD, then it will render the currency field in the same format as your Opportunity.

You might consider a Visualforce page for the new and edit screen of your custom object if you need to keep the currency sync'ed to your Opportunity.

Answer 2

I've used this formula before.

IF( 
  someCurrencyField__c >= 1000000, 
  TEXT(FLOOR(someCurrencyField__c / 1000000)) & ",", 
  "") & 
IF( 
  someCurrencyField__c >= 1000, 
  RIGHT(TEXT(FLOOR(someCurrencyField__c / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(someCurrencyField__c)), 3) & "." & 
IF( 
  MOD(someCurrencyField__c , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(someCurrencyField__c , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(someCurrencyField__c , 1), 2) * 100, 99)) 
)

This approach actually uses a TEXT formula so that you have control over how the numbers are displayed on your custom object.

For more detail, I got it from an Appirio blog post originally.