Is there a way to implement Date Range Filters in the column header of a Tabulator table?
Tabulator - Filter by date range ( from / to) in Header
3.6k views Asked by user14412043 At
2
There are 2 answers
1
On
<!DOCTYPE html>
<html>
<head>
<title>Table</title>
<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet">
<link href="dist/css/bootstrap/tabulator_bootstrap.min.css" rel="stylesheet">
<link rel='stylesheet' href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.6.3/css/font-awesome.min.css'>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css'>
<link rel='stylesheet' href='https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css'>
<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js'></script>
<script src='https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js'></script>
<link href='https://fonts.googleapis.com/css?family=Lato' rel='stylesheet'>
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="keywords" content="" />
</head>
<style>
body {
font-family: 'Lato';font-size: 22px;
}
.tabulator-row.tabulator-row-even {
background-color: #fbfcfd;
}
.tabulator-row .tabulator-cell {
border-right: 1px solid #e7ecf1;
}
.tabulator .tabulator-header .tabulator-col {
border-right: 1px solid #d4d4d4;
}
</style>
<body>
<!--<table id="example-table">
<thead>
<tr>
<th width="200">11111</th>
<th tabulator-align="center">2222</th>
<th>3333</th>
<th>4444</th>
<th width="150">555555</th>
<th>6</th>
</tr>
</thead>
<tbody>
<tr>
<td>Billy Bob</td>
<td>12</td>
<td>male</td>
<td>1</td>
<td>red</td>
<td>22/04/1994</td>
</tr>
<tr>
<td>Mary May</td>
<td>1</td>
<td>female</td>
<td>2</td>
<td>blue</td>
<td>14/05/1982</td>
</tr>
</tbody>
</table>-->
<div style="padding:0px 0px 20px 0px">
<button id="reactivity-add">Add New Row</button>
<button id="reactivity-delete">Remove Row</button>
<button id="reactivity-update">Update First Row Name</button>
<button id="hidecar" onclick="hidecol('car')">Hide Driver Col</button>
<button id="showcar" onclick="showcol('car')">Show Driver Col</button>
</div>
<div id="example-table"></div>
<script>
//**********define row context menu starts*****************
var headerMenu = [
{
label:"Hide Column",
action:function(e, column){
column.hide();
}
},
]
//**********define row context menu ends*****************
//************Create Date Editor starts*****************
var dateEditor = function(cell, onRendered, success, cancel){
//cell - the cell component for the editable cell
//onRendered - function to call when the editor has been rendered
//success - function to call to pass the successfuly updated value to Tabulator
//cancel - function to call to abort the edit and return to a normal cell
//create and style input
var cellValue = moment(cell.getValue(), "DD/MM/YYYY").format("YYYY-MM-DD"),
input = document.createElement("input");
input.setAttribute("type", "date");
input.style.padding = "4px";
input.style.width = "100%";
input.style.boxSizing = "border-box";
input.value = cellValue;
onRendered(function(){
input.focus();
input.style.height = "100%";
});
function onChange(){
if(input.value != cellValue){
success(moment(input.value, "YYYY-MM-DD").format("DD/MM/YYYY"));
}else{
cancel();
}
}
//submit new value on blur or change
input.addEventListener("blur", onChange);
//submit new value on enter
input.addEventListener("keydown", function(e){
if(e.keyCode == 13){
onChange();
}
if(e.keyCode == 27){
cancel();
}
});
return input;
};
//************Create Date Editor ends*****************
var table = new Tabulator("#example-table", {
layout:"fitColumns", //fit columns to width of table (optional)
responsiveLayout:"hide",//Responsive
//selectable:true,//highlight row on select
height:"auto",
//maxHeight:"100%",
height:"335px",
tooltips:true,
fitColumns:true,
rowContextMenu: "rowMenu", //add context menu to rows
addRowPos:"top",
scrollToRowPosition :"top",
footerElement:"<button>Custom Button</button>",
movableRows: true, //Drag and Drop Rows
movableColumns: true, //Drag and Drop column
resizableRows:true, //allow row order to be changed
history:true, //allow undo and redo actions on the table
paginationSize:7, //allow 7 rows per page of data
virtualDomHoz:true,
reactiveData:true, //turn on data reactivity
data:"tabledata", //load data into table
rowClick:function(e, row){ //trigger an alert message when the row is clicked
//alert("Row " + row.getData().id + " Clicked!!!!");
},
//tooltip
tooltips:function(cell){
//cell - cell component
//function should return a string for the tooltip of false to hide the tooltip
return cell.getColumn().getField() + " - " + cell.getValue(); //return cells "field - value";
},
//Filters
headerFilterPlaceholder:"filter data...", //set column header placeholder text
columns:[
{title:"", field:"", cssClass: "text-underline", headerSort:false, headerFilter:true, headerFilter:"input", formatter:"rowSelection", frozen:true, titleFormatter:"rowSelection", width:50, hozAlign:"center", cellClick:function(e, cell){cell.getRow().toggleSelect();}},
{title:"Name", field:"name", headerFilter:true, headerFilterPlaceholder:"Search Name..", headerFilter:"select",responsive:0,editor:"input", validator:["minLength:3", "maxLength:10", "string"],headerMenu:headerMenu},
{title:"Progress", headerMenu:"headerMenu",headerFilter:"select", field:"progress", width:150, formatter:"progress", sorter:"number", headerFilterPlaceholder:"Filter by (%)..",headerMenu:headerMenu},
{title:"Gender", headerMenu:"headerMenu", field:"gender", responsive:0, editor:"select", editorParams:{"male":"Male", "female":"Female"}, headerFilter:true, headerFilterPlaceholder:"Select Gender", headerFilterParams:{"male":"Male", "female":"Female"},headerMenu:headerMenu},
{title:"Rating", headerMenu:"headerMenu", field:"rating", editor:"star", align:"center", responsive:0,headerFilter:"number", headerFilterPlaceholder:"at least...", headerFilterFunc:">=",headerMenu:headerMenu},
{title:"Favourite Color", headerMenu:"headerMenu",field:"col", headerFilter:"input",responsive:0,headerMenu:headerMenu},
{title:"Date of Birth", field:"dob", width:150, headerFilter:minMaxFilterEditor, headerFilterFunc:minMaxFilterFunction, headerFilterLiveFilter:false},
{title:"Driver", field:"car", hozAlign:"center", formatter:"tickCross", headerFilter:"tickCross", headerFilterParams:{"tristate":true},headerFilterEmptyCheck:function(value){return value == null},headerMenu:headerMenu},
{title:"", field:"", width:50, headerMenu:headerMenu, headerSort:false,allowEmpty:true, allowTruthy:true, tickElement:"<i class='fa fa-check'></i>", crossElement:"<i class='fa fa-times'></i>"},
],
initialSort:[ //set the initial sort order of the data
{column:"name", dir:"asc"},
],
//freeze first row on data load
/*dataLoaded:function(data){
var firstRow = this.getRows()[0];
if(firstRow){
firstRow.freeze();
}
},*/
//pagination
pagination:"local", //enable local pagination.
paginationAddRow:"table",
paginationSizeSelector:[7, 10, 15, 20, 25],
movableColumns:true,
});
//add row to bottom of table on button click
document.getElementById("reactivity-add").addEventListener("click", function(){
tabledata.push({name:"IM A NEW ROW", progress:100, gender:"male"});
});
//remove bottom row from table on button click
document.getElementById("reactivity-delete").addEventListener("click", function(){
tabledata.pop();
});
//update name on first row in table on button click
document.getElementById("reactivity-update").addEventListener("click", function(){
tabledata[0].name = "IVE BEEN UPDATED";
});
//*****Show hide columns with buttons starts*********************
const columns = table.getColumns(true);
hidecol = (colName) => {
columns.forEach((col) => {
if (col.getDefinition().field === colName) {
col.hide();
}
});
}
showcol = (colName) => {
columns.forEach((col) => {
if (col.getDefinition().field === colName) {
col.show();
}
});
}
//*****Show hide columns with buttons ends*****************
//****************************Create Date Editor Starts*************************
var dateEditor = function(cell, onRendered, success, cancel){
//cell - the cell component for the editable cell
//onRendered - function to call when the editor has been rendered
//success - function to call to pass the successfuly updated value to Tabulator
//cancel - function to call to abort the edit and return to a normal cell
//create and style input
var cellValue = moment(cell.getValue(), "DD/MM/YYYY").format("YYYY-MM-DD"),
input = document.createElement("input");
input.setAttribute("type", "date");
input.style.padding = "4px";
input.style.width = "100%";
input.style.boxSizing = "border-box";
input.value = cellValue;
onRendered(function(){
input.focus();
input.style.height = "100%";
});
function onChange(){
if(input.value != cellValue){
success(moment(input.value, "YYYY-MM-DD").format("DD/MM/YYYY"));
}else{
cancel();
}
}
//submit new value on blur or change
input.addEventListener("blur", onChange);
//submit new value on enter
input.addEventListener("keydown", function(e){
if(e.keyCode == 13){
onChange();
}
if(e.keyCode == 27){
cancel();
}
});
return input;
};
//****************************Create Date Editor Ends*************************
//custom max min header filter
var minMaxFilterEditor = function(cell, onRendered, success, cancel, editorParams){
var end;
var container = document.createElement("span");
//create and style inputs
var start = document.createElement("input");
start.setAttribute("type", "date");
start.setAttribute("placeholder", "Min");
start.style.padding = "4px";
start.style.width = "50%";
start.style.boxSizing = "border-box";
start.value = cell.getValue();
function buildValues(){
success({
start:start.value,
end:end.value,
});
}
function keypress(e){
if(e.keyCode == 13){
buildValues();
}
if(e.keyCode == 27){
cancel();
}
}
end = start.cloneNode();
end.setAttribute("placeholder", "Max");
start.addEventListener("change", buildValues);
start.addEventListener("blur", buildValues);
start.addEventListener("keydown", keypress);
end.addEventListener("change", buildValues);
end.addEventListener("blur", buildValues);
end.addEventListener("keydown", keypress);
container.appendChild(start);
container.appendChild(end);
return container;
}
//custom max min filter function
function minMaxFilterFunction(headerValue, rowValue, rowData, filterParams){
//headerValue - the value of the header filter element
//rowValue - the value of the column in this row
//rowData - the data for the row being filtered
//filterParams - params object passed to the headerFilterFuncParams property
//convert strings into dates
if(headerValue.start != ""){
headerValue.start = new Date(headerValue.start);
}
if(headerValue.end != ""){
headerValue.end = new Date(headerValue.end );
}
//compare dates
if(rowValue){
rowValue = new Date(rowValue);
if(headerValue.start != ""){
if(headerValue.end != ""){
return moment(rowValue) >= headerValue.start && rowValue <= headerValue.end;
}else{
return rowValue >= headerValue.start;
}
}else{
if(headerValue.end != ""){
return rowValue <= headerValue.end;
}
}
}
return true; //must return a boolean, true if it passes the filter.
}
//date ranges
$(function() {
var table = $("#example").DataTable();
// Date range vars
minDateFilter = "";
maxDateFilter = "";
$("#daterange").daterangepicker();
$("#daterange").on("apply.daterangepicker", function(ev, picker) {
minDateFilter = Date.parse(picker.startDate);
maxDateFilter = Date.parse(picker.endDate);
$.fn.dataTable.ext.search.push(function(settings, data, dataIndex) {
var date = Date.parse(data[1]);
if (
(isNaN(minDateFilter) && isNaN(maxDateFilter)) ||
(isNaN(minDateFilter) && date <= maxDateFilter) ||
(minDateFilter <= date && isNaN(maxDateFilter)) ||
(minDateFilter <= date && date <= maxDateFilter)
) {
return true;
}
return false;
});
table.draw();
});
});
//**************************define row context menu contents Ending)**********************
var tabledata = [
{id:1, name:"Andy", progress:100, gender:"male", rating:1, col:"red",height:1, dob:"8/10/2020", car:1, lucky_no:5},
{id:2, name:"Mary May", progress:70, gender:"female", rating:2, col:"blue", dob:"9/10/20220", car:2, lucky_no:10},
{id:3, name:"Chris", progress:50, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:2, lucky_no:5},
{id:4, name:"Sourav", progress:10, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:5, name:"Dev", progress:30, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:2, lucky_no:5},
{id:6, name:"May", progress:1, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:7, name:"Joker", progress:8, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:2, lucky_no:5},
{id:8, name:"Balcky", progress:1, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:9, name:"Browny", progress:18, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:2, lucky_no:5},
{id:10, name:"Shaun", progress:90, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:11, name:"Ram", progress:100, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:1, lucky_no:5},
{id:12, name:"Krish", progress:71, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:13, name:"Ravan", progress:18, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:1, lucky_no:5},
{id:14, name:"Karnan", progress:81, gender:"female", rating:2, col:"blue", dob:"14/05/1982", car:true, lucky_no:10},
{id:15, name:"Dhur", progress:62, gender:"male", rating:1, col:"red", dob:"14/04/1984", car:1, lucky_no:5},
];
//load sample data into the table
table.setData(tabledata);
</script>
</body>
</html>
The Header Filter Example Docs include an example of a max min header filter for integers.
Based on this we can look at making our own custom header filter for date ranges, in this example i am going to use the built in
Dateobject but you can choose any date library that works for you. We will also assume that dates will be entered in the YYYY-MM-DD format, you can use which ever date picker library you would like insteadSo to start with we will create out custom filter editor (this will build the header filter elements)
Then we will create out filter function that filters by these date ranges:
Then we can assign these to a column in its column definition object using the headerFilter and headerFilterFunc peroperties: