data-order sorting difference between languages

Pichutan

Dear community,

we have a table which contains a column with costs and a custom currency called "NO-CUR" since we don't know which currency the customer used.

To have a locale independend sorting we are using the data-order attribute and adding the computer representation of the number. eg: 123.456, 123.45 and so on.

In the english version this one works quite good but as soon as the customer changes to german, the numbers get treated differently. In that case 123.456 is higher than 123.45 because in germany we use a dot as thousand separator.

Is there any solution for it? We were quite sure, it worked before we swapped to DataTables 2.1. Were there any changes?

Thanks in advance and still, happy new year!
Pichutan

allan

Hi Pichutan,

data-order should always use a period for the decimal. The display (i.e. what is in the cell) should be localised, but data-order, since the end user doesn't see it, should be what the computer knows as a number.

If that doesn't help, can you link to a page showing the issue so we can take a look and diagnose it further.

Allan

rf1234

Happy New Year, Pichutan.

I have the exact same situation and I don't have to use "data-order". I auto-detect formatted-numbers in English or German. And then set the ordering. This works for all of my many data tables; no extra code required for any individual table.

In my case I only have two currencies: EUR and CHF, but you an add as many currencies as you like to the array on top. This code is executed BEFORE data table initialization. And yes: It could be written more elegantly ... this was one of the first things I wrote in Javascript.

German formatted number: 1.000.000,99
English formatted number: 1,000,000.99

var cur = [ 'EUR', 'CHF' ];

//auto detection only works if ALL cells of a column comply with the criterion !!!
//fields must contain an ISO currency code or space to be detected as formatted-numbers
//since all table fields with ISO codes always contain a number this works.
//in addition German number fields should be detected as formatted-num regardless of
//whether they contain an ISO code or not
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
    if (typeof data !== 'undefined') {
        if ( data != null )  {
            var i=0;
            while ( cur[i] ) {
                if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                    if ( data.search( cur[i] ) > -1 )   {
                        return 'formatted-num';
                    }
                } else { //for pure numbers we want the same sorting as well!!
                    return 'formatted-num';
                }
                i++;
            }
            if ( data === '') {
                return 'formatted-num';
            }                
            if (lang == 'de') {
//we are not using strict mode in moment.js so that 28.12.2017 09:42 is also
//recognized as a date!!
                if ( ! moment(data, 'L').isValid() ) {
                    if ( isNaN(data) ) {
                        data = data.replace( /[\.]/g, "" );
                        data = data.replace( /[\,]/g, "." );
                        if ( ! isNaN(data) ) {
                            return 'formatted-num';
                        }
                    }
                }
            }                    
        }
    }
    return null;
} );

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTable.ext.type.order, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.toString().replace( /[\.]/g, "" );
            a = a.toString().replace( /[\,]/g, "." );
        } else {
            a = a.toString().replace( /[\,]/g, "" );
        }
        a = a.toString().replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
//14 digit negative number to make sure empty cells always stay at the bottom / top
            return -99999999999999;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

Pichutan

Hello @allan ,

I prepared a fiddle for this problem. But actually I don't know how to add the language there, maybe you have a way to test it. It works perfectly fine in english but as soon as our users switch to german, the sorting is off.

https://jsfiddle.net/Pichutan/j032pwmc/8/

Since I can't give you access to our product, I made a screenshot of the same data I prepared for you in the fiddle, showing the problem. I know it is not perfect..

Happy new year, @rf1234 !

Thanks for your input I will check it out! What wonders me, it was definetly working before with the data-order attribute.

Thank you for your help!

Pichutan

allan

It works perfectly fine in english but as soon as our users switch to german, the sorting is off.

You'll need to clarify this for me please. Is it a browser or OS prefernce they change, or an option on your website?

If it is an option on your web-site, then presumably it would be possible to recreate an example with the not working state in JSFiddle? The example you linked to, appears to be working as expected?

Allan

Pichutan

Hey @allan,

They change a setting on our website, (browser independend) and depending on language they chose we load the language pack for DataTables. Does the language setting has an effect on the sorting?

Otherwise I will try to set up a small test thing, but it may be a lot of work, so maybe we can get around it

allan

Does the language setting has an effect on the sorting?

It shouldn't if you are using data-order, no.

An example showing the broken state would be really useful. It is hard to debug something that is working as expected, as the current example appears to be

Allan

Pichutan

Hello @allan ,

We found the issue, it seems indeed to be an issue with the German language file.

The updated fiddle you can find here: https://jsfiddle.net/Pichutan/j032pwmc/11/

Pichutan

choc

FYI: This issue is not just with German, but also with French.

I also tried with zh-HANT, it is working as expected though.

See this demo: https://jsfiddle.net/7dsnpmva/

allan

Thanks for the example! The German and French language files are setting the language.decimal option and that is effect the data-order property, which I think is wrong. I'll look into what I can do about that.

Allan

allan

I've been looking into this, and if the translation file includes language.decimal then any data returned from the rendering function (which is what happens with data-order) will be passed through a transformer to convert the given decimal to a period decimal.

Unfortunately, I don't think that is correct if data-order is specified, I think that should be the IEEE number and shouldn't be reformatted. The problem is that is fairly baked into DataTables (the transform happens after the renderer).

The workaround is to override the language file's definitions:

new DataTable('#example', {
    language: {
        url: '//cdn.datatables.net/plug-ins/2.2.0/i18n/de-DE.json',
        thousands: ',',
        decimal: '.'
    },
});

https://jsfiddle.net/xut71w4p/

I need to have a proper think about how to handle this better - but that might be a DataTables 3 thing as I'm worried about any change here and backwards compatibility.

Allan

rf1234

The workaround is to override the language file's definitions

Not really because that will change the formatting of your data tables' footers like this for example:

In German this means we have 22.098 table entries while in reality we have 22,098 table entries which in German is: 22.098.

It should be looking like this instead:

When I switch to English my display changes like this:

allan

Oh that's annoying! Well spotted. Yup, I need to come up with a better way to handle this.

The other workaround is to use comma decimal place numbers in data-order at the moment. That probably fixes it in stone since any change will be a backwards compatibility nightmare.

allan