Display more than 3000 records in a VF page

Question

I have a requirement to display around 3000 records in a table on VF page. There might be around 10-15 columns on the table. I also need to perform update operations on the records. It is a list of wrappers(i.e., sObject + chkbox), which have to be displayed Let me know if we can achieve it through pageblocktable or if in any other case, please provide sample code.

Answer 1

It's difficult to give a full code. Below is the format which I'm using in one of my pages working fine. Get the idea and try your own.
VF Page

<apex:pageBlock id="resultBlock" mode="maindetail">                         
    <apex:outputPanel >                             <apex:pageMessages />

         <!-- Page navigation -->
         <div align="left" style="padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px;">         
              <font size="1pt">Page #:&nbsp;
                   <apex:outputLabel value="{!pageNumber}"/>&nbsp;out of&nbsp;
                   <apex:outputLabel value="{!totalPages}"/>&nbsp; &nbsp; &nbsp; &nbsp;
              </font>                                                                                        
              <apex:commandButton action="{!Beginning}" title="Beginning" value="<<" disabled="{!disablePrevious}" reRender="resultBlock" />
              <apex:commandButton action="{!Previous}" title="Previous" value="<" disabled="{!disablePrevious}" reRender="resultBlock" />        
              <apex:commandButton action="{!Next}" title="Next" value=">" disabled="{!disableNext}" reRender="resultBlock" />
              <apex:commandButton action="{!End}" title="End" value=">>" disabled="{!disableNext}" reRender="resultBlock" />                                                              
         </div>       

  </apex:outputPanel>  

  <apex:outputPanel id="resultsPanel" rendered="{!isResultTableVisible}">
    <apex:pageBlockTable id="resultsTbl" value="{!scheduleListUI}" var="item" cellspacing="1px" >
        <!-- Your Columns here -->
    </apex:pageBlockTable>
    </apex:outputPanel>
</apex:pageBlock>

Controller

public with sharing class Controller{

    private integer offset; // Keeps track of the offset     
    private integer pageSize;   // Page size or number of rows           
    public integer totalRecords;    // Total number of records   
    public Boolean isResultTableVisible{get;set;} //use this to control the visibility of your pageBlockTable
    //define your wrapper list as well here.

    public Controller(){
        isResultTableVisible = false;   
        offset = 0;
        pageSize = 10;
        totalRecords = 0;
    }

    public void generateYourTableContentList(){
        // Here use the offset variable to filter your wrapper list
    }

    /**
    * User clicked in beginning
    *   Set the offset to zero so that 1st record onwards will be retrieved
    */ 
    public PageReference Beginning() {
        offset = 0;
        //refresh your 
        return null;
    }


   /**
    * User clicked previous button
    *   reduce offset by one time of page size
    */ 
    public PageReference Previous() {
        offset -= pageSize;  
        generateScheduleListUI();      
        return null;
    }


   /**
    * User clicked next button
    *   increase offset by one time of page size
    */ 
    public PageReference Next() {
        offset += pageSize;   
        generateScheduleListUI();   
        return null;
    }


   /**
    * User clicked end
    *   retrive the final records that can be displayed in the defined page size
    */ 
    public PageReference End() {

        integer modValue = math.mod(totalRecords, pageSize);                
        offset = totalRecords - math.mod(totalRecords, pageSize);

        if((modValue == 0) && (totalRecords > pageSize)) {
           offset = (totalRecords - pageSize);
        }
        generateScheduleListUI();
        return null;
    }

    /**
    * This will disable the next and end buttons
    */ 
    public Boolean getDisableNext() {
        return (offset + pageSize < totalRecords)? false: true;
    }

    /**
    * This will disable the previous and beginning buttons
    */ 
    public Boolean getDisablePrevious() {
        return (offset>0)? false: true;
    }

    /**
    * Get current page number 
    */ 
    public Integer getPageNumber() {
        return offset/pageSize + 1;
    }

    /**
    * Get total number of records pages 
    */ 
    public Integer getTotalPages() {
        if (math.mod(totalRecords, pageSize) > 0) {
            return totalRecords/pageSize + 1;
        } else {
            return (totalRecords/pageSize);
        }
    } 
}

