Sorting and comma separated digits

jayparadis

Hello,
I'm having a problem getting sorting to work properly on my tables. I have tables that are predominantly large numbers separated by commas - for example 15,000,000. If the numbers are six digits or less they sort without a problem however if they're more than six digits the sorting no longer works. Any idea how to make this work?

Thanks,
Jason

jayparadis

I've tried the currency plugin since that was suggested as a workaround however that doesn't seem to be helping at all.

wandmdave

I've been having the same issue so I modified the currency type detection plug in (there are some pieces from code snippets on these forums as well) to detect numbers with commas in them but my columns with numbers formatted with commas still sorted as text. I'm sorting currency values with dollar signs in front of them which are formatted with commas and that type detection and sort works fine. To test my sort I used aoColumnDefs to force a few of my comma formatted number columns to use my custom sort and they sorted fine. I can live with that workaround but I'd love to get my type detection code working. Any help is greatly appreciated. Here is the initialization.

[code]
jQuery.fn.dataTableExt.aTypes.push(
function ( sData )
{
var sValidFirstChars = "-0123456789";
var sValidChars = "0123456789.,";
var Char;
var bDecimal = false;
var i = 0;

/* Check for a valid first char (no period and allow negatives) */
Char = sData.charAt(i);
if (sValidFirstChars.indexOf(Char) == -1)
{
return null;
}
i++;

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

allan

@jayparadis: Perhaps you could show us the code you are using or possibly a link to the page?

What I would suggest you do is use the formatted numbers sorting plug-in: http://datatables.net/plug-ins/sorting#formatted_numbers . Remember you need to set the sType for the plug-in or I've just created a type detection plug-in that will automatically pick up formatted number columns: http://datatables.net/plug-ins/type-detection#formatted_numbers .

@wandmdave: Unfortunately the order of the type detection plug-ins is important. Those at the start of the aTypes array take priority so you want to unshift the function on, rather than pushing it on: http://live.datatables.net/aneraj/edit . That should allow it to operate as expected.

This is a bit of a limitation of DataTables - ideally it would be able to allow multiple sorting types for a column, and in future this might well be done (I plan to investigate at the very least). The only problem is that it would then require every type detection function to run on every cell - not great for performance...

Allan

wandmdave

Thanks for your help Allan.

danobri

The formatted numbers sorting plugin is no longer on the site:
http://datatables.net/plug-ins/sorting#formatted_numbers

Anyone know where I might find it?

Thanks DanO

allan

Hi DanO,

I've just re-added the plug-in to the plug-ins repo and it will be available on the site when DataTables 1.9.2 is released. For now you can get it here: https://github.com/DataTables/Plugins/blob/master/sorting/formatted-numbers.js .

The reason it was removed is that the sorting function is actually now identical to the currency sort, but I think it makes sense to include it back under its own name again to save any confusion.

Allan