Select filter for empty cells
Select filter for empty cells
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!
Replies
When using
search()
orcolumn().search()
with an empty string the search is basically cleared. I would look at usingcolumns.render
to create orthogonal data for thefilter
operation. If the cell data is empty then return a keyword, such asUnassigned
to search on. The select options should automatically pick this up when built ininitComplete
.See this example.
Kevin
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.:
Then to remove it:
You can do the same with a
select
if that's how you want to display the filtering options.Allan
thanks!
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.
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 withcolumns.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
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!
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
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.
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/
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.
The select list search is using a regex search with exact matching of the value, for example:
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:
Smart search mode is not an exact match. You can read about it in the
search()
docs.Kevin
thanks, finally I have managed to get this working:)