c#jqueryasp.net-coredatatables-1.10
How to filter column in the jQuery Datatable
When the below code runs, it will display a datatable with values on it. There is a filter icon on each header column. Click on the filter icon on the header column, it will drop down a filter menu with check box. This filter drop down menu will display all the values in the respective column. After filtering the first column, the data in the datatable is filtered and have less records in it. Then second time when we click on the "Position" filter icon on its header column, it is showing all the values which is not in that "Position" column in the filter drop down list.
I want to have only visible column datas in the respective column when displaying the filter menu drop down for that column. How to do it ?
<!DOCTYPE html>
<html lang="en">
<head>
<title>Jquery DataTable with Custom Filter</title>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css"
type="text/css" rel="stylesheet" media="screen,projection" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.7/css/materialize.min.css" />
<script src="http://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.7/js/materialize.min.js">
</script>
<style>
table.dataTable th {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
table.dataTable td {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
.filterIcon {
height: 16px;
width: 16px;
}
.modalFilter {
display: none;
height: auto;
background: #FFF;
border: solid 1px #ccc;
padding: 8px;
position: absolute;
z-index: 1001;
}
.modalFilter .modal-content {
max-height: 250px;
overflow-y: auto;
}
.modalFilter .modal-footer {
background: #FFF;
height: 35px;
padding-top: 6px;
}
.modalFilter .btn {
padding: 0 1em;
height: 28px;
line-height: 28px;
text-transform: none;
}
#mask {
display: none;
background: transparent;
position: fixed;
left: 0;
top: 0;
z-index: 1;
width: 100%;
height: 100%;
opacity: 1000;
}
</style>
<script>
$(document).ready(function () {
var dataSet = [
["Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800"],
["Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750"],
["Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000"],
["Cedric Kelly", "Senior Javascript Developer", "Edinburgh", "6224", "2012/03/29", "$433,060"],
["Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700"]
];
$('#example').DataTable({
data: dataSet,
"lengthMenu": [[5, 10, 50, -1], [5, 10, 50, "All"]],
"pageLength": 10,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Office" },
{ title: "Extn." },
{ title: "Start date" },
{ title: "Salary" }
],
initComplete: function () {
configFilter(this, [0, 1, 2, 4]);
}
});
$('#example_length,#example_filter').hide();
});
//This function initializes the content inside the filter modal
function configFilter($this, colArray) {
setTimeout(function () {
var tableName = $this[0].id;
var columns = $this.api().columns();
$.each(colArray, function (i, arg) {
$('#' + tableName + ' th:eq(' + arg + ')').append('<img src="http://www.icone-png.com/png/39/38556.png" class="filterIcon" onclick="showFilter(event,\'' + tableName + '_' + arg + '\')" />');
});
var template = '<div class="modalFilter">' +
'<div class="modal-content">' +
'{0}</div>' +
'<div class="modal-footer">' +
'<a href="#!" onclick="clearFilter(this, {1}, \'{2}\');" class=" btn left waves-effect waves-light">Clear</a>' +
'<a href="#!" onclick="performFilter(this, {1}, \'{2}\');" class=" btn right waves-effect waves-light">Ok</a>' +
'</div>' +
'</div>';
$.each(colArray, function (index, value) {
columns.every(function (i) {
if (value === i) {
var column = this, content = '<input type="text" class="filterSearchText" onkeyup="filterValues(this)" /> <br/>';
var columnName = $(this.header()).text().replace(/\s+/g, "_");
var distinctArray = [];
column.data().each(function (d, j) {
if (distinctArray.indexOf(d) == -1) {
var id = tableName + "_" + columnName + "_" + j; // onchange="formatValues(this,' + value + ');
content += '<div><input type="checkbox" value="' + d + '" id="' + id + '"/><label for="' + id + '"> ' + d + '</label></div>';
distinctArray.push(d);
}
});
var newTemplate = $(template.replace('{0}', content).replace('{1}', value).replace('{1}', value).replace('{2}', tableName).replace('{2}', tableName));
$('body').append(newTemplate);
modalFilterArray[tableName + "_" + value] = newTemplate;
content = '';
}
});
});
}, 50);
}
var modalFilterArray = {};
//User to show the filter modal
function showFilter(e, index) {
$('.modalFilter').hide();
$(modalFilterArray[index]).css({ left: 0, top: 0 });
var th = $(e.target).parent();
var pos = th.offset();
console.log(th);
$(modalFilterArray[index]).width(th.width() * 0.75);
$(modalFilterArray[index]).css({ 'left': pos.left, 'top': pos.top });
$(modalFilterArray[index]).show();
$('#mask').show();
e.stopPropagation();
}
//This function is to use the searchbox to filter the checkbox
function filterValues(node) {
var searchString = $(node).val().toUpperCase().trim();
var rootNode = $(node).parent();
if (searchString == '') {
rootNode.find('div').show();
} else {
rootNode.find("div").hide();
rootNode.find("div:contains('" + searchString + "')").show();
}
}
//Execute the filter on the table for a given column
function performFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
var searchString = '', counter = 0;
rootNode.find('input:checkbox').each(function (index, checkbox) {
if (checkbox.checked) {
searchString += (counter == 0) ? checkbox.value : '|' + checkbox.value;
counter++;
}
});
$('#' + tableId).DataTable().column(i).search(
searchString,
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
//Removes the filter from the table for a given column
function clearFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
rootNode.find(".filterSearchText").val('');
rootNode.find('input:checkbox').each(function (index, checkbox) {
checkbox.checked = false;
$(checkbox).parent().show();
});
$('#' + tableId).DataTable().column(i).search(
'',
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
</script>
</head>
<body>
<div id="mask"></div>
<h3>Jquery DataTable with custom filter</h3>
<table id="example" class="bordered material-table centered striped green lighten-
1"></table>
</body>
</html>
Solution
UPDATE
@{
ViewData["Title"] = "Home Page";
Layout = null;
}
@model IEnumerable<WebApplication1.Models.Employee>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Jquery DataTable with Custom Filter</title>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" type="text/css" rel="stylesheet" media="screen,projection" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.7/css/materialize.min.css" />
<script src="~/js/jquery-2.2.4.min.js"></script>
<script src="~/js/jquery.datatables.min.js"></script>
<script src="~/js/materialize.min.js"></script>
<style>
table.dataTable th {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
table.dataTable td {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
.filterIcon {
height: 16px;
width: 16px;
}
.modalFilter {
display: none;
height: auto;
background: #FFF;
border: solid 1px #ccc;
padding: 8px;
position: absolute;
z-index: 1001;
}
.modalFilter .modal-content {
max-height: 250px;
overflow-y: auto;
}
.modalFilter .modal-footer {
background: #FFF;
height: 35px;
padding-top: 6px;
}
.modalFilter .btn {
padding: 0 1em;
height: 28px;
line-height: 28px;
text-transform: none;
}
#mask {
display: none;
background: transparent;
position: fixed;
left: 0;
top: 0;
z-index: 1;
width: 100%;
height: 100%;
opacity: 1000;
}
</style>
<script>
$(document).ready(function () {
var dataSet = [
["Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800"],
["Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750"],
["Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000"],
["Cedric Kelly", "Senior Javascript Developer", "Edinburgh", "6224", "2012/03/29", "$433,060"],
["Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700"]
];
$('#example').DataTable({
data: dataSet,
"lengthMenu": [[5, 10, 50, -1], [5, 10, 50, "All"]],
"pageLength": 10,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Office" },
{ title: "Extn." },
{ title: "Start date" },
{ title: "Salary" }
],
initComplete: function () {
configFilter(this, [0, 1, 2, 4]);
}
});
$('#example_length,#example_filter').hide();
});
//This function initializes the content inside the filter modal
function configFilter($this, colArray) {
setTimeout(function () {
var tableName = $this[0].id;
var columns = $this.api().columns();
$.each(colArray, function (i, arg) {
$('#' + tableName + ' th:eq(' + arg + ')').append('<img src="https://localhost:7059/38556.png" class="filterIcon" onclick="showFilter(event,\'' + tableName + '_' + arg + '\')" />');
});
var template = '<div class="modalFilter">' +
'<div class="modal-content">' +
'{0}</div>' +
'<div class="modal-footer">' +
'<a href="#!" onclick="clearFilter(this, {1}, \'{2}\');" class=" btn left waves-effect waves-light">Clear</a>' +
'<a href="#!" onclick="performFilter(this, {1}, \'{2}\');" class=" btn right waves-effect waves-light">Ok</a>' +
'</div>' +
'</div>';
$.each(colArray, function (index, value) {
columns.every(function (i) {
if (value === i) {
var column = this, content = '<input type="text" class="filterSearchText" onkeyup="filterValues(this)" /> <br/>';
var columnName = $(this.header()).text().replace(/\s+/g, "_");
var distinctArray = [];
column.data().each(function (d, j) {
if (distinctArray.indexOf(d) == -1) {
var id = tableName + "_" + columnName + "_" + j;
content += '<div><input type="checkbox" value="' + d + '" id="' + id + '"/><label for="' + id + '"> ' + d + '</label></div>';
distinctArray.push(d);
}
});
var newTemplate = $(template.replace('{0}', content).replace('{1}', value).replace('{1}', value).replace('{2}', tableName).replace('{2}', tableName));
$('body').append(newTemplate);
modalFilterArray[tableName + "_" + value] = newTemplate;
content = '';
}
});
});
}, 50);
}
var modalFilterArray = {};
//User to show the filter modal
function showFilter(e, index) {
var table = $('#' + index.split('_')[0]).DataTable();
var columnIdx = parseInt(index.split('_')[1]);
var column = table.column(columnIdx);
var filterContainer = $(modalFilterArray[index]);
var content = '<input type="text" class="filterSearchText" onkeyup="filterValues(this)" /> <br/>';
var distinctArray = [];
table.rows({ search: 'applied' }).every(function (rowIdx, tableLoop, rowLoop) {
var data = this.data()[columnIdx];
if (distinctArray.indexOf(data) == -1) {
var id = index + "_" + rowIdx;
content += '<div><input type="checkbox" value="' + data + '" id="' + id + '"/><label for="' + id + '"> ' + data + '</label></div>';
distinctArray.push(data);
}
});
filterContainer.find('.modal-content').html(content);
$('.modalFilter').hide();
var th = $(e.target).parent();
var pos = th.offset();
filterContainer.width(th.width() * 0.75);
filterContainer.css({ 'left': pos.left, 'top': pos.top }).show();
$('#mask').show();
e.stopPropagation();
}
//This function is to use the searchbox to filter the checkbox
function filterValues(node) {
var searchString = $(node).val().toUpperCase().trim();
var rootNode = $(node).parent();
if (searchString == '') {
rootNode.find('div').show();
} else {
rootNode.find("div").hide();
rootNode.find("div:contains('" + searchString + "')").show();
}
}
//Execute the filter on the table for a given column
function performFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
var searchString = '', counter = 0;
rootNode.find('input:checkbox').each(function (index, checkbox) {
if (checkbox.checked) {
searchString += (counter == 0) ? checkbox.value : '|' + checkbox.value;
counter++;
}
});
$('#' + tableId).DataTable().column(i).search(
searchString,
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
//Removes the filter from the table for a given column
function clearFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
rootNode.find(".filterSearchText").val('');
rootNode.find('input:checkbox').each(function (index, checkbox) {
checkbox.checked = false;
$(checkbox).parent().show();
});
$('#' + tableId).DataTable().column(i).search(
'',
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
</script>
</head>
<body>
<div id="mask"></div>
<h3>Jquery DataTable with custom filter</h3>
<table id="example" class="bordered material-table centered striped green lighten-1"></table>
</body>
</html>
After testing, below code works in my side. For testing, I change the script source and the icon url.
@{
ViewData["Title"] = "Home Page";
Layout = null;
}
@model IEnumerable<WebApplication1.Models.Employee>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Jquery DataTable with Custom Filter</title>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" type="text/css" rel="stylesheet" media="screen,projection" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.7/css/materialize.min.css" />
<script src="~/js/jquery-2.2.4.min.js"></script>
<script src="~/js/jquery.datatables.min.js"></script>
<script src="~/js/materialize.min.js"></script>
<style>
table.dataTable th {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
table.dataTable td {
border-bottom: 1px solid #333;
border-right: 1px solid #333;
}
.filterIcon {
height: 16px;
width: 16px;
}
.modalFilter {
display: none;
height: auto;
background: #FFF;
border: solid 1px #ccc;
padding: 8px;
position: absolute;
z-index: 1001;
}
.modalFilter .modal-content {
max-height: 250px;
overflow-y: auto;
}
.modalFilter .modal-footer {
background: #FFF;
height: 35px;
padding-top: 6px;
}
.modalFilter .btn {
padding: 0 1em;
height: 28px;
line-height: 28px;
text-transform: none;
}
#mask {
display: none;
background: transparent;
position: fixed;
left: 0;
top: 0;
z-index: 1;
width: 100%;
height: 100%;
opacity: 1000;
}
</style>
<script>
$(document).ready(function () {
var dataSet = [
@foreach (var employee in Model)
{
@Html.Raw($"[\"{employee.Name}\", \"{employee.Position}\", \"{employee.Office}\", \"{employee.Extn}\", \"{employee.StartDate}\", \"{employee.Salary}\"],")
}
];
$('#example').DataTable({
data: dataSet,
"lengthMenu": [[5, 10, 50, -1], [5, 10, 50, "All"]],
"pageLength": 10,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Office" },
{ title: "Extn." },
{ title: "Start date" },
{ title: "Salary" }
],
initComplete: function () {
configFilter(this, [0, 1, 2, 4]);
}
});
$('#example_length,#example_filter').hide();
});
// This function initializes the content inside the filter modal
function configFilter($this, colArray) {
setTimeout(function () {
var tableName = $this[0].id;
var columns = $this.api().columns();
$.each(colArray, function (i, arg) {
$('#' + tableName + ' th:eq(' + arg + ')').append('<img src="https://localhost:7059/38556.png" class="filterIcon" onclick="showFilter(event,\'' + tableName + '_' + arg + '\')" />');
});
var template = '<div class="modalFilter">' +
'<div class="modal-content">' +
'{0}</div>' +
'<div class="modal-footer">' +
'<a href="#!" onclick="clearFilter(this, {1}, \'{2}\');" class=" btn left waves-effect waves-light">Clear</a>' +
'<a href="#!" onclick="performFilter(this, {1}, \'{2}\');" class=" btn right waves-effect waves-light">Ok</a>' +
'</div>' +
'</div>';
$.each(colArray, function (index, value) {
columns.every(function (i) {
if (value === i) {
var column = this, content = '<input type="text" class="filterSearchText" onkeyup="filterValues(this)" /> <br/>';
var columnName = $(this.header()).text().replace(/\s+/g, "_");
var distinctArray = [];
column.data().each(function (d, j) {
if (distinctArray.indexOf(d) == -1) {
var id = tableName + "_" + columnName + "_" + j;
content += '<div><input type="checkbox" value="' + d + '" id="' + id + '"/><label for="' + id + '"> ' + d + '</label></div>';
distinctArray.push(d);
}
});
var newTemplate = $(template.replace('{0}', content).replace('{1}', value).replace('{1}', value).replace('{2}', tableName).replace('{2}', tableName));
$('body').append(newTemplate);
modalFilterArray[tableName + "_" + value] = newTemplate;
content = '';
}
});
});
}, 50);
}
var modalFilterArray = {};
// User to show the filter modal
function showFilter(e, index) {
$('.modalFilter').hide();
$(modalFilterArray[index]).css({ left: 0, top: 0 });
var th = $(e.target).parent();
var pos = th.offset();
console.log(th);
$(modalFilterArray[index]).width(th.width() * 0.75);
$(modalFilterArray[index]).css({ 'left': pos.left, 'top': pos.top });
$(modalFilterArray[index]).show();
$('#mask').show();
e.stopPropagation();
}
// This function is to use the searchbox to filter the checkbox
function filterValues(node) {
var searchString = $(node).val().toUpperCase().trim();
var rootNode = $(node).parent();
if (searchString == '') {
rootNode.find('div').show();
} else {
rootNode.find("div").hide();
rootNode.find("div:contains('" + searchString + "')").show();
}
}
// Execute the filter on the table for a given column
function performFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
var searchString = '', counter = 0;
rootNode.find('input:checkbox').each(function (index, checkbox) {
if (checkbox.checked) {
searchString += (counter == 0) ? checkbox.value : '|' + checkbox.value;
counter++;
}
});
$('#' + tableId).DataTable().column(i).search(
searchString,
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
// Removes the filter from the table for a given column
function clearFilter(node, i, tableId) {
var rootNode = $(node).parent().parent();
rootNode.find(".filterSearchText").val('');
rootNode.find('input:checkbox').each(function (index, checkbox) {
checkbox.checked = false;
$(checkbox).parent().show();
});
$('#' + tableId).DataTable().column(i).search(
'',
true, false
).draw();
rootNode.hide();
$('#mask').hide();
}
</script>
</head>
<body>
<div id="mask"></div>
<h3>Jquery DataTable with custom filter</h3>
<table id="example" class="bordered material-table centered striped green lighten-1"></table>
</body>
</html>