+ Start a Discussion
Nagarjuna Reddy.PNagarjuna Reddy.P 

Trigger to update parent picklist field

Hi All,
I'm little confused to choose  trigger/process builder with my scenario could you please help me with this.
I have parent and child objects with Lookup relationship. I need to auto update parent picklist field with conditions as follows
1.If parent has child records created in last 2 months from todays date update to valueA
2.If parent has child records in the past and there have not been any childs in last 2 months from todays date then ValueB
3.If parent never have any childs update to valueC

I hope we cant  use process builder for this and trigger is preferred to write on child object but which trigger context i should use.
Could any one please give me an idea how to do this.
thank you.
Best Answer chosen by Nagarjuna Reddy.P
Danish HodaDanish Hoda
Hi Nagarjuna,
PFB the overview you need to follow:

Inside batch start() method:
--> String sQuery =  'SELECT Id, picklistField__c, (SELECT Id, createdDate FROM child__r ORDER BY CreatedDate DESC) FROM ParentObj__c';
--> Database.query(squery);

Inside execute() method:
--> Date today = Date.today();
--> Date twoMonthsBefore = today.addDays(-60);
--> List<ParentObj__c> updateParentObjs = new  List<ParentObj__c>();
--> for(ParentObj__c pObj : scope){ //scope is the list returned from the start method
          if(!pObj.child__r.isEmpty()){
              if(pObj.child__r[0].createdDate > twoMonthsBefore){
                   pObj.picklistField__c = 'ValueA';
                   updateParentObjs.add(pObj);
               }else if(pObj.child__r[0].createdDate < twoMonthsBefore){
                    pObjj.picklistField__c = 'ValueB';
                    updateParentObjs.add(pObj);
               }
           }else{
              pObj.picklistField__c = 'ValueC';
              updateParentObjs.add(pObj);
           }
       }
if(!updateParentObjs.isEmpty()){
   update updateParentObjs; 
}

Leave finish() method empty.
 

All Answers

Danish HodaDanish Hoda
Hi Nagarjuna,
Your requirement can neither be achieved using PB nor with Trigger.
You need to write Batch class to achieve this.
Nagarjuna Reddy.PNagarjuna Reddy.P
Hi Danish,
thanks for your reply, I'm really surprised hearing to use batch class. Could you please explain how i can achieve this through batch class.
the important thing i need to say you is I'm upserting 100's of child records through data loader and then mapping parent with newly upserted child records.
Danish HodaDanish Hoda
Hi Nagarjuna,
PFB the overview you need to follow:

Inside batch start() method:
--> String sQuery =  'SELECT Id, picklistField__c, (SELECT Id, createdDate FROM child__r ORDER BY CreatedDate DESC) FROM ParentObj__c';
--> Database.query(squery);

Inside execute() method:
--> Date today = Date.today();
--> Date twoMonthsBefore = today.addDays(-60);
--> List<ParentObj__c> updateParentObjs = new  List<ParentObj__c>();
--> for(ParentObj__c pObj : scope){ //scope is the list returned from the start method
          if(!pObj.child__r.isEmpty()){
              if(pObj.child__r[0].createdDate > twoMonthsBefore){
                   pObj.picklistField__c = 'ValueA';
                   updateParentObjs.add(pObj);
               }else if(pObj.child__r[0].createdDate < twoMonthsBefore){
                    pObjj.picklistField__c = 'ValueB';
                    updateParentObjs.add(pObj);
               }
           }else{
              pObj.picklistField__c = 'ValueC';
              updateParentObjs.add(pObj);
           }
       }
if(!updateParentObjs.isEmpty()){
   update updateParentObjs; 
}

Leave finish() method empty.
 
This was selected as the best answer
Nagarjuna Reddy.PNagarjuna Reddy.P
Thank u Danish for your help, I'll work on it.
Nagarjuna Reddy.PNagarjuna Reddy.P
Hi Danish,Thank you for your help, I have 4 look up relations from child to Account.so If any one of those relational records created I need to update  account field however the date conditions are same. I'm trying to write child to parent query but not succesful. how can I do this.
Do I need to write separate batch for each relation.
Danish HodaDanish Hoda
Hi Nagarjuna,
First of all, I am really happy that my solution helped you achieve your requirement.

