Problem sorting formatted numbers

rf1234

I have a column containing formatted amounts. If I sort them I get this result (German rendering):

-101,97 EUR
-131,54 EUR
-191,78 EUR
-31,81 EUR
-68,00 EUR

If I render them the English way I get this:

EUR -101.97
EUR -131.54
EUR -191.78
EUR -31.81
EUR -68.00

If I sort the other way around the result is equally wrong:

EUR -68.00
EUR -31.81
EUR -191.78
EUR -131.54
EUR -101.97

I guess data tables does not detect that these are formatted numbers and sorts them like strings. What could I do about it? I do all the number formatting on the server side with PHP.

In javascript I only do this:

columns: [
        {   data: "cashflow.interest_fee",
                render: function ( data, type, row ) {
                    return renderAmountCurrency(data, row.cashFlowCurrency);
                }    
            }
]

function renderAmountCurrency (amount, currency) {
    if (amount == '' || amount =='0') {
        return amount;
    } else {
        if (lang == 'de') {
            return amount + ' ' + currency;
        } else {
            return currency + ' ' + amount;
        }
    }
}

rf1234

I dropped the currency to get closer to the problem:
German rendering:

-101,97
-131,54
-191,78
-31,81
-68,00

English rendering:

-31.81
-68.00
-101.97
-131.54
-191.78

Looks like data tables can't handle the currency and it doesn't like a decimal comma either. I looked at the sorting plug ins and saw that this was all supposed to be working with data tables. The only plugin I use is the moment date time sorting plugin which works perfectly for me with only one line of code.

I am using Data Tables 1.10.15

rf1234

@allan, got the problem solved now by using this deprecated(!) plugin:
https://datatables.net/plug-ins/sorting/formatted-numbers

With the plugin I have no issues with any kind of format including the currency.

"DataTables 1.10+ has formatted number detection and sorting abilities built- in. As such this plug-in is marked as deprecated, but might be useful when working with old versions of DataTables."

Looks like there is a bug in current data tables version 1.10.15 because the plugin is still required. The issue is probably that the automatic detection of formatted numbers does not work. Using the plugin you need to tell it which columns should be sorted as formatted numbers. That is not really comfortable but better than wrong sorting. Please advise!

allan

I guess data tables does not detect that these are formatted numbers and sorts them like strings.

Correct - because they contain non-numeric data. DataTables would automatically detect it if you used €101 for example, but not EUR.

Instead what you need to do is use a plug-in such as this one.

That plug-in won't correctly handle the comma for a decimal character though - you'd need to modify the plug-in slightly to let it cope with that.

Allan

rf1234

Well, that does not explain why data tables isn't recognizing the German rendered amounts even without any currency code or sign (see above). There is a decimal comma issue in the current data tables version regardless of whether or not a currency sign or an ISO currency code is included.

In addition it would be great if you recognized the standard ISO-currency codes:
https://en.wikipedia.org/wiki/ISO_4217

In many cases it just isn't handy to use the symbols like € or $. The sterling sign isn't even on my keyboard ... And for many currencies there are no symbols to use.

I am already using the plug-in you mentioned now. So I have a work around. That plug-in has no decimal comma issue. It works perfectly fine!

allan

There is a decimal comma issue in the current data tables

Have you specified the language.decimal option? It appears to work okay in this example.

Documentation for it is available here.

In addition it would be great if you recognized the standard ISO-currency codes

That's not something that is going to be added to DataTables core - but it sounds like a perfect candidate for a plug-in!

Regards,
Allan

rf1234

Yes, I have it in the default settings like this. But it does not seem to have any effect. Everything else works fine ...

    //Data tables language settings
    if (lang === 'de') {      
        $.extend( true, $.fn.dataTable.defaults, {
            "language": {
                "decimal": ",",
                "thousands": ".",
                "info": "Anzeige _START_ bis _END_ von _TOTAL_ Einträgen",
                "infoEmpty": "Keine Einträge",
                "infoPostFix": "",
                "infoFiltered": "(gefiltert aus insgesamt _MAX_ Einträgen)",
                "loadingRecords": "Bitte warten Sie - Daten werden geladen ...",
                "lengthMenu": "Anzeigen von _MENU_ Einträgen",
                "paginate": {
                    "first": "Erste",
                    "last": "Letzte",
                    "next": "Nächste",
                    "previous": "Zurück"
                },
                "processing": "Verarbeitung läuft ...",
                "search": "Suche:",
                "searchPlaceholder": "Suchbegriff",
                "zeroRecords": "Keine Daten! Bitte ändern Sie Ihren Suchbegriff.",
                "emptyTable": "Keine Daten vorhanden",
                "aria": {
                    "sortAscending":  ": aktivieren, um Spalte aufsteigend zu sortieren",
                    "sortDescending": ": aktivieren, um Spalte absteigend zu sortieren"
                },
                //only works for built-in buttons, not for custom buttons
                "buttons": {
                    "create": "Neu",
                    "edit": "Ändern",
                    "remove": "Löschen",
                    "copy": "Kopieren",
                    "csv": "CSV-Datei",
                    "excel": "Excel-Tabelle",
                    "pdf": "PDF-Dokument",
                    "print": "Drucken",
                    "colvis": "Spalten Auswahl",
                    "collection": "Auswahl",
                    "upload": "Datei auswählen...."
                },
                "select": {
                    "rows": {
                        _: '%d Zeilen ausgewählt',
                        0: 'Zeile anklicken um auszuwählen',
                        1: 'Eine Zeile ausgewählt'
                    }
                }
            }            
        } );        
    } else {
        $.extend( true, $.fn.dataTable.defaults, {
            "language": {
                "select": {
                    "rows": {
                        _: '%d rows selected',
                        0: 'Click row to select',
                        1: '1 row selected'
                    }
                }
            }
        } );
    }

