+ Start a Discussion
Anton van DijkAnton van Dijk 

count number of records (lookup) in a field

Hi guys,

I have 2 custom objects (ObjectA__c and ObjectB__c)
in ObjectA__c I have created a lookup field for ObjectB__c.

My problem occurs when I want to have a field CountUsed__c in ObjectB__c that counts the number of times the object is used in the lookup field in ObjectA__c.

I've tried creating a formula field, but not it's not clear to me what the formula should be.
I also tried creating an Apex trigger. However, I couldn't get the select statement to work.

I want a field in ObjectB__c that is readonly and counts the number of time the object is used in the lookupfield @ ObjectA__c

Please advice the correct way to approach this :)
Best Answer chosen by Anton van Dijk
EldonEldon
Hi Anton,

Fixed the bug. Please try the below trigger and let me know.
 
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }

   else if(Trigger.isUpdate) {
     for(ObjectA__c  test:Trigger.New) {    
        if(test. ObjectB__c != null){
           ObjectAids.add(test.ObjectB__c);     
        }   
     }

     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);       
     }      
   }
   else{
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('ObjectB__c');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c();  
     objB.id=BId;
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
   
   update ObjectBList;

}



Regards
 
 

All Answers

pkpnairpkpnair
Anton,
You can try this app
https://appexchange.salesforce.com/listingDetail?listingId=a0N3A00000EO7F9UAL
-Prakash
EldonEldon
Hi Anton,

Try the below trigger on objectA__c
 
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }

   else if(Trigger.isUpdate) {
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);     
     }

     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);       
     }      
   }
   else{
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('ObjectB__c');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c(Id=BId);     
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
   
   update ObjectBList;

}

Let me know if you have any issues.

Regards
Amit Singh 1Amit Singh 1
Hi Anton,

You can achieve it in two ways.

1 - Convert lookup relationship to Master Detail and then create a Rollup Summary field in ObjectB__c which will count ObjectA__c Object records.
2 - Create a Number field in ObjectB__c which will store the total count of ObjectA__c and then create a new formula field which will hold the newly create field value then add this formula field into the layout and remove Number filed. This way you will find a field which is read-only.
3 -  Built a trigger which will be responsible for counting ObjectA__c records and then updating ObejectB__c records with the total value.

Sample code for the trigger is given below and in the below code I am counting no of Attachments into Account Object.
 
trigger trgCountAttachments on Attachment (After insert, After delete, After undelete) {
    Set<Id> parentIdsSet = new Set<Id>();
    List<Account> accountListToUpdate = new List<Account>();
    IF(Trigger.IsAfter){
        IF(Trigger.IsInsert || Trigger.IsUndelete){
            FOR(Attachment a : Trigger.new){
                if(a.ParentId!=null){   
                   parentIdsSet.add(a.ParentId); 
                }
            }
        }
        IF(Trigger.IsDelete){
            FOR(Attachment a : Trigger.Old){
                if(a.ParentId!=null){   
                   parentIdsSet.add(a.ParentId); 
                }
            }
        }
    }
    System.debug('#### parentIdsSet = '+parentIdsSet);
    List<Account> accountList = new List<Account>([Select id ,Name, No_Of_Attachments__c, (Select id, Name From Attachments) from Account Where id in:parentIdsSet]);
    FOR(Account acc : accountList){
        List<Attachment> attachmentList = acc.Attachments;
        acc.No_Of_Attachments__c = attachmentList.size();
        accountListToUpdate.add(acc);
    }
    try{
        update accountListToUpdate;
    }catch(System.Exception e){
        
    }
}
 
Let me know if this works. 

Thanks,
Amit Singh. 
Anton van DijkAnton van Dijk
Hi guys,

Thank you for your quick responses.
I don't want to be reliant on apps, so that's not an option.
The Master-Detail relationship is not an option either, since it makes the field required on ObjectA. In my case the field could be empty too.
The combination of number and formula field doesn't feel like a clean option, because I'll have more fields that don't provide more useful data.

