5

I am receiving an error stated above on line 26**(listAccount1)**, can anyone guide me on how to solve it?

public class FRB_UpdateExceptionsforBatch
{

  public static void FRB_Exceptions(){
   string FRBRecordTypeID = utility.getFRBProfileNameLabel();
      List<Account> lstAccount = new List<Account>();
        List<Account> lstAccount1 = new List<Account>();
        List<Account> lstAccount2 = new List<Account>();
        List<Account> lstAccount3 = new List<Account>();
   lstAccount = [Select id,FRB_Firm_Check__c,Number_of_Exception__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_11_19_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) FROM Account WHERE FRB_Firm_Check__c = TRUE];
    if(lstAccount.size()>0){
          for( Account acc : lstAccount )
          {
              List<Process_Adherence_Monitoring__c> lstCont = acc.Process_Adherence_Monitoring__r;

              acc.Number_of_Exception__c = lstCont.size();
              acc.FRB_Firm_Check__c = true;
          }

          update lstAccount;
    }


      //To update 4-6 bucket

    lstAccount1 = [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_4_6_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where FRB_Firm_Check__c = TRUE];

      if(lstAccount1.size()>0){
          for( Account acc : lstAccount1 )
          {
              List<Process_Adherence_Monitoring__c> lstCont1 = acc.Process_Adherence_Monitoring__r;               
              acc.Number_of_exceptions_under_4_6_bucket__c= lstCont1.size();
              acc.FRB_Firm_Check__c = true;
          }

          update lstAccount1 ;
      }


      //To update 6-11 bucket

       lstAccount2 =  [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_7_10_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where FRB_Firm_Check__c = TRUE];

      if(lstAccount2.size()>0){
          for( Account acc : lstAccount2 )
          {
              List<Process_Adherence_Monitoring__c> lstCont2 = acc.Process_Adherence_Monitoring__r;

              acc.Number_of_exceptions_under_7_10_bucket__c= lstCont2.size();
              acc.FRB_Firm_Check__c = true;
          }
          update lstAccount2 ;
      }

      //To update 11 bucket

          lstAccount3 =  [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_11_19_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where FRB_Firm_Check__c = TRUE];

      if(lstAccount3.size()>0){
          for( Account acc : lstAccount3 )
          {
              List<Process_Adherence_Monitoring__c> lstCont3 = acc.Process_Adherence_Monitoring__r;

              acc.Number_of_exceptions_under_11_19_bucket__c= lstCont3.size();
              acc.FRB_Firm_Check__c = true;
          }                   
          update lstAccount3 ;
      }

    }  
}
7

You get this error when you have a child list that is too large. Have a look at SOQL For Loops:

  • You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set. For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

    for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
                        FROM Account WHERE Id IN ('<ID value>')]) { 
        List<Contact> contactList = acct.Contacts; // Causes an error
        Integer count = acct.Contacts.size(); // Causes an error
    }
    

To avoid getting this exception, use a for loop to iterate over the child records, as follows.

for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
                    FROM Account WHERE Id IN ('<ID value>')]) { 
    Integer count=0;
    for (Contact c : acct.Contacts) {
        count++;
    }
}

So in your case, you should be able to change this line:

List<Process_Adherence_Monitoring__c> lstCont1 = acc.Process_Adherence_Monitoring__r;

And instead use a for loop:

List<Process_Adherence_Monitoring__c> lstCont1 = new List<Process_Adherence_Monitoring__c>();
for (Process_Adherence_Monitoring__c child : acc.Process_Adherence_Monitoring__r)
    lstCont1.add(child);
  • Thanks for your suggestion , but even If I write a separate query on child object I am getting an error of more than 50000 records.Can I apply future method? – manchesterunited Feb 13 '17 at 9:21
  • Um. Yes you may be able to make it work with a future method. It looks like batch processing may be more appropriate if you're seeing that volume, though. – Adrian Larson Feb 13 '17 at 9:22
  • I wrote a future method as well but there I am getting the 50001 records error as well @adrianlarson. – manchesterunited Feb 13 '17 at 9:25

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.