Decimal with Comma in Table Editor Data

titechno

Description of problem:
I have the problem that when editing a decimal number with inline editing the number gets displayed with a "." instead of "," as a decimal seperator (I live in germany where comma is used as the decimal seperator). When someone tries to change one number and doesnt correct the . to a , the program ignores the . and the number gets stored without a seperator (So the number gets multiplied by ten).

I have solved it for the initial table with another thread: https://datatables.net/forums/discussion/24626

Like this:

        language: {
            url: '/lang/de_de.json',
            decimal: ',',
            thousands: '.'
        },
        ajax: {
            url: "/Gesellschafter/GesellschafterReportingApi"
            ,
            dataSrc: function (json) {
                if (json.data) {
                    for (var i = 0, ien = json.data.length; i < ien; i++) {
                        json.data[i].UmsatzGesellschafter.Umsatz = formatNumbers(json.data[i].UmsatzGesellschafter.Umsatz);
                        json.data[i].UmsatzGesellschafter.Menge = formatNumbers(json.data[i].UmsatzGesellschafter.Menge);
                    }
                    return json.data;
                }
            }
        },

and the format function

function formatNumbers(data) {
    if (data == null) return data;
    var rounded = data.toFixed(2);
    var str = rounded.toString();
    var num = str.replace(".", ",");
    return num;
}

So initially all the data gets correct and the point is replaced by a comma.

The Problem occurs when a number gets edited. After editing a row the data for that row gets reloaded and the old problem occurs.

Please somebody got any idea how to solve this, is there an event for data row update I could use?

titechno

I solved the problem with a getFormator on the server side like this:

                    .Field(new Field("UmsatzGesellschafter.Menge").Validator((val, d, host) => isDecimal(val, true)
                            ? null
                            : "Bitte geben Sie eine gültige Zahl an"
                            )
                            .SetFormatter((val, data) => formatDecimalSet(val))
                            .GetFormatter((val, data) => formatDecimalForEditSet(val))
                            )

With the formatting function being like this:

        private string formatDecimalForEditSet(object val)
        {
            if (val.ToString().Equals("") || val == null)
            {
                return "";
            }
            else
            {
                return val.ToString().Replace('.', ',');
            }
        }

rf1234

In case you need to expand your solution with various number formats:

My solution is bilingual. It needs to comply with English and German number formatting depending on the selected language.
I use get and set formatters on the server in order to avoid client side reformatting. To make sure the numbers are in the right format I use https://plentz.github.io/jquery-maskmoney/ to mask the user input fields accordingly.

Here are my two little helper functions that assign the right masks depending on the class that I add dynamically:

//maskMoney plugin: http://plentz.github.io/jquery-maskmoney/
function maskAmount() {
    //amount fields are defined as DEC[15,2] in MySql Database    
    $('.amountEnglish').maskMoney({allowZero: true, allowNegative: true});
    $('.amountEnglishAllowEmpty').maskMoney({allowZero: true, 
                                  allowNegative: true, allowEmpty: true});
    $('.amountEnglishNoDecPlaces').maskMoney({allowZero: true,
                                   allowNegative: true, precision: 0});
    $('.amountGerman').maskMoney({thousands: '.', decimal: ',',allowZero: true,
                                  allowNegative: true});
    $('.amountGermanAllowEmpty').maskMoney({thousands: '.', decimal: ',',allowZero: true,
                                  allowNegative: true, allowEmpty: true});
    $('.amountGermanNoDecPlaces').maskMoney({thousands: '.', decimal: ',',
                          allowZero: true, allowNegative: true, precision: 0});
    $('.amountEnglish, .amountEnglishNoDecPlaces, \n\
       .amountGerman,  .amountGermanNoDecPlaces').each(function() {
        if ($(this).val() == 0 || $(this).val() == '0,00' || $(this).val() === '-') {
            $(this).val('');
        }
    });
    $('.amountEnglishAllowEmpty, .amountGermanAllowEmpty').each(function() {
        if ($(this).val() === '-') {
            $(this).val('');
        }
    });
}

//maskMoney plugin: http://plentz.github.io/jquery-maskmoney/
function maskPercentage() { 
    $('.percentEnglishNoDecPlaces').maskMoney(
            {allowZero: true, allowNegative: true,
             precision: 0, suffix: ' %'});    
    $('.percentGermanNoDecPlaces').maskMoney(
            {thousands: '.', decimal: ',',allowZero: true, allowNegative: true,
             precision: 0, suffix: ' %'});   
    $('.percentEnglish').maskMoney(
            {allowZero: true, allowNegative: true,
             precision: 3, suffix: ' %'});    
    $('.percentGerman').maskMoney(
            {thousands: '.', decimal: ',',allowZero: true, allowNegative: true,
             precision: 3, suffix: ' %'});   
    $('.percentEnglishNoDecPlaces, .percentGermanNoDecPlaces, \n\
       .percentEnglish, .percentGerman').each(function() {
        if ($(this).val() == 0 || $(this).val() == '0,000' || $(this).val() === '-') {
            $(this).val('');
        }
    });
}