DataTables brackets-negative plug-in with dynamic columns

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.

Solution

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 );
    };
}());