Code Samples – Using DataTables.js with Visualforce via Standard and Custom Controllers

Using jQuery plugins in your Visualforce pages can enable you to easily sort and filter data tables. There are a few jQuery plugins which offer you this functionality and this guide discusses them as well as why they are easier to work with than leveraging the standard Visualforce components with additional code in your controllers.

Visualforce and data tables

As per the Visualforce Basics trailhead page, outputting records in a table can add a lot of impact to a page.

Although the above example is relatively elementary, more complicated and even more impactful tables can be created and the Visualforce Development Cookbook is a fantastic source for finding such examples.

However, the standard Visualforce components limit a data table in that the filtering and sorting isn’t available as a default. To have these features, your controller will need some additional code. This is an excellent link that demonstrates how to achieve this.

The downside of this is that it’s more complicated and crucially, slower. This is because, as per this very helpful blog post, the sorting / filtering functionality in the aforementioned manner will always be delivered server side (Apex code is server, JavaScript is client) and not client side: ‘sorting the table in the client is orders of magnitude faster’.

Visualforce and JQuery plugin options

So, a more ideal option for a data table in a Visualforce page would be to have sorting and filtering functionality as default and implemented on the client side. This can be achieved via a jQuery plugin that is referenced in a Visualforce page. jQuery is a ‘fast, small, and feature-rich JavaScript library’. Any page that is accessible from a web browser can reference JavaScript, which in turn can reference JavaScript libraries, such as jQuery. So in short, our Visualforce page can reference a jQuery plugin which can be used to sort and filter a data table faster than using Apex logic in the controller. Some beneficial links on how to use JavaScript with Visualforce pages can be found here, here and here.

Tablesorter.js is one such plugin that can be leveraged. This guide is helpful and a short video on how impactful it is can be seen here.

However, I was intrigued by this fantastic Dreamforce video, which goes into comprehensive detail about how to use another jQuery plugin called DataTables.js. I found DataTables.js very easy to use and it is very customisable. Using these examples, you can start to see the number of different options that are available to you, whether you want a certain column to be wider, whether you want scrolling and pagination and how many number of records you would like to be displayed per page.

Putting it all together – Visualforce, Apex and DataTables.js

To demonstrate how to use these three technologies together, we will be creating a Visualforce page that displays data about the 60 Indian Premier League matches that were played in 2016.

Inserting the data

I got this data from Kaggle, which is a platform for predictive modelling / analytics competitions and where one can find an abundant array of different data sets.

If you would like to get this data yourself, download the .zip folder from here and open the ‘matches’ file and filter on all matches played in 2016.

Now in your development environment, create a custom object called ‘IPL Matches’. Ensure that you have checked ‘Deployed’ as the Deployment Status option for this custom object. Next, create the corresponding fields from the columns in the excel file. Then, after converting the file to .csv format, batch insert the data into this newly created custom object via the Apex Data Loader, or you can use Workbench or another ETL tool such as Jitterbit.

The Standard Controller

A very good starting point for learning about standard controllers is this trailhead module from the Visualforce Basics trail.

“Visualforce uses the traditional model–view–controller (MVC) paradigm, and includes sophisticated built-in controllers to handle standard actions and data access, providing simple and tight integration with the Force.com database. These built-in controllers are referred to generally as standard controllers, or even the standard controller”.

This means that in order for data from Salesforce to display on a Visualforce page, a controller is required. A controller is an Apex class which is referenced in a Visualforce page. And it’s this controller which gives the Visualforce page the ability to display Salesforce data on the page. The controller ‘gets’ data from Salesforce and ‘sets’ it on the Visualforce page.

The difference between custom and standard controllers is that a standard controller already provides the Create, Read, Update, Delete (CRUD) operations. And because the vast majority of standard and custom objects have standard controllers already that can be used to interact with the data associated with that object, you don’t need to write the code for the controller yourself. This is explained very clearly in this link from the Visualforce Developer Guide.

  <apex:page standardController="Account" recordSetVar="accounts" tabstyle="account" sidebar="false">

  <apex:pageBlock >

    <apex:pageBlockTable value="{!accounts}" var="a">

      <apex:column value="{!a.name}"/>

    </apex:pageBlockTable>

  </apex:pageBlock>

</apex:page>

The line ‘standardController=”Account” is all that is needed to reference the Visualforce page with the Account standard object. No Apex code is required here. This is very helpful as it means we can leverage DataTables.js and Visualforce in a test or production environment without writing any Apex code.  So let’s attempt this with our custom object that you just create: IPL_Matches__c.

Creating the Visualforce page with the Standard Controller


