Apex Code Development You need to sign in to do that
Don't have an account?
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!
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;
AcctsList.add(acc);
}
}
Update AcctsList;
}
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.
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