+ Start a Discussion
Arup SarkarArup Sarkar 

System.LimitException: and Collection size error

Hi:

We have a List<Task> CallReportData in a controller (apex class) which queries a task object based on date parameters and return rows, which is then fed to a VF page rendered as PDF to display results. There is corresponding get method in the controller.

 

In VF we use the following

<apex:pageblockTable value="{!CallReportData}" var="task">
<apex:column value="{!task.Date__c}" />
</apex:pageblockTable>

 

Problem is when the number of records are more than 1000 I am getting the following error.

 

Collection size 1,695 exceeds maximum size of 1,000

 

I also loop within the main loop another SOQL which is giving me this error when the number of rows from main SOQL is more than 1000.


caused by: System.LimitException: Too many SOQL queries: 101

How to get past the above errors?

 

Regards,

Arup

Best Answer chosen by Admin (Salesforce Developers) 
Arup SarkarArup Sarkar

I was able to get past 1000 record limitation and nested SOQL issue by a) Making a batch of 1000 records and returning it to the VF Page b) Removed the SOQL from within the for loop put it outside, retrieved the ID's in a SET and then used the SET in the IN clause of the query.

 

I got this info from a blog, I wish I copied the URL, but however here are the main blocks of the code.

 

Apex Class:

 

//declare a array of the class   
 private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
//in the constructor define the limit
    public GenerateCallReportController(){
        listLimit = 1000;
    }
//instantiate the class check for the limit and process accordingly
        thousandBlocks = new limitWrapper[]{};
        integer counter = 0;
        integer loopCount = 0;
        Task[] tmpTask = new Task[]{};      
        for(Task a : CallReportData){
            if(counter < listLimit-1){
                tmpTask.add(a);
                counter++;
            }else{
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpTask,loopCount));
                tmpTask = new Task[]{};
                tmpTask.add(a);
                counter = 0;
            }
        }
        if(thousandBlocks.size() == 0){
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpTask,loopCount));        
        }
        return thousandBlocks;

    public class limitWrapper
    {
        public Task[] tasks {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(Task[] paramTasks, integer i)
        {
            tasks = paramTasks;
            blockNumber = i;
        }
    }

 Corresponding VF page:

 

    <apex:pageBlock >
        <apex:repeat value="{!CallReportData}" var="block">        
            <apex:pageblockTable value="{!block.tasks}" var="task">
            <apex:column value="{!task.Account.Name}" />
        </apex:pageblockTable>
      </apex:repeat>
    </apex:pageBlock>

 

Modify the above code according to your needs, this is not a entire working code, just the idea how it is implemented.

 

Thanks all for your help.

All Answers

amidstcloudamidstcloud

Hi Arup,

 

please provide the controller code also ..

 

 

Sandeep001Sandeep001

For error - caused by: System.LimitException: Too many SOQL queries: 101

 

you need to take out the SOQL query outside the FOR loop

 

you are getting error - Collection size 1,695 exceeds maximum size of 1,000 because you cannot bind more than 1000 records to your visualforce controller.

 

 

Ankit AroraAnkit Arora

To remove your first error apply limit to query to 1000 which returns the result in "CallReportData" and we need the code snippet to resolve too many SOQL exception. Am hoping there is a query in for loop.

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

Arup SarkarArup Sarkar

I was able to get past 1000 record limitation and nested SOQL issue by a) Making a batch of 1000 records and returning it to the VF Page b) Removed the SOQL from within the for loop put it outside, retrieved the ID's in a SET and then used the SET in the IN clause of the query.

 

I got this info from a blog, I wish I copied the URL, but however here are the main blocks of the code.

 

Apex Class:

 

//declare a array of the class   
 private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
//in the constructor define the limit
    public GenerateCallReportController(){
        listLimit = 1000;
    }
//instantiate the class check for the limit and process accordingly
        thousandBlocks = new limitWrapper[]{};
        integer counter = 0;
        integer loopCount = 0;
        Task[] tmpTask = new Task[]{};      
        for(Task a : CallReportData){
            if(counter < listLimit-1){
                tmpTask.add(a);
                counter++;
            }else{
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpTask,loopCount));
                tmpTask = new Task[]{};
                tmpTask.add(a);
                counter = 0;
            }
        }
        if(thousandBlocks.size() == 0){
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpTask,loopCount));        
        }
        return thousandBlocks;

    public class limitWrapper
    {
        public Task[] tasks {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(Task[] paramTasks, integer i)
        {
            tasks = paramTasks;
            blockNumber = i;
        }
    }

 Corresponding VF page:

 

    <apex:pageBlock >
        <apex:repeat value="{!CallReportData}" var="block">        
            <apex:pageblockTable value="{!block.tasks}" var="task">
            <apex:column value="{!task.Account.Name}" />
        </apex:pageblockTable>
      </apex:repeat>
    </apex:pageBlock>

 

Modify the above code according to your needs, this is not a entire working code, just the idea how it is implemented.

 

Thanks all for your help.

This was selected as the best answer
Arup SarkarArup Sarkar

Found the URL, go to the end and search for Paul, it is his idea that I implemented, hope it helps everyone who are facing a similar issue.

 

http://boards.developerforce.com/t5/Visualforce-Development/Getting-around-1000-record-collection-size-limit-in/m-p/255905#M33279