8

In Multi-Currency orgs, when a record's Currency is set to a different Currency than the running user's chosen Currency, all Currency fields on that record will be rendered in both the record's Currency AND the running user's Currency, like this:

Currency field

I want to be able to do a single SOQL query to retrieve both of these currency values.

So, say that I have a Currency field Price__c on my object. I want to be able to do something equivalent to the following:

[select Id, Price__c, convertCurrency(Price__c) from MyObject__c]

However, it appears that SOQL only lets you retrieve EITHER Price__c OR convertCurrency(Price__c), but NOT both.

Any ideas on how I could retrieve both without having to do a second SOQL query??

3 Answers 3

9

Use a field alias for the aggregate expression. For example:

[select Id, Price__c, convertCurrency(Price__c) ConvertedPrice from MyObject__c]
2
  • 1
    Wow, I was not expecting this to work without this being an aggregate query, but I tried it and indeed it works! It doesn't appear to work when running a SOQL Query from Developer Console, but I think that's just a limitation of Developer Console.
    – zachelrath
    Sep 14, 2016 at 19:21
  • 1
    Any idea how to access that alias I am not able to access ConvertedPrice from this List. Feb 27, 2021 at 18:08
2

As far as I can tell, the only way to get at this data is to have at least 2 SOQL queries: 1 to get the data, and 1 to query either the CurrencyType table (for simple multi-currency orgs) or DatedConversionRate table (for advanced currency management orgs), which both provide conversion rates. Once you get the conversion rates, you have to manually perform the conversions in order to achieve the desired effect.

-1

I'm working on the same problem. The only solution I could find that avoids multiple queries, is to create a currency formula field declartively and to query that field as well.

2
  • 1
    Gorav - welcome to SFSE; it would be helpful if you elaborated more on your solution
    – cropredy
    Aug 6, 2014 at 22:06
  • 1
    Gorav, I think you're onto something here, but I had to downvote it since in it's current format it isn't really helpful to somebody not familiar with the intricacies of currency handling. As @crop1645 mentioned, can you elaborate a bit? I'd love to turn my downvote into an upvote. Aug 6, 2014 at 22:59

You must log in to answer this question.

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