My user currency is USD and the corporate currency is USD too.
If I change my currency to SGD
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.
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.
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
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.
- Formula Operators and Functions
- Querying Currency Fields in Multi-Currency Orgs
- New Formula Functions