Select filter for empty cells

fathom

Hello,

I have a table with projects and details for those a project is a row. There is a column which shows who is responsible for that project, what I would like to have is a select filter for the unassigned projects.

I am using this script to create a second header row and put there select filters:

$(document).ready( function () {
$('#serverlist thead tr').clone(true).appendTo('#serverlist thead');
DataTable.Buttons.defaults.dom.button.className = 'btn btn-primary';
var table = $('#serverlist').DataTable({
"ordering": true,
"orderCellsTop": true,
"deferRender": true,
"autoWidth": false,
"fixedHeader": true,
"pagingType": "full_numbers",
order: [[1,'asc']],
layout: {
topStart: {
pageLength: { menu: [[-1, 20, 50, 100],['All', 20, 50, 100]]},
buttons: [
{ extend:'csvHtml5', className:'btn btn-primary', text:'CSV (filtered)'},
{ extend:'excelHtml5', className:'btn btn-primary', text:'XLSX (filtered)'}],
}},
pageLength: -1,
initComplete: function() {
this.api().columns([1,6,7,8,12]).every(function() {
let column = this;
let input = $('<input style="width:120px;" type="text">')
.appendTo($('#serverlist thead tr:eq(1) th').eq(column.index()).empty())
.on('keyup', function () {
table
.column( $(this).parent().index() )
.search(this.value)
.draw();
});
});
this.api().columns([2,3,4,5,9,10,11,13,14,15]).every(function() {
let column = this;
let select = $('<select style="min-width:50px;max-width:200px;"><option value=""></option></select>')
.appendTo($('#serverlist thead tr:eq(1) th').eq(column.index()).empty())
.on('change', function () {
var value = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(value ? '^'+value+'$' : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {select.append('<option value="'+d+'">'+d+'</option>');});
});
}
});

Thanks for any ideas/help!

Tamás

Bonus question: In xlsx and csv export, the cloned header line is showing up, do anyone know how to avoid that?
Thanks in advance!

kthorngren

Select filter for empty cells

When using search() or column().search() with an empty string the search is basically cleared. I would look at using columns.render to create orthogonal data for the filter operation. If the cell data is empty then return a keyword, such as Unassigned to search on. The select options should automatically pick this up when built in initComplete.

n xlsx and csv export, the cloned header line is showing up, do anyone know how to avoid that?

See this example.

Kevin

allan

Another option for the filter is to use a function for the search term and have it check for an empty string. This can be quite effective if you are using a button to toggle the filter - e.g.:

table.column(4).search.fixed('searchName', str => str === '');
table.draw();

Then to remove it:

table.column(4).search.fixed('searchName',  null);
table.draw();

You can do the same with a select if that's how you want to display the filtering options.

Allan

fathom

thanks!

fathom

I have an other issue. In one column I have a checkbox that I use to actually assign those projects to ppl. The checkboxes are created with php and it works fine with normal input search, however it is a mess with the select, since the options contain the checkboxes... column.data().unique().sort().each(function (d, j) {select.append('<option value="'+d+'">'+d+'</option>');}); part needs to be tailored somehow, but I can't figure it out.

kthorngren

I guess it depends on what you are expecting to be in the select options list and what you can search on. You could modify the text of d to display what you want like removing the checkbox. You could possibly do what I suggested above with columns.render.

If you still need help with this please build a simple test case showing what you currently have with details of what you want. This will allow us to provide more specific answers.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

fathom

Hello,

Here is my issue (which is not really knowing javascript), I have no idea how that d should be changed: https://jsfiddle.net/7g6Lt34f/2/

Thanks in advance!

kthorngren

Here is ope option:
https://jsfiddle.net/fp5bojh1/

It uses columns.render, as described above, to parse the text value of the cell. You may need to use a different technique depending on the actual cell content.

In the loop creating the select options it uses column().index() to allow processing the column with the checkboxes differently. The same technique is used to extract just the text. Also empty options aren't added to the list.

It is possibly to get duplicates this way. So you may need to create a separate loop for this column to build a unique array of elements.

Kevin

fathom

Thanks for your answer.
In the render function the value extraction does not work on my system, probably cause type in my case is display (?!), setting to that, makes it work, but removes the checkbox, but the search starts to work. Also you have foreseen it I end up with duplicates even though I am extracting only the name of the person (probably that removes the checkboxes), for which I use val.split('<')[3].split('>')[1].trim().
Actually I don't really get why that render function is needed, cause when I use text search field on the same column it is totally enough to just enter a person's name partially and it filters it fine, though it is not the case with a select option with the name only in the value of the option.
Anyway it seems that I failed to implement your example in my case.

kthorngren

In the render function the value extraction does not work on my system, probably cause type in my case is display (?!),

That only affects what is displayed in the cell. See the orthogonal data docs for details. I updated the test case to output the type processing to the console:
https://jsfiddle.net/x9dbzfv5/

Also you have foreseen it I end up with duplicates even though I am extracting only the name of the person (probably that removes the checkboxes), for which I use val.split('<')[3].split('>')[1].trim().

If you have duplicate data then you need to refactor the code a bit. I made an update to show how this might be done:
https://jsfiddle.net/x9dbzfv5/1/

I added a row that would result in a duplicate value with the previous test case.

Actually I don't really get why that render function is needed, cause when I use text search field on the same column it is totally enough to just enter a person's name partially and it filters it fine, though it is not the case with a select option with the name only in the value of the option.

The select list search is using a regex search with exact matching of the value, for example:

column
                .search(value ? '^'+value+'$' : '', true, false)

The value in the select list needs to match the value in the orthogonal filter operation.

The text input is using smart search mode, for example:

table
.column( $(this).parent().index() )
.search(this.value)

Smart search mode is not an exact match. You can read about it in the search() docs.

Kevin