Decimal and thousands separator

GeorgeHelmke

I need to have numbers in an editable table showing as such: 1.000,23. The comma decimal separator is the most important.
I seem to be stuck with 1,000.23.

Using the example in http://datatables.net/reference/option/language.decimal, I made my specification look like this:

    table = $('#MainTable').DataTable(
   {
       bServerSide: true,
       bProcessing: true,
       bFilter: false,
       bPaginate: false,
       info: false,
       ordering: false,
       sServerMethod: "POST",
       sAjaxSource: "@Url.Action("All", "SalesLine")",

       "fnServerParams": function (aoData) {
           aoData.push({ "name": "id", "value": id });
           aoData.push({ "name": "document_type", "value": document_type });
       },

       aoColumns:
       [
           { mData: "DT_RowId", sTitle: "DT_RowId", "bVisible": false },
           { mData: "POrderType", sTitle: "@ResHelper.Loc("POrderType")" },
           { mData: "No_", sTitle: "@ResHelper.Loc("No_")" },
               {
                   "class": 'details-control',
                   "orderable": false,
                   "data": null,
                   "defaultContent": '<button id="FindItem" name="FindItem" type="button" class="btn btn-default btn-xs" data-toggle="tooltip" data-placement="left" title="@ResHelper.Loc("SearchForItem")">  <span class="glyphicon glyphicon-search"></span></button>'
               },

           { mData: "Description", sTitle: "@ResHelper.Loc("Description")" },
           { mData: "Quantity", sTitle: "@ResHelper.Loc("Quantity")" },
           {
                   "class": 'details-control',
                   "orderable": false,
                   "data": null,
                   "defaultContent": '<button id="OpenCalc" name="OpenCalc" type="button" class="btn btn-default btn-xs"  data-toggle="tooltip" data-placement="left" title="@ResHelper.Loc("OpenConfigurator")">  <span class="glyphicon glyphicon-cog"></span></button>'
           },

           { mData: "LineAmount", sTitle: "@ResHelper.Loc("LineAmount")" },
           { mData: "LineDiscount", sTitle: "@ResHelper.Loc("LineDiscount")" },
           { mData: "LineDiscountAmount", sTitle: "@ResHelper.Loc("LineDiscountAmount")" },
           { mData: "Additional_Quantity", sTitle: "@ResHelper.Loc("Additional_Quantity")" },
           { mData: "Additional_Quoted_Price", sTitle: "@ResHelper.Loc("Additional_Quoted_Price")" /*, "mRender": function (data, type, full) { return formatNumbers(data); }*/ },
           { mData: "External_Job_Description", sTitle: "@ResHelper.Loc("External_Job_Description")" },
           { mData: "Document_Type", sTitle: "@ResHelper.Loc("Document_Type")", "bVisible": false },
           { mData: "DocumentNo_", sTitle: "@ResHelper.Loc("DocumentNo_")", "bVisible": false },
           { mData: "LineNo_", sTitle: "@ResHelper.Loc("LineNo_")", "bVisible": false },
           {
               "class": 'details-control',
               "orderable": false,
               "data": null,
               "defaultContent": '<button id="DeleteRecord" name="DeleteRecord" type="button" class="btn btn-default btn-xs"  data-toggle="tooltip" data-placement="left" title="@ResHelper.Loc("DeleteRecord")">  <span class="glyphicon glyphicon-minus"></span></button>'
           },
           {
               "class": 'details-control',
               "orderable": false,
               "data": null,
               "defaultContent": '<button id="LoadFile" name="LoadFile" type="button" class="btn btn-default btn-xs"  data-toggle="tooltip" data-placement="left" title="@ResHelper.Loc("LoadFile")">  <span class="glyphicon glyphicon-import"></span></button>'
           }
    ],

       "oLanguage": {
           "decimal": ",",
           "thousands": "."
       },
       "aLengthMenu": [[10, 25, 50, -1], [10, 25, 50, "@ResHelper.Loc("All")"]]
});

