+ Start a Discussion
gv007gv007 

SOQL Currency Fields

There is any way to query  currency fileds using SOQL
Prateek Singh SengarPrateek Singh Sengar
Hi,
It should be same as querying any other field. If you are talking about mulity currency enviornment then you can use convertCurrency or format method in the soql.

FORMAT applied localization to the currency format based on the user's locale
ConvertCurrency convert currency fields to the user’s currency
 
SELECT amount, FORMAT(amount) frmtAmnt, convertCurrency(amount) cnvAmnt from Opportunity where id ='YOUR_OPP_ID'


 
NagendraNagendra (Salesforce Developers) 
Hi gv007,

As Prateek said it would be similar to query any other field,but if you want to query currency fields in multi-currency orgs please find the below example with explanation.

Use convertCurrency() in the SELECT statement of a SOQL query to convert currency fields to the user’s currency. This action requires that the org has multiple currencies enabled.

The following syntax is for using convertCurrency() with the SELECT clause:
convertCurrency(***field***)
For example:
SELECT Id, convertCurrency(AnnualRevenue)
FROM Account
Use an ISO code that your org has enabled and made active. If you don’t put in an ISO code, the numeric value is used instead of comparative amounts. Using the previous example, opportunity records
with JPY5001, EUR5001, and USD5001would be returned. If you use IN in a WHERE clause, you can’t mix ISO code and non-ISO code values.

To format currencies according to the user’s local, use FORMAT() with SELECT() statements. In this example,convertedCurrency is an alias for the returned amount, which is formatted appropriately in the user interface.
SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate,
FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = <>
SELECT FORMAT(MIN(closedate)) Amt FROM opportunity
If an org has enabled advanced currency management, dated exchange rates are used when converting currency fields on opportunities, opportunity line items, and opportunity history. With advanced currency management, convertCurrencyuses the conversion rate that corresponds to a given field (for example, CloseDate on opportunities). When advanced currency management isn’t enabled, the most recent conversion date entered is used.

Considerations and Workarounds:

You can’t use the convertCurrency() function in a WHERE clause. If you do, an error is returned. 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***
For example:
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.

You can’t convert the result of an aggregate function into the user’s curren
cy by calling the convertCurrency() function. If a query includes a GROUP BY or HAVING clause, currency data returned by using an aggregate function, such as SUM() orMAX(), is in the org’s default currency.

For example:
SELECT Name, MAX(Amount)
FROM Opportunity
GROUP BY Name
HAVING MAX(Amount) > 10000
You can’t use ISO_CODEvalue to represent a value in a particular currency, such as USD, when you use an aggregate function. For example, the following query doesn't work.
SELECT Name, MAX(Amount)
FROM Opportunity
GROUP BY Name
HAVING MAX(Amount) > USD10000
You can’t use convertCurrency() with ORDER BY. Ordering is always based on the converted currency value, just like in reports.

Please mark this as solved if it helps.

Best Regards,
Nagendra.P

 
gv007gv007
Hi Nagendra,
Before posting I went through the docs,still have issue
Brian GrayBrian Gray
Thank you for the answers, I was having the same problem but I managed to solve it with your help ( cutfullprice.com (https://www.cutfullprice.com/) )