jQuery dataTables Tricks

These are some basics and a few tricks for dealing with certain situations in the jQuery dataTables plugin. Information on the plugin can be found here: https://datatables.net/

Sorting By Date

With the necessary options installed for dataTables, such as the moment.js, dataTables should recognize a date column and automatically sort correctly when using the US mm/dd/yyyy format. However, occasionally dataTables fails to recognize the column as a date and, instead treats it is a simple string or we may using a different date format (e.g. dd/mm/yyyy). In cases where dataTables is not sorting the data correctly or, simply to ensure that it always sorts it correctly, and HTML5 data attribute, "data-order", can be added to the <td> column tag with the raw date/time string as follows:

<td data-order="#lastUpdated#">#dateFormat(lastUpdated, 'mm/dd/yyyy')#</td>

In the above example, "lastUpdated" is the raw date extracted from a SQL Server table column with the column data type of datetime or smalldatetime.

Using Excel or PDF Export with Server Side Processing

PDF and Excel exporting of data is limited to the data being displayed in the current dataTables view when using server side processing (processing = true; serverside = true). The reason for this is that these functions work with the data available to the dataTables instance at the time that they are invoked and, with server side processing, dataTables doesn't receives a partial total of all records in accordance with the pagination being used.

Moving the Buttons

Sometimes it may be desirable to move the buttons outside of the main table DOM area that are used for selecting and exporting data. This can be accomplished using the dataTable API:

var table = $('#searchResults').DataTable( {
	//your table definition w/o the buttons being specified, assuming a table ID of 'searchResults'
} );
//define buttons for dataTable and locate in dedicated area
var buttons = new $.fn.dataTable.Buttons(table, {
	dom: {
		container: {
			tag: 'div',
			className: ''
		}
	},
	buttons: 
		[ 'copy', 
		{
			extend: 'excelHtml5',
			exportOptions: {
				modifier: {
					selected: true
				}
			}
		}, 
		{
			extend: 'pdfHtml5',
			orientation: 'landscape',
			pageSize: 'LETTER',
			exportOptions: {
				modifier: {
					selected: true
				}
			}
		}, 'selectAll', 'selectNone']
}).container().appendTo($('#dataTableButtons'));

This method using instance initialization of the buttons to perform the following functions: define the buttons to be used, applying the desired styling to the buttons, and alter the styling of the wrapping div that is used as the button container.

By default, jQuery dataTable will wrap any buttons it creates in a div with two classes: dt-buttons and btn-group (if you are using the Bootstrap 3 add-on). If these class calls on the wrapping div interfere with your desired styling (e.g. you don't desire to wrap your buttons in the 'btn-group' class, you can call the dom container option as shown in the above example to control the classes that are assigned. the dom container accepts two parameters to control the element ("tag") and classes ("className") assigned to that element. If the default wrapping provided by dataTables for the buttons doesn't cause a problem, the the dom specification can be eliminated, simplifying the initialization of the buttons.

When defining your buttons, you use the same code that is used when you use the regular initialization of dataTables. The only difference here is that the buttons are defined as an instance of the Buttons extension and assign it to the dataTable instance. We then use the .container().append() function to insert the buttons into a dedicated div where we want the buttons located on the page (in our example, this is a div with an ID of 'dataTableButtons').

Moving Other Items (e.g. Search Field) Out of Table DOM

As with the buttons, items like the search filter, pagination, records per page, etc. that are traditionally shown at the top of bottom of the table can be relocated elsewhere using controls (form field elements) outside of the table DOM. This is useful if you desire to locate these features to a modal or side control panel area.

For example, to use your own form controls for the search and records per page, the following code can be used:

$('##tableSearchTextField').keyup(function(){
	table.search($(this).val()).draw() ;
});
$('##tablePageLength').on('change', function(){
	table.page.len($(this).val()).draw() ;
});

In the above example, the search field is being assigned to an input text field with an ID of "tableSearchTextField" and a select input type you've created with an ID of "tablePageLength" is being used for the page length (records per page) control. This is the associated HTML:

<input type="text" name="tableSearchTextField" id="tableSearchTextField" 
class="form-control" placeholder="Search within results"/>

<select name="tablePageLength" id="tablePageLength" class="form-control">
    <option value="25">25 results per page</option>
    <option value="50">50 results per page</option>
    <option value="100">100 results per page</option>
    <option value="200">200 results per page</option>
    <option value="-1">All results</option>
</select>

NOTE: If you're using your own controls in place of the dataTables controls, you may want to remove the native dataTables controls from being displayed (if they already are). To do this, edit (or add) the dom definition in your dataTables initialization code.

Adding Checkboxes

A couple of options exist for adding checkboxes to a dataTables rendering to allow the user to select/de-select the row with a checkbox in the header that can be used as a select/de-select all control. If using Bootstrap styling, a solution is available on Gyrocode.com that provides a javascript file, style sheet, and instructions on implementation.

For a go-it yourself route w/o the specific Bootstrap styling, you can create the necessary code to implement the checkboxes. The first step is to add the following column definition to your table initialization code:

columnDefs: [ {
    orderable: false,
    className: 'select-checkbox',
    targets:   0
} ]

So your table initialization code may look something like:

var table = $('##searchResults').DataTable( {
	pageLength: 25,
	lengthChange: true,
	dom: 	"<'row'<'col-sm-6'i><'col-sm-6'p>>" + 
			"<'row'<'col-sm-12'tr>>" +
			"<'row'<'col-sm-6'i><'col-sm-6'p>>",
	order: [[ 2, "desc" ]],
	columnDefs: [ {
    	orderable: false,
    	className: 'select-checkbox',
    	targets:   0
	} ],
	select: {
		style: 'os',
		selector: 'td:first-child'
	}
} );

After your table initialization, you can add code that will enable the select-all/de-select all feature:

example.on("click", "th.select-checkbox", function() {
    if ($("th.select-checkbox").hasClass("selected")) {
        example.rows().deselect();
        $("th.select-checkbox").removeClass("selected");
    } else {
        example.rows().select();
        $("th.select-checkbox").addClass("selected");
    }
}).on("select deselect", function() {
    ("Some selection or deselection going on")
    if (example.rows({
            selected: true
        }).count() !== example.rows().count()) {
        $("th.select-checkbox").removeClass("selected");
    } else {
        $("th.select-checkbox").addClass("selected");
    }
});

And some CSS:

table.dataTable tr th.select-checkbox.selected::after {
    content: "✔";
    margin-top: -11px;
    margin-left: -4px;
    text-align: center;
}

Finally, you will need to add an empty header column and body column to your table code as the first column for each, so dataTables can populate it with the checkbox.

If you are using the Gyrocode.com solution referenced above, you will need to add a unique ID/index numeric value to the new body column where the checkbox is to be displayed. The unique ID number in the first body column won't be displayed, but it is needed to perform calculations that determine how many rows are selected. For example, w/o the unique ID value, checking a single row box will produce a checkbox in the table header, indicating all rows are checked instead of producing a dash in the checkbox that is used to indicate that some of the rows are checked.