+ Start a Discussion
dhoechstdhoechst 

SOQL aggregation with multi-currency

I have the following SOQL used in a VisualForce page:

 

"Select c.Product__r.PA_Product_Class__c productClass, sum(c.Rolling_12_Current__c) sales from PA_Customer_History__c c where c.Account__c = '{!account.Id}' group by c.Product__r.PA_Product_Class__c"

 

The field Rolling_12_Current__c is a currency field and the records I am querying are all in USD. Our corporate currency is EUR. My user is set up with a currency of USD. When the query is run, the sum() returns back a converted value in EUR instead of USD. According to the documentation at http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_querying_currency_fields.htm, this shouldn't happen: "Currency data is converted to the user's locale and then processed by the aggregate function."

 

My user's currency is in USD, so it should display it properly in USD. As a temporary work around, I've changed our exchange rate to 1. Can someone please let me know how I can get the SOQL aggregate to return the values in my user's currency? I tried using convertCurrency, but I get a malformed query error.

dhoechstdhoechst

Just bumping this. Has anybody else run into this problem?

Slaviša MaslićSlaviša Maslić

Hi,

I have the same issue in my Production Org and Sandbox. According to the APEX documentation aggregated currency fields are converted automatically to the running user's currency. However, this is not the case.

 

SFDC- Developer support suggested to define the class with the "with sharing"-keyword. But this doesn't help either.

I'm now waiting for a response from SFDC-dev support. This seems to be a SFDC-Bug!

 

I'll keep you in the loop.

 

Cheers!

YishayYishay

HI Guys,

Do you have any news from SF regarding this bug

 

Thank you

Yishay

Slaviša MaslićSlaviša Maslić

Hi Yishay,

SFDC support told me that they are working on this issue and it should be released in one of following releases.

 

Cheers

Sascha

RovRov

Does anyone know if this bug has been resolved?

JJE_OLDJJE_OLD

Hi,

 

I just came into the same issue. I opened a case to the support.

Did you get any answers?

 

Regards,

RovRov

Salesforce premium support calls me every week, only to say that they are monitoring the case and don't know why this is happening. My case has gone from one Developer Support to second to third.

 

In our case, I have ruled out the possibility of any problems with the formula or trigger. We are trying to rewrite the Apex class. If I have a resolution, shall post it here.

JJE_OLDJJE_OLD

Hi,

 

The Premier Support just gave me a solution.

I have to get the following aggregated result (SUM) from opportunities And copy it in a custom object (Quotation__c) field.

 

AggregateResult[] QuotedOps = [SELECT quotation__c, SUM(FeeProrated__c) FROM opportunity WHERE quotation__c =: quos
GROUP BY quotation__c];

Here are my quotation__c records:

 

List<Quotation__c> quotations = [SELECT Id, FeeTotal__c, CurrencyISOCode FROM quotation__c WHERE Id IN: quos];

I get the quotation currency ISO code:

 

for(Quotation__c quo: quotations){
    string quotationCurrency = quo.CurrencyIsoCode;

I get the conversion rate from my company default currency by querying the currencytype object:

 

    Double conversionRate = [SELECT conversionrate FROM currencytype WHERE isocode =: quotationCurrency LIMIT 1].conversionRate;

    for(AggregateResult ar: QuotedOps){
        qID = (Id)ar.get('quotation__c');
        if(quo.Id == qID){

Then to get the correct amount, I have to multiply the aggregated sum by the conversion rate to the quotation currency:

 

            quo.FeeTotal__c = (Decimal)ar.get('Total')*conversionRate;
        }
    }
}

I though that currency conversion was automatic, but it appears that you have to manage it in your code.

I hope this helps.

dhoechstdhoechst

Thanks for the workaround. Looks like they changed the help docs to match functionality. I just posted an idea about this to allow convertCurrency on aggregates: https://sites.secure.force.com/success/ideaView?c=09a30000000D9xtAAC&id=08730000000b1lLAAQ. Vote it up, please!

chris_parxchris_parx

dhoechst wrote:

Looks like they changed the help docs to match functionality.



Yes indeed... it's a feature, not a bug !.. :smileymad:

ministe_2003ministe_2003

Sorry to dredge up an old thread but I'm doing this at the moment and just wanted to point out the fact that if your org uses dated exchange rates then this method will not necessarily give you the right results.  You'll need to query the DatedConversionRate object to get the correct exchange rate at the time the opp was closed.

oski93oski93

Sorry also for adding to this older thread, but I have just encountered this issue as well.

 

The docs currently state,

 

"If a query includes a GROUP BY or HAVING clause, any currency data returned by using an aggregate function, such as SUM() or MAX(), is in the organization's default currency. "

 

However, I am finding that even if the query does not include a 'GROUP BY' or 'HAVING' clause, the aggregated value returned from SUM(), is in the org's default currency and not in the CurrencyIsoCode of the aggregated records. This seems very clear when SUMming a recordset with just one record. 

 

Can anyone else confirm that this statement in the docs is inaccurate?

 

Thanks.

Rami TaybaRami Tayba
I have the same issue also
pkpnairpkpnair
This is how I solved it. May be this helps
http://how2salesforce.blogspot.nl/2017/01/currency-conversionin-multicurrency-org.html
 
VasantVasant
Yes this post was originally created in 2011 and its 2017 now and we still have this issue i.e. aggregate query returns value in USD and not in record currency. This is really really bad on salesforce part. 
Graeme Smith 7Graeme Smith 7
This is a nightmare, is there no fix for this still after 10 years?? I'm working with bulk records so querying out for conversion rates is not going to fly.
Samar Rizvi 16Samar Rizvi 16
There is simple workaround for it. Create a Formula Number Field, and set its value with currency field that you have. When you do the sum of this formula field, you will get result in the record currency and not organization currency.
Stephanie Boggs 17Stephanie Boggs 17
@Samar Rizvi 16 deserves a lifetime achievement award just for this solution!! <3 
Samar Rizvi 16Samar Rizvi 16
Lol. Thanks Stephanie!