allan

If you have a link to a test case showing the issue, I'd be happy to look into it.

Allan

rf1234

Thanks for now, Allan. I appreciate your help very much! All of the amounts I am displaying are preceded (English) or followed (German) by an ISO currency code. For that reason the standard data tables functionality won't work for me anyway.

I am happy to use the plug in you recommended. It has no issue with the ISO code and decimal commas. The only downside is that you have to declare which columns the plug in should consider as number formatted in columnDefs. No big deal either.

After working with data tables and Editor for a while I would like to reiterate that I still like the product very much and what is really good is that there is always a way to overcome any roadblock one might run into. Particularly due to your awesome support!

allan

The only downside is that you have to declare which columns the plug in should consider as number formatted in columnDefs.

A type detection plug-in could be used to resolve that. Perhaps something like:

$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
  if ( data.match(/\d/) ) {
    return 'formatted-num';
  }
  return null;
} );

The problem with that is that it is really aggressive. It would match date columns for example. Possibly you could add a check for an ISO code into it as well (.indexOf(...)) to make sure that it doesn't capture to much!

I still like the product very much and what is really good is that there is always a way to overcome any roadblock one might run into. Particularly due to your awesome support!

Thank you

Allan

rf1234

Hi Allan, the formatted-num plugin seems to have a bug. It thinks that -1.000.000,00 EUR is bigger than -800.000,00 EUR. The problem only occurs with a) German number formatting and b) if the number of digits is different.

I decided to roll my own and also do the auto detection of formatted-num fields based on the ISO currency codes:

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTableExt.oSort, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.replace(".", "");
            a = a.replace(",", ".");
        } else {
            a = a.replace(",", "");
        }
        a = a.replace( /[^\d]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
            return 0;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );
//fields must contain an ISO currency code to be detected as formatted-numbers
//since all table fields with ISO codes always contain a number this works.
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
    var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                 'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];      
    var i=0;
    while ( cur[i] ) {
        if ( data.search( cur[i] ) > -1 )   {
            return 'formatted-num';
        }
        i++;
    }
    return null;
} );

rf1234

@allan: I am not getting it done; I have no idea what is wrong. It still does not sort negative numbers correctly. -1,000, 000.00 is considered to be bigger than -100,000.00. I have attached a pdf. I sorted by column "Amount Remaining". Actually the result is as if I had nothing at all and just used the regular sorting without any extension.

Here is my code (based on the formatted-num plugin):

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTableExt.oSort, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.replace( /[\.]/g, "" );
            a = a.replace( /[\,]/g, "." );
        } else {
            a = a.replace( /[\,]/g, "" );
        }
        a = a.replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
            return '';
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
             'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];    

//fields must contain an ISO currency code to be detected as formatted-numbers
//since all table fields with ISO codes always contain a number this works.
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
    if (typeof data !== 'undefined') {
        if (data !== null) {
            var i=0;
            while ( cur[i] ) {
                if ( data.search( cur[i] ) > -1 )   {
                    return 'formatted-num';
                }
                i++;
            }
        }
    }
    return null;
} );

Set breakpoints with my debugger. While "$.fn.dataTable.ext.type.detect.unshift" was triggered and worked properly "$.extend( $.fn.dataTableExt.oSort" never got started. I had set the breakpoint at line "if (lang == 'de') {"

allan

Could you run the debugger on the table for me please?

Thanks,
Allan

rf1234

Hi Allan,
this is the debugger reference: ocutel

