2

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.

enter image description here

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?

10
  • Can you try typecast it into Integer instead of Decimal
    – Ashwani
    Jun 18, 2015 at 9:29
  • I've only changed this line, as follows Integer mnthlyTeamTarget = (Integer) [SELECT SUM(Target__c) sum FROM Target__c WHERE Team_MT_Identifier__c IN :teamTargetIden][0].get('Sum'); but this then causes an error 'Invalid conversion from runtime type Decimal to Integer'
    – Alex S
    Jun 18, 2015 at 9:35
  • Use Integer.valueOf(); function then.
    – Ashwani
    Jun 18, 2015 at 9:46
  • Thanks for the suggestion but I'm afraid I'm not familiar with the function, where should I use it?
    – Alex S
    Jun 18, 2015 at 9:56
  • 1
    This way Integer mnthlyTeamTarget = Integer.valueOf([SELECT SUM(Target__c) sum FROM Target__c WHERE Team_MT_Identifier__c IN :teamTargetIden][0].get('Sum'));
    – Ashwani
    Jun 18, 2015 at 9:58

1 Answer 1

3

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;
    }
}

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.