Sort Not Working Correctly

MagicSquares

Hi!

I'm trying to sort a table on a column that contains a variety of data, specifically a number (which will have either a comma or decimal point as the thousands separator, depending on which website is being queried), and which may then be followed by additional data.

Here is the content of two sample cells:

<p style='text-align:right;'>47 </p><p style='text-align:left;margin-top:-12px;margin-bottom:0px;padding-bottom:0px;font-size:2.3em;color:green;font-weight:bold;' valign='bottom'>$$$$</p>

<p style='text-align:right;'>104,269 </p><p style='text-align:left;margin-top:-12px;margin-bottom:0px;padding-bottom:0px;font-size:2.3em;color:green;font-weight:bold;' valign='bottom'></p>

It's the numbers 47 and 104,269 that should be the sort data here.

The full debug data can be seen here:

http://debug.datatables.net/ibinis

Whatever options I try, I cannot get DT to sort the BSR column correctly - it works some times, but not others.

I'm not sure if it's the thousands separator that's causing the problem, or the other content in the cell.

Sorting on the other columns appears to work fine, so I'm thinking it's the other data in that cell that's causing the issue.

Is there an option / set of options I can use to fix this sort, or do I need to create a custom sort function - and if so, how do I go about that?

Any assistance gratefully received!

Thanks,

Mark

MagicSquares

By way of comparison, here's another result from the same script where the sort on the BSR columns works fine:

https://debug.datatables.net/uvowun

I can't see what the difference is that causes it to work in one case and not the other.

MagicSquares

On comparing the debug information for the results that do not sort correctly and the results that do, I noticed that on the format, the Type of the BSR column is html, whereas on the latter, it's html-num-fmt.

I tried to force the BSR column to be treated as html-num-fmt, but perhaps my syntax is incorrect:

    $(document).ready(function(){
$('#bsr-results').dataTable( {
    "language": [ {
        "decimal": ".",
        "thousands": ","
    } ] ,
    "order": [],
    "columnDefs": [
        {
        "type": "html-num-fmt", "targets": 4,
        "orderable": false, "targets": [1,3]
        }
    ],
    "paging": false,
    "searching": false,
    "autoWidth": false,
    "info": false
} );
    });

What I'm trying to accomplish with the columnDefs options is that the second and fourth columns (i.e. Product and Price) should not be sortable, and the fifth column (i.e. BSR) should be treated as html-num-fmt.

allan

I'm not sure if it's the thousands separator that's causing the problem, or the other content in the cell.

The other content in the cell. In the two examples you give above, if you strip the HTML you get:

47 $$$$
104,269

Now DataTables would actually sort them okay, apart from the space character! That is making it be detected as non-numeric, and thus string sorting.

So there are two options:

1. Use numeric only data
2. Use data-order attributes for orthogonal data.

I would very much suggest using 2 if you are able to modify the HTML to add that information easily. The reason being is that it will remove any possibility of the type detection going wrong, and also remove any ambiguity with the thousands / decimal separator, so you wouldn't need to add that into the language options.

Regards,
Allan

MagicSquares

Allan,

Many thanks for putting me out of my misery.

The data-order option worked great, and actually allowed me to sort a couple of other columns that I had disabled because I couldn't see how I could include those.

Best wishes,

Mark

allan

Great to hear :-)

Allan