In continuation to my earlier post about setting up multi-currency in Salesforce, this post will focus on how to query the multi-currency fields in SOQL and some cool formula features which you can use while dealing with multi-currency fields.
Setup
Let’s do the setup first.
- The corporate currency set in the Salesforce org is the Canadian Dollar i.e. CAD.
- Apart from the Canadian Dollar, other currencies supported are – U.S. Dollar (USD), Swiss Franc(CHF), and Indian Rupee(INR).
- Rates are also uploaded in the Salesforce org as shown below –
Scenario:
Opportunity record is having the currency field named “Opportunity Amount”. We need to fetch the Opportunity Amount based on the user’s currency mentioned in their user record.
Solution:
In order to fetch the Opportunity Amount based on the user’s currency, we can use Format() and convertCurrency() functions in SOQL.
Format(): This function can be used in SOQL select statement to format date, time, and currency fields based on the given user locale.
convertCurrency(): This function can also be used in SOQL select statement to convert the currency fields to the user’s currency.
Using the above two functions here is the SOQL statement:
SELECT Name, CurrencyIsoCode, Amount,FORMAT(convertCurrency(amount)) ConvertedinUserCurrency
FROM Opportunity WHERE Name = ‘Burlington Textiles Weaving Plant Generator’
Here is the result:
As you can see, even though in this particular opportunity, the amount is saved in USD, but with the help of above two functions, we are able to convert the currency to the user’s currency i.e. CAD.
Now if we change the user’s currency to let’s say CHF, the same query will result in different value –
Scenario:
Opportunity record is having the currency field named “Opportunity Amount”. There are opportunities with all different currencies. But we need to fetch the opportunities along with their opportunity amount converted into corporate currency i.e. CAD.
Solution:
In order to fetch the Opportunity Amount based on the corporate currency, we can use the formula CURRENCYRATE. This formula will return the conversion rate to the corporate currency for the given currency ISO code.
So we will create a new formula field in Opportunity like below –
Now if we use this field in the SOQL query, the result will show the currency converted into corporate currency.