Answer 2

NOTE: Please keep in mind using offset only supports querying up to 2000 records. As I cannot access Blogs at work please check out this person's way of dealing more than 2000 records. http://stevefouracre.blogspot.co.uk/2011/08/multiple-object-pajination-on-1-vf-page.html

I will be updating this post when I get home to highlight his approach on this post in case the article goes down.

I did this a while back and answered a similar question back in 2012 but using APEX. https://developer.salesforce.com/forums/ForumsMain?id=906F000000092EHIAY#ext-gen28

This will be able to handle multiple pagination queries.

Best advice I can give is make a service class that does the logic for you and you only have to setup once. You are currently doubling your code by having 2 methods because you want 2 paginated set of sobjects...

Here's an example of what I have done in the past when I needed pagination.

Here's the up to date generic version

/**
 * This is a basic pagination service class for the any Sobject.
 * Constructor requires 3 input values.
 * @Input String soqlString - Query related to what you want to pull back.
 * @Input Integer pageSize - Number of records to be returned at a time. 
 * @Input String SobjectName - Sobject API Name
 *
 * @auther Alex Acosta
 **/
public with sharing class CustomSetController {

private String soqlQuery        { get; set; }
private Integer recordCount     { get; set; }
private Boolean hasNext         { get; set; }
private Boolean hasPrevious     { get; set; }
private Integer offset          { get; set; }
private List<Sobject> records   { get; set; }
private Integer pageSize        { get; set; }
private Integer pageNumber      { get; set; }
private String sObjectName      { get; set; }

public CustomSetController(String soqlString, Integer pageSize, String sObjectName){
    this.soqlQuery = soqlString;
    this.sObjectName = sObjectName;
    recordCount = 0;
    if(null == pageSize || 0 == pageSize) 
        pageSize = 5;
    this.pageSize = pageSize;       
    offset = 0;
    pageNumber = 1;
    hasNext = false;
    hasPrevious = false;
    records = new List<Sobject>();
    try{
        getRecordCount();
    }catch(QueryException e){
        // no records found
        recordCount = 0;
    }
}

public Integer getRecordCount(){
    String soqlString = 'SELECT count() ' + this.soqlQuery.subString(this.soqlQuery.lastIndexOfIgnoreCase('from ' + sObjectName));
    if(soqlString.toLowerCase().contains('order by'))
        soqlString = soqlString.substring(0, soqlString.ToLowerCase().indexOf('order by'));
    try{
        recordCount = Database.countQuery(soqlString + ' LIMIT 50000');
    }catch(Exception e){
        recordCount = 0;
    }

    return recordCount;
}

public String getQueryString(){ return this.soqlQuery; }

public Integer getPageSize(){ return this.pageSize; }

public List<Sobject> getRecords(){
    if(recordCount == 0) return new List<Sobject>();

    String soqlString = this.soqlQuery + ' LIMIT ' + pageSize;
    if(offset > 0)
        soqlString = soqlString + ' OFFSET ' + offset;          
    records = Database.query(soqlString);

    return records;
}

public Boolean getHasNext(){
    if(offset + records.size() >= recordCount)  return false;
    return true;
}

public Boolean getHasPrevious(){
    if(offset == 0) return false;
    return true;
}

public List<Sobject> getNext(){
    offset = offset + pageSize;
    if(pageNumber < getTotalPages())
        pageNumber++;
    return getRecords();
}

public List<Sobject> getPrevious(){
    offset = offset - pageSize;
    if(pageNumber > 1)
        pageNumber--;
    return getRecords();
}

public Integer getCurrentPage(){
    return pageNumber;  
}

public Integer getTotalPages(){
    return (math.mod(recordCount, pageSize) > 0) ? Integer.valueOf(Math.floor(recordCount/pageSize)) + 1 : recordCount/pageSize;
}

public List<Sobject> getFirstPage(){
    pageNumber = 1;
    offset = 0;
    return getRecords();
}

public List<Sobject> getLastPage(){
    offset = (getTotalPages()-1) * pageSize;
    pageNumber = getTotalPages();
    return getRecords();
}
}

