Range filter for single table in multi-table page

bryceray1121

My goal is to have a button just 2-3 of several datatables on a page. When the button is clicked it will sort just that table based on date. For instance, the button may say last year. In which case pushing the button would only display rows from the last year (or the button could say last 2 years).

I'm trying to develop a date range filter but have run into a couple problems. Here is what I have so far.
[code]
jQuery("#reports-table").dataTable().fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = new Date(new Date().getFullYear() -1).getTime();
var iMax = new Date().getTime();
var iVersion = aData[3] == "-" ? 0 : new Date(aData[3]).getTime();
if ( iMin == "" && iMax == "" ){
return true;
}
else if ( iMin == "" && iVersion < iMax ){
return true;
}
else if ( iMin < iVersion && "" == iMax ){
return true;
}
else if ( iMin < iVersion && iVersion < iMax ){
return true;
}
return false;
}
);
[/code]

The first problem is I can't figure out how to apply the filter to only a single table based on the tables id. I tried the above but this is not working. The error I receive is:
"jQuery("#reports-table").dataTable().fn is undefined"

I need to figure out the syntax to move this into a function which can be attached to an onclick event for the button. The function would then receive the id name and col number to dynamically sort just that table.

Any suggestions on these problems would be greatly appreciated.

bryceray1121

Any suggestions for this? Thanks.

allan

Hi bryceray1121,

It's actually not possible to have a custom filtering function applied to only a single table at this time. The afnFiltering extension applies to all tables, not just one. So the above the line with "$....fn.dataTableExt..." won't work and it must target just "$.fn.dataTableExt..." like in this example: http://datatables.net/plug-ins/filtering#fnAddTr .

There is however a workaround. What you can do is something like this:

[code]
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
if ( oSettings.nTable != document.getElementById( 'reports-table' );
{
return true;
}

... filtering code
}
);
[/code]
So this will check to see if it's the table you want to filter first, and if not simply return true. If it is then it will apply the filter.

Allan

bryceray1121

I'm just getting around to looking at this, sorry for the slow reply.
I'm a little confused as to how this should be working. Does there need to be a loop in there to go through each table? How would I set this up? Or does the code above internally perform the loop?

This is what I have right now:
[code]
filterByDate = function(id){
alert("1");
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
alert("2");
if ( oSettings.nTable != document.getElementById(id))
{
alert("3");
return true;
}

var iMin = new Date(new Date().getFullYear() -1).getTime();
var iMax = new Date().getTime();
var iVersion = aData[3] == "-" ? 0 : new Date(aData[3]*1).getTime();
alert("iVersion:"+iVersion+", iMin:"+iMin+", iMax:"+iMax);
if ( iMin == "" && iMax == "" ) {
return true;
}
else if ( iMin == "" && iVersion < iMax ) {
return true;
}
else if ( iMin < iVersion && "" == iMax ) {
return true;
}
else if ( iMin < iVersion && iVersion < iMax ) {
return true;
}
return false;
}
);
}
[/code]

I then have a link such as:
A Link
When I click this link the only alert message that prints out is "1" (you will see several embedded alerts for debugging purposes). For some reason it is not even beginning to execute the code in $.fn.dataTableExt.afnFiltering.push.

Any suggestions would be appreciated.

allan

Hi bryceray1121,

The way the custom filtering API works is that DataTables will do the loop for you, and call your function for every row in the table. There is an example here:
http://datatables.net/examples/plug-ins/range_filtering.html

And documentation here:
http://datatables.net/development/filtering#row_filters

I'd imagine you would want to run your function 'filterByDate' before initialisation of the DataTable and run it only once. Not that 'afnFiltering' is applied to all tables if you have multiple tables on the table.

Allan

bryceray1121

The ultimate goal of the filter is to have several buttons above the table labed. "All","Last 6 Months","Last Year","Last 2 Years", etc.

After initialization I need the users to be able to hit these buttons which then filter the table based on the indicated date range. At your suggestion I've included a check to make sure it is only applied to the intended table:
[code]
if ( oSettings.nTable != document.getElementById(id))
{
return true;
}
[/code]

My code is based of the examples in the documentation. There are a couple key important differences I need to be able to implement.
1. In my code you will see I select only 1 table to apply the filter to, this table selection must be dynamic. In other words, I may have different range filters applied to different tables on the page.
2. The filter must also be dynamic (like in the example in the documentation).
Looking through the documentation I think I'm way off on how I originally thought the code was executed. However, I'm not sure how to reorganize it to achieve the above requirements. First off, how would i pass into the function the ID value of the table I'm attempting to change?

