Monday, September 9, 2019

Salesforce: Query Multi-Currency Field

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

Page Layout
My user currency is USD and Corporate currency is USD too.

If I change my currency to SGD

List View
The same goes to List View, it will show the amount in the opportunity currency and the value in your currency in brackets.

In the report, Salesforce gives the option to get the values in the Opportunity currency and in the converted amount -- which is the corporate currency.

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 standard exchange rate, unless you always keep the conversion rate aligned.


No comments:

Post a Comment

Page-level ad