Search

Good catch! Thanks very much for the detailed bug report. I'll look at including your fix in the next release version of DataTables. Regards, Allan

Sure there is better way to do, but this work api.columns( FilterColumnList ).every( function () { var column = this; var select = $('<select><option value=""></option></select>') .appendTo( $(column.footer()).empty() ) .on( 'change', function () { var val = $.fn.dataTable.util.escapeRegex($(this).val()); column .search( val ? '^'+val+'$' : '', true, false ) .draw(); } ); if ( $( column.header() ).hasClass( 'dt-render' ) ) { var cells = api.cells( null, this ); if ( Array.isArray( cells.data()[ 0 ] ) ){ // type array var sArr = cells.data(), dArr = []; // unique set of datas for ( var z=0; z < sArr.length; z++ ){ sArr[ z ].forEach( function( e ) { e = Object.values( e ); if( $.inArray( e[ 0 ], dArr ) === -1 ) dArr.push( e[ 0 ] ); }); } dArr.sort(function(a, b){ if (isNaN( a )) { var aa = a.toLowerCase(); var bb = b.toLowerCase(); } else { var aa = Number( a ); var bb = Number( b ); } if ( aa < bb ) { return -1; } else if ( aa > bb ) { return 1; } return 0; }) dArr.forEach( function ( d, j ) { var t = '<option '; if ( (d) ){ if (savedColumnsFilters){ if (savedColumnsFilters[column[0][0]].search.search.replace(/\\/g,"") == '^'+d+'$'){ t += "selected='selected' "; } } select.append( t + 'value="'+d+'">'+d+'</option>' ); } } ); } else { cells.render('display').unique().sort().each( function ( d, j ) { var t = '<option '; if ( (d) ){ if (savedColumnsFilters){ if (savedColumnsFilters[column[0][0]].search.search.replace(/\\/g,"") == '^'+d+'$'){ t += "selected='selected' "; } } select.append( t + 'value="'+d+'">'+d+'</option>' ); } } ); }; } else { column.data().unique().sort().each( function ( d, j ) { var t = '<option '; if ( (d) ){ if (savedColumnsFilters){ if (savedColumnsFilters[column[0][0]].search.search.replace(/\\/g,"") == '^'+d+'$'){ t += "selected='selected' "; } } select.append( t + 'value="'+d+'">'+d+'</option>' ); } }); } });

I connected my system with database consists of millions of data. However, the system keeps lagging once connecting. So, I plan to use the serverside processing to initialize the table to empty table so it no need to process all the data in a time (and lag). Not sure if it's a good way to deal the heavy data with the datatable. Here is my what I tried but it didnt work, <script type="text/javascript"> $(document).ready(function(){ fill_datatable(); function fill_datatable(age = '', age2 ='' ,dataGender = '') { var dataTable = $('#table_data').DataTable({ processing: true, serverSide: true, " iTotalRecords": 57, "iTotalDisplayRecords": 57, },

Guys, could anyone help me put checkboxes in this example? https://datatables.net/forums/discussion/4426/multi-column-filtering

hi, has any one done user research around column filters pattern? https://datatables.net/extensions/fixedheader/examples/options/columnFiltering.html i just want to check how much comfortable people are using this.

Can any one help me?

where conditions, the column filtering doesnt work. If i

In my JSON I have a nested Object data as array and Object. It looks like that: "contentVMlist":[ { "dbContID":3, "dbContType":2, }, { "dbContID":1, "dbContType":1, } with "aoColumns: [ { "data": "contentVMlist.[ ].dbContType"} ]" i get the data into the table. It shows like this in the table column "2 1". with this.api().columns([9]).every(function () { var column = this; else if (column[0] == 9) { var select2 = $(' ') // the select is not so short i just shortened it now .appendTo($('#dbContType')).on('change', function () { var val = $.fn.dataTable.util.escapeRegex( $(this).val() ); column.search(val ? '^' + val + '$' : '', true, false).draw(); }); if "contentVMlist" only has 1 entry, everything works fine. But if there are 2 or more entries (like "1 3" or " 1 3 2") it cannot see the entries as single occurences. it works if the select would be "1 3 2", but the user only wants to chose one and then all with that type show up (if there is more than one it should show up too) How can I get this done? I tried a few different things that didnt work.

Hi Guys, please refer to the below code. I am developing a HTML Web Resource for a MS Dynamics CRM 365 online. There is a function FetchData which fetches data. I am trying to set the data into the table but unable to do so. If I am able to fetch data then I am unable to to implement initComplete as both are working async. Need assistance. <script type="text/javascript"> // JavaScript source code var isFetchComplete = 0; var resLength = null; function FetchData() { var context = window.parent.Xrm.Page.context; var webAPIPath = "/api/data/v8.1"; var guid = window.parent.Xrm.Page.data.entity.getId(); var tabledata = []; var clientUrl = context.getClientUrl(); var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + "<entity name='activitypointer'>" + "<attribute name='activitytypecode' />" + "<attribute name='subject' />" + "<attribute name='statecode' />" + "<attribute name='prioritycode' />" + "<attribute name='modifiedon' />" + "<attribute name='activityid' />" + "<attribute name='instancetypecode' />" + "<attribute name='community' />" + "<attribute name='scheduledend' />" + "<attribute name='createdby' />" + "<order attribute='modifiedon' descending='false' />" + "<filter type='and'>" + "<condition attribute='isregularactivity' operator='eq' value='1' />" + "</filter>" + "<link-entity name='incident' from='incidentid' to='regardingobjectid' alias='an'>" + "<attribute name='title' />" + "<attribute name='ticketnumber' />" + "</link-entity>" + "<link-entity name='email' from='activityid' to='activityid' alias='anem' link-type='outer'>" + "<attribute name='new_category' />" + "</link-entity>" + "<link-entity name='letter' from='activityid' to='activityid' alias='anle' link-type='outer'>" + "<attribute name='new_category' />" + "</link-entity>" + "<link-entity name='phonecall' from='activityid' to='activityid' alias='anph' link-type='outer'>" + "<attribute name='new_category' />" + "</link-entity>" + "</entity>" + "</fetch>"; var uri = "/activitypointers?fetchXml=" + fetchXML; // Construct a fully qualified URI if a relative URI is passed in. if (uri.charAt(0) === "/") { uri = clientUrl + webAPIPath + uri; } var request = new XMLHttpRequest(); request.open("GET", encodeURI(uri), true); request.setRequestHeader("OData-MaxVersion", "4.0"); request.setRequestHeader("OData-Version", "4.0"); request.setRequestHeader("Accept", "application/json"); request.setRequestHeader("Content-Type", "application/json; charset=utf-8"); request.setRequestHeader("Prefer", "odata.include-annotations=OData.Community.Display.V1.FormattedValue"); request.onreadystatechange = function () { if (this.readyState === 4 && this.status===200) { // request.onreadystatechange = null; var response = JSON.parse(request.response).value; if (response.length != 0 && response != null) { resLength = response.length; for (var i = 0; i < response.length; i++) { resLength--; var subject = response[i]["subject"]; var activityType = response[i]["activitytypecode@OData.Community.Display.V1.FormattedValue"]; var statecode = response[i]["statecode@OData.Community.Display.V1.FormattedValue"]; var priority = response[i]["prioritycode@OData.Community.Display.V1.FormattedValue"]; var duedate = response[i]["scheduledend"]; var createdby = response[i]["_createdby@OData.Community.Display.V1.FormattedValue"]; var category = null; if (activityType == "Phone Call") { category = response[i]["anph_x002e_new_category"]; } else if (activityType == "Email") { category = response[i]["anem_x002e_new_category@OData.Community.Display.V1.FormattedValue"]; } else if (activityType == "Letter") { category = response[i]["anle_x002e_new_category"]; } var modifiedon = response[i]["modifiedon@OData.Community.Display.V1.FormattedValue"]; if (subject != null || subject != undefined) { } else { subject = "NA"; } if (priority != null || priority != undefined) { } else { priority = "NA"; } if (duedate != null || duedate != undefined) { } else { duedate = "NA"; } if (createdby != null || createdby != undefined) { } else { createdby = "NA"; } if (category != null || category != undefined) { } else { category = "NA"; } if (modifiedon != null || modifiedon != undefined) { } else { modifiedon = "NA"; } var row = [subject, activityType, statecode, priority, duedate, createdby, category, modifiedon]; tabledata[i] = row; } } //1 } } request.send(null); return JSON.stringify(tabledata); } $(document).ready(function () { var Fetcheddata = FetchData(); var table = $('MyTable').DataTable(); table.rows.add(Fetcheddata).draw(); //var p1 = new Promise(function (resolve, reject) { // if (resLength <= 0) { // resolve(this); // } //}); //p1.then( //$('#MyTable').DataTable({ // data: Fetcheddata // //initComplete: function () { // // this.api().columns().every(function () { // // var column = this; // // var select = $('<select><option value=""></option></select>') // // .appendTo($(column.header())) // // .on('change', function () { // // var val = $.fn.dataTable.util.escapeRegex( // // $(this).val() // // ); // // //to select and search from grid // // column // // .search(val ? '^' + val + '$' : '', true, false) // // .draw(); // // }); // // column.data().unique().sort().each(function (d, j) { // // select.append('<option value="' + d + '">' + d + '</option>') // // }); // // }); // //} //}) //); }); </script> <title></title> </head> <body> <table id="MyTable" class="display" cellspacing="0" width="100%"> <thead><tr><th>Subject</th><th>Activity Type</th><th>Activity Status</th><th>Priority</th><th>Due Date</th><th>Created By</th><th>Category</th><th>Modified On</th></tr></thead> <tbody> </tbody> </table> </body> </html>

Viewing the tables already works great. Now I have however a problem: I would like with pre-filter the appropriate column on the server by means of combo box. But somehow this does not work properly, where do I have to start? Here is the code in the HTML: <table width="100%" class="table table-striped table-bordered" id="bestellung" cellspacing="0"> <thead> <tr> <th>Shop</th> <th>Bestellnr.</th> <th>Bestellzeit</th> <th>Firma</th> <th>Vname</th> <th>Kunde</th> <th>Zahlart</th> <th>Bestellstatus</th> <th>Zahlstatus</th> <th>Aktionen</th> </tr> <tr id="filterrow"> <th></th> ... <th></th> </tr> </thead> <tbody> </tbody> <tfoot> <tr> <th>Shop</th> ... <th>Aktionen</th> </tr> </tfoot> </table> <script> $(document).ready(function() { $('#bestellung').DataTable( { "language": { "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/German.json"}, "initComplete": function () { this.api().columns([6]).every( function () { var column = this; var select = $('<select size="1" class="form-control input-sm"><option value="999">Alle</option></select>') select.append( '<option value="1" style="background-color:#00572c;color: white;">Vorkasse</option>') select.append( '<option value="2" style="background-color:#ff8040;color: white;">Sofort</option>') .appendTo($(column.header("#filterrow")).empty() ) .on( 'change', function () { var val = $.fn.dataTable.util.escapeRegex( $(this).val() ); column .search( val ? ''+val+'' : '', true, false ) .draw(); } ); } ); }, "lengthMenu": [[25, 50, 100, 250, -1], [25, 50, 100, 250, "Alle"]], "paging": true, "serverSide": true, "stateSave": true, "showFilters": true, "deferRender": true, "scrollY": 500, "scrollX": true, "ajax": { url: "../tabelle_bestell.php", type: 'post' }, columns: [ { data: null, render: function ( data, type, row ) { if (data.shop == 1) { return '<span class="label label-info demo-element">xxx</span>'; } if (data.shop == 3) { return '<span class="label label-warning demo-element">yyyy</span>'; } if (data.shop == 5) { return '<span class="label label-info demo-element">zzz</span>'; } if (data.shop == 6) { return '<span class="label label-info demo-element">aaa</span>'; } } }, { data: "bestellnr"}, { data: "bestzeit"}, { data: "user_f"}, { data: "user_v"}, { data: "user_n"}, { data: "zahlart"}, { data: "bestatus"}, { data: "zahlstatus"}, { data: "becode"} ], "order": [[ 1, "desc" ]], columnDefs: [ { targets: 0, width: "28px", searchable: false, orderable: false },{ targets: 1, width: "50px", searchable: true, },{ targets: 2, width: "85px", searchable: true, },{ targets: 4, visible: false, },{ targets: 5, searchable: true, render: function ( data, type, full ) { if (full['user_t'] != '') { return full['user_t']+' '+full['user_v']+' '+full['user_n']; } else { return full['user_v']+' '+full['user_n']; } }, searchable: true, },{targets: 6, width: "80px", render: function ( data, type, full ) { return '<span class="label demo-element" style="background-color: '+full['zahlart_farbe']+';">'+data+'</span>'; }, s searchable: true, orderable: false, },{ targets: 7, width: "80px", render: function ( data, type, full ) { return '<span class="label demo-element" style="background-color: '+full['bestatus_farbe']+';">'+data+'</span>'; orderable: false }, },{ targets: 8, width: "80px", render: function ( data, type, full ) { return '<span class="label demo-element" style="background-color: '+full['zahlstatus_farbe']+';">'+data+'</span>'; }, orderable: false },{ targets: 9, width: "80px", render: function ( data, type, full ) { return '<button class="edit_value ui-tooltip" onclick="bestdetails(\''+data+'\')"><i class="fa fa-pencil"></i></button>&nbsp;&nbsp;&nbsp;'+ '<button class="edit_value ui-tooltip btn-success" onclick="besttorg(\''+data+'\')"><i class="fa fa-arrow-right"></i></button>'; }, orderable: false, searchable: false }, ] } ) } ); Here is the code in the php: include( "../stlsys/datatable/DataTables.php"); use DataTables\Editor, DataTables\Editor\Field, DataTables\Editor\Format, DataTables\Editor\Mjoin, DataTables\Editor\Options, DataTables\Editor\Upload, DataTables\Editor\Validate; Editor::inst( $db, 'tab1', 'ID' ) ->fields( Field::inst( 'tab1.partnerid as shop' ), Field::inst( 'tab1.bestellnr as bestellnr' ), Field::inst( 'tab1.bestzeit as bestzeit') ->validator( 'Validate::dateFormat', array( 'format'=>'d.M.Y H:i' ) ) ->getFormatter( 'Format::datetime', array( 'from'=>'Y-m-d H:i:s', 'to' =>'d.m.Y H:i' ) ) ->setFormatter( 'Format::datetime', array( 'to' =>'Y-m-d H:i:s', 'from'=>'d.m.Y H:i' ) ), Field::inst( 'tab2.rg_firma as user_f' ) ->options( Options::inst() ->table( 'tab2') ->value( 'ID') ->label( 'rg_firma') ), Field::inst( 'tab2.rg_titel as user_t' ) ->options( Options::inst() ->table( 'tab2') ->value( 'ID') ->label( 'rg_titel') ), Field::inst( 'tab2.rg_vname as user_v' ) ->options( Options::inst() ->table( 'tab2') ->value( 'ID') ->label( 'rg_vname') ), Field::inst( 'tab2.rg_nname as user_n' ) ->options( Options::inst() ->table( 'tab2') ->value( 'ID') ->label( 'rg_nname') ), Field::inst( 'tab1.zahlbetrag as zahlbetrag' ), Field::inst( 'tab3.kurz as zahlart' ) ->options( Options::inst() ->table( 'tab3') ->value( 'ID') ->label( 'kurz') ), Field::inst( 'tab3.hfarbe as zahlart_farbe' ) ->options( Options::inst() ->table( 'tab3') ->value( 'ID') ->label( 'hfarbe') ), Field::inst( 'tab4.bez as bestatus') ->options( Options::inst() ->table( 'tab4') ->value( 'ID') ->label( 'bez') ), Field::inst( 'tab4.hfarbe as bestatus_farbe') ->options( Options::inst() ->table( 'tab4') ->value( 'ID') ->label( 'hfarbe') ), Field::inst( 'tab5.bez as zahlstatus') ->options( Options::inst() ->table( 'tab5') ->value( 'ID') ->label( 'bez') ), Field::inst( 'tab5.hfarbe as zahlstatus_farbe') ->options( Options::inst() ->table( 'tab5') ->value( 'ID') ->label( 'hfarbe') ), Field::inst( 'tab1.becode as becode' ) ) ->leftJoin( 'tab3', 'tab3.ID', '=', 'tab1.zahlart') ->leftJoin( 'tab4', 'tab4.ID', '=', 'tab1.bestatus') ->leftJoin( 'tab5', 'tab5.ID', '=', 'tab1.zahlstatus') ->leftJoin( 'tab2', 'tab2.ID', '=', 'tab1.rgadrid') ->process($_POST) ->json();