SELECT filter with JSON data

nskwortsow

Hi,

The functionality of
http://datatables.net/release-datatables/examples/api/multi_filter_select.html
is very nice, but doesn't work with externally loaded data, it seems.

Do you have a suggestion as to how I can make this work with Editor?

Thanks,

Nathan

allan

Hi Nathan,

The key thing to realise here is that the data is being loaded by Ajax - i.e. it is async. Therefore the code that is running in the demo to built the select menus is being run before the data has been loaded from the server!

So what you need to do is wait until he JSON data have been loaded from the server. This can be done with fnInitComplete . The modification in my example would be:

[code]
$(document).ready(function() {
/* Initialise the DataTable */
var oTable = $('#example').dataTable( {
"oLanguage": {
"sSearch": "Search all columns:"
},
fnInitComplete: function () {
/* Add a select menu for each TH element in the table footer */
$("tfoot th").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} );
}
}
} );
} );
[/code]

(i.e. just shunt it into fnInitComplete which fires when the data base been fully loaded).

Allan

nskwortsow

Thanks, I'm now getting data into the function, but I still get an error:

TypeError: sValue is undefined
[Break On This Error]
if (bIgnoreEmpty == true && sValue.length == 0) continue;

###

when setting sValue, aData[iColumn] is undefined.

A console.log(aData) ouputs the following:

Object { DT_RowId=

"row_39"

, EquipmentName=

"Gewichten"

, f_LanguageID=

"1"

, more...}

###

What's going wrong here?

allan

I don't know I'm afraid. Can you link me to the page?

Allan

nskwortsow

Where it goes wrong is here:

var aData = this.fnGetData(iRow);

aData is correctly populated, but

var sValue = aData[iColumn];

results in UNDEFINED.

- JSON is fed in correctly (data is there)
- When I hard-code in the data field, e.g. aData.EquipmentName, it works.

I think the difference between your code sample and my implementation is the fact that I am using a JOIN.

This means that there are 2 dimensions of a JSON object to loop through, and aData[iColumn] is only one dimension.

Please point me in the right direction.

###

My JSON looks like this:

DT_RowId


"row_2"


EquipmentName


"Ball"


f_LanguageID


"0"


tblLanguages
Object { LanguageID=

"0"

, LName=

"English"

}

allan

> var sValue = aData[iColumn];

Ah. That's wrong as you are using objects - not arrays.

There isn't actually a publicly exposed function to get the data from a column based on objects at the moment (there will be when I finish the 1.10 API). Until then, in the plug-in function you are using replace:

[code]
var aData = this.fnGetData(iRow);
var sValue = aData[iColumn];
[/code]

with:

[code]
var sValue = oSettings.oApi._fnGetCellData( oSettings, iRow, iColumn );
[/code]

Allan

nskwortsow

Works!

nskwortsow

I have several tables on the same page with which I want to use this script.
Which parameters should I pass into

fnGetColumnData = function ( oSettings, iColumn, bUnique, bFiltered, bIgnoreEmpty )

I suspect that I need to pass in custom oSettings for every call?

nskwortsow

Never mind > jquery selector error on my side.

nskwortsow

Sidenote: the select boxes are only updated with new values when I do a page refresh.
Can I link the creation of the select boxes to a different event, so that they always contain the full table content, including newly added rows?

nskwortsow

Sidenote 2: in one of my fields, I use MRender to determine the data shown in the column

return (val == 0) ? "Doctor" : "Patient";

However, the SELECT filter, only shows the 1 and 0 (my database values).

Is there a way around this?

allan

> Can I link the creation of the select boxes to a different event, so that they always contain the full table content, including newly added rows?

I presume that the values can be changed by Editor? If so, then you could use Editor's `onEditComplete` (and `onCreateComplete` ) events to rebuilt the search filters. It would be done by just calling a similar to function to what you've already got that will read the data front he column and rebuilt the `select` control.

[quote]
However, the SELECT filter, only shows the 1 and 0 (my database values).
Is there a way around this?
[/quote]

Yes - the easiest way is to use the display value which can be done by altering the line of code from above to be:

[code]
var sValue = oSettings.oApi._fnGetCellData( oSettings, iRow, iColumn, 'display' );
[/code]

Note the 'new' final parameter to the function. That just tells DataTables you want the display data rather than the raw data.

Allan

Mar

How I can use multi-filter with json.
Get a javascript error "To retrieve DataTable object for the table pass no arguments or see the docs for bRetrieve and bDestroy"

Code



var asInitVals = new Array();

$(document).ready(function() {


TableTools.DEFAULTS.aButtons = [ "copy", "csv", "xls","pdf", "print" ];

$('#example').dataTable( {
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sDom": 'T<"clear">lfrtip',

"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "scripts/jsonp.php",
"fnServerData": function( sUrl, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax( {
"url": sUrl,
"data": aoData,
"success": fnCallback,
"dataType": "jsonp",
"cache": false
} );
}

} );

/* Initialise the DataTable */
var oTable = $('#example').dataTable( {
"oLanguage": {
"sSearch": "Search all columns:"
}
} );

$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );



/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("tfoot input").each( function (i) {
asInitVals[i] = this.value;
} );

$("tfoot input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );

$("tfoot input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("tfoot input").index(this)];
}
} );


} );



Thank you very much