I need to compare QuoteLineItems and Opportunity Products in a trigger, but the queried rows amounts are being automatically converted to user-profile's currency, failing my comparison. How can I force queries to not convert the amounts?


My opportunities are being registered in USD. Some users have another currency in their profile. I have in my trigger a comparison between QuoteLineItem and its corresponding OpportunityProduct that checks their UnitPrice. These are the queries:

String qliQuery = 'select Id, QuoteId, PricebookEntryId, UnitPrice, Quantity, Discount, ServiceDate, SortOrder, CurrencyIsoCode' + qliFields + ' from QuoteLineItem where Id in (' + qliIds + ') order by QuoteId, SortOrder ASC';
List<QuoteLineItem> qlis = Database.query(qliQuery);

... and:

String oliQuery = 'select Id, OpportunityId, PricebookEntryId, UnitPrice, Quantity, Discount, ServiceDate, SortOrder, CurrencyIsoCode' + oliFields + ' from OpportunityLineItem where OpportunityId in (' + oppIds + ') order by OpportunityId, SortOrder ASC';
List<OpportunityLineItem> olis = Database.query(oliQuery);

My comparison fails in their UnitPrice, with the difference being exactly the currency conversion rate between those two currencies. I checked both CurrencyIsoCode values but both are USD.

Another interesting fact: this is a trigger responsible for syncing custom fields between Opportunity Products and QuoteLineItems. The UnitPrice amounts are not converted when some of the synced fields are populated.

I don't know why it is doing this conversion, but is there some way to force queries not to convert it?

  • It shouldn't be doing this, because all values should normally be expressed in the Default Currency for the org. Did you make sure your Default Currency is set correctly?
    – sfdcfox
    Aug 8 '17 at 20:12

You need to use convertCurrency(***field***) in the SELECT statement of a SOQL query to convert currency fields to the user’s currency.

Refer: Querying Currency Fields in Multi-Currency Orgs

Also, think about using FORMAT() to format currencies according to the user’s local.

That way your comparison will work.

  • I want to NOT convert it to the user's currency. For some reason the amounts are being "converted" (amount changes but iso code remains the same). One item has (UnitPrice=1872.00, CurrencyIsoCode=USD) and the other has (UnitPrice=4118.40, CurrencyIsoCode=USD), which is applying a 2.2 conversion factor. Aug 8 '17 at 19:18
  • what are the different currencies in each records? All the records currencies are not same. Mean to say, what is currency of Opportunity, Pricebook, Quote records and as well as User's currency Aug 8 '17 at 19:23
  • Opportunity, Quote, QuoteLineItem and OpportunityProduct are all in USD. The User's currency is BRL. Aug 8 '17 at 19:27
  • so, any specific reasons you do not want to use values from user's currency? Aug 8 '17 at 19:30
  • When I realized the error was because of User's currency, I have changed his profile to USD, he shouldn't be assigned to BRL at all. But anyway this issue intrigued me and I want to understand and fix it. I have some other users that are allowed to have processes in different currencies and this current behavior will result in errors if not fixed. Aug 8 '17 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.