function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jonathan Osgood 3Jonathan Osgood 3 

Sum of child record values in trigger

Hi all,

I have a trigger that calculates the difference of values from year to year on account child records (form__c). Everything is working except for scenarios where a given year = 0. I've added an IF statement to handle this scenario, but what I really need is the SUM of ALL X2_1_a_Total_of_jobs_Year_2__c values under an account. Example, IF(f.X2_1_a_Total_of_jobs_Year_2__c:SUM > 0). 

Do I need to use aggregateResult here? 
 
if(f.X2_1_a_Total_of_jobs_Year_2__c > 0){
               calculated += f.X2_1_a_Total_of_jobs_Year_2__c - f.X2_1_a_Total_of_jobs_Year_1__c;
            }else {
               calculated = f.X2_1_a_Total_of_jobs_Year_1__c;


The full trigger:
 
trigger JobsIncomeCalc on Form__c (after insert, after update, after delete) {
    
    //Forms List
    List <Form__c> FormsList = New List <Form__c>();
    
    //Distinguish whether the list should act on insert, update or delete
    
    if(trigger.isInsert || trigger.isUpdate){
        FormsList = Trigger.New;
    }
    if(trigger.isDelete){
        FormsList = Trigger.Old;
    }
    
    //Account Ids Set 
    Set <id> accountIds = New Set <id>();
    
    //Add form account ids to set
    for(Form__c form: FormsList){
        
        accountIds.add(form.Account__c);
        
        System.debug('account id'+form.Account__c);
        
        accountIds.remove(null);

    }
    
    //Account Map
    
    If(!accountIds.IsEmpty()){
        Map<Id, Decimal> accountMap = New Map <Id, Decimal>();
        
        for(form__c f: [
            select Account__c,
            X2_1_a_Total_of_jobs_Year_1__c,
            X2_1_a_Total_of_jobs_Year_2__c,
            X2_1_a_Total_of_jobs_Year_3__c,
            X2_1_a_Total_of_jobs_Year_4__c,
            X2_1_a_Total_of_jobs_Year_5__c,
            X2_1_a_Total_of_jobs_Year_6__c,
            X2_1_a_Total_of_jobs_Year_7__c,
            X2_1_a_Total_of_jobs_Year_8__c,
            X2_1_a_Total_of_jobs_Year_9__c,
            X2_1_a_Total_of_jobs_Year_10__c
            from Form__c
            where Account__c in :accountIds
        ]){
            if(!accountMap.containsKey(f.Account__c)){
                accountMap.put(f.Account__c, 0);
            }

              
            Double calculated = accountMap.get(f.Account__c);
            
            if(f.X2_1_a_Total_of_jobs_Year_2__c > 0){
               calculated += f.X2_1_a_Total_of_jobs_Year_2__c - f.X2_1_a_Total_of_jobs_Year_1__c;
            }else {
               calculated = f.X2_1_a_Total_of_jobs_Year_1__c;
            }
            
            accountMap.put(f.account__c, calculated);    
        }
        
        if(!accountMap.isEmpty()){
            List<Account> acctsToUpdate = new List<Account>();
            
            for(Id id : accountMap.keySet()){
                acctsToUpdate.add( new Account (
                Id = id,
                X2_1_a_Total_of_Jobs_Hist_Cumulative__c = accountMap.get(id)
                ));
                
            }
            
           update acctsToUpdate;
 
        }  
    }
}

Thank you!
 
ArmouryArmoury
Hi Jonathan,

In your description you mentioned only sum of All the numbers in f.X2_1_a_Total_of_jobs_Year_2__c field is required. But the If-Else logic has the code dependent on f.X2_1_a_Total_of_jobs_Year_1__c as well. Can you clarify this?

So if I ignore year1 field then the below 'Else' condition is not required.
 
else {
      calculated = f.X2_1_a_Total_of_jobs_Year_1__c;
}

 
Jonathan Osgood 3Jonathan Osgood 3
Hi Sathish,

I will need to find the sum of all of my Year fields (f.X2_1_a_Total_of_jobs_Year_1__c, f.X2_1_a_Total_of_jobs_Year_2__c, 
f.X2_1_a_Total_of_jobs_Year_3__c , etc. etc.) grouped by parent Account record. So for example, if Account A has 3 child form__c records, I would need to know the sum of each f.X2_1_a_Total_of_jobs_Year_1__c, f.X2_1_a_Total_of_jobs_Year_2__c, 
f.X2_1_a_Total_of_jobs_Year_3__c on Account A. 

The basic problem I am trying to solve is to NOT count a given year if there was no value entered. The only way I can do that is to sum each field and if a given year is <0, remove that year from my calculation. I was able to accompleis this in a report formula with the following formula:
 
IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_2__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_1__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_2__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_2__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_1__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_3__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_2__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_3__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_3__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_2__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_4__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_3__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_4__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_4__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_3__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_5__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_4__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_5__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_5__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_4__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_6__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_5__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_6__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_6__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_5__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_7__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_6__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_7__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_7__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_6__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_8__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_7__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_8__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_8__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_7__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_9__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_8__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_9__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_9__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_8__c:SUM, 0)+

IF(AND(Form__c.X2_1_a_Total_of_jobs_Year_10__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_9__c:SUM>=0, Form__c.X2_1_a_Total_of_jobs_Year_10__c:SUM>=0), Form__c.X2_1_a_Total_of_jobs_Year_10__c:SUM - Form__c.X2_1_a_Total_of_jobs_Year_9__c:SUM, 0)

I need to transfer the same logic into my trigger. 

Thank you!


 
ArmouryArmoury
Ok.. As per the formula, the issue is in the trigger's else condition. it should be 
calculated += 0;

which is equivalent to not doing any calculation at all. So please remove the else condition as mentioned in my previous comment and it should resolve the issue.. 
Jonathan Osgood 3Jonathan Osgood 3
Unfortunately, this will not work. I need the SUM of all child record values in a given field as in the formula above. The problem is that each record contains a value for year 1, year 2, year 3 etc. For each record, there will always be a year with the value of zero. What I need to determine is whether the SUM of all values in a given year is greater than zero. 

For exmaple:

ACME Acount:

Form A: Year 1 = 3, Year 2 = 0, Year 3 = 0, Year 4 = 0
Form B: Year 1 = 0, Year 2 = 5, Year 3 = 0, Year 4 = 0
Form C: Year 1 = 0, Year 2 = 0, Year 3 = 7, Year 4 = 0

I'm calculating the difference between years as follows (year 2 - year 1) + (Year 4 - Year 3)

IF the sum of a given year is zero, I do not want to count it. Otherwise I will be subtracting by zero and producing a negative number. I think I need to use aggregateResult, but need some guidance on how to implement it in my 'calculated' variable. 

Thank you!