0

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;
    }
}
6
  • 1
    You have shared your requirements. Where exactly you are stuck? The more easily members can understand your question, more frequently you will receive answers – Santanu Boral Jun 6 '17 at 2:27
  • I am stuck with populating aggregate results in child object Funding_Cycles_TA__c – Jay Weera Jun 6 '17 at 2:37
  • You have fetched all Funding_Cycles_TA__c records in a query, so do you want to update all those record's fields with aggregate results? – Nitish Singhal Jun 6 '17 at 2:49
  • Yes, But the values for Reporting_Year__c is already in the Funding_Cycles_TA__c object. I want match that value with the aggregated values. also values in Achieved_Credits_to_Rpting_Yr_End__c in Funding_Cycles_TA__c is cumulative values from Achieved_Credits_Total__c in consecutive years – Jay Weera Jun 6 '17 at 3:02
  • I understood the first condition well, so posted the sample code in answer, please check. You can test at least first requirement. Can you please give an example of second requirement, as an example would help much better. – Nitish Singhal Jun 6 '17 at 3:20
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;
}
5
  • Thanks Nitish, I get the error as Initial term of field expression must be a concrete Sobject: List on line for the second loop – Jay Weera Jun 6 '17 at 23:15
  • Hi Nitish, I managed to sort out the above compile error and other Null value errors. When I test this on DB I get first error: MISSING_ARGUMENT, Id not specified in an update call – Jay Weera Jun 7 '17 at 1:58
  • I managed to get sorted all run time errors. But now the data is not populating on target object – Jay Weera Jun 7 '17 at 2:34
  • Can you please share the code which is saved correctly. You can make edit to your question. – Nitish Singhal Jun 7 '17 at 2:37
  • Hi NItish, Latest update-- I have debug the update trigger. it does not pass the loop for updating values – Jay Weera Jun 7 '17 at 3:19
1

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.