Dynamic filtering values in select elements in Datatables

Question

Using the following code of multi-filtering select inputs in Datatables is it possible to show only available values in the other select inputs upon a selection in one filter? To be more precise, in this example if I select 'Tokyo' as an Office, I would like to populate only the values 'Accountant', 'Integration Specialist', 'Support Engineer' and 'Regional Marketing' in the dropdown menu of Position.

$(document).ready(function() {
$('#example').DataTable( {
    initComplete: function () {
        this.api().columns([1,2]).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();
                } );

            column.data().unique().sort().each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
    }
} );
} );

////// here I get the unique values of each filtered `select` option
$('select').on('change', function () {
            var dtable = $('#datatable').DataTable();

            var filteredArray = [];
            var filteredArray2 = [];

            dtable.column(1, { search: 'applied' }).data()
            .unique()
            .sort()
            .each(function (value, index) {
                filteredArray.push(value);
            });

            dtable.column(2, { search: 'applied' })
            .data()
            .unique()
            .sort()
            .each(function (value, index) {
                filteredArray2.push(value);
            });

            console.log(filteredArray);
            console.log(filteredArray2);

});

In my case I have filters in two columns only as it is shown in the above snippet, so upon selection in one of the two filters I would ideally like to show only available values in the other filter.

Although I have managed to get the unique values of each filter upon a selection I am struggling to hide all the input values that do not exist in the filteredArrays

andrewJames · Accepted Answer · 2020-06-06 20:18:22Z

3

Here is one approach for doing this.

The end result is as follows:

Building a drop-down which only contains the unfiltered (visible) values of a column is relatively straightforward. At the heart of doing this we use the following:

columns( { search: 'applied' } ).data()[index]

Most of the complexity relates to managing the inter-related states of the two drop-downs. After loading the page, whichever drop-down gets used first is designated as the "primary" drop-down and the other is the "secondary". Whenever the user selects a new value from the primary drop-down, we have to clear the secondary drop-down; and then after the primary drop-down filter has been applied, we have to re-build the secondary drop-down's list of values.

The end result is this:

<script type="text/javascript">

/* Each drop-down selection affects the values in the other drop-downs */

var primaryColIdx;
var secondaryColIdx;

$(document).ready(function() {
    $('#example').DataTable( {
        initComplete: function () {
          populateDropdowns(this);
        }
    } );

} );

function populateDropdowns(table) {
    table.api().columns([1,2]).every( function () {
        var column = this;
        //console.log("processing col idx " + column.index());
        var select = $('<select><option value=""></option></select>')
            .appendTo( $(column.footer()).empty() )
            .on( 'change', function () {
                var dropdown = this;
                doFilter(table, dropdown, column);
                rebuildSecondaryDropdown(table, column.index());
            } );

        column.data().unique().sort().each( function ( val, idx ) {
            select.append( '<option value="' + val + '">' + val + '</option>' )
        } );
    } );
}

function doFilter(table, dropdown, column) {
    // first time a drop-down is used, it becomes the primary. This
    // remains the case until the page is refreshed:
    if (primaryColIdx == null) {
        primaryColIdx = column.index();
        secondaryColIdx = (primaryColIdx == 1) ? 2 : 1;
    }

    if (column.index() === primaryColIdx) {
        // reset all the filters because the primary is changing:
        table.api().search( '' ).columns().search( '' );
    }

    var filterVal = $.fn.dataTable.util.escapeRegex($(dropdown).val());
    //console.log("firing dropdown for col idx " + column.index() + " with value " + filterVal);
    column
        .search( filterVal ? '^' + filterVal + '$' : '', true, false )
        .draw();
}

function rebuildSecondaryDropdown(table, primaryColIdx) {
    var secondaryCol;

    table.api().columns(secondaryColIdx).every( function () {
        secondaryCol = this;
    } );

    // get only the unfiltered (unhidden) values for the "other" column:
    var raw = table.api().columns( { search: 'applied' } ).data()[secondaryColIdx];
    // the following uses "spread syntax" (...) for sorting and de-duping:
    // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Spread_syntax
    var uniques = [...new Set(raw)].sort();

    var filteredSelect = $('<select><option value=""></option></select>')
        .appendTo( $(secondaryCol.footer()).empty() )
        .on( 'change', function () {
            var dropdown = this;
            doFilter(table, dropdown, secondaryCol);
            //rebuildSecondaryDropdown(table, column.index());
        } );

    uniques.forEach(function (item, index) {
        filteredSelect.append( '<option value="' + item + '">' + item + '</option>' )
    } );

}

</script>

answered Jun 6, 2020 at 20:18

andrewJames

22.6k11 gold badges32 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

andQlimax Over a year ago

what about if there are several dropdowns? I have a scrollable table with around 40 dropdowns, is there an other way to do it?

andrewJames Over a year ago

@andQlimax - Do you mean 40 drop-downs which are all inter-dependent? (The selected value in any one drop-down will affect the available values in all the others?)

andQlimax Over a year ago

yes exactly..but not sure if there is an easy way to manage (I'm using jquery datatables, but anyway still need to update the dropdown options)

andrewJames Over a year ago

If you don't mind me asking - what is the scenario where you have this requirement? It's very unusual, I think, because it would be potentially complex for a user to actually use. Or, to ask the question a different way: What do you lose if you have 40 independent drop-downs?

andrewJames Over a year ago

OK - understood. In that case, this is more like a drill-down than a traditional filter (or perhaps a hybrid of the two). Take a look at the new SearchBuilder extension for DataTables. Click on the "Add condition" button to see it in action. If that is not the way forward for you, then it's going to be a custom JavaScript solution, with a UI similar to how Excel does it. (Or, you can ask the community in a new question - they may have a better approach than I can think of).

|

Collectives™ on Stack Overflow

Dynamic filtering values in select elements in Datatables

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related