You need to sign in to do that
Don't have an account?
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);
}
}
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);
}
}
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.
Thanks
All Answers
Thanks,
SFDC Beginner.
The below code should work in all the scenarios. You can test out the same and let me know
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
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:-)
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.
Thanks
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.
Did you try the latest snippet that I had updated ?
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;
}
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
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?
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.
a --> b -->c
c is grand child
a is grand parent
display the count of c in grandparent A
using trigger
in lookup relation
Whatsapp Love Status (https://lovestatusz.com/)