1

TL;DR:

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?

Details:

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?

1
  • 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, 2017 at 20:12

1 Answer 1

0

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.

5
  • 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, 2017 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, 2017 at 19:23
  • Opportunity, Quote, QuoteLineItem and OpportunityProduct are all in USD. The User's currency is BRL. Aug 8, 2017 at 19:27
  • so, any specific reasons you do not want to use values from user's currency? Aug 8, 2017 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, 2017 at 19:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .