+ Start a Discussion
Jancy MaryJancy Mary 

Count the number of child records on the each parent object, child has Lookup relationship to parent.

Hi Developers,

Can anyone help me on this issue, I have a Child object that has a Lookup to Parent. I wrote the below apex class and the trigger on child, such that the count of Child records should be shown on each Parent record. I have a number field on the Parent which should be update as per the Trigger.

It works fine except in one scenario, it does not consider the existing Child records on the Parent, hence it shows incorrect count on the Parent record. It works perfect if I add the new Child records. I heard that Batch Apex can resolve this issue, I am not sure how Batch Apex is related here to resolve the isssue. Can I get some guidance here to proceed further.


Any help on this is much appreciated.
Thank you.

Apex Class:

public class ChildCountHelper{
    
    //List<Parent__c> parentList = [select id, child_count__c, (select id from child__r) from Parent__c where id in :parentIDSet];
    
    public List<ID> conList= new List<ID>();
    
    public static void handleBeforeInsert(List<Child__c> childList){
        Set<ID> parentIDSet = new Set<ID>();
        
        for(Child__c childRec: childList){
     
            parentIDSet.add(childRec.Parent__c);
            
        }
        
        Map<ID, Parent__c> parentMap = new map<ID, parent__c>([select id, child_count__c from Parent__c where id in :parentIDSet]);
        for(Child__c childRec: childList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c ++;
        }
        update parentMap.values();
    }
    
    public static void handleBeforeUpdate(List<Child__c> newChildList, List<Child__c> oldChildList){
        Set<ID> parentIDSet = new Set<ID>();
        Map<ID, ID> oldChildMap = new Map<ID, ID>();
        
        for(Child__c childRec: newChildList){
            parentIDSet.add(childRec.Parent__c);
        }
        
        for(Child__c childRec: oldChildList){
            parentIDSet.add(childRec.Parent__c);
            oldChildMap.put(childRec.Id, childRec.Parent__c);
        }
        
        Map<ID, Parent__c> parentMap = new map<ID, parent__c>([select id, child_count__c from Parent__c where id in :parentIDSet]);
        for(Child__c childRec: newChildList)
       {
        /*if(ChildRec.Parent__c!=null){  */
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
           // }
            if(childRec.Parent__c != oldChildMap.get(childRec.id)){
                if(oldChildMap.get(childRec.id) == null && childRec.Parent__c != null){
                    parentMap.get(childRec.Parent__c).child_count__c ++;
                }else if(oldChildMap.get(childRec.id) != null && childRec.Parent__c == null){
                    parentMap.get(oldChildMap.get(childRec.id)).child_count__c --;
                }else if(oldChildMap.get(childRec.id) != null && childRec.Parent__c != null){
                    parentMap.get(oldChildMap.get(childRec.id)).child_count__c --;
                    parentMap.get(childRec.Parent__c).child_count__c ++;
                }
            }
            
        }
        update parentMap.values();
    }
    
    public static void handleBeforeDelete(List<Child__c> childList){
        Set<ID> parentIDSet = new Set<ID>();
        for(Child__c childRec: childList){
            parentIDSet.add(childRec.Parent__c);
        }
        
        Map<ID, Parent__c> parentMap = new map<ID, parent__c>([select id, child_count__c from Parent__c where id in :parentIDSet]);
        for(Child__c childRec: childList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c --;
        }
        update parentMap.values();
    }
    
    public static void handleBeforeUnDelete(List<Child__c> childList){
        Set<ID> parentIDSet = new Set<ID>();
        for(Child__c childRec: childList){
            parentIDSet.add(childRec.Parent__c);
        }
        
        Map<ID, Parent__c> parentMap = new map<ID, parent__c>([select id, child_count__c from Parent__c where id in :parentIDSet]);
        for(Child__c childRec: childList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c ++;
        }
        update parentMap.values();
    }

}




Trigger:


trigger ChildTrigger on Child__c (before insert, after update, after delete, after undelete) {
    if (Trigger.isInsert) {
        ChildCountHelper.handleBeforeInsert((List<Child__c>)Trigger.NEW);
    }else if (Trigger.isUpdate) {
        ChildCountHelper.handleBeforeUpdate((List<Child__c>)Trigger.NEW, (List<Child__c>)Trigger.OLD);
    }else if (Trigger.isDelete) {
        ChildCountHelper.handleBeforeDelete((List<Child__c>)Trigger.OLD);
    }else if (Trigger.isUndelete) {
        ChildCountHelper.handleBeforeUnDelete((List<Child__c>)Trigger.NEW);
    }
}  
Best Answer chosen by Jancy Mary
Akhil AnilAkhil Anil

