How can I get jQuery DataTables to sort on hidden value, but search on displayed value?

Question

I have a simple DataTables grid which contains date columns. I have provided two values for the date in my JSON data set, one for display and one specifically designed so that DataTables can sort it. My web application allows users to choose a bunch of different date formats, so it needs to be flexible.

This is my JSON data that DataTables gets from from the web server via sAjaxSource.

{   Reports : [     { Date: { Sort = "20101131133000", Display : "11/31/2010 1:30 PM" } },      { Date: { Sort = "20100912120000", Display : "1200 EST 2010-09-12" } },    ] } 

It is easy to tell DataTables to sort based on the Date.SortValue property and to make the Display property visible to the user by using fnRender(). So this gets me halfway to my goal.

var dataTableConfig = {   sAjaxSource: "/getreports",   sAjaxDataProp: "Reports",   aoColumns: [     { mDataProp: "User" },     { mDataProp: "Date.Sort",        bSortable: true,        sName: "Date",        bUseRendered: false,        fnRender: function (oObj) {         return oObj.aData[oObj.oSettings.aoColumns[oObj.iDataColumn].sName].Display;       }     }   ] }; 

Here's my problem. I want to allow the user to enter a filter (using the built-in filter input that DataTables provides) based on the displayed value, but they cannot.

For example. If a user entered "EST", they would get zero results because datatables filters based on the value specified in mDataProp not based on the value returned from fnRender.

Can anyone help me figure out how to sort AND filter a date column? Thanks.

Answer 1

I believe the existing answers are deprecated due to updates to DataTables. HTML5 supports attributes that DataTables can use to easily sort columns. Specifically the data-sort attribute. (See https://datatables.net/examples/advanced_init/html5-data-attributes.html)

I can easily sort tables like so:

<table>   <thead>     <tr>       <td>Name</td>       <td>Age</td>     </tr>   </thead>   <tbody>     <tr>       <td>John Doe</td>       <td data-sort="37">2/1/78 (37 years old)</td>     </tr>     <tr>       <td>Jane Doe</td>       <td data-sort="35">12/1/80 (35 years old)</td>     </tr>   </tbody> </table> 

It doesn't matter that the 'Age' column contains numbers, symbols, and letters, DataTables will sort using the 'data-sort' attribute.

Answer 2

This is an old post, but hopefully this will help someone else that comes here.

In a more recent version of DataTables, bUseRendered and fnRender are deprecated.

mRender is the new way of doing this sort of thing and has a slightly different approach.

You can solve your issue with something along the lines of:

... { mDataProp: "Date.Sort"   bSortable: true,    sName: "Date",    // this will return the Display value for everything   // except for when it's used for sorting,   // which then it will use the Sort value   mRender: function (data, type, full) {     if(type == 'sort') return data.Sort;     return data.Display   } } ...