SOQL Aggregate calculation error due to multi-currency

Question

I'm trying to calculate the SUM of all values in my Target__c field, on my Target__c custom object.

When the below code is executed, my debug log shows that updatedTargets contains the correct target record, teamTargetIden contains the correct identifier (a formula field key) and targetsToUpdate has contains the correct value (120,000) from the Target__c record (this is the only record with the selected identifier).

But the SOQL SUM produces a figure of 178,491.74 and I can't work out why?

trigger MonthlyTeamTargetCal on Target__c (after insert, after update) {

    List<Target__c> updatedTargets = new list<Target__c>();
    for (Target__c t : Trigger.New){

        try{
            Target__c oldT = Trigger.oldMap.get(t.Id);
            if(oldT.Target__c != t.Target__c)
            {
                updatedTargets.add(t);
            }
        //if old.Map is null, record has been inserted & (re)calculation should be done
        }catch(System.NullPointerException e){
            updatedTargets.add(t);
        }
    }
    system.debug('updatedTargets contains ' + updatedTargets);

    If(updatedTargets.size() > 0){

    //create a set of Identifiers, which will be used to select Target records to update
    Set<String> teamTargetIden = new Set<String>();
    for(Target__c t : updatedTargets){
        teamTargetIden.add(t.Team_MT_Identifier__c);
    }
        system.debug('teamTargetIden contains ' + teamTargetIden);

    List<Target__c> targetsToUpdate = [SELECT Id, Target__c FROM Target__c
                                        WHERE Team_MT_Identifier__c IN :teamTargetIden];

        system.debug('targetsToUpdate contains ' + targetsToUpdate);

    Decimal mnthlyTeamTarget = (Decimal) [SELECT SUM(Target__c) sum FROM Target__c
                                           WHERE Id IN :targetsToUpdate][0].get('sum');

    system.debug('mnthlyTeamTarget contains ' + mnthlyTeamTarget);

    for(Target__c t : targetsToUpdate){
        t.Team_Target__c = mnthlyTeamTarget;
    }
    update targetsToUpdate;
    }
}

EDIT

The calculation works fine if the record currency is set to my org's corporate currency.

My record's currency is GBP and the conversion rate we're using from USD:GBP is 0.672300.

How can I manage this in the calculation, to allow the use of multiple currencies?

Answer 1

The issue was due to the fact that the Target__c record's currency was GBP, while my org's corporate currency is USD.

Since convertCurrency() can't be combined with the aggregate function, I had to use the solution on this post, to convert the value from the Target__c field, to produce the following

trigger MonthlyTeamTargetCal on Target__c (after insert, after update) {

    List<Target__c> updatedTargets = new list<Target__c>();
    Set<String> teamTargetIden = new Set<String>();
    Set<String> targetCurrency = new Set<String>();

    for (Target__c t : Trigger.New){

        try{
            Target__c oldT = Trigger.oldMap.get(t.Id);
            if(oldT.Target__c != t.Target__c)
            {
                updatedTargets.add(t);
                teamTargetIden.add(t.Team_MT_Identifier__c);
                targetCurrency.add(t.CurrencyIsoCode);
            }
        //if old.Map is null, record has been inserted & (re)calculation should be done
        }catch(System.NullPointerException e){
            updatedTargets.add(t);
            teamTargetIden.add(t.Team_MT_Identifier__c);
            targetCurrency.add(t.CurrencyIsoCode);
        }
    }

    if(updatedTargets.size() > 0){

    List<Target__c> targetsToUpdate = [SELECT Id, Target__c FROM Target__c
                                        WHERE Team_MT_Identifier__c IN :teamTargetIden];

        system.debug('targetsToUpdate contains ' + targetsToUpdate);

    Decimal mnthlyTeamTarget = (Decimal) [SELECT SUM(Target__c) sum FROM Target__c
                                           WHERE Id IN :targetsToUpdate][0].get('sum');

        if(targetCurrency.contains('USD') == false){

            Decimal conversionRate = [SELECT conversionrate FROM currencytype 
                                       WHERE isocode = :targetCurrency LIMIT 1].conversionRate;

            for(Target__c t : targetsToUpdate){
                t.Team_Target__c = mnthlyTeamTarget*conversionRate;
            }

        }else{
            for(Target__c t : targetsToUpdate){
                t.Team_Target__c = mnthlyTeamTarget;
            }
        }
        update targetsToUpdate;
    }
}