I tried the trigger provided by Eldon, however, I'm getting an error when trying to update an ObjectA record.
MISSING_ARGUMENT, Id not specified in an update call: [] Trigger.UpdateParentCount: line 38, column 1

 
Amit Singh 1Amit Singh 1
Hi Anton,

Try with below code.
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }else if(Trigger.isUpdate || Trigger.IsInsert() || Trigger.IsUndelete()) {
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);     
     }     
   }
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c objBId FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('objBId');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c();
     objB.id = BId;
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
   
   update ObjectBList;

}


Let me know if this do the trick :)

Thanks,
Amit Singh.
Anton van DijkAnton van Dijk
Cheers Amit!
Just at line 10 isInsert and isUndelete are no functions, I removed the "()" part.

Working as intended except for 1 situation:
deleting the field value without deleting the record.

inserting a new ObjectA record, updating it with a ObjectB value, deleting the whole ObjectA record are all good.
However when I try to delete the ObjectB value without deleting the ObjectA record I get the following error:
MISSING_ARGUMENT, Id not specified in an update call: [] Trigger.UpdateParentCount: line 27, column 1

 
EldonEldon
Hi Anton,

Can you try this code let me know the output
 
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }

   else if(Trigger.isUpdate) {
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);     
     }

     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);       
     }      
   }
   else{
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('ObjectB__c');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c;  
     objB.id=BId;
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
   
   update ObjectBList;

}

Regards
Anton van DijkAnton van Dijk
Hi Eldon

unfortunately this isnt working.
Line 33 needs a little fix:
new ObjectB__c();

Also I'm still getting the error when deleting an ObjectB__c value from the field in ObjectA__c.
MISSING_ARGUMENT, Id not specified in an update call: [] Trigger.UpdateParentCount: line 39, column 1

I also noticed that if I switch 1 value for another, the old value doesnt get updated in count.
EldonEldon
In the above case i assumed the lookupfield name in objectA__c as ObjectB__c.

If the above code didnt work try another method like below,
 
trigger UpdateParentCount on ObjectB__c  (after insert, after update, after delete, after undelete) {
 IF(preventTrigger.runOnce)return;
list<ObjectB__c> ToUpdate = new list<ObjectB__c>();

for (ObjectB__c  B :  [SELECT Name, CountUsed__c ,(SELECT id FROM ObjectB__r) FROM ObjectB__c where id in trigger.newmap.keyset  ]) {
    
   B.CountUsed__c =  B.ObjectB__r.size();
  ToUpdate.add(B);

}

if(ToUpdate != null){
   update ToUpdate ;
}
Here ObjectB__r is the relationship name and 

the helper class to avoid recursive calling of above trigger,
public with sharing class preventTrigger{
	public static Boolean runOnce = false;
}

Here you will get the count when you do any operation on ObjectB__c

Regards
Amit Singh 1Amit Singh 1

Anton,

Try the below code and let me know the outcome :)

trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        If(test.ObjectB__c!=null)ObjectAids.add(test.ObjectB__c);   
    }      
   }else if(Trigger.isUpdate || Trigger.IsInsert || Trigger.IsUndelete) {
     for(ObjectA__c  test:Trigger.New) {      
        If(test.ObjectB__c!=null)ObjectAids.add(test.ObjectB__c);     
     }     
   }
   if(ObjectAids!=null && ObjectAids.size()>0){
	  AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c objBId FROM ObjectA__c  
	   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
	   for(AggregateResult a:groupedResults) {     
		 Id BId = (ID)a.get('objBId');     
		 Integer count = (INTEGER)a.get('expr0');     
		 ObjectB__c objB = new ObjectB__c();
		 objB.id = BId;
		 objB.CountUsed__c  = count;     
		 ObjectBList.add(objB);      
	   } 
	  update ObjectBList;
	}
}
Thanks,
Amit Singh.
Anton van DijkAnton van Dijk
Thank you Amit,
the new code doesn't give any errors.
assigning a relation updates the count by +1.
however, when I remove the relationship I need a -1 on the count, it doesnt substract after an update to the ObjectA record.
EldonEldon
Hi Anton,

