Combining fixed header, export buttons, and double column filtering issue

YoDavish

Hello I'm trying to combine fixed headers, export buttons, and the select dropdown filter and the individual search filter for each column in the header at the top.

Couple of problems I've noticed:

1. If change the dropdown filter from "column.footer()" to "column.header()" it will display mostly correctly (I have to include a "<br>" to separate the column title from the select options), but when you export the dataset using any of the buttons (CSV, Excel, etc) the dropdown options are included in the header.

2. Atering the CSS to move the footer to the top breaks the "Fixed Header"

3.Adding a second empty <TR> in the <thead> and appending the dropdown filters to "column.header().empty()" breaks the individual input search.

Please advise on how to get all 4 working together and displaying the filters options in the table header. My js code is below:

`//GLOBAL VARS
var tab;
var editor;

$(document).ready(function(){
var timeStampForFileExports = Date.now();

editor = new $.fn.dataTable.Editor({
    ajax: "tableServer-application.php",
    table: "#table",
    fields:
    <?php
        $cols =[];
        foreach ($applicationFields as $row) {
            if ($row['displayField'] == 1 && $row['fieldEditable'] == 1) {
                $fieldInfo = [];
                $fieldInfo["label"] = $row['fieldLabel'];
                $fieldInfo["name"] = $row['sourceTable']. "." .$row['fieldName'];
                if (strlen($row['fieldType'])) $fieldInfo["type"] = $row['fieldType'];
                if ($row['options']){
                    $options = [];
                    foreach ($row['options'] as $option){
                        $options[] = ["label"=>$option['name'],  "value"=>$option['id']];
                    }
                    $fieldInfo["options"] = $options;
                }
                $cols[] = $fieldInfo;
            }
        }
        echo json_encode($cols, JSON_PRETTY_PRINT);
    ?>
});

// Activate an inline edit on click of a table cell based on callaName: 'editable'
$('#table').on( 'click', 'tbody td.editable', function (e) {
    editor.inline( this );
});

tab = $("#table").DataTable({
    dom: "Bfrtlip",
    ajax: {
        url: "tableServer-application.php",
        type: 'POST'
    },
    columns: 
    <?php
        // dynamically display columns for datatables
        $cols =[];
        foreach ($applicationFields as $row) {
            if ($row['displayField'] == 1) {
                // Hard coded, needs review process
                // Changes display of value to label for dropdowns
                if($row['fieldLabel'] == '[columnField1]'){
                    $cols[] = ["title"=>$row['fieldLabel'], "data"=> "[table1].[col1]", "editField" => $row['sourceTable']. "." .$row['fieldName']];
                }elseif($row['fieldLabel'] == '[columnField2]'){
                    $cols[] = ["title"=>$row['fieldLabel'], "data"=> "[table2].[col2]", "editField" => $row['sourceTable']. "." .$row['fieldName']];
                }else{
                    $cols[] = ["title"=>$row['fieldLabel'], "data"=>$row['sourceTable']. "." .$row['fieldName']];
                }
            }
        }
        echo json_encode($cols, JSON_PRETTY_PRINT);
    ?>,
    fixedHeader: true,
    select: {
        style:      'os',
        selector:   'td:first-child',
        blurable:   true
    },
    buttons: [
        {
            extend: 'copy',
            title: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>'
        },
        {
            extend: 'csv',
            filename: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>-'+ timeStampForFileExports
        },
        {
            extend: 'excel',
            filename: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>-'+ timeStampForFileExports
        },
        {
            extend: 'pdf',
            title: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>',
            filename: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>-'+ timeStampForFileExports
        },
        {
            extend: 'print',
            title: '<?PHP echo getPageTitle($_REQUEST['appPageId']);?>'
        },
        { extend: "edit", editor: editor }
    ],
    autoFill: {
        columns: [ 3, 4 ],
        editor:  editor
    },
    keys: {
        editor:  editor
    },
    pageLength: 100,
    lengthMenu: [[10, 25, 50, 100, 250, -1], [10, 25, 50, 100, 250, 'All']],
    initComplete: function () {
        let c = 0;
        tab.columns().every( function () {
            c++;
            var column = this;
            var select = $('<select id="SelectFilter'+c+'" onchange="clearOtherColumnFilter(\'TextFilter'+c+'\')"><option value=""></option></select>')
            .appendTo( $(column.footer()))
            .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>')
            });

            $('select', this.header()).click(function(event) {
                event.stopPropagation();
            });
        });
    }
});
setupDTSearches();

});

//Code for the search bar and filter input on each column.
function setupDTSearches() {
// Setup - add a text input to each head cell
let i = 0;
$('#table thead th').each( function (){
i++;
var title = $(this).text();
$(this).html( '<input id ="TextFilter'+i+'" onchange="clearOtherColumnFilter(\'SelectFilter'+i+'\')" type="text" placeholder="Filter by ' + title + '" /><br><strong>'+title+'</strong>' );
});

// Apply the search
tab.columns().every( function (){
    var that = this;
    $( 'input', this.header() ).on( 'keyup change', function (){
        if ( that.search() !== this.value ) {
            that.search( this.value ).draw();
        }
    });

    $('input', this.header()).click(function(event){
        event.stopPropagation();
    })
});

}`