To control multiple pagination on a single page you would do something like this in your VF controller... Make a variable for each instance, and their own method. This way if anything ever needs to change from the root code, you only have to change it in one place.

public List<Task> notes  { get; set; }
public TaskSetController theNotesController  { get; set; }

public void resetNoteController(){
    theNotesController = new CustomSetController ('SELECT CreatedBy.Name, CreatedDate, Description, Id FROM Task WHERE WhoId = \'' + account.PersonContactId + '\' ORDER BY CreatedDate DESC', 10, 'Task');
    notes = theNotesController.getRecords();
}

public void getNextSetOfNotes(){
    if(getNotesHasNext())
        notes = theNotesController.getNext();
}

public Boolean getNotesHasNext(){
    return theNotesController.getHasNext();
}

public void getPreviousSetOfNotes(){
    if(getNotesHasPrevious())
        notes = theNotesController.getPrevious();
}

public Boolean getNotesHasPrevious(){
    return theNotesController.getHasPrevious();
}

public Integer getTaskRecordCount(){
    return theNotesController.getRecordCount();
}

public Integer getTaskTotalPages(){
    return 0 == theNotesController.getTotalPages() ? 1 : theNotesController.getTotalPages();
}

public Integer getTaskCurrentPage(){
    return theNotesController.getCurrentPage();
}

public void getFirstPageOfNotes(){
    notes = theNotesController.getFirstPage();
}

public void getLastPageOfNotes(){
    notes = theNotesController.getLastPage();
}

Visualforce Page would look as such per Instance of the Pagination Class. You can see when a note is posted the command button will refresh that section using a rerender.

    <apex:pageBlock >
        <apex:pageBlockSection id="theNotes" columns="1"> 
            <apex:panelGrid title="Notes" onclick="showNotes();" styleClass="subSection" >
                <apex:outputText style="color:white;" value="{!IF(displayNotes,'▼','►')} Notes"/>
            </apex:panelGrid>
            <apex:PageBlockSection rendered="{!displayNotes}" columns="2" id="notesSection">
                <apex:panelGroup >
                    <apex:inputTextarea value="{!theNote}" style="width: 80%;height: 150px; display:block; margin: 0 auto;" />
                    <apex:commandButton action="{!createNote}" value="Add Note" style="float:right;margin:15px 9% 0 0;" rerender="notesSection"/>                       
                </apex:panelGroup>

                <apex:panelGroup >
                    <label></label>
                    <apex:dataTable value="{!notes}" var="n" rowClasses="odd,even" id="notesTable" styleClass="tableClass" width="100%" border="1px" cellpadding="10px">
                        <apex:column width="70%">
                            <apex:facet name="header">Notes</apex:facet>
                            <apex:outputText value="{!n.Description}"/>
                        </apex:column>
                        <apex:column >
                            <apex:facet name="header">Created By</apex:facet>
                            <apex:outputText value="{!n.CreatedBy.Name} on {!n.CreatedDate}"/>
                        </apex:column>
                    </apex:dataTable>

                    <apex:panelGroup style="width:100%;text-align:center;display:block;margin:5px 0 0 0;">
                        <apex:outputText value="Page: {!taskCurrentPage} of {!taskTotalPages}" style="float:left !important;" />

                        <apex:outputText value="◄◄" rendered="{!IF(taskCurrentPage != 1, false, true)}" style="margin: 0 10px 0 0;color:#A8A8A8" />
                        <apex:commandLink action="{!getFirstPageOfNotes}" value="◄◄" rendered="{!IF(taskCurrentPage == 1, false, true)}" rerender="notesSection" style="margin: 0 10px 0 0;text-decoration:none;" />
                        <apex:outputText value="◄ Previous" rendered="{!NOT(notesHasPrevious)}" style="margin: 0 5px 0 0;color:#A8A8A8" />
                        <apex:commandLink action="{!getPreviousSetOfNotes}" value="◄ Previous" rendered="{!notesHasPrevious}" rerender="notesSection" style="margin: 0 5px 0 0;text-decoration:none;" />

                        <apex:commandLink action="{!getNextSetOfNotes}" value="Next ►" rendered="{!notesHasNext}" rerender="notesSection" style="margin: 0 0 0 5px;text-decoration:none;" />
                        <apex:outputText value="Next ►" rendered="{!NOT(notesHasNext)}" style="margin: 0 0 0 5px;color:#A8A8A8" />
                        <apex:outputText value="►►" rendered="{!IF(taskCurrentPage >= taskTotalPages, true, false)}" style="margin: 0 0 0 10px;color:#A8A8A8" />
                        <apex:commandLink action="{!getLastPageOfNotes}" value="►►" rendered="{!IF(taskCurrentPage < taskTotalPages, true, false)}" rerender="notesSection" style="margin: 0 0 0 10px;text-decoration:none;" />

                        <apex:outputText value="Total Records: {!taskRecordCount}" style="float:right !important;" />
                    </apex:panelGroup>
                </apex:panelGroup>
            </apex:PageBlockSection>
        </apex:pageBlockSection>
    </apex:pageBlock>

