Sorting, Pagination in Visualforce using DataTables JQuery plug-in.

Standard

datatables in vf

What is this DataTables.net?
DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table.

My Attempt
I did little bit of dotnet programming in my early days. I remember using ado.net controls for binding data to a list on an aspx page with just drag and drop. It was exciting to be able to pass SQL statements to controls directly in design time.

I always wondered if Salesforce would introduce similar VF components. For good or bad, that didn’t happen. However, Salesforce did a better thing by introducing VF components. Encouraging developers to create their our own VF components and reuse them just like you do in dotnet or java world.

Here is my attempt to create a VF component, which can take SOQL query, List of fields and Headers in design time.

I tried keep the code simple, you may tweak it as needed.

Here is the code…enjoy..!

VF Page

<apex:page docType="html-4.01-strict">
    <c:quickTableComp cContainerId="testtable"
        columnFields="Id, Name, AccountNumber"
        columnTitles="Record ID, Account Name, Account Number"
        soql="select Id, Name, AccountNumber from Account"    
    />
</apex:page>

VF Component

<apex:component controller="quickTableHandler">
    <apex:attribute name="cContainerId" type="String" required="true"  description="Id of the container which will hold & display the table" assignTo="{!cContainerId}"  />
    <apex:attribute name="soql" assignTo="{!soql}" type="String" description="SOQL query to execute and populate records." />
    <apex:attribute name="columnFields" assignTo="{!columnFields}" type="String" description="Comma-separated list of fields to display." />
    <apex:attribute name="columnTitles" assignTo="{!columnTitles}" type="String" description="Comma-separated list of column titles." />

    <link rel="stylesheet" type="text/css" href="http://cdn.datatables.net/1.10.0/css/jquery.dataTables.css"/>
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
    <script type="text/javascript" src="http://cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>
    
    <script type="text/javascript">
            $(document).ready( function() {
                $('#{!cContainerId}').dataTable();
            } );
    </script>        

    <div class="full_width">
                
        <table cellpadding="0" cellspacing="0" border="0" class="display" id="{!cContainerId}" style="width:980px">
            <thead>
                <apex:outputPanel layout="none">
                    <tr>
                        <apex:repeat value="{!columnTitlesList}" var="t">
                            <th>
                                <apex:outputText value="{!t}" />
                            </th>                   
                        </apex:repeat>
                    </tr>
                </apex:outputPanel>
            </thead>
            <tbody>
                <apex:repeat value="{!records}" var="r">
                    <tr>
                        <apex:repeat value="{!columnFieldsList}" var="f">
                            <td>
                                <apex:outputField value="{!r[f]}" />
                            </td>
                        </apex:repeat>
                    </tr>
                </apex:repeat>
            </tbody>
        </table>
    </div>    

</apex:component>

Apex Class

public with sharing class quickTableHandler {

    public String columnFields { get; set; }
    public String soql { get; set; }
    public String columnTitles { get; set; }

    public List<String> columnFieldsList {
        get {
            if(columnFieldsList == null && columnFields != null) {
                columnFieldsList = columnFields.split(',');
                for(Integer x = 0; x < columnFieldsList.size(); x++) {
                    columnFieldsList[x] = columnFieldsList[x].trim();
                }
            }
            return columnFieldsList;
        }
        set;
    }
    
    public List<String> columnTitlesList {
        get {
            if(columnTitlesList == null && columnTitles != null) {
                columnTitlesList = columnTitles.split(',');
                for(Integer x = 0; x < columnTitlesList.size(); x++) {
                    columnTitlesList[x] = columnTitlesList[x].trim();
                }
            }
            return columnTitlesList;
        }
        set;
    }

    public List<sObject> records {
        get {
            if(records == null && soql != null) {
                records = Database.query(soql);
            }
            return records;
        }
        set;
    }
}
Advertisements

15 thoughts on “Sorting, Pagination in Visualforce using DataTables JQuery plug-in.

  1. This is definitely a useful component for displaying small quantities of data and allowing for client-side search on all fields – very simply written and easy to use. However, if you’re interested in displaying more than 1000 records in a page (unless your page is in read-only mode), I’d leverage JavaScript Remoting to pull in the data for the table, rather than Visualforce – otherwise you’ll hit the 1000 record collection size limit.

  2. Madryt

    I would like to thnkx for the efforts you have put in writing this web site. I am hoping the same high-grade site post from you in the upcoming also. In fact your creative writing skills has inspired me to get my own web site now. Really the blogging is spreading its wings quickly. Your write up is a great example of it.

  3. Akash

    Yoafzal, i hope you read this, i tried your piece of code, and i noticed the datatables built pages open well in FFox & Chrome. On IE8 the only the list of records appear without the table or anything. I need to be able to implement datatables in salesforce on IE8.

    In case you know any other source from where i can get IE8-supported-datatables-in-Salesforce code, please do tell

  4. @Akash IE8 should not be an issue. I would suggest you do the following:

    1. Please visit http://datatables.net from your IE8 browser
    2. Scroll down to see if the example table is working properly.

    If example works, then its not a browser issue.

    Unfortuntely I do not have any IE8 supported version of datatables. Feel free to reach out to http://datatables.net forum or support.

    Afzal

  5. Gaurish

    I tried this code. The data is fetched successfully but the datatable is not loaded, no search or sort feature is there. Please help what I am missing in this code.

    • @Gaurish, I’ve updated this blog post. It now uses the latest version of datatables plugin.Hopefully this should get you going.

      Hope that helps!

      Afzal

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s