Format / Display issue of Date & Currency in Custom Form (Editor) (using mask.js and moment.js)

OT@CODAN · October 2023

Link to test case: https://live.datatables.net/wejaxone
Debugger code (debug.datatables.net): https://debug.datatables.net/isorax
Error messages shown: -
Description of problem:
Hi!

I work with datatables since a year or so. We bought Editor licence a few months ago, and now we have a project using the Editor Components first time.
We are developing ASP .NET (7) Core Webapps. Currently, I have a Net Core Backend and work with Serverside option (AJAX / Editor Call).
The table data is displayed as I should, no problems here.
The issue I am facing currently is quite strange. If I open the CustomForm first time, the displayed date is not completely rendered (?). The Date (Datum) is empty, and the Currency (Budget...) is displayed unformatted without dots or a comma:

If I close the modal, open it a second time, everything is displayed correct!?

This happens on every row, every time on first try. Second try is always fine. Debugging is fine as I also tried working with
"initEdit" with Date and Currency. The Data given to the form/modal is correct.

Searching the forum did not show any others having the same issue, sadly.

I tried to create a testcase, but the initialization of the Editor Component fails.. Maybe I did something wrong In the Test Environment?

Thank you!
Chris

allan · October 2023

Hi Chris,

It looks like it was just a load order issue with the include files for the test case, and also a few libraries missing. I've added them to get it running, but it looks like the name given in Editor doesn't align with the column data and I'm not sure what is meant to happen there?

Also are you Ajax loading the data on your actual page, or is it in the HTML like in the example?

Allan

OT@CODAN · October 2023

Hi Allan,
thank you for your quick response! And also, for fixing the libraries.

Indeed, the data is loaded from AJAX / Serverside.
Here is the matching Serverside - Script:

response = new Editor(db, "dbo.PCO_ProjektBudget")
        .Model<ProjectBudget>()
        .Field(new Field("Kostentraeger")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("KostentraegerPos")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("InvestmentApp")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("InvestmentAppDate")
            .Validator(Validation.DateFormat(
                "yyyy-MM-dd HH:mm:ss", // Use uppercase HH for 24-hour format
                new ValidationOpts { Message = "Please enter a date in the format yyyy-MM-dd hh:mm:ss" }
            ))
            .GetFormatter(Format.DateSqlToFormat("yyyy-MM-dd HH:mm:ss")) // Convert to display format
            .SetFormatter(Format.DateFormatToSql("yyyy-MM-dd HH:mm:ss")) // Convert to database format
        )
        .Field(new Field("FertigungsauftragJahr")
            .Validator(Validation.NotEmpty())
            .Validator(Validation.Numeric())
        )
        .Field(new Field("BudgetLabor")
            //.Validator(Validation.Numeric())
           .SetFormatter((val, data) =>
           {
               decimal value = 0;
               string valor = val.ToString().Replace(",", ".");
               if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
               {
                   value = parsedObsPercentage;
               }
               return value;
           })
        )
        .Field(new Field("BudgetMaterial")
            //.Validator(Validation.Numeric())
            .SetFormatter((val, data) =>
            {
                decimal value = 0;
                string valor = val.ToString().Replace(",", ".");
                if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
                {
                    value = parsedObsPercentage;
                }
                return value;
            })
        )
        .Field(new Field("BudgetExternal")
           .SetFormatter((val, data) =>
           {
               decimal value = 0;
               string valor = val.ToString().Replace(",", ".");
               if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
               {
                   value = parsedObsPercentage;
               }
               return value;
           })
        )
        .Field(new Field("Timestamp")
            .SetValue(DateTime.Now)
        )
        .TryCatch(true)
        .Debug(true)
        .Process(request)
        .Data();

The Data is loaded with those parts into Editor:

var editor = new DataTable.Editor({
    ajax: '/api/GetProjectBudget',

and Datatable:

// Get Data from API
ajax: {
    url: '/api/GetProjectBudget',
    type: 'POST'
},

The Model from this is right here:

    public class ProjectBudget
    {
        [MaxLength(5)]
        public string Kostentraeger { get; set; }

        [MaxLength(2)]
        public string KostentraegerPos { get; set; }

        [MaxLength(8)]
        public string InvestmentApp { get; set; }

        [Precision(3)]
        public DateTime InvestmentAppDate { get; set; }
        [Precision(10, 2)]
        public decimal BudgetLabor { get; set; }
        [Precision(10, 2)]
        public decimal BudgetMaterial { get; set; }
        [Precision(10, 2)]
        public decimal BudgetExternal { get; set; }
        public int FertigungsauftragJahr { get; set; }

        [Precision(3)]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime Timestamp { get; set; }
    }

This is, where the name and label comes from... In the test case, I changed the names to the translation variables I use in C# ASP Net.
I also added the form which we use in the project.

Adding your code:

// Masked input plugin
(function( factory ){
    if ( typeof define === 'function' && define.amd ) {
        // AMD
        define( ['jquery', 'datatables', 'datatables-editor'], factory );
    }
    else if ( typeof exports === 'object' ) {
        // Node / CommonJS
        module.exports = function ($, dt) {
            if ( ! $ ) { $ = require('jquery'); }
            factory( $, dt || $.fn.dataTable || require('datatables') );
        };
    }
    else if ( jQuery ) {
        // Browser standard
        factory( jQuery, jQuery.fn.dataTable );
    }
}(function( $, DataTable ) {
'use strict';


if ( ! DataTable.ext.editorFields ) {
    DataTable.ext.editorFields = {};
}

var _fieldTypes = DataTable.Editor ?
    DataTable.Editor.fieldTypes :
    DataTable.ext.editorFields;


_fieldTypes.mask = {
    create: function ( conf ) {
        conf._input = $('<input/>').attr( $.extend( {
            id: DataTable.Editor.safeId( conf.id ),
            type: 'text'
        }, conf.attr || {} ) );

        conf._input.mask( conf.mask, $.extend( {
            placeholder: conf.placeholder || conf.mask.replace(/[09#AS]/g, '_')
        }, conf.maskOptions ) );

        return conf._input[0];
    },

    get: function ( conf ) {
        return conf._input.val();
    },

    set: function ( conf, val ) {
        conf._input
            .val( val )
            .trigger( 'change' )
            .trigger( 'input.mask' )
            .trigger( 'keyup.mask' );
    },

    enable: function ( conf ) {
        conf._input.prop( 'disabled', false );
    },

    disable: function ( conf ) {
        conf._input.prop( 'disabled', true );
    },

    canReturnSubmit: function ( conf, node ) {
        return true;
    }
};

}));

to my script seem to fix the problem with the date field. This is now displayed well formatted on the first strike

OT@CODAN · October 2023

Any idea about the currency field not displaying correctly?

allan · October 2023

Can you show me how you define the fields for Editor and also the JSON data that is being used to populate the table please?

Allan

OT@CODAN · October 2023

Hi Allan,

sure! Here is the Editor configuration:

response = new Editor(db, "dbo.PCO_ProjektBudget")
        .Model<ProjectBudget>()
        .Field(new Field("Kostentraeger")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("KostentraegerPos")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("InvestmentApp")
            .Validator(Validation.NotEmpty())
        )
        .Field(new Field("InvestmentAppDate")
            .Validator(Validation.DateFormat(
                "yyyy-MM-dd HH:mm:ss", // Use uppercase HH for 24-hour format
                new ValidationOpts { Message = "Please enter a date in the format yyyy-MM-dd hh:mm:ss" }
            ))
            .GetFormatter(Format.DateSqlToFormat("yyyy-MM-dd HH:mm:ss")) // Convert to display format
            .SetFormatter(Format.DateFormatToSql("yyyy-MM-dd HH:mm:ss")) // Convert to database format
        )
        .Field(new Field("FertigungsauftragJahr")
            .Validator(Validation.NotEmpty())
            .Validator(Validation.Numeric())
        )
        .Field(new Field("BudgetLabor")
            //.Validator(Validation.Numeric())
           .SetFormatter((val, data) =>
           {
               decimal value = 0;
               string valor = val.ToString().Replace(",", ".");
               if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
               {
                   value = parsedObsPercentage;
               }
               return value;
           })
        )
        .Field(new Field("BudgetMaterial")
            //.Validator(Validation.Numeric())
            .SetFormatter((val, data) =>
            {
                decimal value = 0;
                string valor = val.ToString().Replace(",", ".");
                if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
                {
                    value = parsedObsPercentage;
                }
                return value;
            })
        )
        .Field(new Field("BudgetExternal")
           .SetFormatter((val, data) =>
           {
               decimal value = 0;
               string valor = val.ToString().Replace(",", ".");
               if (decimal.TryParse(valor, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out decimal parsedObsPercentage))
               {
                   value = parsedObsPercentage;
               }
               return value;
           })
        )
        .Field(new Field("Timestamp")
            .SetValue(DateTime.Now)
        )
        .TryCatch(true)
        .Debug(true)
        .Process(request)
        .Data();

This is the JSON, received from Server:

[
  {
    "DT_RowId": "row_6",
    "Kostentraeger": "45206",
    "KostentraegerPos": "00",
    "InvestmentApp": "23123   ",
    "InvestmentAppDate": "2023-10-12T00:00:00",
    "FertigungsauftragJahr": 2023,
    "BudgetLabor": 16949849.65,
    "BudgetMaterial": 13002221.98,
    "BudgetExternal": 25003339.88,
    "Timestamp": "2023-10-16T08:21:17.063"
  },
  {
    "DT_RowId": "row_5",
    "Kostentraeger": "11122",
    "KostentraegerPos": "11",
    "InvestmentApp": "101     ",
    "InvestmentAppDate": "2023-10-02T00:00:00",
    "FertigungsauftragJahr": 2021,
    "BudgetLabor": 10000,
    "BudgetMaterial": 20000,
    "BudgetExternal": 30000,
    "Timestamp": "2023-10-16T08:21:26.843"
  },
  {
    "DT_RowId": "row_1",
    "Kostentraeger": "12345",
    "KostentraegerPos": "12",
    "InvestmentApp": "2023_101",
    "InvestmentAppDate": "2023-10-04T00:00:00",
    "FertigungsauftragJahr": 2020,
    "BudgetLabor": 12502.99,
    "BudgetMaterial": 19849.84,
    "BudgetExternal": 52454.54,
    "Timestamp": "2023-10-16T08:22:04.89"
  }
]

Thank you,
Chris

allan · October 2023

Thank you. I finally worked it out. The example page was loading jQuery Mask plug-in v 0.9.0. Updating it to 1.14.0 allows it to work: https://live.datatables.net/wejaxone/11/edit . I also had to change the Editor initialisation a little - type: 'mask' and mask: ... are at the top level of the configuration object for each field.

Allan

OT@CODAN · October 2023

Hi Allan,
Thank you. We are getting closer.... I use Mask Plugin v1.14.16. But changing the Editor initialisation did the trick indeed.
But now, the Date behaviour is like before

In the Example, it never shows up, in my code it shows on the second load of the form.

I added this function:

    // Convert Data from Server / DB into Form
    editor.on('initEdit', function (e, node, data) {

    // Convert DateTime to Date
    var inputDate = new Date(data.InvestmentAppDate);
    data.InvestmentAppDate = formatDateTimeToISODate(inputDate);

    console.log(data.InvestmentAppDate);

    //Return Data
    return data;
    });

To make the example show the date on the second time opening the form.

If you have any more ideas?
Thank you in advance,
Chris

allan · October 2023

In the data:

InvestmentAppDate: "2023-10-04T00:00:00",

However the configuration is:

wireFormat: "YYYY-MM-DD",

They need to exactly match.

You can either change the data given in the JSON, or update the wireFormat.

Allan

OT@CODAN · October 2023

Thank you, Allen!

I changed the wireformat to match the received Data.

Everything works now, like expected.

Regards,
Chris

Format / Display issue of Date & Currency in Custom Form (Editor) (using mask.js and moment.js)

Format / Display issue of Date & Currency in Custom Form (Editor) (using mask.js and moment.js)

Answers

Howdy, Stranger!

Categories

DataTables

Format / Display issue of Date & Currency in Custom Form (Editor) (using mask.js and moment.js)

Format / Display issue of Date & Currency in Custom Form (Editor) (using mask.js and moment.js)

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables