 ShowAll Questionssorted byDate Posted Jonathan Osgood 3

# Trigger calculation on a collection of child records

Hi All,

I'm trying to run a simple (ish) calculation. For all Account child records (form__c), I have 2 fields: Year and Total Jobs. My calculation is as follows: Sum of differences between each year:
i.e.
(Total Jobs Year 2 - Total Jobs Year 1) + (Total Jobs Year 3 - Total Jobs Year 2) + (Total Jobs Year 4 - Total Jobs Year 3)

So, I need to iterate through each Form__c record, parse out year 1 = total jobs, etc. Return the Sum of Differences (X2_1_a_Total_of_Jobs_Hist_Cumulative__c = CalculatedTotalJobs) in the parent account field. Here's what I have so far. I'm thinking I'm going to have to use Maps instead? Any feedback appreciated!

```trigger TotalNumberOfJobsTrigger on Account (after insert, after update, after delete) {

//Accounts list
List<Account> AcctsList = new List <Account>();

//Distinguish whether the list should act on inseret, update or delete

if(trigger.isInsert || trigger.isUpdate){
AcctsList = Trigger.New;
}
if(trigger.isDelete){
acctsList = Trigger.old;
}

//iterate through and find related form records
for(List<Form__c> formsList: [SELECT id, Name, X2_1_a_Total_of_Jobs_Hist_Cumulative__c,(SELECT Account__c, X1a_EcOps_Total_number_of_jobs__c, Name FROM Forms__r)
FROM Account WHERE id IN :Trigger.new]){

if(form__c.Report_Year__c='Year 1'){
Integer TotalJobsY1 = form__c.X1a_EcOps_Total_number_of_jobs__c;
}

if(form__c.Report_Year__c='Year 2'){
Integer TotalJobsY2 = form__c.X1a_EcOps_Total_number_of_jobs__c;
}
if(form__c.Report_Year__c='Year 3'){
Integer TotalJobsY3 = form__c.X1a_EcOps_Total_number_of_jobs__c;
}
if(form__c.Report_Year__c='Year 4'){
Integer TotalJobsY4 = form__c.X1a_EcOps_Total_number_of_jobs__c;
}

for(Account acc: [SELECT id, Name, X2_1_a_Total_of_Jobs_Hist_Cumulative__c,(SELECT Account__c, Name FROM Forms__r)
FROM Account WHERE id IN :formsList]){

//Formula to Calculate difference in year/records.

Integer CalculatedTotalJobs = (TotalJobsY2 - TotalJobsY1) +
(TotalJobsY3 - TotalJobsY2) +
(TotalJobsY4 - TotalJobsY3);

//Set  calculated value in account field
acc.X2_1_a_Total_of_Jobs_Hist_Cumulative__c = CalculatedTotalJobs;
}

}

Update AcctsList;

}
``` JeffreyStevens
Johathan,

I had a bunch of code written - trying to pupulate a map like <Accountid,list<year2String+year1Sring,differenceValue>>  - but it got a bit messy and I didn't have it quite right.  so - I just deleted it all.  :)

But I did get to thinking - that maybe you could add a field on the Form__c object - like "Difference from Prev year".  Then put a trigger on the Form__c object - that basically find's the previous year, and does the subtraction.  If there isn't a previous year - then ofcourse the difference would be zero.

If you had a schema that looked like that, and all of the differences pupulated - would it make this problem much eaiser?

If not - ping me again - and I'll clean up the code I did have and re-post in the AM. Jonathan Osgood 3
Thanks Jeffrey,
I like the idea of simplifying! I think that could could work. The tricky part is that the "year" in this case is a picklist and not a date field. Do you think my
```if(form__c.Report_Year__c='Year 1') {

Integer TotalJobsY1 = form__c.X1a_EcOps_Total_number_of_jobs__c;

}```
logic would sufficiently handle that?

Thanks again for taking a look!

Jonathan