with an editor spec like this:

    editor = new $.fn.dataTable.Editor({
        ajax: "@Url.Action("Save", "SalesLine")",
        table: "#MainTable",
        fields: [
            { label: "@ResHelper.Loc("POrderType"):", name: "POrderType" },
            { label: "@ResHelper.Loc("No_"):", name: "No_" },
            { label: "@ResHelper.Loc("Description"):", name: "Description" },
            { label: "@ResHelper.Loc("Quantity"):", name: "Quantity" },

            { label: "@ResHelper.Loc("LineAmount"):", name: "LineAmount" },
            { label: "@ResHelper.Loc("LineDiscount"):", name: "LineDiscount" },
            { label: "@ResHelper.Loc("LineDiscountAmount"):", name: "LineDiscountAmount" },
            { type: "readonly", label: "@ResHelper.Loc("Additional_Quantity"):", name: "Additional_Quantity" },
            { type: "readonly", label: "@ResHelper.Loc("Additional_Quoted_Price"):", name: "Additional_Quoted_Price" },
            { label: "@ResHelper.Loc("External_Job_Description"):", name: "External_Job_Description" },
            { label: "@ResHelper.Loc("Document_Type"):", name: "Document_Type" },
            { label: "@ResHelper.Loc("DocumentNo_"):", name: "DocumentNo_" },
            { label: "@ResHelper.Loc("LineNo_"):", name: "LineNo_" }
        ]
    });

allan

bServerSide: true,

This means that the processing it all being done at the server-side - including sorting, etc. The server-side doesn't know anything about your language configuration in the DataTable on the client-side, so the language setting there will have no effect.

In your script "@Url.Action("All", "SalesLine")", you would need to modify the database read to return the required format - or you could some kind of formatter.

Which field it is specifically, and what database are you using?

Allan

GeorgeHelmke

I do not care about sorting at this juncture.

I do some processing client side using render, which I took out for this situation. I can swap comma and period for display purposes.

Here is what I need, and it happens client side:
When the user goes to edit a field, that it is shown as 12,34. Not 12.34. And that it accepts the comma.

allan

Can you show me the raw JSON data that is being loaded form your server-side script please? The Editor form will just display that raw data, so ideally the serve r would be returning data in the format you want. Otherwise a loop will be needed to reformat it on the client-side.

Allan

GeorgeHelmke

Here it is, and I can see at once it is coming to the table as . decimal. So that is the server issue I will need to address. Would that mean that the editor is just doing a "garbage in - garbage out"?

{"sEcho":1,"iTotalRecords":0,"iTotalDisplayRecords":100000,"aaData":[{"Document_Type":"Order","DocumentNo_":"800620","LineNo_":10000,"POrderType":"OFFSET","No_":"10100","ShipmentDate":"\/Date(-62135596800000)\/","Description":"Stiftet hefte PLUSS omslag","Description2":null,"UOM":null,"Quantity":1000,"Lineount":40902.66,"LineDiscount":-16,"Linet":-5641.75,"Unice":35.26091,"External_Job_Description":"1.000 Brosjyrer, 32 sider i format A4, i 4+4 farger","ComponentName":null,"Pages":32,"FormatCode":"A4","ColorsFront":4,"ColorsBack":4,"Paper":"ANT0938694","ConfigurationStatus":0,"CalculatedAmount":0,"OrderType":null,"Comments":"","ItemDescription":null,"LineDiscountType":null,"DepartmentCode":null,"Status_Code":null,"Additional_Quoted_Price":0,"Additional_Quantity":1000,"DT_RowId":"800620|Order|10000"}]}

allan

Basically yes. The value is a standard (in the sense of programming languages) decimal number, so Editor just shows that. The same would apply to any other value, not just numbers.

So there are a number of options:

1. The best thing to do would be to instruct the database server to use commas as the decimal separator. I'm not sure how many databases support that though!
2. Reformat the original number, at the server, on both read and write (1, coming from the db to the client, and 2, from the client to the db)
3. Use ajax.dataSrc to reformat the number. The trouble here is you'll need to "deformat" it when submitting to the server.

Presumably you don't want your user to need to put in thousands separators? So you might actually need two numbers in the data - one used by the DataTable for display and the other used for Editor.

Allan

GeorgeHelmke

Fine, I'll sort it out. Good to know why, and what limitatio