Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I am working on pagination and i am able to display my records on my VF page and i have done pagination to get the record get display. But the pagination work only when the no of records is less than 2000. If the number of records are more than 2000 and when we click on the last button which show all the last records salesforce throws an error

Maximum SOQL offset allowed is 2000 Error is in expression '{!endbtn}' in component <apex:commandButton> in page accountpagination_vf. An unexpected error has occurred. Your development organization has been notified.

I understand why this issue is getting its because salesforce have limit of 2000 on records for pagination. But mu client have more than 2000 records and he want all to be get displayed on the VF page on pagination. Please guide me the best way to achieve this scenario. my code for VF page :

<apex:page controller="accountPagination_ctrl">
 <apex:pageBlock >
  <apex:form >
      <apex:commandButton value="Search" action="{!searchresult}"/>

  <apex:dataTable value="{!accLst}" var="ac">
      <apex:column headerValue="Name">
          <apex:outputField value="{!ac.Name}"/>
      </apex:column>
      <apex:column headerValue="Id">
          <apex:outputField value="{!ac.Id}"/>
      </apex:column>
  </apex:dataTable>

  <apex:commandButton value="FIRST" action="{!fstbtn}" disabled="{!prv}"/>
  <apex:commandButton value="PRE" action="{!prvbtn}" disabled="{!prv}"/>
  <apex:commandButton value="NXT" action="{!nextbtn}" disabled="{!nxt}"/>
  <apex:commandButton value="END" action="{!endbtn}" disabled="{!nxt}"/>
 </apex:form>
</apex:pageBlock>

controller :

public class accountPagination_ctrl{

public List<Contact> accLst {get; set;}
public List<Contact> accLst_size {get; set;}
public integer counter = 0;
public integer recNum = 0;
public integer limits = 5;
public set<id> contactID {get; set;}

public accountPagination_ctrl(){
    accLst = new List<Contact> ();
    accLst_size = new List<Contact> ();
    contactID = new set<id>();
    Result();
}

public void Result(){
    accLst_size = [SELECT Name, Id FROM Contact WHERE name != null];
    recNum = accLst_size.size();

}
public void searchresult(){


    //getResult();
    SYSTEM.DEBUG('Number of records ' + recNum ); 
    accLst = [SELECT Name, Id FROM Contact WHERE id in: contactID and name != null LIMIT :limits OFFSET: counter  ];

}

public void nextbtn(){
    counter += limits ;
    searchresult();
}

public void prvbtn(){
    counter -= limits ;
    searchresult();
}

public void fstbtn(){
    counter = 0;
    searchresult();
}

public void endbtn(){
    counter = recNum - math.mod(recNum,limits);
    searchresult();
}

public boolean getprv(){
    if(counter == 0)
        return true;
    else
        return false;
}

public boolean getnxt(){
    if((counter + limits) > recNum)
        return true;
    else
        return false;
}
}

please help me out i am open to any idea you suggest.

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

There is a good article about pagination in Apex here: https://developer.salesforce.com/page/Paginating_Data_for_Force.com_Applications that lists various options.

You have two options for pagination inside of Visualforce pages, OFFSET pagination, and StandardSetController pagination. With OFFSET pagination you are limited to 2,000 records; however, with a StandardSetController you can paginate up to 10,000. So, one thing you could do would be to refactor to use the StandardSetController pagination.

Another thing that you could do, would be to add some sort of filtering capability, so that the user can get to the records they need. For example, if you would return more than 2,000 records, display a message saying something like "Showing 2,000 records but there could be more. Please use a filter to narrow down the results."

share|improve this answer
    
what if it i need to do to display more than 10000 records. –  Anu Jul 9 at 16:20
    
Filtering is a good idea. What human could grok 10,000 or more records? And if a computer is consuming the data, why use VisualForce? Sounds like this is one platform limitation that really does protect developers from awful requirements, and gives us a good cop out. –  John Gaughan Jul 9 at 19:07
    
I don't want all the 10 000 records on the VF page at once. If the user have 30 000 records in his object. Then what will be the solution for that. If we display more 50 records per page that is total 200 page or 100 records per page that is 100 page this if the user need to move to 101 page than what ? sorry if i am wrong. just want to clear why 10 000 limit on pagination. Is there any method to improve 10 000. I know it is silly but crazy to know more do anybody have any new idea. That's it. –  Anu Jul 10 at 11:14
    
The 10,00 limit is imposed by the platform. I suppose you could do something where you mimic offset yourself programmatically, but the actual StandardSetController is limited to 10K. –  Peter Knolle Jul 10 at 14:08
    
Is it not possible to update the list on the Vf page used for pagination. When i changed the current value and hit next button i am getting an error that Modified rows exist in the records collection!. –  Anu Jul 10 at 15:44
add comment

I would look into using StandardSetController, see the docs here. This does not make it unlimited however, but it ups the limit from 2,00 to 10,000, which might help. In general, I try to make filters on the page to allow the end user to filter down the size of retruned results.

Here are a few blog posts about using StandardSetController for pagination. Hope that helps.

http://forceguru.blogspot.com/2011/04/pagination-in-salesforce.html

http://hisrinu.wordpress.com/2012/01/09/pagination-using-standardsetcontroller/

share|improve this answer
    
what if it i need to do to display more than 10000 records. –  Anu Jul 9 at 16:28
    
I guess I would have to ask what the use case is where a user would need to page through 10,000 records in the UI at one time? Seems like a reasonable limit. I would push back as to why you couldn't filter it further than that, and if you can't, I can't imagine why you would need all the records in the UI at once? –  sfdc_ninja Jul 9 at 20:33
    
I don't want all the 10 000 records on the VF page at once. If the user have 30 000 records in his object. Then what will be the solution for that. If we display more 50 records per page that is total 200 page or 100 records per page that is 100 page this if the user need to move to 101 page than what ? sorry if i am wrong. just want to clear why 10 000 limit on pagination. Is there any method to improve 10 000. I know it is silly but crazy to know more do anybody have any new idea. That's it. –  Anu Jul 10 at 11:13
    
I think you sre stuck to come up with your own solution if you want to move beyond 10,000. The platform has this as a limit. I think it is because if you need more than 10k records loaded into the ViewState of a VF page, then I don't think a VF page is the proper solution, as I can't think of a use case where you would need to cycle through that many records in the UI. –  sfdc_ninja Jul 10 at 14:13
    
Is it not possible to update the list on the Vf page used for pagination. When i changed the current value and hit next button i am getting an error that Modified rows exist in the records collection!. –  Anu Jul 10 at 15:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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