+ Start a Discussion
hemmhemm 

Getting around 1000 record collection size limit in pageBlockTable

On a VF page, I have a pageBlockTable.  There are times where there are more than 1000 records in the collection to be rendered.  When this occurs, the Visualforce collection limit of 1000 is hit and the page doesn't load.  I need to figure out a creative solution for getting around this.  In the end...

 

  1. I need all records to render on the page. If I do any pagination, it'll happen client-side after the records are loaded in the HTML.  I know the first responses to this will be about whether I really need to have all those records on the page and how there is doubt about whether I need to, but for the purposes of this forum post, please work with me here.
  2. I want to keep the look and feel of a pageBlockTable if possible.

 

When not using pageBlockTables, I have used a construct similar to the following to get through a collection of more than 1000 items.

 

<apex:repeat value="{!myCollection}" var="item" rows="1000" first="0">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="1000">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="2000">
{!item.text}
</apex:repeat>

 

pageBlockTable has the rows and first parameters, but if I do that, I'd be getting a new pageBlockTable everytime.

 

The options I can think of are:

 

  • Get a creative solution from the forums to actually utilize the pageBlockTable (purpose of this post)
  • Use apex:dataTable and try to use style classes to mimix the pageBlockTable look and feel. This is a nice possibility I haven't tried yet.
  • Use apex:repeat tags and make up my own HTML styling

 

Any help is appreciated.

 

 

Best Answer chosen by Admin (Salesforce Developers) 
sam_Adminsam_Admin

Intially i got error 4607 exceeds maximum size of 1000, but after looking at this post , i have modified my Vf and class to the below but still iam getting Collection size 1,001 exceeds maximum size of 1,000.

 

what am doing wrong here ?

 

<apex:page controller="thousandLimit">    
   <apex:pageBlock >
      <apex:repeat value="{!thousandBlocks}" var="block">
            <apex:pageBlockTable value="{!block.cases}" var="c">
            <apex:column value="{!c.CaseNumber}"/>
            <apex:column value="{!c.owner.name}"/>
            <apex:column value="{!c.App_Process__c}"/>
            <apex:column value="{!c.Load__c}"/>
            <apex:column value="{!c.subject}"/>
            <apex:column value="{!c.Estimated_Completion_Date__c}"/>   
            <apex:column value="{!c.Complete__c}"/>
            <apex:column value="{!c.Priority}"/>
            <apex:column value="{!c.Case_Age_Days__c}"/>                    
            </apex:pageBlockTable>
        </apex:repeat>
     </apex:pageBlock>  
</apex:page>

 

 

public class thousandLimit
{
    private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
    private final integer listLimit;
    
    public thousandLimit()
    {
        listLimit = 1000;
    }
    
    public limitWrapper[] getthousandBlocks()
    {
        thousandBlocks = new limitWrapper[]{};
        
        integer counter = 0;
        integer loopCount = 0;
        case[] tmpcase = new case[]{};
        
        for(case c:[select CaseNumber,owner.name,App_Process__c,Load__c,subject,Estimated_Completion_Date__c,Complete__c,Priority,Case_Age_Days__c from Case
                where (Status != 'Closed' or Status != 'Declined') and Estimated_Completion_Date__c = null and owner.Alias like 'EA%' order by owner.name])
        {
            if(counter < listLimit)
            {
                tmpcase.add(c);
                counter++;
            }
            else
            {
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
                tmpcase = new case[]{};
                tmpcase.add(c);
                counter = 0;
            }            
        }
        
        if(thousandBlocks.size() == 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
        }
        
        return thousandBlocks;
    }
    
