Language, "decimal point is comma" not working

rf1234

Hi Allan,
I am using this in the table default settings:

    //Data tables language settings
    if (lang === 'de') {
        showAllLabel = 'Alles anzeigen';        
        $.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"
                },
                select: {
                    rows: {
                        _: '%d Zeilen ausgewählt',
                        0: 'Zeile anklicken um auszuwählen',
                        1: 'Eine Zeile ausgewählt'
                    }
                }
            }            
        } );        
    }
    //custom button added with language specific label
    $.fn.dataTable.ext.buttons.showAll = {
        text: showAllLabel,
        className: 'btn-showall-color hidden'
    };

Everything works except for

"decimal": ",",
"thousands": ".",

The mySql database field is defined as DECIMAL(15,2). A value of e.g. 60000 is shown in datatables as 60000.00 (as it is in a mySql workbench query). So the thousands point is missing and the decimal point is not converted to a decimal comma!
Any solution for this?

allan

Those parameters don't effect the data that is displayed in the table - they are used primarily for the table information output, but also for the sorting information.

Use the number renderer if you want to change the format of the number that it output by SQL.

Allan

rf1234

Unfortunately I couldn't get the number renderer to work because I am already using something else for rendering:

{   data: null,
    render: function ( data, type, row ) {
           if (lang == 'de') {
               return row.rfp.amount + ' ' + row.rfp.currency;
            } else {
                return row.rfp.currency + ' ' + row.rfp.amount;
            }
      }    
 },

I have no idea how to integrate the number helper into this. I have multiple currencies and depending on the language the currency needs to be displayed before or after the amount. Do you have an idea how to resolve this?

I fixed it on the server side with PHP like this:

Editor::inst( $db, 'rfp' )
->field(
Field::inst( 'rfp.amount' )
//->validator( 'Validate::numeric', array('required' => true, 'message' => $msg[0]) )
->getFormatter( function($val, $data, $opts) {
    if ($_SESSION['lang'] === 'de') {     
        return number_format($val, 2, ',', '.');
    } else {
        return number_format($val, 2);
    }
})
->setFormatter( function($val, $data, $opts) {
    //explode to get rid of the 1,000 separators
    if ($_SESSION['lang'] === 'de') {     
        $numberArray = explode('.', $val);
    } else {
        $numberArray = explode(',', $val);
    }
    //implode without delimiter to join the pieces again
    $numberString = implode($numberArray);
    //replace the German decimal comma with a period
    if ($_SESSION['lang'] === 'de') {   
        $numberString = str_replace(',', '.', $numberString);
    }
    return $numberString;
})

MySql accepts this without any issue as long as the German 50.000.000,97 or the English 50,000,000.97 are transformed into 50000000.97. (The MySql field format is DECIMAL(15,2)).

Another issue is that validator does not work very well in this environment either. It rejects German numbers like -50.000.000,99 because it thinks they are not numeric.
Any idea? Many thanks in advance.

allan

I have no idea how to integrate the number helper into this.

You can make use of the number renderer built into DataTables directly by using:

$.fn.dataTable.render.number( .... ).display( numberToRender );

It might be easier to do something like:

var numberRenderer = $.fn.dataTable.render.number( .... ).display;

and then simply use:

numberRenderer( numberToRender );

whenever you want to render a number.

Another issue is that validator does not work very well in this environment either. It rejects German numbers like -50.000.000,99 because it thinks they are not numeric.

The numeric validator has a decimal option which you can use to tell it what to expect for the decimal place. You'd need to dynamically switch that based on the user's session language.

Allan

rf1234

Thank you, Allan! You are doing a great job and your product is awesome!