Apex Code Development You need to sign in to do that
Don't have an account?
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?
The full trigger:
Thank you!
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!
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; }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:
I need to transfer the same logic into my trigger.
Thank you!
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..
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!