How do I filter a second table based off first tables row selection?

Hgoober

I am working with two tables and would like the first table to act as a filter for the second table based off the row selection of the first. The first table has a lists of service sections like Executive, HR, Fiscal, Facilities Management, and so on , and the second the employees that are in that section. This is what I have tried.

var table1 = $('#sectionsTable').DataTable({
columns: [
                        {
                "data": "sectionsNumber"
            },
            {
                "data": "sectionsName"
            },
            {
                "data": "sectionsNumber"
            }
]



$('#sectionsTable' tbody').on('click', 'tr', function () {

        var data = table1.row( this ).data();
        table2.api().column(1).search(data.serviceName).draw();
        
    } );

});

var table2 = $('#personsTable').DataTable({
columns: [
                        {
                "data": "sectionsNumber"
            },
            {
                "data": "sectionsName"
            },
            {
                "data": "sectionsNumber"
            },
            {
                "data": "personNumber"
            },
                        {
                "data": "personName"
            },
                        {
                "data": "personPosition"
            }
        ],
searchCols:[
null,
 { "search": "",regex: true },
null,
null,
null,
null

]

});

any help would be great. Thank you.

allan

Is table2 populated with the entire data set (i.e. everything), or do you need to do an Ajax call based on the selected items?

I think you probably want something like:

var data = table1.rows( { selected: true } ).data().pluck( 'serviceName' ).unique();

to get the selected serviceName options. Then use a regex search on the second table:

table2.column(1).search( data.join('|'), true, false ).draw();

Allan

Hgoober

Yes they are both full tables with entire data sets. After using the function I noticed that it doesn’t always work. Some times it will show nothing other times it will show what I want and others the wrong data. Is this due to the regex search type? Is there a different way to handle this? Thank you.

allan

It depends what is being searched on. If you have regex special characters in the search string, then yes, that would cause a problem and you would need to escape them with $.fn.dataTable.util.escapeRegex().

Do you notice a pattern in which data causes the issue?

Allan

Hgoober

That seems to stop the filtering. Here is what happens. Both tables load up. All data shows. Click the service and it will filter all the data away from the second table. Click the service again and the proper data will show in table 2. Very strange. Also the service have a underscore in them. So HR_2876 or 217_357

allan

I'm afraid I'd need a link to a page showing the issue to be able to debug it.

Allan

Hgoober

So I have removed the line of code

searchCols:[
null,
 { "search": "",regex: true },
null,
null,
null,
null
 
]

and it started working.

Thanks Allan for all your help.