I have a DataTables table ing from a database. I want to have a dropdown enabled when I select rows from the dataset. The dropdown will be populated with every option from that column.
It has been suggested that this be done using rowCallback, but I haven't been able to figure out how to create the editable fields for the columns by row once the checkbox is enabled.
The function, on select would cause that row's Class and Category cells to bee dropdown menus populated with all of the existing options in Class and Category to choose from.
HTML
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href=".3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" type="text/css" href=".3.0/css/select.dataTables.min.css" media="screen" />
<link rel="stylesheet" type="text/css" href=".5.6/css/buttons.dataTables.min.css" media="screen" />
<script charset="utf8" src=".3.1/jquery.min.js"></script>
<script charset="utf8" src=".10.19/js/jquery.dataTables.min.js"></script>
<script charset="utf8" src=".3.0/js/dataTables.select.min.js"></script>
<script charset="utf8" src=".5.6/js/dataTables.buttons.min.js"></script>
<script charset="utf8" src=".5.6/js/buttons.flash.min.js"></script>
<script charset="utf8" src=".5.6/js/buttons.html5.min.js"></script>
<script charset="utf8" src=".5.6/js/buttons.print.min.js"></script>
<script charset="utf8" src=".editor.min.js"></script>
</head>
</html>
<body>
<div class="container">
<table id="samples" class="display nowrap pact hover dt-left" width="100%"></table>
</table>
</form>
<script type="text/javascript" charset="utf8" src="JS/datatables.js"></script>
</body>
Jquery
$(document).ready( function () {
var table = $('#samples').DataTable( {
"processing": true,
"serverSide": false,
"pageLength": -1,
"lengthMenu": [[100, 250, 500, -1], [100, 250, 500, "All"]],
ajax: "datatables.php",
columns: [
{data: '',
defaultContent: '0',
visible: false },
{data: '',
defaultContent: '',
orderable: false,
className: 'select-checkbox',
targets: 1
},
{title : 'Sample Name', 'className': 'dt-left', data: 1},
{title : 'Region/Program', 'className': 'dt-left', data: 2},
{title : 'Class', 'className': 'dt-left', data: 3},
{title : 'Category', 'className': 'dt-left', data: 4},
{title : 'QC Concerns', 'className': 'dt-left', data: 5}
],
select: {
style: 'multi',
},
order: ([[ 4, 'asc' ], [ 5, 'asc' ], [ 3, 'asc' ]]),
orderFixed: [0, 'desc'],
dom: 'Bfrtip',
buttons: [
{
extend: 'excel',
text: '<span class="fa fa-file-excel-o"></span> Download (ALL) or (SELECTED)',
exportOptions: {
columns: [ 2, 5 ],
modifier: {
search: 'applied',
order: 'applied'
}
}
},
{
text: 'Use Selected Library',
action: function ( e, dt, node, config) {
alert( 'This buton needs to pass the Sample Name and Category columns to php.' );
}
},
{
text: 'Upload Predefined Library',
action: function ( e, dt, node, conf ) {
alert( 'This button needs to allow a csv file to be uploaded and passed to php.' );
}
}
]
} );
table.on( 'select', function ( e, dt, type, indexes ) {
if ( type === 'row' ) {
var row = table.row( dt );
table.cell({ row: row.index(), column: 0 } ).data( '1' );
table.draw();
}
});
table.on( 'deselect', function ( e, dt, type, indexes ) {
if ( type === 'row' ) {
var row = table.row( dt );
table.cell({ row: row.index(), column: 0 } ).data( '0' );
table.draw();
}
});
} );
Data sample
const srcData = [
{Name: '752', Region: '7', Class : 'unknown', Category : 'unknown', QC_Concerns: 'yes'},
{Name: 'North 5th', Region: 'NWA', Class : 'unknown', Category : 'square', QC_Concerns: 'yes'},
{Name: 'Elmdale', Region: '6', Class : 'back', Category : 'circle', QC_Concerns: ''},
{Name: 'Rosebud', Region: '7', Class : 'back', Category : 'triangle', QC_Concerns: ''},
{Name: 'Letterkenny', Region: 'GO', Class : 'back', Category : 'square', QC_Concerns: ''},
{Name: '632nd', Region: 'GO', Class : 'front', Category : 'ball', QC_Concerns: ''},
{Name: 'Water', Region: '4', Class : 'front', Category : 'ball', QC_Concerns: ''},
{Name: 'Dirt', Region: '1', Class : 'front', Category : 'mermaid', QC_Concerns: ''},
];
I have a DataTables table ing from a database. I want to have a dropdown enabled when I select rows from the dataset. The dropdown will be populated with every option from that column.
It has been suggested that this be done using rowCallback, but I haven't been able to figure out how to create the editable fields for the columns by row once the checkbox is enabled.
The function, on select would cause that row's Class and Category cells to bee dropdown menus populated with all of the existing options in Class and Category to choose from.
HTML
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn./bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables/select/1.3.0/css/select.dataTables.min.css" media="screen" />
<link rel="stylesheet" type="text/css" href="https://cdn.datatables/buttons/1.5.6/css/buttons.dataTables.min.css" media="screen" />
<script charset="utf8" src="https://ajax.googleapis./ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/1.10.19/js/jquery.dataTables.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/select/1.3.0/js/dataTables.select.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/buttons/1.5.6/js/buttons.flash.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/buttons/1.5.6/js/buttons.html5.min.js"></script>
<script charset="utf8" src="https://cdn.datatables/buttons/1.5.6/js/buttons.print.min.js"></script>
<script charset="utf8" src="https://editor.datatables/extensions/Editor/js/dataTables.editor.min.js"></script>
</head>
</html>
<body>
<div class="container">
<table id="samples" class="display nowrap pact hover dt-left" width="100%"></table>
</table>
</form>
<script type="text/javascript" charset="utf8" src="JS/datatables.js"></script>
</body>
Jquery
$(document).ready( function () {
var table = $('#samples').DataTable( {
"processing": true,
"serverSide": false,
"pageLength": -1,
"lengthMenu": [[100, 250, 500, -1], [100, 250, 500, "All"]],
ajax: "datatables.php",
columns: [
{data: '',
defaultContent: '0',
visible: false },
{data: '',
defaultContent: '',
orderable: false,
className: 'select-checkbox',
targets: 1
},
{title : 'Sample Name', 'className': 'dt-left', data: 1},
{title : 'Region/Program', 'className': 'dt-left', data: 2},
{title : 'Class', 'className': 'dt-left', data: 3},
{title : 'Category', 'className': 'dt-left', data: 4},
{title : 'QC Concerns', 'className': 'dt-left', data: 5}
],
select: {
style: 'multi',
},
order: ([[ 4, 'asc' ], [ 5, 'asc' ], [ 3, 'asc' ]]),
orderFixed: [0, 'desc'],
dom: 'Bfrtip',
buttons: [
{
extend: 'excel',
text: '<span class="fa fa-file-excel-o"></span> Download (ALL) or (SELECTED)',
exportOptions: {
columns: [ 2, 5 ],
modifier: {
search: 'applied',
order: 'applied'
}
}
},
{
text: 'Use Selected Library',
action: function ( e, dt, node, config) {
alert( 'This buton needs to pass the Sample Name and Category columns to php.' );
}
},
{
text: 'Upload Predefined Library',
action: function ( e, dt, node, conf ) {
alert( 'This button needs to allow a csv file to be uploaded and passed to php.' );
}
}
]
} );
table.on( 'select', function ( e, dt, type, indexes ) {
if ( type === 'row' ) {
var row = table.row( dt );
table.cell({ row: row.index(), column: 0 } ).data( '1' );
table.draw();
}
});
table.on( 'deselect', function ( e, dt, type, indexes ) {
if ( type === 'row' ) {
var row = table.row( dt );
table.cell({ row: row.index(), column: 0 } ).data( '0' );
table.draw();
}
});
} );
Data sample
const srcData = [
{Name: '752', Region: '7', Class : 'unknown', Category : 'unknown', QC_Concerns: 'yes'},
{Name: 'North 5th', Region: 'NWA', Class : 'unknown', Category : 'square', QC_Concerns: 'yes'},
{Name: 'Elmdale', Region: '6', Class : 'back', Category : 'circle', QC_Concerns: ''},
{Name: 'Rosebud', Region: '7', Class : 'back', Category : 'triangle', QC_Concerns: ''},
{Name: 'Letterkenny', Region: 'GO', Class : 'back', Category : 'square', QC_Concerns: ''},
{Name: '632nd', Region: 'GO', Class : 'front', Category : 'ball', QC_Concerns: ''},
{Name: 'Water', Region: '4', Class : 'front', Category : 'ball', QC_Concerns: ''},
{Name: 'Dirt', Region: '1', Class : 'front', Category : 'mermaid', QC_Concerns: ''},
];
2 Answers
Reset to default 3I failed to prehend your sample data structure, so I demonstrated the way of how it can be done, using my own:
//table source data
const srcData = [{
id: 1,
item: 'apple',
category: 'fruit'
}, {
id: 2,
item: 'banana',
category: 'fruit'
}, {
id: 3,
item: 'carrot',
category: 'vegie'
}, {
id: 4,
item: 'raspberry',
category: 'berry'
}, {
id: 5,
item: 'potato',
category: 'vegie'
}
];
//DataTable initialization
const dataTable = $('#mytable').DataTable({
dom: 't',
data: srcData,
select: 'multi',
columns: Object.keys(srcData[0]).map(key => {
return {
title: key,
data: key
}
})
});
//grab all the unique sorted data entries from the necessary row
const category = dataTable.column(2).data().unique().sort();
//Row click handler
dataTable.on('deselect', (event, row, type, index) => writeCell($(row.node()).find('select')));
dataTable.on('select', (event, row, type, index) => $(row.node()).find('td:eq(2)').html('<select>' + category.reduce((options, item) => options += `<option value="${item}" ${item == row.data().category ? 'selected' : ''}>${item}</option>`, '') + '</select>'));
//Drop down menu stop event propagation
$('#mytable').on('click', 'tbody td select', event => event.stopPropagation());
//Write dropdown value into table
var writeCell = dropdown => {
const currentRow = dataTable.row(dropdown.closest('tr'));
const rowData = currentRow.data();
rowData.category = dropdown.val();
currentRow.remove();
dataTable.row.add(rowData).draw();
};tbody tr:hover {
background: lightgray !important;
cursor: pointer;
}
tbody tr.selected {
background: gray !important;
}<!doctype html>
<html>
<head>
<script type="application/javascript" src="https://code.jquery./jquery-3.3.1.min.js"></script>
<script type="application/javascript" src="https://cdn.datatables/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="application/javascript" src="https://cdn.datatables/select/1.3.0/js/dataTables.select.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables/1.10.19/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables/select/1.3.0/css/select.dataTables.min.css">
</head>
<body>
<table id="mytable"></table>
</body>
</html>This is going to add the ddl on your 3rd column for all the values in that column.
initComplete: function () { // After DataTable initialized
this.api().columns([2]).every(function () {
/* use of [2] for third column. Leave blank - columns() - for all.
Multiples? Use columns[0,1]) for first and second, e.g. */
var column = this;
var select = $('<select><option value=""/></select>')
.appendTo($('.datatable .dropdown .third').empty()) /* for multiples use .appendTo( $(column.header()).empty() ) or .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>')
});
}); // this.api function
} //initComplete function
});
});
$(window).resize(function () {
$('.datatable').removeAttr('style');
});