Trigger to populate aggregate results from child object to another child object

Question

I want to populate aggregate results in Funding_Cycles_TA__c Which is a child object of Training_Agreement__c. The trigger is on Training_Plan__c which is another child object of Training_Agreement__c. I have written a trigger which get aggregate results successfully when updating a Training_Plan__c record.

Actually now I have two issues

1. The aggregate values respect to Reporting_Year__c (which is grouped by field in aggregate results) should be populated in respective Reporting_Year__c in destination object that is in Funding_Cycles_TA__c.

2. achievedCredits should be cumulative with consecutive year and populated in Achieved_Credits_to_Rpting_Yr_End__c (the value in achievedCredits should be added to the next achievedCredits and so on. My trigger is as below

trigger

trigger PopulateFCTAFromTP on Training_Plan__c (After Update, after insert, after delete) {

    Set<Id> TAIds = new Set<Id>();
    List< Training_Agreement__c> tasForUpdate = new List<Training_Agreement__c>();
    Training_Plan__c[] TPs = null;

    if(Trigger.isInsert||Trigger.isUpdate){
        TPs = Trigger.new;
    }else if(Trigger.isDelete){
        TPs = Trigger.old;
    }
    //select the Training plans
    for (Training_Plan__c TP : TPs) {
        if((TP.Training_Agreement__c != null) 
           ||(TP.TEC_Status__c=='Active') 
           ||(TP.TEC_Status__c=='Grace')
           ||(TP.TEC_Status__c=='Complete')
           ||(TP.TEC_Status__c=='On Hold') 
           ||(TP.TEC_Status__c=='Withdrawn')
           ||(TP.TEC_Status__c=='Pending Transfer')){
               TAIds.add(TP.Training_Agreement__c);
           }
    }
    //Aggregate SOQL from Funding_Cycles_TPS__c
    List<AggregateResult> agrResults =
        [SELECT 
         Reporting_Year__c reportingYear,
         sum(Achieved_Credits_Total__c) achievedCredits, 
         sum(Funded_Achieved_Credits__c) fundedCredits, 
         Training_Plan_Standard__r.Training_Plan__r.Training_Agreement__c ita
         FROM Funding_Cycles_TPS__c
         WHERE Training_Plan_Standard__r.Training_Plan__r.Training_Agreement__c in :TAIds 
         //where  Training_Plan_Standard__r.Training_Plan__r.Training_Agreement__c = 'a0w0M00000VpgtI' 
         GROUP BY Reporting_Year__c, Training_Plan_Standard__r.Training_Plan__r.Training_Agreement__c];

    //Corrosponding Funding_Cycles_TA__c(Child records)need to be updated from Training Agreement
    List<Funding_Cycles_TA__c>  fctasToUpdate = [Select Reporting_Year__c,
                                                Achieved_Credits_in_Rpting_Yr_Funded__c,
                                                Achieved_Credits_in_Rpting_Yr_Total__c,
                                                Achieved_Credits_to_Rpting_Yr_End__c
                                                From Funding_Cycles_TA__c
                                                where Training_Agreement__c in: TAIds order by Reporting_Year__c ];


    for (AggregateResult ar: agrResults) {
        fctasToUpdate.add(new Funding_Cycles_TA__c(Achieved_Credits_in_Rpting_Yr_Funded__c = (Decimal)ar.get('fundedCredits'), 
                                                   Achieved_Credits_in_Rpting_Yr_Total__c = (Decimal)ar.get('achievedCredits')));

    }  
    if(!tasForUpdate.isEmpty()){
        update tasForUpdate;
    }
}

Answer 1

Based on comments:

You need to write a loop within loop, to match the condition for each and every record you have fetched in aggregate results. Please check the sample code below:

//loop within loop to match the year value on child objects.
for (AggregateResult ar: agrResults) {
    for(Funding_Cycles_TA__c FCTAToUpdate : fctasToUpdate){
        //Here is the matching condition.
        if(FCTAToUpdate.Reporting_Year__c == ar.reportingYear){
            fctasToUpdate.Achieved_Credits_in_Rpting_Yr_Total__c = (Decimal)ar.get('achievedCredits'); //populated the value.
        }
    }
} 
if(!fctasToUpdate.isEmpty()){
    update fctasToUpdate;
}

Answer 2

From what I understood from your question - You want to add the exsisting value of Achieved_Credits_in_Rpting_Yr_Total__c to sum(Achieved_Credits_Total__c) achievedCredits and store it in Achieved_Credits_to_Rpting_Yr_End__c. If this is correct then you just need to use:

//loop within loop to match the year value on child objects.
for (AggregateResult ar: agrResults) {
    for(Funding_Cycles_TA__c FCTAToUpdate : fctasToUpdate){
        //Here is the matching condition.
        if(FCTAToUpdate.Reporting_Year__c == ar.reportingYear){
  fctasToUpdate.Achieved_Credits_to_Rpting_Yr_End__c = fctasToUpdate.Achieved_Credits_in_Rpting_Yr_Total__c + (Decimal)ar.get('achievedCredits');
  }
 }
} 
if(!fctasToUpdate.isEmpty()){
    update fctasToUpdate;
}