    public class limitWrapper
    {
        public case [] cases {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(case[] accs, integer i)
        {
            cases = accs;
            blockNumber = i;
        }
        
    }
}

All Answers

TehNrdTehNrd

All visualforce repeat/table componenents are currently limited to 1000. This includes apex:repeat, apex:dataTable, apex:pageBlockTable, etc. They only thing you can really do is create list of lists and then roll your own dataTable and copy the pageblock styling. Here is an example:

 

 

List<List<Account>> accounts = new List<List<Acccount>();
List<Account> tempList = new List<Account>();

for(Account a : [select Id, Name, Industry from Account]){
	tempList.add(a);
	
	if(tempList.size() == 1000){//limit of vf repeat components
		accounts.add(tempList);
		tempList = new List<Account>();
	}
}
accounts.add(tempList);


<table border="1">
	<apex:repeat value="{!accounts}" value="accts">
		<apex:repeat value="{!accts}" value="a">
			<tr>
			  <td>{!a.Id}</td>
			  <td>{!a.Name}</td>
			  <td>{!a.Industry}</td>
			</tr>
		</apex:repeat>
	</apex:repeat>
</table>

 

 

There are probably some syntax typos as I just whipped this up in notepad.

 

-Jason

hemmhemm

Bummer.  Was afraid of that.  Would be nice if VF matched the no size limit of Apex.  Get rid of the arbitrary 1000 record limitation and let the other limits like View State and all take care of it.  

TehNrdTehNrd

Ya, I think they left the 1000 limit on visualforce components because rerender performance of large dataTables is pretty bad and can make the browser freeze up, something to watch out for.

 

With the work around above the limit you need to look out for is heap size as the lists start to become large.

TehNrdTehNrd

Here is something I am about to try. I've got the need to build a huge table and rather than use a List and binding to fields I am going to attempt to build a string of HTML in apex and then pass this to the page. The limits I'll be going up against are script statements and help size. Another pain point will be currency and number formatting in Apex. I have no idea if this will work but I'll let you know how this turns out.

hemmhemm

Good luck.  I am thinking the best approach right now would be to craft the HTML in VF and then use apex:repeat tags to run through groups of 1000 records like this...

 

 

<apex:repeat value="{!myCollection}" var="item" rows="1000" first="0">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="1000">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="2000">
{!item.text}
</apex:repeat>

 

I haven't ventured into this yet.  Either way, we need to recreate the HTML, which brings us back to the s-control model for doing this than the VF model.  Ideally VF would just increase the limit.  1000 records is an arbitrary number.

 

Greg RohmanGreg Rohman

Hi Hemm.

 

I attempted something similar to your code below, but am still getting the "exceeds maximum size of 1,000" error.

 

 

<apex:repeat value="{!myCollection}" var="item" rows="1000" first="0">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="1000">
{!item.text}
</apex:repeat>
<apex:repeat value="{!myCollection}" var="item" rows="1000" first="2000">
{!item.text}
</apex:repeat>

It's almost as if the entire collection is still passed to the VF page, but the "rows" parameter only limits what is actually displayed.

 

SF support, please advise. I'm building some custom Excel-export reports that require the display of more than 1,000 records.

 

-Greg

 

 

PaulATPimptastiPaulATPimptasti

Hello!

 

We recently came up against this too and here's how we solved it (I've had to replace the custom object with Account - but hopefully you'll get the idea).

 

The Page:

 

 

<apex:page controller="thousandLimit">    
    <apex:pageBlock >
        <apex:repeat value="{!thousandBlocks}" var="block">
            <apex:pageBlockTable value="{!block.Accounts}" var="a">
                <apex:column value="{!a.Name}"/>                
            </apex:pageBlockTable>
        </apex:repeat>
    </apex:pageBlock>  
</apex:page>

 

 

The Controller:

 

 

public class thousandLimit
{
    private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
    private final integer listLimit;
    
    public thousandLimit()
    {
        listLimit = 999;
    }
    
    public limitWrapper[] getthousandBlocks()
    {
        thousandBlocks = new limitWrapper[]{};
        
        integer counter = 0;
        integer loopCount = 0;
        Account[] tmpAccount = new Account[]{};
        
        for(Account a:[SELECT Id, Name FROM Account])
        {
            if(counter < listLimit)
            {
                tmpAccount.add(a);
                counter++;
            }
            else
            {
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpAccount,loopCount));
                tmpAccount = new Account[]{};
                tmpAccount.add(a);
                counter = 0;
            }            
        }
        
        if(thousandBlocks.size() == 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpAccount,loopCount));
        }
        
        return thousandBlocks;
    }
    
    public class limitWrapper
    {
        public Account[] accounts {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(Account[] accs, integer i)
        {
            accounts = accs;
            blockNumber = i;
        }
        
    }
}

 

Essentially what this does is use a wrapper class to hold 1000 accounts at a time, thus getting round the limit in VisualForce.  In theory you could store 1,000,000 records, but if you did you'd  run into other Governor Limits.  But it's a nice thought.

 

 

Hope you find this useful.

 

Paul

mChakmChak

This one works really great, were able to display more than 1000 records. But I encountered a small issue. After 1st block of 1000 records, it again prints the column header. Is there any way, we could get rid of printing column headers after 1000 records?

sam_Adminsam_Admin

