DataTables for SalesForce (DT2Apex and DataTableController)

If you've ever read any posts on my blog - or spent longer than about 5 minutes talking to me for that matter - you'll know that I love DataTables. You'll also probably know that we at work use SalesForce a lot. I've used DataTables quite a bit in Visualforce pages to display data and such-like as well as in projects outside of work and within work but not using SalesForce but I've never been able to figure out a way of displaying either Standard or Custom objects using them... until last week that is.

There are a couple of steps involved in getting DataTables to work within SalesForce, I bow to Keith Clarke's Connecting DataTables to JSON generated by Apex and Daniel's DATATABLES IN VISUALFORCE, PART 1 to give me a place from where to get started.

DataTables Server-side processing rocks in the PHP applications I've written but there's something of a learning curve as the server-side script needs to be able to read the JSON that DataTables is going to send to it and it also needs to send back information in the correct JSON format in order for it to be displayed. Both of these formats are described here.

In order for SalesForce to understand the JSON being sent to it I made use of the really rather marvellous JSON2Apex to generate this class:

//
// Generated by JSON2Apex http://json2apex.herokuapp.com/
//
// The supplied json has fields with names that are reserved words in apex
// and so can only be parsed with explicitly generated code, this option
// was auto selected for you.

public class DT2Apex {
    public static void consumeObject(JSONParser parser) {
        Integer depth = 0;
        do {
            JSONToken curr = parser.getCurrentToken();
            if (curr == JSONToken.START_OBJECT || 
                curr == JSONToken.START_ARRAY) {
                depth++;
            } else if (curr == JSONToken.END_OBJECT ||
                curr == JSONToken.END_ARRAY) {
                depth--;
            }
        } while (depth > 0 && parser.nextToken() != null);
    }

    public class Order {
        public Integer column {get;set;} 
        public String dir {get;set;} 