Hi Jancy,

Try the update snippet below. That should cover the scenario wherein you change the parent of a child record. Kindly mark it as a solution, if that works out.
 
trigger UpdateOrder on Child__c (after insert, after update, after delete, after undelete) {

   List<Parent__c> ct = new List<Parent__c>();
   
   Set<Id> custord = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   if(Trigger.isUpdate) {

     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }

     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   {
     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), Parent__c FROM Child__c where Parent__c IN :custord GROUP      BY Parent__c ];
   
   for(AggregateResult ar:groupedResults) {
     
     Id custid = (ID)ar.get('Parent__c');
     
     Integer count = (INTEGER)ar.get('expr0');
     
     Parent__c cust1 = new Parent__c(Id=custid);
     
     cust1.child_count__c = count;
     
     ct.add(cust1);
      
   }
   
   
   update ct;

}

Thanks

All Answers

sfdc Beginnersfdc Beginner
Apex Class:


public class CountContacts
{
     public void CountContacts(list<Contact> conID)
    {
         //INSERT. New contacts only
         Set<ID> aID = new Set<ID>();
                  
         for(Contact con:conID)
         {
             aID.add(con.AccountID);
         }
         
         list<Account> acc = [select Id,Number_of_contacts__c from Account where ID IN :aID];
         list<Contact> con = [select ID from Contact where AccountID IN :aID];
         
         for(Account acn:acc)
         {
             acn.Number_of_contacts__c=con.Size();
         }
         
         Database.Update(acc);
               
       // Substracting Contacts When deleted.
    
          for(Contact con1:conID)
         {
             aID.add(con1.AccountID);
         }         

         list<Account> a1 = [select Id,Number_of_contacts__c from Account where ID IN :aID];
         list<Contact> con_1 = [select ID from Contact where AccountID IN :aID];
         
         for(Account a:a1)
         {
             a.Number_of_contacts__c=con_1.Size();
         }
         
         Database.Update(a1);
          
       }  //method ends here
     
      public void CountContacts(list<Contact> conID,Map<ID,Contact> conmapIds)
      {
            //first parameter is Trigger.New  &   second parameter is  Trigger.OldMap
             Set<ID> aId = new SET<ID>(); //for New AccountIDs
             
             Set<ID> OldAId = new Set<ID>(); //for Old AccountIDs
             
             //Map<ID,Contact> cmapid = [select AccountID from Contact where ID IN :conmapIds];
             
             for(Contact opp : conID)
             {
             
                 if (opp.AccountId != conmapIds.get(opp.id).AccountId)
                {
                             aId.add(opp.AccountId);
                             
                             OldAId.add(conmapIds.get(opp.id).AccountId);
                }
            }
            
           if (!aId.isEmpty())
           {
                  //for new Accounts
                  List<Account> acc = [select id,Number_of_contacts__c from Account where Id in:aId];
                  
                  //For New Account Contacts
                  List<Contact> con = [select id from contact where AccountId in :aId];

                  /*  This is For Old Contacts Count */

                  //for Old Accounts
                  List<Account> Oldacc = [select id,Number_of_contacts__c from Account where Id in:OldAId];

                 //For Old Account Contacts
                  List<Contact> OldCon=[select id from contact where AccountId in :OldAId];

                //For New Accounts
                 for(Account a : acc)
                 {
                    a.Number_of_contacts__c=con.size();
                 }
              
                 Database.Update(acc);

                //For Old Accounts
                for(Account a : Oldacc)
               {
                 a.Number_of_contacts__c=OldCon.size();
               }

               Database.Update(Oldacc);
         }
      
      }//end of method
   
 } // end of class


Trigger:

trigger trg_CountContacts on Contact (after Insert,after Update,after Delete,after Undelete)
{
     CountContacts  obj = new CountContacts();
     
     if ((Trigger.IsInsert) || (Trigger.IsUndelete))
     {
         obj.CountContacts(Trigger.New);
     }
     
      if (Trigger.IsDelete)
     {
         obj.CountContacts(Trigger.Old);
     }
     
      if (Trigger.IsUpdate)
     {
         obj.CountContacts(Trigger.New,Trigger.OldMap);
     }
}
If this solves your Problem, Mark it as the Best Answer.


