Combining fixed header, export buttons, and double column filtering issue
Combining fixed header, export buttons, and double column filtering issue
YoDavish
Posts: 123Questions: 46Answers: 3
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:
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.
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();
})
});
}`
Answers
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
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
@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:
@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:
})
`
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
@kthorngren
http://live.datatables.net/civaduye/1/
You are getting a bunch of console errors. Please fix those so we can help with the problem you are asking about.
Kevin
found the error, had to change "table" to "tab" and the column search worked.