[SalesForce] SOQL Aggregate calculation error due to multi-currency

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?

Best Answer

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

Related Solutions

Advanced Currency Management – How to Use ACM in Custom Objects

Short answer: you can't do this without a major investment.

Longer answer: my job is developing FinancialForce's PSA application, which requires supporting dated exchange rates and other ACM features on custom objects. Salesforce has confirmed to us more than once that there is no native way to do this, so we've implemented our own currency conversion from the ground up to support our needs. We do support reading the dated exchange rates from ACM's tables, but we still have to do all our own math on the conversions in apex logic. At the very least this means that you can tap into the same currency data for your calculations, even if the implementation of the logic must be done independently.

The net effect is that if you want ACM functionality outside of the officially supported objects you're going to have to build you own currency conversion system, and your own apex-managed rollups rather than using the native functionality, for this to work. If you do go this route you'll probably want to turn off parenthesized currency conversions as they'd use the non-dated rates and effectively lie to users of your custom currency system.

This is obviously a non-trivial solution to the problem, but it's the only one that's currently possible today.

[SalesForce] Unit Test is Providing 0% Coverage for Apex Trigger

The trigger code is having lot of redundancy, So I modified it. before posting the modified code few lines of explanation what I changed. you collected account Ids twice and did the condition check twice where once is more enough. I am also made changes to the test class, where Task data is provided which is not required because the trigger has to create it once the necessary conditions are met. I am expecting this trigger should work for you, if there is any errors feel free to post as a comment.

trigger OpportunityAfter on Opportunity (after update){

    Set AccountIDs = new Set();

    for(Opportunity o : trigger.new){
        if(opp.StageName == 'Closed - Lost' && opp.Business_Line__c == 'Workforce' && 
            opp.Survey_Type__c != 'APulse' && trigger.oldMap.get(opp.Id).StageName != opp.StageName){

                AccountIDs.add(o.AccountId);
         }
    }

    Task[] tasksToInsert = new Task[]{};

    for (Account acc : [Select Id, Name, OwnerId from Account where Id IN :AccountIds]){
        for (Opportunity opp : trigger.new){
            if(opp.AccountId == acc.Id){
                Task tas = new Task();
                tas.WhatId=acc.Id;
                tas.OwnerId=opp.OwnerId;
                tas.Subject='Follow-up: ' + opp.name + ' - ' + acc.name;
                tas.ActivityDate=(opp.CloseDate + 180);
                tas.Description='Follow up to see how engagement initiatives are going and if Avatar can help. Refer to lost opportunity: ' + opp.name;
                tasksToInsert.add(tas);                                                         
            }
        }
    }
    if(tasksToInsert != null && !tasksToInsert.isEmpty())
    Database.insert(tasksToInsert);

}

@isTest
public class TestClass {

    static testMethod void myUnitTest() {

    //Set up user
    User u1 = [SELECT Id FROM User WHERE Email='abc@sampleemail.com'];
    test.startTest();  
    //Run As U1
    System.RunAs(u1){

    System.debug('Testing trigger... (single record validation)');

    //NEW Account record
    Account acc = new account();
    acc.Name = 'Test Account';
    acc.Industry = 'Healthcare';

    insert acc;

    //NEW Opportunity record
    Opportunity opp = new Opportunity();
    opp.Name = 'Test Opportunity'; 
    opp.CloseDate = System.today();
    opp.StageName = 'S1 - Investigative';
    opp.Type = 'Repeat Business - Wrkforce HC';
    opp.AccountId = acc.Id;
    opp.Survey_Type__c = 'Product Name';

    insert opp;

    opp.StageName = 'Closed - Lost';
    opp.Reason_Won_Lost__c = 'Relationship';
    opp.Business_Line__c = 'Workforce'  //added this line to meet the if condition while collecting the set of id in the trigger.

    update opp;
   test.stopTest();
    // Here I am expecting the task has been inserted and doing a query for it         and check it in the assert statement.
   Task  testTask=[Select Id, WhatId from Task where WhatId=:acc.Id];
    //Validate single insert
    System.assertEquals(acc.Id, testTask.WhatId);

    }
    }
}

Best Answer

Related Solutions

Advanced Currency Management – How to Use ACM in Custom Objects

[SalesForce] Unit Test is Providing 0% Coverage for Apex Trigger

Related Topic