Editor: Create row onClick

kmbonin

Trying to create a new row with the only two required fields my logan_prodData table needs: dateAdded and JobID (FK to logan_jobs). I'm getting the error: table id=ProductionData - Column 'jobID' in field list is ambiguous

What am I doing wrong?

Controller:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_prodData", "recordID")
                    .Model<ProductionJobsDataModel>()
                    .Field(new Field("logan_prodData.PComp")
                        .Validator(Validation.Numeric()))
                    .Field(new Field("logan_prodData.CurEstEndDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.PMEnd")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.recordID"))
                    .Field(new Field("logan_prodData.jobID"))
                    .Field(new Field("logan_prodData.deptID")
                        .Options("logan_dept", "deptID", "department")
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_user.fullName"))
                    .Field(new Field("logan_status.status"))
                    .Field(new Field("logan_status.displayColor"))
                    .Field(new Field("logan_dept.department"))
                    .LeftJoin("logan_jobs", "logan_jobs.jobId", "=", "logan_prodData.jobId")
                    .LeftJoin("logan_dept", "logan_dept.deptID", "=", "logan_prodData.deptID")
                    .LeftJoin("logan_status", "logan_status.statusID", "=", "logan_prodData.statusID")
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

JS to create row onClick:

$('#ProductionData tbody').on('click', 'a.editor_create', function (e) {
        e.preventDefault();
        var cellContents = table.cell( ($(this).closest('tr').next('tr')),1).data()
        editor
            .create(false)
            .val('logan_prodData.jobID', cellContents)
            .val('logan_prodData.dateAdded', Date.now())
            .submit();
    });

kmbonin

Just modified my model and the error cleared. When I perform the onClick action, though, I get the error:

TypeError: fields[n] is undefined
fields[ n ].set( v );

in file dataTables.editor.js.

Any idea what this error is?

allan

Are you able to give me a link to the page so I can try to debug it please? My guess is that there isn't a field with the name logan_prodData.jobID, but I'm not sure as the Javascript initialisation isn't included above.

Regards,
Allan

kmbonin

Can't give you a link to the page as it's an internal company page that I am developing on their server. Here's my debug support request code if that helps:

DataTables debug bookmarklet
Upload complete - debug code: amelox

And here is the initialization code:

(function($){
$(document).ready(function() {
     editor = new $.fn.dataTable.Editor( {
        ajax: './api/ProductionData',
        table: '#ProductionData',
        fixedHeader: true,
        fields: [
            {
                "label": "Planner:",
                "name": "logan_jobs.plannerID",
                "type": "select"
            },            
            {
                "label": "Description:",
                "name": "logan_jobs.description",
                "type": "select"
            },
            {
                "label": "Department:",
                "name": "logan_dept.department",
                "type": "select"
            },
            {
                "label": "PM Date:",
                "name": "logan_prodData.PMEnd",
                "type": "date",
                "def": function () { return new Date(); },
                "dateFormat": "m/d/yy"
            }
        ]
     });
    
    var table = $('#ProductionData').DataTable( {
        ajax: './api/ProductionData',
        order: [4, 'asc'],
        fixedHeader: true,
        columns: [
            {
                "class": "details-control",
                "orderable": false,               
                "data": null,
                "defaultContent": "<img src='img/icons/blue-plus-icon.png' title='View Details' width='16px' />"
            },
            {
                "data": "logan_prodData.jobID",
                "visible": false, "targets": 1
            },
            {
                "data": function (data, type, row) {
                    return data.logan_jobs.jobNumber + ' - ' + data.logan_user.fullName + ' - ' + data.logan_jobs.customer + ' - ' + data.logan_jobs.description;
                },
                "visible": false, "targets": 2
            },
            {
                "data": "logan_dept.department"
            },
            {
                "data": "logan_prodData.PMEnd"
            },
            {
                "data": "logan_prodData.CurEstEndDate"
            },
            {
                "data": "logan_prodData.PComp"
            }
        ],
        select: true,
        lengthChange: false,
        drawCallback: function (settings) {
            var api = this.api();
            var rows = api.rows({ page: 'current' }).nodes();
            var last = null;

            api.column(2, { page: 'current' }).data().each(function (group, i) {

                if (last !== group) {
                    $(rows).eq(i).before(
                        '<tr class="group"><td colspan="5"><a href="" class="editor_create"><img src="img/icons/icon_add.png" width="16px;"/></a> ' + group + '</td></tr>'
                    );

                    last = group;
                }
            });
        }
    });

allan

Thanks for the code. It does indeed appear that there is no Editor field by the name of logan_prodData.jobID.

If you want to be able to set that field using the code .val('logan_prodData.jobID', cellContents) you'd need to have a field with that name.

Allan

kmbonin

Okay, I got it working, but then my group sorting was conflicting with the results, so I modified the group to display in two cells instead of one, then changed the classes appropriately. The sort works fine, but my row creation no longer works, and I get this error when I try to invoke it:

TypeError: editor.create(...).val(...).submit is not a function

editor

I have verified that the add row code is being called and has the correct JobID to be inserted. It just doesn't seem to know what "editor" is anymore.

Full JS: (includes a row-details controller with info which is the first function).

function format(d) {
    // `d` is the original data object for the row
    return '<table cellpadding="15" cellspacing="0" border="0" style="padding-left:50px; width:75%;">' +
       
        '<tr>' +
            '<td><a href="jobs_details.aspx?jobNumber=' + d.logan_jobs.jobNumber + '"target="_blank"><img src="img/icons/white-zoom-icon.jpg" title="View Details" width="20px"/></a></td>' +
            '<td style="font-weight:bold">Part Number:</td>' +
            '<td>' + d.logan_jobs.partNumber + '</td>' +
            '<td style="font-weight:bold">Ship By:</td>' +
            '<td>' + d.logan_jobs.shipByDate + '</td>' +
        '</tr>' +
        '<tr>' +
            '<td></td>' +
            '<td style="font-weight:bold">Sales Order:</td>' +
            '<td>' + d.logan_jobs.salesOrder + '</td>' +
            '<td style="font-weight:bold">Quote Number:</td>' +
            '<td>' + d.logan_jobs.quoteNumber + '</td>' +
        '</tr>' +
    '</table>';
}

    
(function($){
$(document).ready(function() {
     editor = new $.fn.dataTable.Editor( {
        ajax: './api/ProductionData',
        table: '#ProductionData',
        fixedHeader: true,
        fields: [
             {
                 "label": "JobID:",
                 "name": "logan_prodData.jobID"
             },
            {
                "label": "Planner:",
                "name": "logan_jobs.plannerID",
                "type": "select"
            },            
            {
                "label": "Description:",
                "name": "logan_jobs.description",
                "type": "select"
            },
            {
                "label": "Department:",
                "name": "logan_dept.department",
                "type": "select"
            },
            {
                "label": "PM Date:",
                "name": "logan_prodData.PMEnd",
                "type": "date",
                "def": function () { return new Date(); },
                "dateFormat": "m/d/yy"
            }
        ]
     });
    
    var table = $('#ProductionData').DataTable( {
        ajax: './api/ProductionData',
        order: [1, 'asc'],
        fixedHeader: true,
        columns: [
            {
                "class": "details-control",
                "orderable": false,               
                "data": null,
                "defaultContent": "<img src='img/icons/blue-plus-icon.png' title='View Details' width='16px' />"
            },
            {
                "data": "logan_prodData.jobID",
                "visible": false, "targets": 1
            },
            {
                "data": function (data, type, row) {
                    return data.logan_jobs.jobNumber + ' - ' + data.logan_user.fullName + ' - ' + data.logan_jobs.customer + ' - ' + data.logan_jobs.description;
                },
                "visible": false, "targets": 2
            },
            {
                "data": "logan_dept.department"
            },
            {
                "data": "logan_prodData.PMEnd"
            },
            {
                "data": "logan_prodData.CurEstEndDate"
            },
            {
                "data": "logan_prodData.PComp"
            }
        ],
        select: true,
        lengthChange: false,
        drawCallback: function (settings) {
            var api = this.api();
            var rows = api.rows({ page: 'current' }).nodes();
            var last = null;

            api.column(2, { page: 'current' }).data().each(function (group, i) {

                if (last !== group) {
                    $(rows).eq(i).before(
                        '<tr><td class="group"><a href="" class="editor_create"><img src="img/icons/icon_add.png" width="16px;"/></a></td><td colspan="5" class="group2"> ' + group + '</td></tr>'
                    );

                    last = group;
                }
            });
        }
    });  

    $('#ProductionData tbody').on('click', 'a.editor_create', function (e) {
        e.preventDefault();
        var cellContents = table.cell(($(this).closest('tr')), 1).data();
        
        editor
            .create(false)
            .val('logan_prodData.jobID', cellContents)
            .submit()
            .on('postCreate postRemove', function () {
                // After create or edit, a number of other rows might have been effected -
                // so we need to reload the table, keeping the paging in the current position
                table.ajax.reload(null, false);
            });
    });
            

    new $.fn.dataTable.Buttons(table, [        
        {
            extend: "create",
            editor: editor,
            formButtons: [
                'Save',
                { label: 'Cancel', fn: function () { this.close(); } }
            ]
        },
        {
            extend: "edit",
          editor: editor,
            formButtons: [
                'Save',
                { label: 'Cancel', fn: function () { this.close(); } }
            ]
        },
        {
            extend: "remove",
            editor: editor,
            formMessage: function (e, dt) {
                return 'Are you sure you want to delete this record?';
            },
            formButtons: [
                'Delete',
                { label: 'Cancel', fn: function () { this.close(); } }
            ]
        },
        {
            extend: 'collection',
            text: 'Export',
            buttons: [
                'copy',
                'excel',
                'csv',
                'pdf',
                'print'
            ]
        }
    ] );

    table.buttons().container()
        .prependTo($('div.fg-toolbar:eq(0)', table.table().container()));

    // Add event listener for opening and closing details
    $('#ProductionData tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });

    // Order by the grouping
    $('#ProductionData tbody').on('click', 'td.group2', function () {
        var currentOrder = table.order()[0];
        if (currentOrder[0] === 2 && currentOrder[1] === 'asc') {
            table.order([2, 'desc']).draw();
        }
        else {
            table.order([2, 'asc']).draw();
        }
    });
} );

}(jQuery));

allan

It is a global editor variable that you are using. It would probably be best to use var editor = new $.fn.dataTable.Editor( {....

Also, could cellContent be undefined? If so, val() will return a value, not an Editor instance.

Allan

kmbonin

Okay, so I added the var declaration to line 25 (see above code post) so that it now reads var editor...

But no luck. Still says the TypeError: editor.create(...).val(...).submit is not a function.

Any other ideas?

allan

Could cellContent be undefined?

What happens if you break the chain up? e.g.:

editor.create( ... );
editor.val( ... );
editor.submit();

Allan

kmbonin

So I broke up the chain, and that worked, but cellContent is, in fact, undefined. But I'm not sure why?? Column index 1 is the jobID, and that is never null. I'm getting the closest <tr> to the image which is in a group (see attachment, green icon on the gray rows).

var table = $('#ProductionData').DataTable( {
        ajax: './api/ProductionData',
        order: [1, 'asc'],
        fixedHeader: true,
        columns: [
            {
                "class": "details-control",
                "orderable": false,               
                "data": null,
                "defaultContent": "<img src='img/icons/blue-plus-icon.png' title='View Details' width='16px' />"
            },
            {
                "data": "logan_prodData.jobID",
                "visible": false, "targets": 1
            },
            {
                "data": function (data, type, row) {
                    return data.logan_jobs.jobNumber + ' - ' + data.logan_user.fullName + ' - ' + data.logan_jobs.customer + ' - ' + data.logan_jobs.description;
                },
                "visible": false, "targets": 2
            },
            {
                "data": "logan_dept.department"
            },
            {
                "data": "logan_prodData.PMEnd"
            },
            {
                "data": "logan_prodData.CurEstEndDate"
            },
            {
                "data": "logan_prodData.PComp"
            }
        ],
        select: true,
        lengthChange: false,
        drawCallback: function (settings) {
            var api = this.api();
            var rows = api.rows({ page: 'current' }).nodes();
            var last = null;

            api.column(2, { page: 'current' }).data().each(function (group, i) {

                if (last !== group) {
                    $(rows).eq(i).before(
                        '<tr><td class="group"><a href="" class="editor_create"><img src="img/icons/icon_add.png" width="16px;"/></a></td><td colspan="5" class="group2"> ' + group + '</td></tr>'
                    );

                    last = group;
                }
            });
        }
    });  

    $('#ProductionData tbody').on('click', 'a.editor_create', function (e) {
        e.preventDefault();
       
        var cellContents = table.cell(($(this).closest('tr')), 1).data();
        alert(cellContents);
        
        editor.create(false);
        editor.val('logan_prodData.jobID', cellContents);
        editor.submit();
        editor.on('postCreate postRemove', function () {
                // After create or edit, a number of other rows might have been effected -
                // so we need to reload the table, keeping the paging in the current position
                table.ajax.reload(null, false);
            });
    });

allan

ellContent is, in fact, undefined

Good - that explains why the chaining didn't work then. val is returning the values rather than an Editor instance.

I'd need a link to the page to be able to debug it fully, but perhaps try:

table.cell(($(this).closest('tr')[0]), 1).data();

Allan

kmbonin

Figured it out (finally). When using the group functionality, which creates a row above the target group of rows, the ID is actually in the next <tr>, not the group <tr>. So this worked, for anyone who is following this thread:

var cellContents = table.cell($(this).closest('tr').next('tr'), 1).data();

allan

Good to hear you've got it working now! Thanks for posting back.

Allan