+ Start a Discussion
Jonathan Osgood 3Jonathan 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:
(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;
        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;

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 3Jonathan 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!