Answer 3

You can achieve this requirement by Pagination if you want to use PageBlock table because no body can see 3000 record at a time,

http://forceguru.blogspot.in/2011/04/pagination-in-salesforce.html http://www.infallibletechie.com/2013/01/pagination-with-filter-criteria-using.html

If you want to 3000 record on page then you need to create your own table and apply the standard pageblocktable css on your table but this method may give exception of view state.

--

Answer 4

How you present your data is a design/usability decision: perhaps using the browser's scrolling is a good way to go for your particular use case. Or perhaps pagination or search would be better.

I suggest you try it to find out, but an apex:pageblocktable showing all the data may run you into a view state size problem. A colleague has recently had to refactor to use raw HTML (with the CSS classes copied from the pages Salesforce generates) inside an apex:repeat instead because every row output added to the view state (and the view state size has an upper limit that his page exceeded.) But that work-around isn't too hard to do.

If you are going to paginate, go for a solution that makes use of SOQL's OFFSET keyword.

Answer 5

You can actually display 3000 records on a page if you need. The below code may not be exact and may need minor changes.

Controller:

public class MultiExample
{
  public List<MultiWrapper> wraps{get; set;}

  public MultiExample()
  {
    wraps = new List<MultiWrapper>();
    wraps.add(wrap);
    MultiWrapper wrap = new MultiWrapper();
    for (Account acc: [SELECT Id FROM Account LIMIT 3000])
    {
      if (wrap.size() == 1000)
      {
        wrap = new MultiWrapper();
        wraps.add(wrap);
      }
      wrap.addAcc(acc);
    }
  }

  public class MultiWrapper
  {
    public List<Account> accs{get; set;}

    public MultiWrapper()
    {
      accs = new List<Account>();
    }

    public void addAcc(Account acc)
    {
      accs.add(acc);
    }
  }
}

VF Page:

<apex:repeat value="{!wraps}" var="wrap">
  <apex:pageBlockTable value="{!wrap.accs}" var="{!acc}">
    <apex:column>
      <apex:outputField value="{!acc.Id}" />
    </apex:column>
  </apex:pageBlockTable>
</apex:repeat>

The downside is that it will create a new table every 1000 records, but they should be right on top of each other so it shouldn't be a big deal.