The page has 5 tables. I sort the 4th Table (#tblCashFlow) by column 5 Amount Remaining cashflow.amount_remaining

rf1234

@allan, now it gets really weird ... Even though "$.fn.dataTable.ext.type.detect.unshift" is being triggered and returns the proper value for columns containing an ISO currency code it still seems to cause the problem somehow.

Because if I add this to columnDefs

{ type: 'formatted-num', targets: 5 }

it suddenly works!

So somehow the output of "$.fn.dataTable.ext.type.detect.unshift" is being ignored by data tables while the hard coded version above is being executed.

debugger reference for this is apuqob

allan

Another possibility is that the type detection plug-in isn't matching the data you expected.

This is the code:

function(data) {
    if (typeof data !== 'undefined') {
        if (data !== null) {
            var i = 0;
            while (cur[i]) {
                if (data.search(cur[i]) > -1) {
                    return 'formatted-num';
                }
                i++;
            }
        }
    }
    return null;
}

Which of the five tables in the debug trace is it that we are looking at specifically here?

Allan

rf1234

I sort the 4th Table (#tblCashFlow) by column 5 Amount Remaining cashflow.amount_remaining

rf1234

Hi Allan,

"Another possibility is that the type detection plug-in isn't matching the data you expected."

Well, I stepped through this with the debugger and it is matching the data properly. I also use the exact same logic here and it works as expected:

var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                 'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];

//custom button for cashflow csv generation
$.fn.dataTable.ext.buttons.csvCashFlow = {
    extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
                   filename: 'cashFlow',
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                // Strip $ from salary column to make it numeric
                if (typeof data !== 'undefined') {
                    if (data !== null) {  
                        if ( column > 11 ) {
                            return '';  //get rid of the changed manually column
                        }
                        var i=0;  //get rid of the currency ISO codes
                        while ( cur[i] ) {
                            if ( data.search( cur[i] ) > -1 )   {
                                data = data.split(cur[i]).join("");
                            }
                            i++;
                        }
                        //rate must be adjusted to fraction to be able to calculate
                        if (column == 6) {
                            if (data !== '') {
                                if (lang === 'de') {
                                    //replace the only comma with a period
                                    data = data.replace(",", ".");
                                }
                                data = data / 100;
                                if (lang === 'de') {
                                    //replace the only period with a comma
                                    data = data.toString();
                                    data = data.replace(".", ",");
                                }
                            }
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data !== null) {
                        if ( column > 11 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};

Any further ideas please?

allan

Hi,

Looking at the debug trace for #tblCashFlow, it shows that there is a row with the following data:

1. Loan, Variable Rate
2. Balance due on
3. 20/09/2027
4. 20/09/2027
5. 20/09/2027
14. Visible
15. 41

Its the first row in the data set for the table. Where is that coming from - I don't see it in the JSON response, but I also don't see RowGroup being used for that table.

It looks to me like it is that data that wouldn't match the type detection since there is no currency in the columns where there is currency in other rows.

Allan

rf1234

Yes, such a row exists it is actually not the first but the final row in the table. (Columns 3 and 4 (in your list 4. and 5.) are hidden in the standard display). The row indicates the final balance which is nothing because this it is a full repayment loan.
I wasn't aware that it is not detected if the first row of a column does not contain the ISO code. Since Editor does not allow me to use ORDER BY I can't even change this or can I? I replaced the space in column 5 (in your list it is 6.) with "0 Euro" and it worked fine.

So is this only an issue if the first row does not comply? Or is it an issue if any one row of a column does not comply? If the former is the case: is there a way for me to order the rows on the server properly so that the issue isn't there?

rf1234

To be more precise: What I would really need is an auto-detect that detects the column if there is at least one row of the respective column that complies. Anything else is likely to cause problems.

allan

The first row isn't complying with the auto type detection as is. The simple solution would be to modify the type detection plug-in to allow for an empty string. Then modify the corresponding sort plug-in to treat an empty string as -Infinity or something.

Allan

rf1234

It's not that easy I am afraid ... but can you tell me do all rows of a column need to comply or just the first one?

allan

but can you tell me do all rows of a column need to comply or just the first one?

All cells in the column must comply or it won't be matched.

Daft question perhaps, but why can't you add a check for an empty string?

Allan

rf1234

Good morning Allan,
sure I can add a check for an empty string ... Just wanted to make sure that this will not recognize other string columns that contain empty cells as formatted numbers. For that reason it is very good news for me that ALL cells in a column must comply! Maybe you can add that hint to the docs?!

So here is my final solution:

var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
             'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];

//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 ( data.search( cur[i] ) > -1 )   {
                    return 'formatted-num';
                }
                i++;
            }
            if ( data === '') {
                return 'formatted-num';
            }                
            if (lang == 'de') {
                if ( ! moment(data, 'L', true).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.replace( /[\.]/g, "" );
            a = a.replace( /[\,]/g, "." );
        } else {
            a = a.replace( /[\,]/g, "" );
        }
        a = a.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;
    }
} );

allan

Good to hear you have it working.

The fact that it checks all cells is implicit in:

a companion type detection plug-in that will automatically scan a column's data and determine if the ordering plug-in can order the data in the column

But I'll look at making that more explicit.

Thanks for the feedback.

Allan

rf1234

I need it very explicit, Allan. English is not my first language as you will have noticed for sure