For your second requirement, you can simply write after insert trigger to update the parent record.
Nagarjuna Reddy.PNagarjuna Reddy.P
Hi Danish, even for the first requirement we can write trigger but we used batch apex. My Complete requirement is follows
I have custom child object with 4 lookup relations(lookup1,lookup2--lookup4) to parent. If any one of these relational records are created(either lookup1 record or lookup2 record or ---- lookup4 record) I need to update parent field which is only field to update if any child records is created with date conditions explained above.
I dont think trigger will satisfy my requirement bcz i dont need to update parent field based on events.
Danish HodaDanish Hoda
Hi Nagarjuna,
The first requirement you have checks and updates the field based on the child record w.r.t today. You cannot achieve this by using trigger as trigger fires only when a record is modified (inserted/updated).
On the contrary, your Batch class will run daily and check the data for the 2 last months and update the field accordingly.
Nagarjuna Reddy.PNagarjuna Reddy.P
Danish,
1.If parent has child records created in last 2 months from todays date update to valueA
2.If parent has child records in the past and there have not been any childs in last 2 months from todays date then ValueB
3.If parent never have any childs update to valueC
these conditions applies for all the lookup fields. there are no two requirements here only one requirement.each lookup has individual child relationship name we queried only for one child relationship in batch apex. If record of any relationship either 1,2 ,3 or 4 is created then parent field is to be updated.I hope Now u can have clear idea about my requirement.
Danish HodaDanish Hoda
Hi Nagarjuna,
You need to update the query string in start method to accomodate all the four relationships like:
String sQuery =  'SELECT Id, picklistField__c, (SELECT Id, createdDate FROM child1__r ORDER BY CreatedDate DESC), (SELECT Id, createdDate FROM child2__r ORDER BY CreatedDate DESC), (SELECT Id, createdDate FROM child3__r ORDER BY CreatedDate DESC), (SELECT Id, createdDate FROM child4__r ORDER BY CreatedDate DESC)  FROM ParentObj__c';
The reason I am saying it to be done by Batch apex :
  • Whenever a child record is created, it will update the parent field as valueA
  • But you cannot determine the values to be set as ValueB/ValueC on parent as trigger only fires when a record is modified (insert, update, delete, undelete)
  • Please refer your condition - last 2 months from todays date, the value todays date is changes daily
Nagarjuna Reddy.PNagarjuna Reddy.P
Hi Danish, 
Used your updated query string and its worked well.but while I'm testing it in Partial copy sandbox where there are 10k plus account records to process through batch apex but 4 to 5 batches are failed and throwing error as First error: Aggregate query has too many rows for direct assignment, use FOR loop.
actually no account has 200 child records in my sandbox.
so I used for loop but even its showing the same error, I dont no why this occurs eventhough iterating through for loop and even i tested by using limit 200 in sub query but the same error is throwing. you can see my code here

global with sharing class batchHandler implements Database.Batchable<SObject> {
    global Database.QueryLocator start(Database.BatchableContext bc){

        String squery = 'SELECT Id,Referral_Stage__c,Account_Type__c,(SELECT Id,createdDate from child1__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child2__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child3__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child4__r ORDER BY CreatedDate DESC) from Account';

        return Database.getQueryLocator(squery);
    }

    global void execute(Database.BatchableContext bc,List<Account> scope){
      Date today = Date.today();
      Date twoMonthsBefore = today.addDays(-10);
      List<Account> accounts = new List<Account>();
      //if(scope.size()>0){
      for(Account acc:scope){

           if(!acc.child1__r .isEmpty()){

                       for(List<Referral__c> insurance:acc.child1__r ){
                                   if(insurance[0].createdDate > twoMonthsBefore){
                                  acc.Referral_Stage__c='Currently Referring';
                                  accounts.add(acc);
                            } else if(insurance[0].createdDate < twoMonthsBefore){
                                    acc.Referral_Stage__c='Referred Inactive';
                                    accounts.add(acc);
                              }
       
                      } 

           }else if(!acc.child2__r .isEmpty()){

                             for(List<Referral__c> mso:acc.child2__r ){
                                           if(mso[0].createdDate > twoMonthsBefore){
                                                          acc.Referral_Stage__c='Currently Referring';
                                                          accounts.add(acc);
                                         }else if(mso[0].createdDate < twoMonthsBefore){
                                                     acc.Referral_Stage__c='Referred Inactive';
                                                      accounts.add(acc);
                                               }
                                   }

              }else if(!acc.child3__r .isEmpty()){

                                       for(List<Referral__c> ipa:acc.child3__r ){
                                                  if(ipa[0].createdDate > twoMonthsBefore){
                                                       acc.Referral_Stage__c='Currently Referring';
                                                      accounts.add(acc);
                                                }else if(ipa[0].createdDate < twoMonthsBefore){
                                                         acc.Referral_Stage__c='Referred Inactive';
                                                         accounts.add(acc);
                                             }         
                                   }

         }else if(!acc.PCPs_Referred_Patients__r.isEmpty()){

                                         for(List<Referral__c> pcp:acc.child4__r ){
                                                    if(pcp[0].createdDate > twoMonthsBefore){
                                                               acc.Referral_Stage__c='Currently Referring';
                                                               accounts.add(acc);
                                       }else if(pcp[0].createdDate < twoMonthsBefore){
                                                     acc.Referral_Stage__c='Referred Inactive';
                                                     accounts.add(acc);
                                       }
                             }

          }
             else{
                              acc.Referral_Stage__c='Never Referred';
                             accounts.add(acc);
                  }
        }
      
   // }
      if(!accounts.isEmpty()){
          update accounts;
      } 
    }
    global void finish(Database.BatchableContext bc){

    }
}

This working with no issues in developer sandbox but in partial copy throwing error.. do you have any idea about this type of error and pls let me know if anything wrong with my query and code.