Fixed the bug. Please try the below trigger and let me know.
 
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }

   else if(Trigger.isUpdate) {
     for(ObjectA__c  test:Trigger.New) {    
        if(test. ObjectB__c != null){
           ObjectAids.add(test.ObjectB__c);     
        }   
     }

     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);       
     }      
   }
   else{
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('ObjectB__c');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c();  
     objB.id=BId;
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
   
   update ObjectBList;

}



Regards
 
 
This was selected as the best answer
Amit Singh 1Amit Singh 1
Anton,

Try below code.
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        If(test.ObjectB__c!=null)ObjectAids.add(test.ObjectB__c);   
    }      
   }else if(Trigger.isUpdate || Trigger.IsInsert || Trigger.IsUndelete) {
     for(ObjectA__c  test:Trigger.New) {      
        If(test.ObjectB__c!=null)ObjectAids.add(test.ObjectB__c);  
        else If(Trigger.oldMap.get(test.Id).ObjectB__c!=test.ObjectB__c && test.ObjectB__c==null)ObjectAids.add(test.ObjectB__c);	
     }     
   }
   if(ObjectAids!=null && ObjectAids.size()>0){
	  AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c objBId FROM ObjectA__c  
	   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
	   for(AggregateResult a:groupedResults) {     
		 Id BId = (ID)a.get('objBId');     
		 Integer count = (INTEGER)a.get('expr0');     
		 ObjectB__c objB = new ObjectB__c();
		 objB.id = BId;
		 objB.CountUsed__c  = count;     
		 ObjectBList.add(objB);      
	   } 
	  update ObjectBList;
	}
}

Thanks,
Amit Singh.
Anton van DijkAnton van Dijk
Awesome! This is exactly what I needed.
Thank you for the fix.
 
EldonEldon
Hi Anton,

There was one more bug which would not update count  while deleting the last child :) Please see the below code which satisfies all your req,
 
trigger UpdateParentCount on ObjectA__c  (after insert, after update, after delete, after undelete) {

   List<ObjectB__c> ObjectBList = new List<ObjectB__c>();   
   Set<Id> ObjectAids = new Set<Id>();
   
   if(Trigger.isDelete) {
     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);   
         }      
   }

   else if(Trigger.isUpdate) {
     for(ObjectA__c  test:Trigger.New) {    
        if(test. ObjectB__c != null){
           ObjectAids.add(test.ObjectB__c);     
        }   
     }

     for(ObjectA__c  test:Trigger.Old) {      
        ObjectAids.add(test.ObjectB__c);       
     }      
   }
   else{
     for(ObjectA__c  test:Trigger.New) {      
        ObjectAids.add(test.ObjectB__c);    
     }
   }
   
   AggregateResult[] groupedResults = [SELECT COUNT(Id), ObjectB__c FROM ObjectA__c  
   where ObjectB__c IN :ObjectAids GROUP BY ObjectB__c ];
   


if(groupedResults.size() ==0){
      list<ObjectB__c> zeros =  [SELECT id,CountUsed__c  FROM ObjectB__c
   where id IN :ObjectAids];
    for(ObjectB__c c : zeros){
        ObjectB__c objB = new ObjectB__c();  
        objB.id=c.id;
        objB.CountUsed__c  = 0;     
        ObjectBList.add(objB); 
    }
  }


else{
   for(AggregateResult a:groupedResults) {     
     Id BId = (ID)a.get('ObjectB__c');     
     Integer count = (INTEGER)a.get('expr0');     
     ObjectB__c objB = new ObjectB__c();  
     objB.id=BId;
     objB.CountUsed__c  = count;     
     ObjectBList.add(objB);      
   }   
}
   
   update ObjectBList;

}


Regards