Salesforce: Query Multi-Currency Field

When you have multi-currency enabled in your Salesforce org., you will see the converted value of currency fields in the page layout, list view, and report.

Creating Record

When creating a new record, the default selected currency is User currency, instead of Corporate currency.

Viewing Record

• If User currency equal to record currency --> no amount conversion 
• If User currency different from record currency --> record currency appear at front, continue with user current and the conversion amount under brackets. This has nothing related to corporate currency.

Page Layout

My user currency is USD and the corporate currency is USD too.

If I change my currency to SGD

List View

The same goes for ListView, if the record currency is different from your user currency, the list view will show the amount in the opportunity currency and the value in your currency in brackets.

Report

Currency fields in reports are shown in their original currencies. Salesforce gives the option to get the values in the Opportunity record currency and in the converted amount -- by default, when creating a report, the converted currency will be in the user personal currency, but the user will be able to change to any active currency.

SOQL

When you query Salesforce currency fields, SOQL will always return currency values as defined in the CurrencyIsoCode.

Format() and convertCurrency()

We can use Format() and convertCurrency() functions for currency field in SOQL:
- Use FORMAT with the SELECT clause to apply localized formatting to standard and custom number, date, time, and currency fields, the format applied these fields reflect the appropriate format for the given user locale.
- Use convertCurrency() in the SELECT clause to convert currency fields to the user’s currency.

Looks at this sample: SELECT Id, CurrencyIsoCode, Amount, convertCurrency(Amount) UserAmount, FORMAT(amount) TextAmount, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity order by currencyisoCode

You can’t use the convertCurrency() function in a WHERE clause. If you do, an error is returned, but you can use the following syntax to convert a numeric value to the user’s currency from any active currency in your org. WHERE Object_name Operator ISO_CODEvalue

e.g. SELECT Id, Name FROM Opportunity WHERE Amount > USD5000
In this example, opportunity records are returned if the record’s currency Amount value is greater than the equivalent of USD5000. For example, an opportunity with an amount of USD5001 is returned, but not JPY7000.

How to get currency value in corporate currency using SOQL?
In Spring ’18 release, Salesforce introduces new formula functions ADDMONTHS, CURRENCYRATE, MCEILING, MFLOOR and WEEKDAY. So this purpose, we can make use of CurrencyRate() function.

CURRENCYRATE returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0.

We can create a simple formula field
Amount / CURRENCYRATE(TEXT(CurrencyIsoCode))

Here is the result:

If you implement dated exchange rate, CurrencyRate() function do not support it yet, it will always use the standard exchange rate, unless you always keep the conversion rate aligned.

Reference:

• Formula Operators and Functions
• Querying Currency Fields in Multi-Currency Orgs
• Format()
• New Formula Functions