Making one DataTable respond to search/length/page changes in another DataTable

DataTables can be applied to multiple tables in a page and sometimes the content in one table needs to be driven by the content in another table. For example, checkboxes in the first table on a page might act as a filter on later tables in a page. (The setup of such filtering is not covered here; the mechanism that can be used is custom filtering.)

But DataTables also supports a search mechanism that reduces the table rows to ones that match and a pagination mechanism where the number of rows shown or the page shown can be changed. By default, changes to those values in the first table will not cause the later tables to be re-filtered and re-drawn. So if you want the later tables to only correspond to the checked checkboxes that are visible in the first table, extra code has to be added.

The good news is that DataTables does generate events for the changes and so provides a convenient point to hook in extra code. The only complication is that it appears necessary to defer until after the current event processing has been completed by using setTimeout.

So assuming the tables are distinguished by classes firstMarker and laterMarker, this code will get the later tables re-drawn (and so re-filtered) to be consistent with the first table:

(function($) {
    $(document).ready(function() {

        var firstTable = $('table.firstMarker');
        firstTable.DataTable();

        var laterTables = $('table.laterMarker');
        var laterDataTables = [];
        laterTables.each(function() {
            laterDataTables.push($(this).DataTable());
        });

        var drawLaterDataTables = function() {
            setTimeout(function() {
                $.each(laterDataTables, function(index, value) {
                    value.draw();
                });
            }, 0);
        };
        firstTable.on('search.dt', drawLaterDataTables);
        firstTable.on('length.dt', drawLaterDataTables);
        firstTable.on('page.dt', drawLaterDataTables);

        // Filtering logic not shown here
    });
})(jQuery.noConflict());

Cool data tables using @RestResource, AngularJS and trNgGrid

I have an AngularJS application that shows tables of data using:

  • an Apex class that does dynamic SOQL and populates instances of a simple Apex class that are serialised to the client as JSON via the @RestResource annotation
  • the client side is AngularJS that pretty much just passes the JSON data through to a page template
  • the presentation work is all done by the excellent trNgGrid component and Bootstrap styling

The Apex code is clean and simple:

@RestResource(urlMapping='/report')
global without sharing class ReportRest {
    global class Claim {
        public String employeeName;
        public String department;
        public String reportsTo;
        public String claimNumber;
        public String status;
        public String leaveType;
        public Date startDate;
        public Date endDate;
        Claim(SObject c) {
            SObject e = c.getSObject('Employee__r');
            employeeName = (String) e.get('Name');
            department = (String) e.get('Department');
            SObject r = e.getSObject('ReportsTo');
            reportsTo = r != null ? (String) r.get('Name') : null;     
            claimNumber = (String) c.get('Name');
            status = (String) c.get('Status__c');
            leaveType = (String) c.get('LeaveType__c');
            startDate = (Date) c.get('StartDate__c');
            endDate = (Date) c.get('EndDate__c');
        }
    }
    @HttpGet  
    global static Claim[] get() {
        Claim[] claims = new Claim[] {};
        String soql = ...;
        for (SObject sob : Database.query(soql)) {
            claims.add(new Claim(sob));
        }
        return claims;
    }
}

and the trNgGrid markup is even more impressive:

<table tr-ng-grid="tr-ng-grid" class="table table-condensed" items="items"
      order-by="orderBy" order-by-reverse="orderByReverse">
  <thead>
    <tr>
      <th field-name="employeeName"/>
      <th field-name="department"/>
      <th field-name="reportsTo"/>
      <th field-name="claimNumber"/>
      <th field-name="status"/>
      <th field-name="leaveType"/>
      <th field-name="startDate" display-format="longDate" display-align="right"/>
      <th field-name="endDate" display-format="longDate" display-align="right"/>
    </tr>
  </thead>
</table>

You just define the column headers and trNgGrid generates the rows from the JSON data array (called “items” here). The resulting table has column sorting and column searching and other features can be enabled too. As it is written in AngularJS, it leverages AngularJS features such as filters (“longDate” here) for custom formatting.

What is great about this arrangement is that there is no tedious coding involved: all the code serves a purpose and the grunt work is handled by the frameworks. It also scores high on “ease of modification”: an extra column only takes a few minutes to add.

(Contrast this with the JavaScript required in e.g. Connecting DataTables to JSON generated by Apex.)

Here is a screen shot from the real application (with different columns):

TrNgGrid

Connecting DataTables to JSON generated by Apex

I had a quick go at coming up with an answer to this DataTables sAjaxSource question on Salesforce Stackexchange but didn’t get very far. Using DataTable’s sAjaxSource directly runs you into the problem that the Visualforce page containing the table and the Apex REST API that supplies the JSON use different host names and so the same origin security policy blocks the access. (The answer includes one way to work-around this.)

The approach I tried was to use Apex’s @RemoteAction mechanism, where a JavaScript function is generated in the page. So instead of referencing a URL, the JavaScript function is called and how it connects back to the server is its concern. The trick to getting it to work is to recognise that the JavaScript function that you add (via the fnServerData property) is intended to provide an interception point for the request to the URL you specify (via the sAjaxSource property). So both must be specified, even though in this case the URL is not used.

This an example of the output (using default styles):

dt

Here is the Visualforce page; most of the JavaScript is just cleaning up the JSON data returned from the controller to suit DataTables:

<apex:page controller="DataTableController">

<link
rel="stylesheet"
type="text/css"
href="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css"
/>

<apex:sectionHeader title="DataTables"/>