Intially i got error 4607 exceeds maximum size of 1000, but after looking at this post , i have modified my Vf and class to the below but still iam getting Collection size 1,001 exceeds maximum size of 1,000.

 

what am doing wrong here ?

 

<apex:page controller="thousandLimit">    
   <apex:pageBlock >
      <apex:repeat value="{!thousandBlocks}" var="block">
            <apex:pageBlockTable value="{!block.cases}" var="c">
            <apex:column value="{!c.CaseNumber}"/>
            <apex:column value="{!c.owner.name}"/>
            <apex:column value="{!c.App_Process__c}"/>
            <apex:column value="{!c.Load__c}"/>
            <apex:column value="{!c.subject}"/>
            <apex:column value="{!c.Estimated_Completion_Date__c}"/>   
            <apex:column value="{!c.Complete__c}"/>
            <apex:column value="{!c.Priority}"/>
            <apex:column value="{!c.Case_Age_Days__c}"/>                    
            </apex:pageBlockTable>
        </apex:repeat>
     </apex:pageBlock>  
</apex:page>

 

 

public class thousandLimit
{
    private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
    private final integer listLimit;
    
    public thousandLimit()
    {
        listLimit = 1000;
    }
    
    public limitWrapper[] getthousandBlocks()
    {
        thousandBlocks = new limitWrapper[]{};
        
        integer counter = 0;
        integer loopCount = 0;
        case[] tmpcase = new case[]{};
        
        for(case c:[select CaseNumber,owner.name,App_Process__c,Load__c,subject,Estimated_Completion_Date__c,Complete__c,Priority,Case_Age_Days__c from Case
                where (Status != 'Closed' or Status != 'Declined') and Estimated_Completion_Date__c = null and owner.Alias like 'EA%' order by owner.name])
        {
            if(counter < listLimit)
            {
                tmpcase.add(c);
                counter++;
            }
            else
            {
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
                tmpcase = new case[]{};
                tmpcase.add(c);
                counter = 0;
            }            
        }
        
        if(thousandBlocks.size() == 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
        }
        
        return thousandBlocks;
    }
    
    public class limitWrapper
    {
        public case [] cases {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(case[] accs, integer i)
        {
            cases = accs;
            blockNumber = i;
        }
        
    }
}

This was selected as the best answer
sam_Adminsam_Admin

I solved this by reducing list limit to 999,    listLimit = 999;

PaulATPimptastiPaulATPimptasti

@sam_admin - that's correct 1000 actuall is 1001!  I'd forgotten that position 0 is actually the 1st record.

 

HTH.

OnkiOnki

<apex:page controller="thousandLimit" readonly="true">    == It will increase your data from 1000 to 10000.
   <apex:pageBlock >
      <apex:repeat value="{!thousandBlocks}" var="block">
            <apex:pageBlockTable value="{!block.cases}" var="c">
            <apex:column value="{!c.CaseNumber}"/>
            <apex:column value="{!c.owner.name}"/>
            <apex:column value="{!c.App_Process__c}"/>
            <apex:column value="{!c.Load__c}"/>
            <apex:column value="{!c.subject}"/>
            <apex:column value="{!c.Estimated_Completion_Date__c}"/>   
            <apex:column value="{!c.Complete__c}"/>
            <apex:column value="{!c.Priority}"/>
            <apex:column value="{!c.Case_Age_Days__c}"/>                    
            </apex:pageBlockTable>
        </apex:repeat>
     </apex:pageBlock>  
</apex:page>

ar_sfdcar_sfdc

WOW!! the read only thing helped me greatly!! Thanks

sri14sri14

issue with the VF Components​, not being able to display more than 1000 records in the file.

Can you please look into them  and see what can be done in SFDC?

 

Thank you for your time!

 

Sincerely

sri

Aditya ShastriAditya Shastri

Adding readonly attribute in page tag is a great solution for this. 

bujjibujji

Hi,

 

Can't we do pagination for this.I am also facing same problem.

 

Thanks,

Bujji

Aravinthraj techresourceAravinthraj techresource

i want to create 50 list but want to show oly 10 list per page like that show all list in 5 pages .how to set limit and show the lists in multiple pages???

hitesh90hitesh90

Hi sam_Admin,

 

below is the solution of your answer.

<apex:page controller="thousandLimit">    
   <apex:pageBlock >
      <apex:repeat value="{!thousandBlocks}" var="block">
            <apex:pageBlockTable value="{!block.cases}" var="c">
            <apex:column value="{!c.CaseNumber}"/>
            <apex:column value="{!c.owner.name}"/>
            <apex:column value="{!c.App_Process__c}"/>
            <apex:column value="{!c.Load__c}"/>
            <apex:column value="{!c.subject}"/>
            <apex:column value="{!c.Estimated_Completion_Date__c}"/>   
            <apex:column value="{!c.Complete__c}"/>
            <apex:column value="{!c.Priority}"/>
            <apex:column value="{!c.Case_Age_Days__c}"/>                    
            </apex:pageBlockTable>
        </apex:repeat>
     </apex:pageBlock>  
</apex:page>

 

 

public class thousandLimit
{
    private limitWrapper[] thousandBlocks = new limitWrapper[]{};
    