allan

First of I would suggest not putting your afnFiltering function into filterByDate - this would have the effect of adding the filtering function to DataTables every time you call that function - which from what you describe is not what you want. Just add the afnFiltering method once. You can have global (or limited scope if you prefer) parameters which can then be set to effect which table the function is applied to and what the search parameter is. Does that all make sense?

Allan

bryceray1121

To be honest I'm still a little confused. I guess I don't understand how the function is working.

Lets say I have 4 tables on my page. 2 of them will be using this function to filter the results by date range. However, the actual range applied to each of these will be different. In addition, each tables date range should be able to be changed after initiation on a button press.

What i'm confused with is how and when the filter function is applied. I add this function, but then how does it know when a user clicks the button to adjust the date range?

allan

My turn to be slightly confused :-). I thought you were wondering about filtering, rather than sorting. Did you mean "when the filter function is applied"?

Have a look at this - it might help a little (I hope :-) ):

[code]
var ganIgnoreTables = array(
$('#example1')[0], $('#example2')[0]
);

$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
if ( $.inArray( oSettings.nTable, ganIgnoreTables ) != -1 )
{
return true;
}

var iMin = new Date(new Date().getFullYear() -1).getTime();
var iMax = new Date().getTime();
var iVersion = aData[3] == "-" ? 0 : new Date(aData[3]*1).getTime();
alert("iVersion:"+iVersion+", iMin:"+iMin+", iMax:"+iMax);
if ( iMin == "" && iMax == "" ) {
return true;
}
else if ( iMin == "" && iVersion < iMax ) {
return true;
}
else if ( iMin < iVersion && "" == iMax ) {
return true;
}
else if ( iMin < iVersion && iVersion < iMax ) {
return true;
}
return false;
}
);
[/code]
DataTables will apply the custom filter whenever the filtering occurs. You can see here that I've defined a global array of nodes (ganIgnoreTables) and added two elements to it. If the table node matches either than it will not apply this filter - otherwise it will run through the filter as the logic requires.

So to activate the filter, either call fnFilter() or fnDraw() - that will cause DataTables to run through the filtering of the table.

Allan

bryceray1121

It may not be pretty, but I believe I've finally got it working.

[code]
var defaultMin = new Date(new Date().setMonth(new Date().getMonth() - 6)).getTime();
var filterDateTables = {};
filterDateTables["#labs-table"] = defaultMin;
filterDateTables["#labResults-table"] = defaultMin;

$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = null;
var tableSrc = null;
for(key in filterDateTables){ //Check if the active table is in the the filterDateTables array
tableSrc = $(key)[0];
if(tableSrc == oSettings.nTable){
iMin = filterDateTables[key];
break;
}
}
if ( iMin == null )
{
return true;
}

var iMax = new Date().getTime();
var colIndex = $(".filterDate",tableSrc).parent().children().index($(".filterDate",tableSrc)); //Dynamically determine the index of the column which stores the date type
var iVersion = aData[colIndex] == "-" ? 0 : new Date(aData[colIndex]).getTime();
if ( iMin == "" && iMax == "" ) {
return true;
}
else if ( iMin == "" && iVersion < iMax ) {
return true;
}
else if ( iMin < iVersion && "" == iMax ) {
return true;
}
else if ( iMin < iVersion && iVersion < iMax ) {
return true;
}
return false;

}
);

filterByDate = function(id,months){
var min = "";
if(months !== undefined){ //If no month is defined display all results
min = new Date(new Date().setMonth(new Date().getMonth() - months)).getTime(); //Subtract user defined months from current date to determine minimum date.
}
filterDateTables[id] = min; //Set min
$(id).dataTable().fnDraw(); //Apply filter
}
[/code]

In addition to this:
Tables which require this functionality all have one statically defined column which contains the date information to filter on. That column has a class of "filterDate" which signals to use that column in the filtering process. Each button on the main page then references filterByDate. It includes the date range to filter and the id of the table to apply the filter to.

allan

Looks good to me :-).

No code is ever pretty when taking into account all possibilities... but that looks good. Nice one and thanks for posting your working code!

Regards,
Allan