Excel autofilter style column filters

iecwebmast

Is it possible to use select boxes instead of text input boxes as column filters?
I would like to use the datatables with "Excel autofilter" style select boxes,
with choices based on the items in the column.
Cheers,
iecwebmast

iecwebmast

I found an example of this with another jquery plugin, but I really prefer DataTables.
I'm sure this must be possible to do in DataTables too.

http://www.trirand.com/blog/jqgrid/jqgrid.html
Click on "New in Version 3.5" and then on "integrated search toolbar"
to see the example.

allan

Hi iecwebmast,

Absolutely yes this is possible - however at the moment it is necessary to manually create the select menus - there is no automatic way of doing this. An 'advanced' column control plug-in is definitely on my agenda, it's just a case of getting around to it!

Regards,
Allan

allan

Here we go: http://datatables.net/examples/api/multi_filter_select.html . It's not exactly world changing(!) - but it does show how a select menu could be created and used with fnFilter :-). Using the fnGetColumnData API plug-in from Benedikt Forchhammer it's actually fairly quick an easy (thanks Benedikt!).

Regards,
Allan

iecwebmast

Thank you very much Allan!
I will let you know as soon as we have it working on our site!!!
Cheers,
iecwebmast

iecwebmast

I have one more question... can the select filters be above the tbody instead of below in the tfoot?
I'd like to put them in the thead, below the column headers... and above the tbody.

allan

Yup - no reason why that can't be done. You could simply create another TR row in THEAD and put the select elements in there.

Allan

iecwebmast

Hi Allan,
That's what I thought... but when I do this I get an error "DataTables warning: Unexpected number of TD elements. Expected 570 and got 285..." Can you tell me what I've done wrong?

My thead now looks like this:
[code]


Rendering engine
Browser
Platform(s)
Engine version
CSS grade









[/code]

I looked at the jscript and changed the select to use thead instead of tfoot

[code]
/* Add a select menu for each TH element in the table header */
$("thead th").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
[/code]

allan

Use TD elements for your second row in the THEAD, rather than TH elements. Probably an area of DataTables that needs cleaned up in future - but DataTables effectively ignores TD elements in the header - but counts all TH elements...

Allan

iecwebmast

Hi Allan!
Thanks for the help on the TH/TD fix!

I've got another couple of questions. It seems that the autofilter is created for EVERY column automatically. Some columns contain long text descriptions which can't logically be filtered with a list. How can I "skip" adding an autofilter to a column?

The other question I have is about the uniqueness of the items in the autofilter. Example: when I select "10" as a number to filter a column by, it shows me 10, 100, 104, 105... and 110. My column contains Committee numbers, so I need to filter the list to show ONLY committee number "10".

iecwebmast

Hi again Allan,

I found this on "bSearchable"(Enable or disable filtering on the data in this column.)
http://datatables.net/usage/columns#bSearchable

But when I try to use the script I get the following error:
"DataTables warning: Unable to re-initialise DataTable. Please use the API to make any configuration changes required."

[code]
$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
{ "bSearchable": false },
null,
null,
null,
null
] } );
} );
[/code]

Can you tell me how I can use this to script to select which columns to filter?

Cheers,
iecwebmast

allan

That code looks fine to me. Are you calling $('#example').dataTable() somewhere else? (or perhaps another selector which is including it by mistake?). As the error notes, you can only initialise a table once, but you can certainly initialise it that once!

Allan

iecwebmast

I'm running the "autofilter" script you gave me... it does have a "$('#example').dataTable."
How can I then add bSearchable bit?

[code]
(function($) {
/*
* Function: fnGetColumnData
* Purpose: Return an array of table values from a particular column.
* Returns: array string: 1d data array
* Inputs: object:oSettings - dataTable settings object. This is always the last argument past to the function
* int:iColumn - the id of the column to extract the data from
* bool:bUnique - optional - if set to false duplicated values are not filtered out
* bool:bFiltered - optional - if set to false all the table data is used (not only the filtered)
* bool:bIgnoreEmpty - optional - if set to false empty values are not filtered from the result array
* Author: Benedikt Forchhammer
*/
$.fn.dataTableExt.oApi.fnGetColumnData = function ( oSettings, iColumn, bUnique, bFiltered, bIgnoreEmpty ) {
// check that we have a column id
if ( typeof iColumn == "undefined" ) return new Array();

// by default we only wany unique data
if ( typeof bUnique == "undefined" ) bUnique = true;

// by default we do want to only look at filtered data
if ( typeof bFiltered == "undefined" ) bFiltered = true;

// by default we do not wany to include empty values
if ( typeof bIgnoreEmpty == "undefined" ) bIgnoreEmpty = true;

// list of rows which we're going to loop through
var aiRows;

// use only filtered rows
if (bFiltered == true) aiRows = oSettings.aiDisplay;
// use all rows
else aiRows = oSettings.aiDisplayMaster; // all row numbers

// set up data array
var asResultData = new Array();

for (var i=0,c=aiRows.length; i -1) continue;

// else push the value onto the result data array
else asResultData.push(sValue);
}

return asResultData;
}}(jQuery));


function fnCreateSelect( aData )
{
var r='', i, iLen=aData.length;
for ( i=0 ; i

przemek.ch

The example http://datatables.net/examples/api/multi_filter_select.html look good but it does not provide the exact excels auto-filtering functionality.

In excel you can select multiple filtering values using a list with check boxes and values.

1. so we can use something like that http://code.google.com/p/dropdown-check-list/
to build the list with checkboxes

2. then build a regex for fnFilter function using selected data

3. oTable.fnFilter( $(this).val(), i, true );

Do You think this will work?