1

I'm trying to build a page which shows a list of records in Salesforce database. There can be scenarios where there can be more than 30k or 50k or even more records.

I'm currently implementing pagination with OFFSET and LIMIT keyword. However OFFSET has a limit of 2000 and hence I cant go to the next page after reaching this limit. I tried StandardSetController too however the maximum records that it can be instantiated with is limited to 10k. Tried Visualforce remoting too with SOQL FOR loop for building a list and using it to populate a table with AngularJS ng-repeat. However, I'm getting CPU time limit exceeded error.

Is there a way by which I can accomplish the display of 50k+ records in VF page?

Below is my code

Visualforce Page

<apex:page standardcontroller="CREmailReport__c" 
      extensions="EmailRecipientsPagination" showHeader="false" 
       sidebar="false" readOnly="true" cache="false">


  <apex:pageBlock >   
  <apex:pageBlockButtons location="top" >
   <apex:outputPanel id="myButtons">
    <apex:form >
    <apex:commandButton action="{!Beginning}" title="Beginning" value="<<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
    <apex:commandButton action="{!Previous}" title="Previous" value="<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>        
    <apex:commandButton action="{!Next}" title="Next" value=">" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
    <apex:commandButton action="{!End}" title="End" value=">>" disabled="{!disableNext}" reRender="myPanel,myButtons"/>        
   </apex:form>
  </apex:outputPanel>
   </apex:pageBlockButtons>

    <apex:pageBlockSection title="Email Recipients List (Total List Size: 
     {!total_size})" collapsible="false">
     <apex:outputPanel id="myPanel">
      <apex:pageMessages id="theMessages" />
       <apex:pageBlockTable value="{!numbers}" var="n" align="center">
    <apex:column value="{!n.CR_Id__c}" />
    <apex:column value="{!n.Email__c}" />
    <apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}
    </apex:facet>
    </apex:pageBlockTable>
      </apex:outputPanel>
       </apex:pageBlockSection>

   </apex:pageBlock>

   </apex:page>

Controller Extension

public with sharing class EmailRecipientsPagination {
public EmailRecipientsPagination() {

}

  private integer counter=0;  //keeps track of the offset
  private integer list_size=20; //sets the page size or number of rows
  public integer total_size; //used to show user the total size of the list
  public String reportId;
  public EmailRecipientsPagination(ApexPages.StandardController 
 standardController) {

  total_size = [select count() from CR_Email_Receiver__c];
 //set the total size in the constructor
   reportId = standardController.getRecord().Id;
  }

  public CR_Email_Receiver__c[] getNumbers() {
    try {
     CR_Email_Receiver__c[] numbers =[SELECT Id, CR_Id__c, Email__c, 
      CR_Group_Id__c 
               FROM CR_Email_Receiver__c WHERE Email_Report__c = :reportId 
      LIMIT :list_size OFFSET :counter];

     return numbers;
      } 
     catch (QueryException e) {
     ApexPages.addMessages(e);   
     return null;
     }
 }

  public PageReference Beginning() { //user clicked beginning
   counter = 0;
   return null;
  }

  public PageReference Previous() { //user clicked previous button
   counter -= list_size;
   return null;
   }

   public PageReference Next() { //user clicked next button
    counter += list_size;
     return null;
   }

   public PageReference End() { //user clicked end
    counter = total_size - math.mod(total_size, list_size);
   return null;
  }

   public Boolean getDisablePrevious() { 
    //this will disable the previous and beginning buttons
     if (counter>0) return false; else return true;
   }

    public Boolean getDisableNext() { //this will disable the next and end 
     buttons
      if (counter + list_size < total_size) return false; else return true;
      }

    public Integer getTotal_size() {
      return total_size;
    }

     public Integer getPageNumber() {
      return counter/list_size + 1;
    }

    public Integer getTotalPages() {
      if (math.mod(total_size, list_size) > 0) {
        return total_size/list_size + 1;
      } else {
        return (total_size/list_size);
     }
   }

}
  • 1
    With a page size of 20, is someone really going to sit there and go through 2,500 pages? I can think of a couple of ways to work around the limitations you've found, but needing a workaround is generally a strong indication that you should stop for a moment, and consider why you need a workaround. What information are your users trying to find? Is displaying thousands upon thousands of records the best way to surface that information? – Derek F Nov 5 '17 at 12:24
  • @DerekF. I'm displaying the recipients to which an email is sent from a mass mailing system. The table is built with jquery data tables so that users can sort and search for a specific recipient/email in the table. By this way users can have a 360 degree view of all the recipients by opening up a email record detail page – Vishnu J Menon Nov 9 '17 at 15:50