<table id="table" cellpadding="0" cellspacing="0" border="0">
    <thead>
        <th>Name</th>
        <th>Birthdate</th>
        <th>Phone</th>
        <th>Email</th>
        <th>Salary</th>
    </thead>
    <tbody>
    </tbody>
</table>

<script
type="text/javascript"
charset="utf8"
src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"
>
</script>
<script
type="text/javascript"
charset="utf8"
src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"
>
</script>
<script>

var j$ = jQuery.noConflict();

var fields = ['Name', 'Birthdate', 'Phone', 'Email', 'Salary__c'];

var aoColumns = [];
for (var i = 0; i < fields.length; i++) {
    aoColumns.push({'mData': fields[i]});
}

j$(document).ready(function() {
    j$('#table').dataTable({
        'aoColumns': aoColumns,
        'bProcessing': true,
        'bServerSide': true,
        'bFilter': false,
        'sAjaxSource': 'fakeUrl',
        'fnServerData': function(sSource, aoData, fnCallback) {
            console.log(JSON.stringify(aoData));
            // Call the @RemoteAction JavaScript function
            DataTableController.contacts(aoData, function(result, event) {
                if (event.type != 'exception') {
                    console.log(JSON.stringify(result));
                    for (var i = 0; i < result.aaData.length; i++) {
                        var r = result.aaData[i];
                        for (var j = 0; j < fields.length; j++) {
                            var field = fields[j];
                            if (r[field] == undefined) {
                                // DataTables pops a dialog for undefined values
                                r[field] = null;
                            } else if (field == 'Birthdate') {
                                // Dates transmitted as longs
                                var d = new Date(r[field]);
                                r[field] = ''
                                        + (d.getMonth() + 1)
                                        + '/'
                                        + d.getDate()
                                        + '/'
                                        + d.getFullYear()
                                        ;
                            }
                        }
                    }
                    // Call back into the DataTable function
                    fnCallback(result);
                } else {
                    alert(event.message);
                }
            });
        }
    });
});

</script>

</apex:page>

Most of the complexity in the Apex code is in interpreting the request parameters sent by DataTables including things like the multi-column sorting. Note that the conversion from JSON to Apex objects and Apex objects to JSON is left to the platform.

// See https://datatables.net/usage/server-side
global class DataTableController {

    // Defines shape of JSON response
    global class Response {
        public Integer sEcho;
        public Integer iTotalRecords;
        public Integer iTotalDisplayRecords;
        public SObject[] aaData;
        Response(Integer echo, Integer total, SObject[] sobs) {
            this.sEcho = echo;
            this.iTotalRecords = total;
            this.iTotalDisplayRecords = total;
            this.aaData = sobs;
        }
    }

    // DataTable passes JSON definition of what server should do
    private class Params {
    
        Map<String, Object> m = new Map<String, Object>();
        
        Integer echo;
        Integer start;
        Integer length;
        String[] columns;
        Integer[] sortColumns;
        String[] sortDirections;
        
        Params(List<Map<String, Object>> request) {
            for (Map<String, Object> r : request) {
                m.put((String) r.get('name'), r.get('value'));
            }
            echo = integer('sEcho');
            start = integer('iDisplayStart');
            length = integer('iDisplayLength');
            columns = stringArray('mDataProp');
            sortColumns = integerArray('iSortCol');
            sortDirections = stringArray('sSortDir');
        }
        
        String[] stringArray(String prefix) {
            String[] strings = new String[] {};
            for (Object o : array(prefix)) {
                strings.add(o != null ? esc(String.valueOf(o)) :null);
            }
            return strings;
        }
        
        Integer[] integerArray(String prefix) {
            Integer[] integers = new Integer[] {};
            for (Object o : array(prefix)) {
                integers.add(o != null ? Integer.valueOf(o) : null);
            }
            return integers;
        }

        Object[] array(String prefix) {
            Object[] objects = new Object[] {};
            for (Integer i = 0; true; i++) {
                Object o = m.get(prefix + '_' + i);
                if (o != null) {
                    objects.add(o);
                } else {
                    break;
                }
            }
            return objects;
        }
        
        Integer integer(String name) {
           Object o = m.get(name);
           if (o instanceof Decimal) {
               return ((Decimal) o).intValue();
           } else if (o instanceof Integer) {
               return (Integer) o;
           } else {
               return null;
           }
        }
        
        // Guard against SOQL injection
        String esc(String s) {
            return s != null ? String.escapeSingleQuotes(s) : null;
        }
    }
    
    @RemoteAction
    global static Response contacts(List<Map<String, Object>> request) {
    
        Params p = new Params(request);

        String soql = ''
                + ' select ' + String.join(p.columns, ', ')
                + ' from Contact'
                + ' order by ' + String.join(orderBys(p), ', ')
                + ' limit :length'
                + ' offset :start'
                ;
        System.debug('>>> soql=' + soql);

        Integer start = p.start;
        Integer length = p.length;
        return new Response(
                p.echo,
                [select Count() from Contact limit 40000],
                Database.query(soql)
                );
    }
    
    private static String[] orderBys(Params p) {
        Map<String, String> soqlDirections = new Map<String, String>{
                'asc' => 'asc nulls last',
                'desc' => 'desc nulls first'
                };
        String[] orderBys = new String[] {};
        Integer min = Math.min(p.sortColumns.size(), p.sortDirections.size());
        for (Integer i = 0; i < min; i++) {
            orderBys.add(''
                    + p.columns[p.sortColumns[i]]
                    + ' '
                    + soqlDirections.get(p.sortDirections[i])
                    );
        }
        return orderBys;
    }
}

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
});

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.