You need to sign in to do that
Don't have an account?
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...
- 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.
- 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.
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
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:
There are probably some syntax typos as I just whipped this up in notepad.
-Jason
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.
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.
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.
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...
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.
Hi Hemm.
I attempted something similar to your code below, but am still getting the "exceeds maximum size of 1,000" error.
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
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:
The Controller:
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
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?
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;
}
}
}
I solved this by reducing list limit to 999, listLimit = 999;
@sam_admin - that's correct 1000 actuall is 1001! I'd forgotten that position 0 is actually the 1st record.
HTH.
<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>
WOW!! the read only thing helped me greatly!! Thanks
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
Adding readonly attribute in page tag is a great solution for this.
Hi,
Can't we do pagination for this.I am also facing same problem.
Thanks,
Bujji
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???
Hi sam_Admin,
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.
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!
Thank you!
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.
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
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.
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
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.