Thanks,
SFDC Beginner.
Akhil AnilAkhil Anil
Hi Jancy,

The below code should work in all the scenarios. You can test out the same and let me know
 
trigger UpdateOrder on Child__c (after insert, after update, after delete, after undelete) {

   List<Parent__c> ct = new List<Parent__c>();
   
   List<Id> custord = new List<Id>();
   
   if(Trigger.isDelete) {
     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   {
     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), Parent__c FROM Child__c where Parent__c IN :custord GROUP      BY Parent__c ];
   
   for(AggregateResult ar:groupedResults) {
     
     Id custid = (ID)ar.get('Parent__c');
     
     Integer count = (INTEGER)ar.get('expr0');
     
     Parent__c cust1 = new Parent__c(Id=custid);
     
     cust1.child_count__c = count;
     
     ct.add(cust1);
      
   }
   
   
   update ct;

}


 
atla satheeshkumaratla satheeshkumar
Below code is working i all scenarios.I have tested it.

trigger childCount on opportunitychild__c (after insert,after delete,after undelete) {

set<id> oppId = new Set<id> ();

if(Trigger.isInsert&&Trigger.isAfter){
for(opportunitychild__c oc:Trigger.new){
oppId.add(oc.opportunity__c);
}
}

if(Trigger.isDelete && Trigger.isAfter){
for(opportunitychild__c oc:Trigger.old){
oppId.add(oc.opportunity__c);
}
}

if(Trigger.isUnDelete && Trigger.isAfter){
for(opportunitychild__c oc:Trigger.new){
oppId.add(oc.opportunity__c);
}
}


List<Opportunity> opp = New List<Opportunity> ();
List<Opportunity> oppupdate = New List<Opportunity> ();
List<opportunitychild__c> oppChild = New List<opportunitychild__c> ();

opp= [select id from Opportunity where id in:oppid];
oppchild = [select id from opportunitychild__c where opportunity__c in :oppid];

Integer count =oppchild.size();

system.debug('*****count'+count);

for(Opportunity opp2 :opp){
opp2.childcountfrmLookup__c =count;
oppupdate.add(opp2);
}

update oppupdate;



}

Please mark it as Best answer.if it solves your issue

Regards
satish atla

 
Jancy MaryJancy Mary
Hi All,


Thank you very much for the qucik response, I went through the answers posted, and found one working at nearest to my requirement.

@Akhil Anil, I got your solution working fine when a child record is insterted, deleted, undeleted. But it did not work when I change the related parent(updated Child by changing its Parent). I understand the update is not defined in the Trigger, if you have the solution for update handy, it would be great help if you can share it over here.

Also I will try from at my end to review the logic of your trigger, if I can figure out what I missed in my Apex class which is not considering the already existing records. I wanted to follow 'One Trigger per Object' and 'Logic-less Trigger' policy while implementing this as per the below blog.

https://developer.salesforce.com/page/Trigger_Frameworks_and_Apex_Trigger_Best_Practices

Thanks a lot.
Cheersssssss:-)
  
Akhil AnilAkhil Anil

Hi Jancy,

Try the update snippet below. That should cover the scenario wherein you change the parent of a child record. Kindly mark it as a solution, if that works out.
 
trigger UpdateOrder on Child__c (after insert, after update, after delete, after undelete) {

   List<Parent__c> ct = new List<Parent__c>();
   
   Set<Id> custord = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   if(Trigger.isUpdate) {

     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }

     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   {
     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), Parent__c FROM Child__c where Parent__c IN :custord GROUP      BY Parent__c ];
   
   for(AggregateResult ar:groupedResults) {
     
     Id custid = (ID)ar.get('Parent__c');
     
     Integer count = (INTEGER)ar.get('expr0');
     
     Parent__c cust1 = new Parent__c(Id=custid);
     
     cust1.child_count__c = count;
     
     ct.add(cust1);
      
   }
   
   
   update ct;

}

Thanks
This was selected as the best answer
Jancy MaryJancy Mary
Hi satheeshkumar,

 I followed your logic and implemented the same in my apex class, it works perfect when I insert, delete and undelete a child record, however it does not work as expected when I update the parent on the child record. I mean when I change the related parent record on the child, the count is not updated as expected on the old parent and the new parent.

I tried to analyze and find the solution, unfortunately I failed and still trying for it. Could you give a try here to help me.

 
Akhil AnilAkhil Anil
Hi Jancy,