    private final integer listLimit;
    
    public thousandLimit()
    {
        listLimit = 1000;
    }
    
    public limitWrapper[] getthousandBlocks()
    {
        thousandBlocks = new limitWrapper[]{};
        
        integer counter = 0;
        integer loopCount = 0;
        case[] tmpcase = new case[]{};
        
        for(case c:[select CaseNumber,owner.name,App_Process__c,Load__c,subject,Estimated_Completion_Date__c,Complete__c,Priority,Case_Age_Days__c from Case
                where (Status != 'Closed' or Status != 'Declined') and Estimated_Completion_Date__c = null and owner.Alias like 'EA%' order by owner.name])
        {
            if(counter < listLimit)
            {
                tmpcase.add(c);
                counter++;
            }
            else
            {
                loopCount++;
                thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
                tmpcase = new case[]{};
                tmpcase.add(c);
                counter = 0;
            }            
        }
        
        if(thousandBlocks.size() == 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
        }
        
        return thousandBlocks;
    }
    
    public class limitWrapper
    {
        public case [] cases {get;set;}
        public integer blockNumber {get;set;}
        public limitWrapper(case[] accs, integer i)
        {
            cases = accs;
            blockNumber = i;
        }
        
    }
}

 

---

  if(thousandBlocks.size() == 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
        }

 

Replace this condition of above answer with below..

 

  if(counter > 0)
        {
            loopCount++;
            thousandBlocks.add(new limitWrapper(tmpcase,loopCount));
        }

 

I think this is helpful to you.

 

Geoff DGeoff D
readonly="true" worked for me. Thank you Onki.
Alex SchachAlex Schach
@Onki - The readonly = "true" solution worked perfectly for me! 

I already had a wrapper class and lots of other complicated code that I didn't want to re-write. This allowed it to work perfectly with virtually no change to the Apex.

Thank you!
smriti kumari 10smriti kumari 10
Setting the readonly attribute as true can only help if there is no DMLs in the controller side which would otherwise throw an exception. The best way to overcome this error is to go for pagination and set max limit of records in a VF page within 0-1000 at a time. This would help you in bulk data handling and would prevent VF page crash for collection size more than 1000 since you can only list maximum 1000 elements in visual force pages components with one state.

Thank you!
Mustika MurniMustika Murni
Thank you
N Hemanth kumarN Hemanth kumar
how can we acheive more than 10000 records?
David Roberts 4David Roberts 4
Thanks Sam_Admin. I need this because I can't use readonly bacause I want DMLs.
Great solution.
Yes, listLimit = 999 is the only change required.
Hitesh90 your "correction" is incorrect.
The test "if(thousandBlocks.size() == 0)" is to check if the first block was never filled (that is, under 1000 cases were added to the block), in which case the partial block is added.
 
NasirNasir
@sam_Admin

Hi Sam,

I came across this issue and choose your solution and implemented it. Only one issue i found that after 1000 record, i can see the header again. How can i get rid of the header ? Please let me know on this?

@DavidRoberts4 

Did you face the header issue? If yes, how did u solve it?

Thanks
Nasir 
David Roberts 4David Roberts 4
Hi Nasir,
Yes, I faced the same issue but was able to ignore it as I was only debugging my data.
On a related note: I also came across the "Transient" declaration which avoided my large arrays affecting the View State limits.
Regards,
Dave.
NasirNasir
Hi David,

thanks for the reply. Yes i know by using transient we can solve view state error. But i am facing the issue of two headers. Still looking out for solution. I removed the heards by using javascript. But it is removing both the headers. I need to remove the 2nd headres.

Thanks
Nasir Jawed
Anindya Halder 17Anindya Halder 17
@TehNrd Thank you -- you are a life saver.
I was facing thsi issue not while displaying more than 1000 records in a VF page but while deploying a second VF page as excel.
all sorted now using your nested apex:repeat.