I have a DataTable for which I generate dynamic headers before its initialization. This means that I cannot predict column datatypes, so I rely on DataTables identifying the column type based on the data to get the sorting to work.
Some columns that are returned have currency data (Example format: $234,392.01). Sometimes, the data is surrounded by parentheses to represent a negative value: ($234,392.01). Because of the parentheses, DataTables identifies this column as a string and sorts it incorrectly.
I've tried using brackets-negative plug-in, but it didn't really do anything. However, I am not doing anything extra beyond just pasting the plug-in in my code.
Is there anything additional I need to do to use brackets-negative plug-in? If this is a limitation caused by dynamic columns, is there another way I could solve this issue?
And just to be clear, if there are no parentheses in the column, DataTables sorts the currency data correctly.
Test case where the plug-in does not work (not sure why code formatting is so terrible):
$(document).ready(function() {
var columns = [
{ "data": "Column5", "title": "Column5" },
];
var data = [
{"Column5":"($371.17)"},{"Column5":"$94,170.30"},{"Column5":"$868,588.32"},
{"Column5":"$81,874.09"},{"Column5":"$89.99"},{"Column5":"$2,800,594.59"},
{"Column5":null},{"Column5":null},{"Column5":null},{"Column5":null},
{"Column5":null},{"Column5":null},{"Column5":null},{"Column5":null},
{"Column5":null}
];
$('#example').dataTable( {
"columns": columns,
"data": data
} );
} );
body {
font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
margin: 0;
padding: 0;
color: #333;
background-color: #fff;
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!DOCTYPE html>
<html>
<head>
<link href="//datatables.net/download/build/nightly/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.20/sorting/brackets-negative.js"></script>
<meta charset=utf-8 />
<title>DataTables - JS Bin</title>
</head>
<body>
<div class="container">
<table id="example" class="display" width="100%">
</table>
</div>
</body>
</html>
I've noticed that if null records are removed it works, but I need it to work with nulls. Also, if nulls are kept and the parentheses are removed it also works.
It looks like the brackets-negative plug-in does not support null
values.
The code below is a modified version of the brackets-negative plug-in to return 'currency' if data is null
, and to treat null
and ''
(empty string) values as 0
when ordering.
I was able to replace the 0
with Number.NEGATIVE_INFINITY
so that null
and ''
are always treated as the lowest values.
(function(){
// Change this list to the valid characters you want to be detected
var validChars = "$£€c" + "0123456789" + ".-,()'";
// Init the regex just once for speed - it is "closure locked"
var
str = jQuery.fn.dataTableExt.oApi._fnEscapeRegex( validChars ),re = new RegExp('[^'+str+']');
$.fn.dataTableExt.aTypes.unshift(
function ( data )
{
// Added this check for null
if ( data === null) {
return 'currency';
}
if ( typeof data !== 'string' || re.test(data) ) {
return null;
}
return 'currency';
}
);
$.fn.dataTable.ext.type.order['currency-pre'] = function ( data ) {
// Added '|| data === null'
// Treat '' and null as 0
if ( data === '' || data === null) {
return 0;
}
//Check if its in the proper format
if(data.match(/[\()]/g)){
if( data.match(/[\-]/g) !== true){
//It matched - strip out parentheses & any characters we dont want and append - at front
data = '-' + data.replace(/[\$£€c\(\),]/g,'');
}else{
//Already has a '-' so just strip out non-numeric charactors exluding '-'
data = data.replace(/[^\d\-\.]/g,'');
}
}else{
data = data.replace(/[\$£€\,]/g,'');
}
return parseFloat( data );
};
}());