Did you try the latest snippet that I had updated ?
 
Jancy MaryJancy Mary
Hi Akhil,

I finally decided to go with your Trigger, it works absolutely perfect. The reason why I did not conclude this at earliest is I wanted the same process to be defined in Apex Class whoes methods I call in Trigger written on Child object. We wanted to follow the below rules:

1. One Trigger per Object.
2. Logic-less Trigger.

Hence we built Apex Class and called these methods in Trigger making the trigger as logicless trigger. But after going through Trigger which works absolutely fine I concluded that choosing Trigger would be the rite option in my scenario. All left was after implementing the Trigger, how can I update the existing records to show the count with out performing any DML operation on them. We thought of writing a batch apex which is called by our Trigger. But I analysed the below truth today.

"You can call the batch from a trigger, but it would hit the potential limits. We can only have 5 batches queued or executing at once. If the Trigger calls batch  jobs each time when it fire, then it exceeds the limit." Please correct me here if my understanding is wrong.

So I found alternate option of using Data Loader to touch base all the existing records to edit them once and save with out making any changes. Doing this will update all my existing records and rest everythig will be taken care by the Trigger.

 

Trigger which works perfect for me:

trigger Countchild on Child__c (after insert, after update, after delete, after undelete) {

   List<Parent__c> ct = new List<Parent__c>();
   
   Set<Id> custord = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   if(Trigger.isUpdate) {

     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }

     for(Child__c test:Trigger.Old) {
      
        custord.add(test.Parent__c);   
    
     }   
   
   }
   else
   {
     for(Child__c test:Trigger.New) {
      
        custord.add(test.Parent__c);   
    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), Parent__c FROM Child__c where Parent__c IN :custord GROUP      BY Parent__c ];
   
   for(AggregateResult ar:groupedResults) {
     
     Id custid = (ID)ar.get('Parent__c');
     
     Integer count = (INTEGER)ar.get('expr0');
     
     Parent__c cust1 = new Parent__c(Id=custid);
     
     cust1.child_count__c = count;
     
     ct.add(cust1);
      
   }
   
   
   update ct;

}

 
Jancy MaryJancy Mary
Hi Guys, 

Need your help again, we worked on this indepth and finally got the below Apex class working fine. However, when I have one child record associated with the Parent, if I clear the Parent name on the child record and save it, the count for child records on Parent still shows 1. It actually should show 0. 

All parents which are associated with 0 child  records shows the count as 1 instead of 0, this is not the issue when the Parent has atleast 1 child record. Seems like the issue is on the highlited lines of the below code under
handleBeforeUpdate. 

My question is, will the aggregate result return the value 0, or is there any different way to make it work when the value() is 0. Please help me on this. 