        public Order(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'column') {
                            column = parser.getIntegerValue();
                        } else if (text == 'dir') {
                            dir = parser.getText();
                        } else {
                            System.debug(LoggingLevel.WARN, 'Order consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    public class Search_Z {
        public String value {get;set;} 
        public Boolean regex {get;set;} 

        public Search_Z(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'value') {
                            value = parser.getText();
                        } else if (text == 'regex') {
                            regex = parser.getBooleanValue();
                        } else {
                            System.debug(LoggingLevel.WARN, 'Search_Z consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    public Integer draw {get;set;} 
    public List<Columns> columns {get;set;} 
    public List<Order> order {get;set;} 
    public Integer start {get;set;} 
    public Integer length {get;set;} 
    public Search_Z search_Z {get;set;} // in json: search
    public String object_Z {get;set;} // in json: object

    public DT2Apex(JSONParser parser) {
        while (parser.nextToken() != JSONToken.END_OBJECT) {
            if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                String text = parser.getText();
                if (parser.nextToken() != JSONToken.VALUE_NULL) {
                    if (text == 'draw') {
                        draw = parser.getIntegerValue();
                    } else if (text == 'columns') {
                        columns = new List<Columns>();
                        while (parser.nextToken() != JSONToken.END_ARRAY) {
                            columns.add(new Columns(parser));
                        }
                    } else if (text == 'order') {
                        order = new List<Order>();
                        while (parser.nextToken() != JSONToken.END_ARRAY) {
                            order.add(new Order(parser));
                        }
                    } else if (text == 'start') {
                        start = parser.getIntegerValue();
                    } else if (text == 'length') {
                        length = parser.getIntegerValue();
                    } else if (text == 'search') {
                        search_Z = new Search_Z(parser);
                    } else if (text == 'object') {
                        object_Z = parser.getText();
                    } else {
                        System.debug(LoggingLevel.WARN, 'Root consuming unrecognized property: '+text);
                        consumeObject(parser);
                    }
                }
            }
        }
    }
    
    public class Columns {
        public String data {get;set;} 
        public String name {get;set;} 
        public Boolean searchable {get;set;} 
        public Boolean orderable {get;set;} 
        public Search_Z search_Z {get;set;} // in json: search

        public Columns(JSONParser parser) {
            while (parser.nextToken() != JSONToken.END_OBJECT) {
                if (parser.getCurrentToken() == JSONToken.FIELD_NAME) {
                    String text = parser.getText();
                    if (parser.nextToken() != JSONToken.VALUE_NULL) {
                        if (text == 'data') {
                            data = parser.getText();
                        } else if (text == 'name') {
                            name = parser.getText();
                        } else if (text == 'searchable') {
                            searchable = parser.getBooleanValue();
                        } else if (text == 'orderable') {
                            orderable = parser.getBooleanValue();
                        } else if (text == 'search') {
                            search_Z = new Search_Z(parser);
                        } else {
                            System.debug(LoggingLevel.WARN, 'Columns consuming unrecognized property: '+text);
                            consumeObject(parser);
                        }
                    }
                }
            }
        }
    }
    
    
    public static DT2Apex parse(String json) {
        return new DT2Apex(System.JSON.createParser(json));
    }
}

With this I was laughing! This is the corresponding test class:

//
// Generated by JSON2Apex http://json2apex.herokuapp.com/
//

@IsTest
public class DT2Apex_Test {
    
    // This test method should give 100% coverage
    static testMethod void testParse() {
        String json = '{'+
        '    \"draw\": 1,'+
        '    \"columns\": ['+
        '        {'+
        '            \"data\": \"Name\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Street__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Post_Town__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Postcode__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"Address__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        },'+
        '        {'+
        '            \"data\": \"UPRN__c\",'+
        '            \"name\": \"\",'+
        '            \"searchable\": true,'+
        '            \"orderable\": true,'+
        '            \"search\": {'+
        '                \"value\": \"\",'+
        '                \"regex\": false'+
        '            }'+
        '        }'+
        '    ],'+
        '    \"order\": ['+
        '        {'+
        '            \"column\": 0,'+
        '            \"dir\": \"asc\"'+
        '        }'+
        '    ],'+
        '    \"start\": 0,'+
        '    \"length\": 10,'+
        '    \"search\": {'+
        '        \"value\": \"\",'+
        '        \"regex\": false'+
        '    },'+
        '    \"object\": \"Location__c\"'+
        '}';
        DT2Apex r = DT2Apex.parse(json);
        System.assert(r != null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Order objOrder = new DT2Apex.Order(System.JSON.createParser(json));
        System.assert(objOrder != null);
        System.assert(objOrder.column == null);
        System.assert(objOrder.dir == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Search_Z objSearch_Z = new DT2Apex.Search_Z(System.JSON.createParser(json));
        System.assert(objSearch_Z != null);
        System.assert(objSearch_Z.value == null);
        System.assert(objSearch_Z.regex == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex objRoot = new DT2Apex(System.JSON.createParser(json));
        System.assert(objRoot != null);
        System.assert(objRoot.draw == null);
        System.assert(objRoot.columns == null);
        System.assert(objRoot.order == null);
        System.assert(objRoot.start == null);
        System.assert(objRoot.length == null);
        System.assert(objRoot.search_Z == null);
        System.assert(objRoot.object_Z == null);

        json = '{\"TestAMissingObject\": { \"TestAMissingArray\": [ { \"TestAMissingProperty\": \"Some Value\" } ] } }';
        DT2Apex.Columns objColumns = new DT2Apex.Columns(System.JSON.createParser(json));
        System.assert(objColumns != null);
        System.assert(objColumns.data == null);
        System.assert(objColumns.name == null);
        System.assert(objColumns.searchable == null);
        System.assert(objColumns.orderable == null);
        System.assert(objColumns.search_Z == null);
    }
}

Keith Clarke used an older version of DataTables so I didn't need to provide a fake sAjaxSource but other than that my client-side script was quite similar to his. I basically pass the ajax option a function like this:

"processing": true,
"serverSide": true,
"ajax": function (data, callback, settings) {
    data.object = "<YOUR OBJECT NAME HERE>";
    DataTableController.getdata(
        JSON.stringify(data), 
        function(result, event) {
            if (event.type != 'exception') {
                callback(result);
            } else {
                alert(event.message);
            }
        }
    )
}

This means that my Controller (DataTableController) needs to grab the functionality of DT2Apex in order to understand what DataTables wants. It sort of knows the structure of the data it needs to return so that DataTables can understand it and display it properly (Response). This is DataTableController:

global class DataTableController {
    // https://force201.wordpress.com/2014/03/15/connecting-datatables-to-json-generated-by-apex/
    @RemoteAction
    @ReadOnly
    global static Response getData(String request) {
        // Use our nice parser from http://json2apex.herokuapp.com/
        DT2Apex parsedRequest = DT2Apex.parse(request);
        // Get the total number of records
        Integer recordsTotal = Database.countQuery('SELECT COUNT() FROM ' + parsedRequest.object_Z);
        // Get our field names
        List<String> fieldNames = new List<String>();
        for (DT2Apex.Columns c : parsedRequest.columns) {
            fieldNames.add(c.data);
        }
        // Sort out our ordering using field names
        List<String> orderBy = new List<String>();
        for (DT2Apex.Order o : parsedRequest.order) {
            orderBy.add(fieldNames.get(o.column) + ' ' + o.dir);
        }
        if(parsedRequest.search_Z.value != ''){
            List<String> stringField = new List<String>();
            String type=parsedRequest.object_Z;
            Map<String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
            Schema.SObjectType leadSchema = schemaMap.get(type);
            Map<String, Schema.SObjectField> fieldMap = leadSchema.getDescribe().fields.getMap();
            for (String fieldName: fieldMap.keySet()) {
                //It provides to get the object fields label.
                String fieldLabel = fieldMap.get(fieldName).getDescribe().getLabel();
                //It provides to get the object fields data type.
                Schema.DisplayType fielddataType = fieldMap.get(fieldName).getDescribe().getType();
                if(fielddataType == Schema.DisplayType.String){
                    stringField.add(fieldName);
                }
            }
            List<String> whereClause = new List<String>();
            for (String f : stringField) {
                whereClause.add(f + ' LIKE \'%' + parsedRequest.search_Z.value + '%\'');
            }
            // Get the total number of records
            Integer recordsFiltered = Database.countQuery('SELECT COUNT() FROM ' + parsedRequest.object_Z + ' WHERE ' + String.join(whereClause, ' OR '));
            // Our query with WHERE:
            String soql = ' SELECT ' + String.join(fieldNames, ', ')
                    + ' FROM ' + parsedRequest.object_Z
                    + ' WHERE ' + String.join(whereClause, ' OR ')
                    + ' ORDER BY ' + String.join(orderBy, ', ')
                    + ' LIMIT ' + parsedRequest.length
                    + ' OFFSET ' + parsedRequest.start;
            return new Response(
                parsedRequest.draw, 
                recordsTotal,
                recordsFiltered,
                Database.query(soql)
            );
        }else{
            // Our boring query:
            String soql = ' SELECT ' + String.join(fieldNames, ', ')
                + ' FROM ' + parsedRequest.object_Z
                + ' ORDER BY ' + String.join(orderBy, ', ')
                + ' LIMIT ' + parsedRequest.length
                + ' OFFSET ' + parsedRequest.start;
            // Our response!
            return new Response(
                parsedRequest.draw, 
                recordsTotal,
                recordsTotal,
                Database.query(soql)
            );
        }
    }
    global class Response {
        public Integer draw;
        public Integer recordsTotal;
        public Integer recordsFiltered;
        public SObject[] data;
        Response(Integer draw, Integer total, Integer totalQuery, SObject[] sobs) {
            this.draw = draw;
            this.recordsTotal = total;
            this.recordsFiltered = totalQuery;
            this.data = sobs;
        }
    }
}

It makes use of @ReadOnly in order to be able to query the COUNT(*) of objects with more than 50,000 records and, so long as we don't play silly-bugger's and allow the user to display more than too many records, we should be fine!

The nice thing about it is we can define our own Object (<YOUR OBJECT NAME HERE>) so the same class can be used to generate tables for Apples and Oranges.

All the hard work is done in the client-side script in terms of asking for the data; we're just using a single class to produce data for any number of objects. A working example is this:

<apex:page controller="DataTableController">
    <head>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jqc-1.11.3,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,b-1.0.3,b-flash-1.0.3,b-html5-1.0.3,b-print-1.0.3/datatables.min.css"/>
    </head>
    <body>
        <table id="contactsTable" class="display"></table>
        <script type="text/javascript" src="https://cdn.datatables.net/r/dt/jqc-1.11.3,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,b-1.0.3,b-flash-1.0.3,b-html5-1.0.3,b-print-1.0.3/datatables.min.js"></script>
        <script>
            j$ = jQuery.noConflict();
            j$(document).ready( function () {
                var locationTable = j$('[id$="contactsTable"]').DataTable({
                    "dom": "Blfrtip",
                    "buttons": [
                        "copy", "csv", "pdf"
                    ],
                    "columns":[
                        {
                            "title":"ID",
                            "data": "Id",
                            "visible": false
                        },{
                            "title":"Name",
                            "data": "Name",
                            "render": function(data, type, row, meta){
                                return j$("<a></a>",{
                                    "text":data,
                                    "href": "../"+ row.Id
                                }).prop('outerHTML')
                            }
                        },{
                            "title":"Department",
                            "data":"Department",
                            "defaultContent":""
                        },{
                            "title":"Owner",
                            "data":"Owner.Name"
                        }
                    ],
                    "processing": true,
                    "serverSide": true,
                    "filter": true,
                    "ajax": function (data, callback, settings) {
                        data.object = "Contact";
                        DataTableController.getData(
                            JSON.stringify(data), 
                            function(result, event) {
                                if (event.type != 'exception') {
                                    callback(result);
                                } else {
                                    alert(event.message);
                                }
                            }
                        )
                    }
                });
            });
        </script>
    </body>
</apex:page>

This makes use of the new CDN for DataTables and also includes the new buttons code - which so far seems to work a treat - it also gives nice added extras so you can export the data as a PDF or CSV or copy all visible rows to the clipboard. Altogether this rocks!

I've been doing something with locations objects with Easting and northing so I've used Chris Veness's Geodesy functions within a columns render function to make a link to Google Maps with the correct Latitude and Longitude.

My next job is to try and write a test class for DataTableController, I've spent a wee bit of time trying to figure out how to write one but I seem to be stumped - any ideas would be most welcome.

Semantic UI age Validation

We're doing lots with Semantic UI forms and I'm sort of loving and hating their form validation... more loving than hating after discovering the ability to add validation rules!

We need to check ages before submitting a form and, while we check on the server-side, it's always nice to check on the client-side as well! We've implemented a DateTimePicker and restricted the selectable dates within it but the user can still enter an invalid date manually. To validate this using Semantic UI I wrote this simple Validation RUle and added it to $.fn.form.settings.rules:

$.fn.form.settings.rules.age = function(value, age) {
    if(moment(value, "DD/MM/YYYY").isBefore(moment().subtract(parseInt(age, 10), "year"))){
        return true;
    }else{
        return false;
    }
}

Like most of the things I write these days it requires Moment.js and to use it simply add a rule like this to your form:

"Birthdate": {
    "identifier" : "Birthdate",
    "rules": [
        {
            "type"   : "age[18]",
            "prompt" : "You need to be at least 18"
        }
    ]
}

This will check to see if the date entered is older than 18 years. To change the number of years simply alter 18.

Datatables and Semantic UI

We're doing loads of work with Semantic UI at the minute at work and so I've spent some time this morning getting Datatables to work nicely with it. With the update of Datatables to 1.10.8 we can now access the numbers pagingType and this is just crying out for tweaking. In order to do this I added this function on the draw call back:

"pagingType": "numbers",
"drawCallback": function() {
    var tableA_paginate = $("#tableA_paginate");
    var page_numbers = tableA_paginate.find("span").children().clone(true);
    tableA_paginate.empty().append(page_numbers);
    tableA_paginate.addClass("right floated ui pagination menu");
    tableA_paginate.children().each(function(k, v){
        $(v).addClass("item");
        if($(v).hasClass("current")){
            $(v).addClass("active");
        }
        if($(v).hasClass("ellipsis")){
            $(v).addClass("disabled");
        }
    })
},

That left the length and search parts though so I got to thinking about grids within Semantic and I altered the dom to (actually, you should probably do this in order for the pagination above to look nice):

"dom": '<"ui grid form"<"four wide column"l><"eight wide column"><"four wide column"f>>t<"ui grid"<"eight wide column"i><"right floated eight wide column"p>>',

And then to turn the length and search parts of the Datatable into proper Semantic UI elements I added this initialization complete callback:

"initComplete": function(settings, json) {
    // Tidy up length select
    var tableA_length = $("#tableA_length");
    var select = tableA_length.find("select").clone(true);
    tableA_length.find("select").remove();
    select.attr("id", select.attr("name") + "_select").addClass("ui dropdown");
    tableA_length.find("label").attr("for", select.attr("name") + "_select");
    tableA_length.append(select);
    tableA_length.addClass("field");
    select.dropdown();
    // Tidy up search
    var tableA_filter = $("#tableA_filter");
    var input = tableA_filter.find("input").clone(true);
    tableA_filter.find("input").remove();
    input.attr("id", tableA_filter.attr("id") + "_input");
    tableA_filter.find("label").attr("for", tableA_filter.attr("id") + "_input");
    tableA_filter.append(input);
 },

A fully working example is on JSFiddle, with a couple of added extras that we needed for our implementation.

I know that there has been some discussion about implementing a Semantic UI theme but I've found this use of callbacks to work a treat... perhaps I should get my finger out and make a proper plug-in though...?

I think I'm correct in putting the pagination into a draw callback as this gets redrawn on each table draw and the other parts of the table don't seem to be so I've kept them in the initialization callback.