Saving dates back to database with Editor form and Inline editing

RyanG94

Hi,

I am having an issue with saving dates back to my database in the correct format, i am using both inline and the editor form to edit the dates. They are loaded into the view with Json and then rendered as DD/MM/YYYY. I need to be able to edit them and then submit the form back to the controller. Dates that have been edited submit back correctly, however if a date is not edited then it is passed back as LatestDate: #1/1/0001 12:00:00 AM#.

data: "LatestDate",
title: "Latest Date",
type: 'date',
className: "dt-head-center dt-body-center editable",
render: DataTable.render.datetime('DD/MM/YYYY'),
width: "80px"

Edtior Form below

 function convertJsonDateToShortDate(data) {
                const dateValue = new Date(parseInt(data.substr(6)));
                return dateValue.toLocaleDateString();
            }

            function isShortDateFormat(dateString) {
                const regex = /^\d{2}\/\d{2}\/\d{4}$/;
                return regex.test(dateString);
            }
            

                

            // build editor form
            editor = new $.fn.dataTable.Editor({
                table: "#dtPurchaseOrderExpedites",
                idSrc: 'OrderNo',
                formOptions: {
                    main: {
                        focus: 4
                    }
                },
                template: '#customForm',
                fields: [
                    {
                        label: 'Latest date:',
                        name: 'LatestDate',
                        type: 'datetime',
                        format: "DD/MM/YYYY",
                        data: function (data) {
                            if (!isShortDateFormat(data.LatestDate)) {
                                data.LatestDate = convertJsonDateToShortDate(data.LatestDate);
                            }
                            return data.LatestDate;
                        }
                    }            
                ],

            }); //End Editor

Thanks in advance for any ideas.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

What is the "wire format" - i.e. when you load the data into the DataTable, what is it read as? ISO8601?

I would strongly suggest you remove the data function from the Editor initialisation. It can be used as a getter and setter, but fields.getFormatter and fields.setFormatter are much easier to work with.

Allan

RyanG94

Hi Alan, the date is loaded in as a timestamp. I have attached a screenshot of when i remove the rendering completely. i had tried using the Get and Set formatters to deal with the dates but wasnt able to get it to display correctly.

I have included the code below, i may have made a mistake in it somewhere as the field was still displaying the timestamp.

 {
                        label: 'Latest date:',
                        name: 'LatestDate',
                        type: 'datetime',
                        format: 'DD/MM/YYYY', // Format to display to the end user
                        getFormatter: function (val) {
                            // Convert the timestamp to a human-readable date format
                            return moment(val).format('DD/MM/YYYY');
                        },
                        setFormatter: function (val) {
                            // Convert the human-readable date format back to the timestamp format
                            return moment(val, 'DD/MM/YYYY').valueOf();
                        }
                    }

allan

Ah good old .NET. I presume you are Ajax loading the data for the DataTable? What script are you using to do that? Are you using our .NET libraries for Editor, or something else? Can the conversion to ISO8601 be done there?

Allan

RyanG94

Yeah i am using an ajax post to the load the Json into the datatable.

  "ajax": {
                        url: "/PurchaseOrderExpedite/GetPurchaseOrderExpedites",
                        data: { cG: '@ViewBag.cG' },
                        type: "POST",
                        dataType: 'json',
                        dataSrc: "",
                }

I have not been using the .NET libraries, i am not aware of how this works. Our controller returns a Json result with a collection of lines in an object. The object is made up of the properties in the model in which mine is a Date field.
We are quite new to this so may not be making the best use of all the tools available to us.