+ Start a Discussion
Derek Patrick DayaDerek Patrick Daya 

Count number of child records using SOQL Aggregate

I am new to this and I hope someone can help me with this.

What my aim is to count the number of AVTRRT__Job_Applicant__c records with a date value on Last_Activity__c field on AVTRRT__Job_Applicants__c object that is associated to AVTRRT__Job__c and place the total count on the field called Number_of_Applicants_with_Activities__c on AVTRRT__Job__c object.

I think Agrgregate SOQL is the only option I have since using the code below conflicts with another trigger as it causes continues loop.

trigger DerekRollUp on AVTRRT__Job_Applicant__c (after insert, after update, after delete, after undelete) {
Map updateJob = new Map(); Set updateJobIds = new Set();

// If we are inserting, updating, or undeleting, use the new ID values
if(Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete)
for(AVTRRT__Job_Applicant__c applicant:Trigger.new)
updateJobIds.add(applicant.AVTRRT__Job__c);

// If we are updating, some Jobs might change, so include that as well as deletes
if(Trigger.isUpdate || Trigger.isDelete)
for(AVTRRT__Job_Applicant__c applicant:Trigger.old)
updateJobIds.add(applicant.AVTRRT__Job__c);

// Do not create a record for null field
updateJobIds.remove(null);

// Create in-memory copies for all Jobs that will be affected
for(Id JobId:updateJobIds)
updateJob.put(JobId,new AVTRRT__Job__c(id=JobId,Number_of_Applicants_with_Activities__c=0));

// Run an optimized query that looks for all Jobs that meet the if/then criteria
for(AVTRRT__Job_Applicant__c applicant:[select id,AVTRRT__Job__c from AVTRRT__Job_Applicant__c where AVTRRT__Job__r.Id in :updateJobIds and Last_Activity_Date__c !=null]) updateJob.get(applicant.AVTRRT__Job__c).Number_of_Applicants_with_Activities__c++;

// Update all the Jobs with new values.
Database.update(updateJob.values());
}
ShashankShashank (Salesforce Developers) 
If you are facing a recursive trigger issue, this will help: https://help.salesforce.com/apex/HTViewSolution?id=000133752&language=en_US

You can also just use a count() function, like this:

integer count = [select count() from contact];