+ Start a Discussion
KevSnellKevSnell 

Pagination and Sort by Column Name Click

Hi All,

 

Has anyone been able to create a Visualforce Page that shows a table which has pagination (next and previous) and the ability to click a column name to sort? There seems to be lots of one or the other but not both at the same time.

 

I have created a table using the ApexPages.StandardSetController which has allowed me to do pagnination on the table but I can't seem to find a solution to sort by clicking the column name which works with the standardsetcontroller.

 

Any code examples or links to blogs that may help would be really appreciated.

Ankit AroraAnkit Arora

Bingo!! I think you are looking for this :

 

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

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

KevSnellKevSnell

That's great for pagnination and a similar approach to what I already have but how do you add the ability to sort by when clicking a column header name??

 

Thanks

Kev

KevSnellKevSnell

Hi all,

 

I have attempted this myself using code out there on the net and the forum and have come up with the following:

 

Could everyone look over my code and see if there are any flaws in it.  I'm new to Apex Coding so it has been a bit or trial and error as I don't fully understand it yet so can't tell if I made a mistake somewhere.

 

I have tested the code with the limited records I have in my org and the Visual Page seems to work.  Currently it is set with page size 4 for testing, but it allows you to sort by pressing a column header name and then go next or previous with the pages and seems to keep the ordering while going through the pages.

 

Thanks

Kev

 

Visual Force Page:

<apex:page controller="MyPagingController" tabStyle="Account">
        <apex:sectionHeader title="Accounts List with Paging"></apex:sectionHeader>
        <apex:form >
        <apex:pageBlock title="" id="pageBlock">
        <div align="right" style="display:{!IF(NOT(ISNULL(accounts)),'block','none')}">
        <font size="1pt">Page #:&nbsp;<apex:outputLabel value="{!PageNumber}"/>&nbsp;out of&nbsp;<apex:outputLabel value="{!totalPageNumber}"/>&nbsp;&nbsp;&nbsp;&nbsp;</font>
        <apex:commandButton value="Previous" action="{!previousBtnClick}" disabled="{!previousButtonEnabled}" reRender="pageBlock"></apex:commandButton>
        <apex:commandButton value="Next" action="{!nextBtnClick}" reRender="pageBlock" disabled="{!nextButtonDisabled}" ></apex:commandButton>
        </div>
        <br/><br/>
        <apex:pageBlockTable value="{!accounts}" var="a" rendered="{!NOT(ISNULL(accounts))}" rows="{!PageSize}">
        
       <apex:column >
         <apex:facet name="header">   
           <apex:commandLink action="{!ViewData}" value="Account Name{!IF(sortExpression=='name',IF(sortDirection='ASC','▼','▲'),'')}" id="cmdSort">
             <apex:param value="name" name="column" assignTo="{!sortExpression}" ></apex:param>
           </apex:commandLink>
         </apex:facet>
         <apex:outputLink value="/{!a.Id}" target="_blank">{!a.Name}</apex:outputLink>
       </apex:column>
       <apex:column value="{!a.Phone}">
         <apex:facet name="header">
           <apex:commandLink action="{!ViewData}" value="Phone{!IF(sortExpression=='Phone',IF(sortDirection='ASC','▼','▲'),'')}">
             <apex:param value="Phone" name="column" assignTo="{!sortExpression}" ></apex:param>
           </apex:commandLink>
         </apex:facet>
       </apex:column>
        <apex:column value="{!a.BillingCity}">
          <apex:facet name="header">
           <apex:commandLink action="{!ViewData}" value="Billing City{!IF(sortExpression=='BillingCity',IF(sortDirection='ASC','▼','▲'),'')}">
             <apex:param value="BillingCity" name="column" assignTo="{!sortExpression}" ></apex:param>
           </apex:commandLink>
         </apex:facet>
       </apex:column>
        <apex:column value="{!a.BillingCountry}">
          <apex:facet name="header">
           <apex:commandLink action="{!ViewData}" value="Billing Country{!IF(sortExpression=='BillingCountry',IF(sortDirection='ASC','▼','▲'),'')}">
             <apex:param value="BillingCountry" name="column" assignTo="{!sortExpression}" ></apex:param>
           </apex:commandLink>
         </apex:facet>
       </apex:column>
       
        </apex:pageBlockTable>
        <div align="right" style="display:{!IF(NOT(ISNULL(accounts)),'block','none')}">
        <br/>
        <font size="1pt">Page #:&nbsp;<apex:outputLabel value="{!PageNumber}"/>&nbsp;out of&nbsp;<apex:outputLabel value="{!totalPageNumber}"/>&nbsp;&nbsp;&nbsp;&nbsp;</font>
        <apex:commandButton value="Previous" action="{!previousBtnClick}" disabled="{!previousButtonEnabled}" reRender="pageBlock"></apex:commandButton>
        <apex:commandButton value="Next" action="{!nextBtnClick}" reRender="pageBlock" disabled="{!nextButtonDisabled}" ></apex:commandButton>
        </div>

        </apex:pageBlock>
        </apex:form>
    </apex:page>