kthorngren

Cloning is probably not going to work with FixedHeader. I would start with this FixedHeader column search example and adjust to support the input types you want. If you need further help with debugging please post a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

but when you export the dataset using any of the buttons (CSV, Excel, etc) the dropdown options are included in the header.

Use of the orderCellsTop, has shown in the example, will affect which header row is used for export. It will be the same row that is used for sorting.

Kevin

YoDavish

@kthorngren

Hello I used that example as a starting point and was able to clone the thead and put the input search just like the URL, however, it will not perform the search. I am using editor and this the order of the code:

   $(document).ready(function(){
// Setup - add a text input to each head cell
let count = 0;
$('#table thead tr').clone(true).appendTo( '#table thead' );
$('#table thead tr:eq(1) th').each( function (i) {
    var title = $(this).text();
    count++;
    $(this).html( '<input id ="TextFilter'+count+'" 
           onchange="clearOtherColumnFilter(\'SelectFilter'+count+'\')" type="text" 
           placeholder="Filter by ' + title + '" />' );

    $( 'input', this ).on( 'keyup change', function () {
        if ( table.column(i).search() !== this.value ) {
            table
                .column(i).search( this.value ).draw();
        }
    } );
} );

var timeStampForFileExports = Date.now();

editor = new $.fn.dataTable.Editor({
    ajax: "tableServer-taskQueue.php",
    table: "#table",
    fields: <?php echo $jsonEditorFields; ?>
});
// Activate an inline edit on click of a table cell based on callaName: 'editable'
$('#table').on( 'click', 'tbody td.editable', function (e) {
    editor.inline( this );
});

tab = $("#table").DataTable({
            orderCellsTop: true,
            fixedHeader: true
     })

YoDavish

@kthorngren thanks for the response. I was able to replicate the example and move the individual search input below the header, however, I'm not able to get it to perform the search anymore, I am using editor. Below is the order of the code:

   `$(document).ready(function(){
// Setup - add a text input to each head cell
let count = 0;
$('#table thead tr').clone(true).appendTo( '#table thead' );
$('#table thead tr:eq(1) th').each( function (i) {
    var title = $(this).text();
    count++;
    $(this).html( '<input id ="TextFilter'+count+'" onchange="clearOtherColumnFilter(\'SelectFilter'+count+'\')" type="text" placeholder="Filter by ' + title + '" />' );

    $( 'input', this ).on( 'keyup change', function () {
        if ( table.column(i).search() !== this.value ) {
            console.log(table.column(i));
            table.column(i).search( this.value ).draw();
        }
    } );
} );

var timeStampForFileExports = Date.now();

editor = new $.fn.dataTable.Editor({
    ajax: "tableServer-taskQueue.php",
    table: "#table",
    fields: <?php echo $jsonEditorFields; ?>
});

// Activate an inline edit on click of a table cell based on callaName: 'editable'
$('#table').on( 'click', 'tbody td.editable', function (e) {
    editor.inline( this );
});

tab = $("#table").DataTable({
        orderCellsTop: true,
    fixedHeader: true,
    initComplete: function () {
        let c = 0;
        tab.columns().every( function () {
            c++;
            var column = this;
            var select = $('<select id="SelectFilter'+c+'" onchange="clearOtherColumnFilter(\'TextFilter'+c+'\')"><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>')
            });

            $('select', this.header()).click(function(event) {
                event.stopPropagation();
            });
        });
    }

})
`

kthorngren

Please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

YoDavish

@kthorngren

http://live.datatables.net/civaduye/1/

kthorngren

You are getting a bunch of console errors. Please fix those so we can help with the problem you are asking about.

Kevin

YoDavish

found the error, had to change "table" to "tab" and the column search worked.