<apex:page standardcontroller="IPL_Matches__c" recordSetVar="iplMatches" >

    <head>

        <apex:includescript value="//code.jquery.com/jquery-1.11.1.min.js" / >

            <apex:includescript value="//cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js" />

            <apex:stylesheet value="//cdn.datatables.net/1.10.4/css/jquery.dataTables.css" />

            <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%3E%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20j%24%20%3D%20jQuery.noConflict()%3B%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20j%24(document).ready(%20function%20()%20%7B%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20var%20iplMatches%20%3D%20j%24('%5Bid%24%3D%22iplMatches%22%5D').DataTable(%7B%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20%22order%22%3A%20%5B%5D%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20%7D)%3B%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20%7D)%3B%0A%0A%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%C2%A0%20%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />

        </head>

        <apex:form>

            <apex:pageBlock title="IPL Matches - 2017">

                <apex:pageBlockTable value="{!iplMatches}" var="i" id="iplMatches" >

                    <apex:column value="{!i.Date__c}"/>

                    <apex:column value="{!i.Home_Team__c}"/>

                    <apex:column value="{!i.Away_Team__c}"/>

                    <apex:column value="{!i.Best_player__c}"/>

                    <apex:column value="{!i.Date__c}"/>

                    <apex:column value="{!i.Season__c}"/>

                    <apex:column value="{!i.Toss_Decision__c}"/>

                    <apex:column value="{!i.Toss_Winner__c}"/>

                    <apex:column value="{!i.Umpire_1__c}"/>

                    <apex:column value="{!i.Umpire_2__c}"/>

                    <apex:column value="{!i.Venue__c}"/>

                    <apex:column value="{!i.Win_by_runs__c}"/>

                    <apex:column value="{!i.Win_by_wickets__c}"/>

                    <apex:column value="{!i.Winner__c}"/>

                </apex:pageBlockTable>

            </apex:pageBlock>

        </apex:form>

    </apex:page>

There are a few key things to point out here. Firstly, we are referencing jQuery and dataTables.js via the external Content Delivery Network method (CDN) method. An alternative, that I think is generally considered to be best practice, is to download jQuery and DataTables.js and upload them as a static resource which is then referenced in the Visualforce page. This is known also as the Salesforce CDN. The difference between the 2 methods is explained in detail here. For the purpose of simplicity, we’ll use the external CDN method for now.

Now, navigate to https://[yourserver].visual.force.com/apex/iplmatches, where [yourserver] is the Salesforce server that your environment is hosted on. In my case, it is https://c.eu6.visual.force.com/apex/iplmatches. You can find your server in the URL. If you have a doubt about the server performance, you can look at Salesforce Trust here.

We can see a two key features here: pagination, sorting and filtering.

We can see that the filtering is extremely fast and responsive. When I input ‘del’ only those records which contain ‘del’ are returned. We can also see that if you click on a column, the entire table is sorted straight away. Lastly, the pagination is available and a dropdown in the top left is editable.

However, we input 60 records into our IPL_Matches__c object and only 20 records are being displayed.

As per the trailhead link, the standard controller by default will only show the first 20 records. In order to control the number of records displayed on each page, you can use a controller extension to set the pageSize.

The Extension Controller

So far, we have covered Standard Controllers. So what is the difference between a Standard and an Extension controller? On this link, the author Peter Chittum, explains what extensions are really clearly:

“I tend to be a bit picky with my terminology here. Controllers are controllers. Extensions are not controllers. And for me, the difference is that extensions cannot live by themselves on a page. In other words, I’m not permitted to use extensions, unless my page has a controller in the first place.

Another distinguishing feature is that a page is allowed only one controller ever. But I can add as many extensions as I need.”

An extension is really just a slight modification to an existing standard or custom controller. Since we want to keep the functionality of the standard controller, we can simply extend it using the Apex code below:


public with sharing class ext1 {

ApexPages.StandardSetController controller;   

public ext1(ApexPages.StandardSetController controller) {

        this.controller = controller;        

    controller.setPageSize(controller.getResultSize());

    }

}

 

And in our standard controller, we would change the top line to

<apex:page standardcontroller="IPL_Matches__c" recordSetVar="iplMatches" extensions="ext1">

You may have noticed that the extension uses ‘StandardSetController’. We are using a StandardSetController and not a StandardController because the latter is for just 1 record, whereas we wish to return a number of records. This link here from Salesforce Stackexchange helps to point out the differences.

Having created the extension and referenced it in our standard controller, we can now refresh our Visualforce page whereby we see that 60 records have been returned over a number of pages, depending on how many records we wish to show in the top right hand dropdown:

The DataTables.js options

What if we want to have all of the records displayed on a single page, instead of having pagination? Looking at the documentation, we would change line 10 in the standard controller form “order”: [] to “paging”: false.

There are so many options that you can leverage in DataTables.js. You can make some columns be wider than others, you can have scrolling, multi-column ordering, multiple tables and you can even get far more complicated by using the DataTables.js API.

Overall, DataTables.js is easy to use and works very fast in a Visualforce page. It can add a lot more impact to your data tables.

Subscribe To The Monthly Newsletter

No Spam. No Rubbish. Just great content from the Salesforce Industry.

You have Successfully Subscribed!

2 thoughts on “Code Samples – Using DataTables.js with Visualforce via Standard and Custom Controllers

  1. This sounds great and I will soon build a POC using this. Before I start one question: Can Ligthning components not do that?

Add Comment