thank you.
Danish HodaDanish Hoda
Hi Nagarjuna,
Plz check the for-loop you are using :
for(List<Referral__c> insurance:acc.child1__r ){

Rather this should be : for(Referral__c insurance:acc.child1__r ){

 
Danish HodaDanish Hoda
please update the same in other loops as well
Nagarjuna Reddy.PNagarjuna Reddy.P

Hi Danish,
Thank you for your reply and suggestion, I tried your suggestion replacing List<Referral__c> with Referral__c in for loop and faced error in the immediate if condition if(insurance[0].createdDate > twoMonthsBefore){   stating Expression must be a list type : Referral__c, so i used separe list to store those child records and referred in if condition and error is resolved, but still batch is failed and infact the number of failed batches are increased than previous. Its giving the same error First error: Aggregate query has too many rows for direct assignment, use FOR loop.
Danish HodaDanish Hoda
Hi Nagarjuna,
Didn't get your comment above, plz check if you have modfied your code as below and still getting the error:
if(!acc.child1__r.isEmpty()){

                       for(Referral__c insurance:acc.child1__r ){
                            if(insurance.createdDate > twoMonthsBefore){
                                  acc.Referral_Stage__c='Currently Referring';
                                  accounts.add(acc);
                            } else if(insurance.createdDate < twoMonthsBefore){
                                    acc.Referral_Stage__c='Referred Inactive';
                                    accounts.add(acc);
                              }
       
                      }

do the same to other if conditions as well
Nagarjuna Reddy.PNagarjuna Reddy.P
Yes Danish, modified the code same as above but still getting the same error.
Danish HodaDanish Hoda
Can we connect over skype? 
Plz ping me <danish.hoda>
Nagarjuna Reddy.PNagarjuna Reddy.P
Danish,in the Apex jobs page it's showing the same error message,but i received a personal email with error message as 
Apex script unhandled exception caused by: System.ListException: Duplicate id in list: 001f400000V3oixAAB.
Danish HodaDanish Hoda
Thanks for the help Nagarjuna!
Please update your code as below:
global with sharing class batchHandler implements Database.Batchable<SObject> {
    global Database.QueryLocator start(Database.BatchableContext bc){

        String squery = 'SELECT Id,Referral_Stage__c,Account_Type__c,(SELECT Id,createdDate from child1__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child2__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child3__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child4__r ORDER BY CreatedDate DESC) from Account';

        return Database.getQueryLocator(squery);
    }

    global void execute(Database.BatchableContext bc,List<Account> scope){
      Date today = Date.today();
      Date twoMonthsBefore = today.addDays(-10);
      Set<Account> accounts = new Set<Account>();
      List<Account> accToUpdate = new List<Account>();
      //if(scope.size()>0){
      for(Account acc:scope){

           if(!acc.child1__r .isEmpty()){

                       for(Referral__c insurance:acc.child1__r ){
                                   if(insurance.createdDate > twoMonthsBefore){
                                  acc.Referral_Stage__c='Currently Referring';
                                  accounts.add(acc);
                            } else if(insurance.createdDate < twoMonthsBefore){
                                    acc.Referral_Stage__c='Referred Inactive';
                                    accounts.add(acc);
                              }
       
                      } 

           }else if(!acc.child2__r .isEmpty()){

                             for(Referral__c mso:acc.child2__r ){
                                           if(mso.createdDate > twoMonthsBefore){
                                                          acc.Referral_Stage__c='Currently Referring';
                                                          accounts.add(acc);
                                         }else if(mso.createdDate < twoMonthsBefore){
                                                     acc.Referral_Stage__c='Referred Inactive';
                                                      accounts.add(acc);
                                               }
                                   }

              }else if(!acc.child3__r .isEmpty()){

                                       for(Referral__c ipa:acc.child3__r ){
                                                  if(ipa.createdDate > twoMonthsBefore){
                                                       acc.Referral_Stage__c='Currently Referring';
                                                      accounts.add(acc);
                                                }else if(ipa.createdDate < twoMonthsBefore){
                                                         acc.Referral_Stage__c='Referred Inactive';
                                                         accounts.add(acc);
                                             }         
                                   }

         }else if(!acc.PCPs_Referred_Patients__r.isEmpty()){

                                         for(Referral__c pcp:acc.child4__r ){
                                                    if(pcp.createdDate > twoMonthsBefore){
                                                               acc.Referral_Stage__c='Currently Referring';
                                                               accounts.add(acc);
                                       }else if(pcp.createdDate < twoMonthsBefore){
                                                     acc.Referral_Stage__c='Referred Inactive';
                                                     accounts.add(acc);
                                       }
                             }

          }
             else{
                              acc.Referral_Stage__c='Never Referred';
                             accounts.add(acc);
                  }
        }
      
   // }
      if(!accounts.isEmpty()){
          //update accounts;
         accToUpdate.addAll(accounts);
      }
      if(!accToUpdate.isEmpty()){
          update accToUpdate;
      }
    }
    global void finish(Database.BatchableContext bc){

    }
}