Thanks a lot,
Jancy Mary
public class calculateDesigCount{
  
  
  public static void handleAfterDelete(List<XYZ__c> childList){
    List<Contact> J= new List<Contact>();
    Set<Id> Conts = new Set<Id>();
  
      for(XYZ__c d: childList){
      if(d.Con__c!=null) // added this condition, if any designation record doesn't have the parent Contact associated, it will throw an error while trying to associate Contact, also when try to delete that particular Designation.
      Conts.add(d.Con__c);
      } 
      
      for(AggregateResult  ar: [SELECT COUNT(Id) cid, Con__c pid from XYZ__c WHERE Con__c IN: Conts GROUP BY Con__c]) {
      Contact c = new Contact();
      c.id=(id)ar.get('pid');  
      c.Child_Recounds_Count__c = (integer)ar.get('cid');                               
      J.add(c);
      }
      
    update J;
  }
  
  
  public static void handleAfterUpdate(List<XYZ__c> newChildList, List<XYZ__c> oldChildList){
     Set<Id> Conts = new Set<Id>();
     List<Contact> J= new List<Contact>();
     
       for(XYZ__c d: newChildList){
       if(d.Con__c!=null)
       Conts.add(d.Con__c);
       }
     
       for(XYZ__c d: oldChildList){
       if(d.Con__c!=null)
       Conts.add(d.Con__c);
       }  
     
       for(AggregateResult  ar: [SELECT COUNT(Id) cid, Con__c pid from XYZ__c WHERE Con__c IN: Conts GROUP BY Con__c]) {
       Contact c = new Contact();
       c.id=(id)ar.get('pid');   // this line assigns the Parent Id
       c.Child_Recounds_Count__c = (integer)ar.get('cid');      // this line assigns the number of child count to the count field.              
       J.add(c); 
       }
     
     update J;
  }
  
  
  public static void handleAfterInsert(List<XYZ__c> childList){
     Set<Id> Conts = new Set<Id>();
     List<Contact> J= new List<Contact>();
     
       for(XYZ__c d: childList){
       if(d.Con__c!=null)
       Conts.add(d.Con__c);
       }
     
       for(AggregateResult  ar: [SELECT COUNT(Id) cid, Con__c pid from XYZ__c WHERE Con__c IN: Conts GROUP BY Con__c]) {
       Contact c = new Contact();
       c.id=(id)ar.get('pid');  // getting the id  with ar.get('pid') and assigning it to the cust1.id
       c.Child_Recounds_Count__c = (integer)ar.get('cid');                    //[if integer doesn't work use decimal]            
       J.add(c);  
       }
     
     update J;
  }
  
  
  public static void handleAfterUnDelete(List<XYZ__c> childList){
     Set<Id> Conts = new Set<Id>();
     List<Contact> J= new List<Contact>();
     
       for(XYZ__c d: childList){
       if(d.Con__c!=null)
       Conts.add(d.Con__c);
       }
     
       for(AggregateResult  ar: [SELECT COUNT(Id) cid, Con__c pid from XYZ__c WHERE Con__c IN: Conts GROUP BY Con__c]) {
       Contact c = new Contact();
       c.id=(id)ar.get('pid');    // getting the id  with ar.get('pid') and assigning it to the cust1.id
       c.Child_Recounds_Count__c = (integer)ar.get('cid');                    //[if integer doesn't work use decimal]            
       J.add(c);
       }
      
     update J;
  }
  

}



 
Vrushali Mali 4Vrushali Mali 4
Hi Akhil Anil,
I apply your trigger but when am deleting all Contacts for that related Accounts then count is not updating it showing value 1 in count field....What should i do?
Kunal Singh Gusain 9Kunal Singh Gusain 9
did you found the reason why your code was only updating the parent's count_child field for new records and not for existing records?
you need to pay attention here,

 for(Child__c childRec: childList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c ++;
        }

you are iterating over only the new records only that are newly created (as childList) and counting based upon only those records, whereas you shoud have queried database for all contacts related to that parent and iterated over this list instead of 'childList'.
modify this piece logic to in case of after trigger:
list<contact> allChildList=[select Parent__c from Child__c where Parent__c in : parentIDSet];

for(Child__c childRec: allChildList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c ++;
        }

but this will not work if you are using 'before trigger' as 'allChildList' will not contain newly created children because 'allChildList' fetches child records from database and in case of 'before trigger' those new records are not present in database.

in case of before trigger there will be a small modification : 

list<contact> allChildList=[select Parent__c from Child__c where Parent__c in : parentIDSet];

allChildList.addAll(childList);     // have to add newly created records to those already existing in database

for(Child__c childRec: allChildList){
            if(parentMap.get(childRec.Parent__c).child_count__c == null){
                parentMap.get(childRec.Parent__c).child_count__c = 0;
            }
            parentMap.get(childRec.Parent__c).child_count__c ++;
        }

I know you already have known the reason but it might be helpful to others
someone rectify me if I am wrong somewhere.
PraveenchanduPraveenchandu
Create 3 custom Objects
a --> b -->c
c is grand child
a is grand parent
display the count of c in grandparent A
using trigger
in lookup relation
lakhwinder singh 9lakhwinder singh 9
Here is the list of latest whatsapp status collection you can share love, sad, attitude, romantic, attitude, sad, cool, friendship status and quotes from Whatsapp Love Status and share with you husband, wife, boyfriend, girlfriend, boys, girls in diffrent language hindi, punjabi, tamil, englis, marathi.
Whatsapp Love Status (https://lovestatusz.com/)
pk chawalpk chawal
if you are looking for best machetes in 2020 then you should visit our site we upload top rated products and best buying guide so check out our website https://themacheteguide.com/ for machete guide.
Sam Smith 60Sam Smith 60
We hire seasoned and highly qualified professionals who know the importance of delivering polished papers essaywriter (https://grademiners.com/). With a team of competent writers, you will submit top-notch papers to your tutor and get impressive results.
damion medamion me
Visual voicemail apps organize your messages in an email-like interface so you can quickly prioritize them without having to spend minutes or even hours listening to everyone. https://androidpowerhub.com/which-are-the-best-voicemail-apps/