Apex Class:

public class MyPagingController
    {
    private List<Account> accounts;
    private List<Account> pageAccounts;
    private String sortDirection = 'ASC';
    private String sortExp = 'name';
    private Integer pageNumber;
    private Integer pageSize;
    private Integer totalPageNumber;
    public Integer getPageNumber()
    {
    return pageNumber;
    }
    
    public String sortExpression
    {
        get 
        {
        return sortExp;
        } 
        set
        {
        if (value == sortExp)
        sortDirection = (sortDirection == 'ASC')? 'DESC' : 'ASC';
        else
        sortDirection = 'ASC';
        sortExp = value;
        }
    }

    public String getSortDirection()
    {
    if (sortExpression == null || sortExpression == '')
    return 'ASC';
    else
    return sortDirection;
    }
             
    public void setSortDirection(String value)
    {  
    sortDirection = value;
    }
    
    public List<Account> getAccounts()
    {
    return pageAccounts;
    }
    public Integer getPageSize()
    {
    return pageSize;
    }
    public Boolean getPreviousButtonEnabled()
    {
    return !(pageNumber > 1);
    }
    public Boolean getNextButtonDisabled()
    {
    if (accounts == null) return true;
    else
    return ((pageNumber * pageSize) >= accounts.size());
    }
    public Integer getTotalPageNumber()
    {
    if (totalPageNumber == 0 && accounts !=null)
    {
    totalPageNumber = accounts.size() / pageSize;
    Integer mod = accounts.size() - (totalPageNumber * pageSize);
    if (mod > 0)
    totalPageNumber++;
    }
    return totalPageNumber;
    }
    public MyPagingController()
    {
    pageNumber = 0;
    totalPageNumber = 0;
    pageSize = 4;
    ViewData();
    }
    public PageReference ViewData()
    {
    accounts = null;
    totalPageNumber = 0;
    BindData(1);
    return null;
    }
    private void BindData(Integer newPageIndex)
    {
    try
    {
    string sortFullExp = sortExpression  + ' ' + sortDirection;
    if (accounts == null)
    accounts = Database.query('Select id, Name, BillingCity, BillingCountry, Phone from Account order by ' + sortFullExp + ' limit 1000');
    pageAccounts = new List<Account>{};
    Transient Integer counter = 0;
    Transient Integer min = 0;
    Transient Integer max = 0;
    if (newPageIndex > pageNumber)
    {
    min = pageNumber * pageSize;
    max = newPageIndex * pageSize;
    }
    else
    {
    max = newPageIndex * pageSize;
    min = max - pageSize;

    }
    for(Account a : accounts)
    {
    counter++;
    if (counter > min && counter <= max)
    pageAccounts.add(a);
    }
    pageNumber = newPageIndex;
    if (pageAccounts == null || pageAccounts.size() <= 0)
    ApexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO,'Data not available for this view.'));
    }
    catch(Exception ex)
    {
    ApexPages.addmessage(new ApexPages.message(ApexPages.severity.FATAL,ex.getMessage()));
    }
    }
    public PageReference nextBtnClick() {
    BindData(pageNumber + 1);
    return null;
    }
    public PageReference previousBtnClick() {
    BindData(pageNumber - 1);
    return null;
    }
    }



 

GreenhornGreenhorn

Hi,

I tried the above code for opportunities instead of accounts. For less opportunities it is working fine. But it is giving me error "Maximum view state size limit(135KB) exceeded. Actual view state for this page was 279.04KB"  for near about 200 opportunities.   

KevSnellKevSnell

Hi,

 

I'm using  the code for smaller amounts of data so that is possibly why I haven't got this error.  Did a search I found this:

 

View state size issue occurs when the variables declared as public are storing data greater than 135KB. So it is better to declare these variables as transient.

 

Transient Keyword: Use the transient keyword to declare instance variables that can't be saved, and shouldn't be transmitted as part of the view state for a Visualforce page.

 

For more detail follow the below link :

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_classes_keywords_transient.htm

 

Looks like if you have the Development Mode Footer turned on and you also turn on the View State Tab for it you can check which variable it taking the kilobytes.  Then I guess you could set the variable as transient and it may fix the problem.


Please let me know how you get on.


Kev