0

I am getting the following error for my contact count trigger below. I am not sure what this error means. It only affects 2 out of 300,000+ accounts in my org.

Error: Invalid Data. Review all error messages below to correct your data. Apex trigger NumberOfContacts caused an unexpected exception, contact your administrator: NumberOfContacts: execution of BeforeUpdate caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop: External entry point

trigger NumberOfContacts on Account (before insert, before update) {
    if(trigger.isinsert)
        for(account a:trigger.new)
            a.Number_of_contacts__c = 0;
    else
        for(account a:[select id,(select id from contacts) from account where id in :trigger.new])
            trigger.newmap.get(a.id).Number_of_contacts__c = a.contacts.size();

        for(account b:[select id,(select id from contacts where Inactive__c = False) from account where id in :trigger.new])
            trigger.newmap.get(b.id).Number_of_active_contacts__c = b.contacts.size();
}
1

This is one of the big gotchas of using Parent-Child subqueries (a.k.a a Left Outer Join from this article)

If there are enough child records (limit seems to be around 250), Salesforce won't allow you to access the list of child records without using a for loop.

The basic version of this workaround is

List<Account> accounts = [SELECT Id, (SELECT Id FROM Contacts) FROM Account LIMIT 10];

Map<Id, List<Contact>> accountToContacts = new Map<Id, List<Contact>>();

for(Account acct :accounts){
    accountToContacts.put(acct.Id, new List<Contact>());

    // Instead of using acct.Contacts.size(), or contactList = acct.Contacts
    //   we need to use a loop to go through the child records one by one
    for(Contact cont :acct.Contacts){
        accountToContacts.get(acct.Id).add(cont);
    }

}

Since you'll need to loop over every related Contact, I don't think there's a benefit to maintaining two separate queries to get the total number of Contacts and the number of active Contacts. I'd include the Inactive__c field in your query, initialize another Map<Id, List<Contact>>, and then have an if inside the loop over contacts to put only the active ones into one of your maps.

After you've satisfied Salesforce's whims, you'll need to loop over your Accounts to actually get the counts.

// Assuming before update
for(Account acct :trigger.new){
    acct.Number_of_Active_Contacts__c = accountActiveContacts.get(acct.Id).size();
    // and a very similar line for setting the total number of contacts

}
  • 1
    It also seems to be dependent on API version; I recently saw a question where they were able to have up to 10,000 child items without getting that error. – sfdcfox Nov 11 '16 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.