Client-side sorting and pagination of an apex:pageBlockTable – more on sorting

OK so things looked a little too good to be true in Client-side sorting and pagination of an apex:pageBlockTable and I’ve had to do a bit more work to get the sorting working for datetime, date and currency columns. The problem is of course that the client-side JavaScript only has the formatted data available not the underlying (typed) data, and many formats are possible.

Below is my first cut at handling datetime, date and currency columns for the “English (United States)” locale. To reduce configuration, the type of a column is automatically detected rather than needing to be configured.

var j$ = jQuery.noConflict();

var sorting = {
    
    // Determine the format the column values are in
    typeDetect: function (a) {
        if (a) {
            if (a.match(/\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{1,2} (AM|PM)/)) {
                return 'sf-datetime';
            } else if (a.match(/\d{1,2}\/\d{1,2}\/\d{4}/)) {
                return 'sf-date';
            } else if (a.match(/\(?\\$[\d\,\.]+\)?/)) {
                return 'sf-currency';
            }
        }
        return null;
    },
    
    // e.g. '8/17/2012 6:37 AM'
    datetimePre: function (a) {
        var b = a.match(/(\d{1,2})\/(\d{1,2})\/(\d{4}) (\d{1,2}):(\d{1,2}) (AM|PM)/);
        if (b && b.length == 7) {
            var month = b[1], day = b[2], year = b[3], hour = b[4], min = b[5], ap = b[6];
            if (hour == '12') hour = '0';
            if (ap == 'PM') hour = parseInt(hour, 10) + 12;
            if (month.length == 1) month = '0' + month;
            if (day.length == 1) day = '0' + day;
            if (hour.length == 1) hour = '0' + hour;
            if (min.length == 1) min = '0' + min;
            return year + month + day + hour + min;
        } else {
            return '';
        }
    },
    
    // e.g. '8/17/2012'
    datePre: function (a) {
        var b = a.match(/(\d{1,2})\/(\d{1,2})\/(\d{4})/);
        if (b && b.length == 4) {
            var month = b[1], day = b[2], year = b[3];
            if (month.length == 1) month = '0' + month;
            if (day.length == 1) day = '0' + day;
            return year + month + day;
        } else {
            return '';
        }
    },
    
    // e.g. '$1,019.05' or '($500.00)'
    currencyPre: function (a) {
        var b = a.match(/(\()?(\\$)([\d\,\.]+)(\))?/);
        if (b && b.length == 5) {
            return parseFloat((b[1] == '(' ? '-' : '') + b[3].replace(/\,/g, ''));
        } else {
            return 0;
        }
    },
    
    asc: function (a, b) {
       return a - b;
    },
    
    desc: function (a, b) {
       return b - a;
    }
}

// Register extra type detection
j$.fn.dataTableExt.aTypes.unshift(sorting.typeDetect);

// Register extra sorting functions
j$.extend(j$.fn.dataTableExt.oSort, {
    
    'sf-datetime-pre': sorting.datetimePre,
    'sf-datetime-asc': sorting.asc,
    'sf-datetime-desc': sorting.desc,
   
    'sf-date-pre': sorting.datePre,
    'sf-date-asc': sorting.asc,
    'sf-date-desc': sorting.desc,
   
    'sf-currency-pre': sorting.currencyPre,
    'sf-currency-asc': sorting.asc,
    'sf-currency-desc': sorting.desc
});

Winter ’14 – no more code statement limit

Having bumped up against the 200,000 code statement governor limit in some valid cases, it’s good to see that this safety mechanism is changing (presumably) for the better:

There is no more code statement limit. Although we’ve removed this limit, we haven’t removed the safety mechanism it provided. Instead we’ve limited the CPU time for transactions. The limits are now 10,000ms for synchronous Apex and 60,000ms for asynchronous Apex.

(From Winter ’14 Developer Preview; see the clarifying comments too.)

Edit: Also see Script Limits, Begone!

Client-side sorting and pagination of an apex:pageBlockTable

I’ve been using the jQuery tablesorter plugin for some time now in Visualforce pages and it has worked well. But recently a requirement came up to display multiple tables in a single Visualforce page and for each table to support (client-side) pagination. A quick try of the tablesorter pager plugin confirmed that it didn’t handle such a case well and so I followed the advice of moving to the jQuery DataTables plugin instead. The conversion only took a couple of hours.

Here is what I learned in the form of a contrived example. It produces this output:

DataTables

Step 1 is to create a zip file that has folders called “js”, “images” and “css” with those components copied from the “media” folder of the DataTables distribution. You should also include one of the license files. This is then uploaded as a static resource, in this example called “jQueryDataTablesZip”.

Step 2 is the controller Apex:

public with sharing class DemoController {
    public Contact[] getContacts() {
        return [
                select FirstName, LastName, Birthdate, Email, LastModifiedDate, OwnerId
                from Contact
                order by Name
                limit 500
                ];
    }
}

Step 3 is the Visualforce page:

<apex:page controller="DemoController">

<apex:stylesheet value="{!URLFOR($Resource.jQueryDataTablesZip, 'css/jquery.dataTables.css')}"/>
<style type="text/css">
.sorting {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_both.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_asc {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_asc.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_desc {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_desc.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_asc_disabled {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_asc_disabled.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
.sorting_desc_disabled {
    background: #f2f3f3 url('{! URLFOR($Resource.jQueryDataTablesZip, 'images/sort_desc_disabled.png') }') no-repeat center right !important;
    padding-right: 20px !important;
}
table.dataTable tr.odd { background-color: white; }
table.dataTable tr.even { background-color: white; }
table.dataTable tr.odd td.sorting_1 { background-color: white; }
table.dataTable tr.odd td.sorting_2 { background-color: white; }
table.dataTable tr.odd td.sorting_3 { background-color: white; }
table.dataTable tr.even td.sorting_1 { background-color: white; }
table.dataTable tr.even td.sorting_2 { background-color: white; }
table.dataTable tr.even td.sorting_3 { background-color: white; }
.dataTables_length, .dataTables_filter, .dataTables_info, .dataTables_paginate {
    padding: 3px;
}
</style>

<apex:sectionHeader title="Data Tables Demo"/>
<apex:pageBlock >
    <apex:pageBlockSection columns="1">
        <apex:pageBlockTable value="{!contacts}" var="c" styleClass="dataTable">
            <apex:column value="{!c.FirstName}"/>
            <apex:column value="{!c.LastName}"/>
            <apex:column value="{!c.Birthdate}"/>
            <apex:column value="{!c.Email}"/>
            <apex:column value="{!c.LastModifiedDate}"/>
            <apex:column value="{!c.OwnerId}"/>
        </apex:pageBlockTable>
    </apex:pageBlockSection>
</apex:pageBlock>

<script type="text/javascript" language="javascript" src="{!URLFOR($Resource.jQueryDataTablesZip, 'js/jquery.js')}"></script>
<script type="text/javascript" language="javascript" src="{!URLFOR($Resource.jQueryDataTablesZip, 'js/jquery.dataTables.js')}"></script>
<script type="text/javascript" language="javascript">
var j$ = jQuery.noConflict();
j$('table.dataTable').dataTable({
    sPaginationType: "full_numbers"
});
</script>

</apex:page>

Most of the work is fixing up the image URLs to reference the zip-based resources and tweaking the styling. Note that DataTable supports many other configuration options and looks pretty open for extension.

In summary, with next to no work you get:

  • sortable columns
  • pagination
  • search (filtering)

and no nasty surprises (at least so far for me) when your page becomes more complicated.