Date Field Emptied When Editing Other Fields

mikedmason

Currently I have an issue when I edit another field, it causes my Date field to be blank.

First you can see I can pick a date. And verified the date is stored in the DB.

When editing another field it completely overwrites the date field, and only the date field.

I placed a console.log in the setFormatter and getFormatter of the shopping_cart.due_date to log out the data.
As you can see the initial load pulls a full timestamp which is odd because my formatter should be setting it to YYYY/MM/DD and it is only stored in my DB as YYYY-MM-DD

Front End

jQuery(document).ready(function(){
            var user_session = JSON.parse(session.getItem('user'))
            

            var request_package = {
                                    'request_package' : {
                                        'user': user_session
                                    }
                                    
                                }

            console.log(request_package)
            

            editor = new jQuery.fn.dataTable.Editor({
                ajax: {
                    "type":"POST",
                    "url": api.URL + "/dtapi/shopping_cart/get",
                    "data": request_package
                },
                table: "#shopping_cart_table",
                fields: [
                {
                    
                    name: "shopping_cart.designnumber",
                    type: "readonly"
                },
                {
                    
                    name: "shopping_cart.description",
                    type: "readonly"
                },
                {
                    
                    name: "shopping_cart.po",
                    type: "text"
                },
                {
                    
                    name: "shopping_cart.due_date",
                    type: "date"
                },
                {
                    
                    name: "shopping_cart.special_instructions",
                    type: "text"
                },
                {
                    
                    name: "shopping_cart.quantity"
                    
                   
                }]
            })


            jQuery('#shopping_cart_table').on( 'click', 'tbody td', function (e) {

                var target_cell = e.currentTarget.cellIndex
               

                if(target_cell != 6){
                    var table = jQuery('#shopping_cart_table').DataTable()
                
                    editor.inline( table.cell(this).index() , {
                        onBlur: 'submit'
                    });
                    


                }else{
                    var item_id = e.currentTarget.childNodes[0].childNodes[0].attributes[1].value
                    console.log(target_cell)
                    console.log(e)
                    console.log('item 6 clicked')

                    vthis.remove_from_cart(item_id)
                }
            } );


            jQuery('#shopping_cart_table').DataTable({
                

                dom: "rtip",
                'paging': true,
                'processing': true,
                'serverSide': true,
                "ajax": {
                    "url": api.URL + "/dtapi/shopping_cart/get", //API
                    "type": "POST",
                    "data": request_package
                },
                "columns": [
                    
                    { "data": "shopping_cart.designnumber" },
                    { "data": "shopping_cart.description" },
                    { "data": "shopping_cart.po" },
                    { "data": "shopping_cart.due_date"},
                    { "data": "shopping_cart.special_instructions"},
                    { "data": "shopping_cart.quantity" },
                    {
                        "data": null,
                        "className": "center",
                        "render": function(data, type, full, meta){
                            var item_id = data.shopping_cart.id;
                            console.log(item_id)
                            return '<div class="align-center"><button id="remove_from_cart" item_id="'+item_id+'" class="editor_edit btn red" title="Remove"><svg aria-hidden="true" focusable="false" data-prefix="fas" data-icon="trash" role="img" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 448 512" class="svg-inline--fa fa-trash fa-w-14"><path fill="currentColor" d="M432 32H312l-9.4-18.7A24 24 0 0 0 281.1 0H166.8a23.72 23.72 0 0 0-21.4 13.3L136 32H16A16 16 0 0 0 0 48v32a16 16 0 0 0 16 16h416a16 16 0 0 0 16-16V48a16 16 0 0 0-16-16zM53.2 467a48 48 0 0 0 47.9 45h245.8a48 48 0 0 0 47.9-45L416 128H32z" class=""></path></svg> Remove</button></div>'

                        }
                    }
                ]
            })

            
        });

Back End

dtapi.post('/shopping_cart/get', async function(req, res, next){
    console.log('/shopping_cart/get')
    var request_package = req.body.request_package




    let editor = new Editor(mysql_kiwiweb_db, 'shopping_cart')
    .fields(
        new Field('pd_images.pd_number'),
        new Field('pd_images.file_id'),
        new Field('uploads.id'),
        new Field('shopping_cart.id'),
        new Field('shopping_cart.user_id'),
        new Field('shopping_cart.designnumber'),
        new Field('shopping_cart.description'),
        new Field('shopping_cart.po'),
        new Field('shopping_cart.due_date')
            .setFormatter(function(val, data){
                console.log(data)
                return moment(val).format('YYYY/MM/DD')
            })
            .getFormatter(function(val,data){
                console.log(data)
                return moment(val).format('YYYY/MM/DD')
            })
        ,
        new Field('shopping_cart.special_instructions'),
        new Field('shopping_cart.quantity'),
        
    )
    .where(function(){
        if(request_package.user.group == 'ADMIN'){
            this.where('shopping_cart.user_id', '>', '0')
        }else{
            this.where('shopping_cart.user_id', '=', request_package.user.id)
        }
    })
    .leftJoin('pd_images','shopping_cart.designnumber', '=', 'pd_images.pd_number')
    .leftJoin('uploads', 'pd_images.file_id', '=' , 'uploads.id')
        
    

    await editor.process(req.body)
    .catch(function(err){
        console.log(err)
    })
    res.json(editor.data());
})

allan

In your Knex database configuration object could you add debug: true please? Then run the sequence that causes the error again and copy / paste the debug output from the console to here? That will contain the SQL that is being executed, so I can see a little bit more about what is happening.

One thing - the database will use ISO8601 as the storage format, so the setFormatter should be returning ISO8601 - i.e. YYYY-MM-DD.

Allan

mikedmason

Hey Allan,
I was able to get this to work by using dashes(-) instead of using slashes(/) in the formatter.