Sorting Issue

kraftomatic

Hey Allan,

I'm currently have a small issue with sorting. I'm not trying to sort currency, but the following list of values don't sort consistently. Here's an example:

104
100.002
101.2
99.8

On columns without a "9X.XX" value, the sorting works great. The smaller number throws it off and it appears that it's sorting via a string rather than a number, but do I need to use one of the added plug-ins to correct this?

Thanks Much.

allan

> I need to use one of the added plug-ins to correct this?

Very likely - there must be something in the column that is causing the numeric detection to failover to strings - have you got HTML in the column for example? If so the HTML with numbers plug-in would be what is needed.

Allan

kraftomatic

I think so Allan. Here's what a row looks like:

100.097
99.827
99.703
99.629
99.455
100.2

So then I assume I need these two functions:

jQuery.fn.dataTableExt.oSort['num-html-asc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" );
var y = b.replace( /<.*?>/g, "" );
x = parseFloat( x );
y = parseFloat( y );
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['num-html-desc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" );
var y = b.replace( /<.*?>/g, "" );
x = parseFloat( x );
y = parseFloat( y );
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

However, can I tie them in without altering how the table sorts currently? For example, here's what I've got currently:

var table = $('#example').dataTable( {
"aoColumnDefs": [ { "bSortable": false, "aTargets": [0] } ],
"aaSorting": [[ 1, 'asc' ]],
"sDom": "<'row'<'span6'l><'span6'f>r>t<'row'<'span6'i><'span6'p>>",
"sPaginationType": "bootstrap",
"oLanguage": {
"sLengthMenu": "_MENU_ records per page"
}
} );


Thanks.

allan

Use the type detection plug-in noted in the sorting plug-ins documentation :-). http://datatables.net/plug-ins/type-detection#numbers_html . Put that in with your sorting plug-in and it will all "just work" :-)

Allan

kraftomatic

Magic! Thanks Allan. :)

kraftomatic

Allan - there are some instances where a cell value will have an "N/A" value, rather than a numeric value. This causes the header sorting to not function properly it seems. Is there something that can allow N/A to be at either the top or the bottom of the asc/desc sort order and have the numbers still sort correctly?

Thanks.

kraftomatic

The issue is becoming more complex. There are a number of decimal values:

99.3
77.56
101.2

However in some cells, there is an "N/A" value with an href tag around it. Adding the html sort seems to be throwing this off in some columns, but not others. I'm not yet sure how to build a work around for this situation ...

allan

There are a couple of options - but basically it comes down to modifying the plug-ins to pick up the N/A as well. Since you are using the type detection plug-in and N/A isn't a number, then it isn't going to match that - so you could perhaps add a check in the code to look for N/A and when found treat it like 0 would be (or 999999 if you want it at the end).

Allan

kraftomatic

Thanks Allan. I added a check, but when sValidChars.indexOf(Char) is equal to -1, it's returning null. Should I be converting the value to a zero there? I want to confirm that the string is "N/A" and not just an "N" or an "A" obviously.

[code]
Query.fn.dataTableExt.aTypes.unshift( function ( sData )
{
sData = typeof sData.replace == 'function' ?
sData.replace( /<.*?>/g, "" ) : sData;
sData = $.trim(sData);

var sValidFirstChars = "0123456789-";
var sValidChars = "0123456789.";
var Char;
var Char2;
var bDecimal = false;

/* Check for a valid first char (no period and allow negatives) */
Char = sData.charAt(0);
Char2 = sData;

if (Char2 == "N/A")
{
//alert("Char2: " + Char2 + " Char: " + Char);
Char = "0";
Char2 = "0";
//alert("Char2: " + Char2 + " Char: " + Char);
}

if (sValidFirstChars.indexOf(Char) == -1)
{
return null;
}

/* Check all the other characters are valid */
for ( var i=1 ; i