How to refilter dropdown lists when a column is selected and remove options when exported/printed

DuaneLakoduk

Link to test case: https://live.datatables.net/kavibala/1/edit
I am adding select lists to datatables list using the column().search() functions described here: https://datatables.net/reference/api/column().search(). The end user wants filtering to work similar to an excel spreadsheet.
There are a couple issues I am having trouble resolving.
1. When one of the dropdown lists are selected, the list is filtered on the selected value, but the other dropdown lists still contain all options from the unfiltered list. Ideally, I would like to be able to update the other dropdown lists to contain only the values for the filtered list.
2. The Excel, and Print results contain all the option values in the header row concatenated with the column header. I would like to be able to remove all of the filtering options for the Export to Excel and the Printed output.

I have not been able to find documentation to resolve these two issues. I have seen other postings describing a similar issue, with a response describing export to PDF. I was not able resolve my issue using that example.

Thanks

kthorngren

The recommended approach is to use two rows in the thead. This will solve two issues; first the column won't be sorted when using the select input and second the export will not contain the select input.

See this example from this thread which shows the use of two thead rows and has code for the cascading options lists. Note the use of orderCellsTop which moves the sorting capability to the top row and should also force the export to use this row.

Kevin

DuaneLakoduk

Thanks, this helps. options are loaded, and re-filtered when changed. Sorting on clicking select list solved.

The only issue I am seeing now is due to the original developer having declared the the first column visible: false in the columnDefs, and the first column in the table header style markup as display:none. This is causing all select lists to be appended one column to the right and the last column select list is not rendered.

My posted sample didn't show the hidden column, as I didn't anticipate it being and issue since the placement of my original select lists were in the correct position.

Thanks.

kthorngren

Your original example uses this code to filter the columns that the filters are applied to:

table.columns('.select-filter').every(....)

And you have the classname select-filter assigned to all but the first column. You will need to do the same in the createDropdowns() function, for example:

function createDropdowns(api) {
    api.columns('.select-filter').every(function() {

Kevin

DuaneLakoduk

Thanks again, but that isn't solving the issue. The problem is the api sees the hidden column 0 and jQuery doesn't see the hidden column. The api and jQuery don't agree on the column indexes when there are hidden columns.

The api.columns() function skips the first hidden, non-searchable column and sets col=1.
Then jQuery uses the col variable in the selector. $('thead tr:eq(1) td:eq(' + col + ') select').val();

jQuery selects the Vendor column because the FY column is not visible. The select list and options from the Order column are appended to the Vendor column.
jQuery sees the Order column as col(0), Vendor as col(1).
The api sees FY as col(0), Order as col(1), Vendor as col(2)

The api.cells(null, col, { ....) function then gets the items from the api col(1) "Order" and appends them to the jQuery colum(1) "Vendor" .
The first column is skipped and each subsequent column's select list contains the options from the previous column.

The table design has the FY column hidden and a group header for each FY displayed instead. When printed, or exported, the first FY column is displayed and the group header is hidden.

"columnDefs": [
{
"targets": [0],
"visible": false,
"searchable": false
},

api.columns('.select-filter').every(function () {
if (this.searchable()) { // column (0) is skipped (searchable: false )
var that = this;
var col = this.index(); // first searchable column index is column 1

    var selected = $('thead tr:eq(1) td:eq(' + col + ') select').val(); // jQuery selector targets column (1) --> Vendor, displayed column

if (selected === undefined || selected === '') {
// Create the select element
$('thead tr:eq(1) td')
.eq(col)
.empty();
var select = $('<select style="width:125px"><option value=""></option></select>')
.appendTo($('thead tr:eq(1) td').eq(col))
.on('change', function () {
that.search($(this).val()).draw();
createDropdowns(api);
});

api
    .cells(null, col, {
        search: 'applied'
    })
    .data()
    .sort()
    .unique()
    .each(function (d) {
        select.append($('<option>' + d + '</option>'));
    }); 

<thead>
<tr style="vertical-align: top">
<th class="col" style="padding-left:25px; display:none">FY</th> // column ignored
<th class="col-md-1 select-filter" style="padding-left:5px; vertical-align: bottom; padding-bottom: 5px;">Order</th> // column 0
<th class="col-md-2 select-filter" style="vertical-align: bottom; padding-bottom: 5px;">Vendor</th> // column 1
... other 7 columns ...
</tr>

Thanks

kthorngren

I see. Use column.index() to convert the column index to the visible index. The visible index will be used for the jQuery selectors and the column index (col) is used for the Datatable API. Updated example:
https://live.datatables.net/mibonuga/1/edit

Kevin

DuaneLakoduk

That works perfectly. Thanks for the assistance and the references.