This Currency related formula functions very much expected by many users having clients across borders. When it finally came out in Spring 18′ Release I was very optimistic about its functionality – expecting it would return a server value similar to NOW() function. Sadly, it didn’t. What it does is just returns the Exchange rate value that you specify in Multicurrency Setup menu. Which is very useful when you manage sales across countries.
This said it can still serve many multi-currency related use cases. A couple of examples and tips on how to use it right are below.
First off, you need to Enable the Multi-Currency in the Setup – Company Information.
Then add the currencies in the Currency Management page
Return the conversion rate to the corporate currency for the given CurrencyIsoCode, or 1.0 if the currency is invalid.
NOTE: the naming of this function is slightly misleading and may result in a wrong understanding. It’s thus important to note that it will NOT return the currency based on Dated exchange rates, it will return the currency RATE FROM MANAGE CURRENCY RATES TAB.
CURRENCYRATE(IsoCode); IsoCode – the currency code in the ISO format, you will also have to put it in quotes (ex. “EUR”) as the expected value is Text. The return format of the result is Number – keep it in mind when writing your formulas.
The full list of ISO CODES can be found in your Currency Management page in Setup, as you can only reference the currencies that you have enables for your org it’s the best place to go.
USE CASE1: Calculate a level of customer support based on the Cumulative Purchase Amount with Multi-Currency enabled. The Support Level is defined as Gold for the Purchase Amount Higher than USD 1000/GBP 800/EUR 750. Depending on the Currency for each customer Amount Field can only show one value, and thus the equivalent has to be calculated
You can use the “Opportunity Currency” field in a formula to know to calculate this:
Support level (text) =
IF (Amount / CASE(TEXT(CurrencyIsoCode), ‘GBP’, 8, ‘EUR’, 7.5, 1)) > 1000, “Gold”, “Standard”)
USE CASE2: display the reference Opportunity Amount in 3 different currencies on the Opportunity detail page.
Make sure you read the NOTE section above before
- Create a 3 formula field for the Opportunity Object for each of the currencies respectively.
I decided to fancy up the outcome by adding the Currency sign. That’s why I chose the Text format, and converted the result into text after calculations. Also, I used the MCEILING function to round it up to the nearest integer (and kill 2 birds in one stone to show the example of the function below)
Amount in USD (text) =
TEXT( MCEILING(CURRENCYRATE(“USD”)) * Amount) & ” $”
Amount in GPB =
TEXT( MCEILING(CURRENCYRATE(“GPB”)) * Amount) & ” £”
Amount in JPY =
TEXT( MCEILING(CURRENCYRATE(“JPY”)) * Amount) & ” ¥”