One-to-many nested datatable in editor

ezdavis

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
Is it possible to have a one-to-many nested editor table? I see how you do it in the example here with a checkbox. But I need to have a nested table.

I have a list of tickets which displays fine and I can edit as normal. But then I also have the list of TicketDetails that need edited. See my model below.

data: [
  {
    "TicketID": 4650,
    "TicketTypeID": 1,
    "StatusID": 1,
    "StatusTitle": "New",
    "SeverityID": 1,
    "SeverityTitle": "High",
    "Subject": "Test 14",
    "DateExpected": "/Date(1648699200000)/",
    "DateCreated": "/Date(1648758398023)/",
    "DateModified": "/Date(1648758398023)/",
    "DateCompleted": null,
    "CreatedBy": "edavis",
    "DepartmentID": 2,
    "DepartmentTitle": "IT",
    "AssignedTo": null,
    "AssignedToDate": null,
    "ResolutionID": null,
    "ResolutionTitle": null,
    "Hours": null,
    "TicketDetails": [
      {
        "Key": 15864,
        "TicketDetailID": 15864,
        "TicketID": 4650,
        "DateCreated": "/Date(1648758398027)/",
        "CreatedBy": "edavis",
        "Details": "Testing 14"
      }
    ]
  }
]

In my editor I have setup my fields as follows

fields: [            
            { name: "TicketID", label: 'ID', type: "readonly"},
            {
                name: "StatusID", label: 'Status',
                type: "select",
                options: [getStatusDropdownEdit()]},
            //{ name: "StatusTitle", label: 'Status' },
            {
                name: "SeverityID", label: 'Severity',
                type: "select",
                options: [getSeverityDropdownEdit()] },
            //{ name: "SeverityTitle", label: 'Severity' },
            { name: "Subject", label: 'Subject' },
            {
                name: "DepartmentID", label: 'Department',
                type: "select",
                options: [getDepartmentDropdownEdit()] },
            //{ name: "DepartmentTitle", label: 'Department' },
            {
                name: "DateCreated", label: 'Date Created', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "DateExpected", label: 'Date Expected', type: "datetime",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "DateModified", label: 'Date Modified', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }, visible: false
            },
            {
                name: "DateCompleted", label: 'Date Completed', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            { name: "CreatedBy", label: 'Created By' },
            {
                name: "AssignedTo", label: 'Assigned To',
                type: "select",
                options: [getAssignedToDropdownEdit()] },
            {
                name: "AssignedToDate", label: 'Assigned Date', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "ResolutionID", label: 'Resolution',
                type: "select",
                options: [getResolutionDropdownEdit()] },
            //{ name: "ResolutionTitle", label: 'Resolution' },
            { name: "Hours", label: 'Hours' },
            {
                label: 'Ticket Details:',
                name: 'TicketDetails[]',
                type: 'datatable',
                editor: ticketDetailsEditor,
                optionsPair: {
                    value: 'id',
                },
                config: {
                    searching: false,
                    info: false,
                    paging: false,
                    select: false,
                    order: [[0, 'asc']],
                    data: 'TicketDetails[]',
                    columns: [
                        {
                            title: 'ID',
                            data: 'TicketDetailID',
                            type: "readonly",
                        },
                        {
                            title: 'Ticket ID',
                            data: 'TicketID',
                            type: "readonly",
                            visible: false
                        },
                        {
                            title: 'Created By',
                            data: 'CreatedBy',
                            type: "readonly",
                        },
                        {
                            data: "DateCreated", title: 'Created', type: "date",
                            render: function (d) {
                                if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                                else { return ''; }
                            }
                        },
                        {
                            title: 'Details',
                            data: 'Details',
                            type: "readonly",
                            orderable: false,
                        }
                    ],
                    buttons: [
                        { extend: "create", className: 'btn-dark', editor: ticketDetailsEditor }
                    ]
                }
            }
        ]

You can see in my TicketDetails field I'm trying to pass the data 'TicketDetails[]' but this doesn't work.

I'm trying to set this up so that the nested table (TicketDetails) basically uses local editing, which would allow changes to be submit when I update the parent editor (Tickets) rather than setting this up to have 2 calls to return my data and 2 calls to submit my changes.

Any suggestions? I've found examples of creating a nested child table in the parent table like an accordion but that's not what I'm trying to do. I'm wanting this all to be done in one editor.

Thanks!

allan

Hi,

Do you mean like this?

It isn't currently possible to have the data batch submitted - i.e. if you edit the child information then that will get submitted and then you come back to the parent view and can select from it.

That specific example shows a single selection only, but you can do multi-selection as well.

Regards,
Allan

ezdavis

Yes, I guess I should have mentioned that I used your nested example to set it up originally. That works but requires multiple ajax calls. I then tried to merge it together with the one-to-many join example.

It isn't currently possible to have the data batch submitted - i.e. if you edit the child information then that will get submitted and then you come back to the parent view and can select from it.

Would this be a possible future feature request? It seems like between these 2 examples and local table editing you have a lot of what's needed but understand things can be a lot more complicated than meets the eye.

allan

It isn't something that I'd been planning on adding I'm afraid, as I generally consider the Ajax requests to be fairly cheep and at the moment the two Editors would typically call two different controllers. If it was all submitted as one, then there would been to be knowledge about the child Editor instance form the parent. I'd also be concerned about validation errors - say there is a validation error in the child when submitting a parent.

I fear that this would probably be a significant amount of work to remove an Ajax call. Sorry.

Allan

ezdavis

Alright, that makes sense.

One more thing with the nested editor field, how do I pass the TicketID from my parent editor? It's set as the idSrc.

ajax: {
            url: "/ServiceDesk/LoadTicketDetailsDataTable",
            type: "POST",
            contentType: "application/json",
            data: function (d) {
            d.ticketID = "3640";
            }
},

My nested editor ajax call looks like this but I need "3640" to not be hard coded so I can pull the correct details for the ticket.

colin

You could use a variable instead, it doesn't have to be a string constant. How are you expecting to get the value?

Colin

ezdavis

ajax: {
                        url: "/ServiceDesk/LoadTicketDetailsDataTable",
                        type: "POST",
                        contentType: "application/json",
                        data: function (d) {
                            d.ticketID = editorEdit.field('TicketID');
                            //d.ticketID = "3640";
                        }
                    },

Something like this but it doesn't work because the parent editor (editorEdit) isn't finished initializing yet.

colin

Could you get the value from the table instead - using row().data() or cell().data()? The table itself would be initialised,

Colin

ezdavis

I haven't been able to get that to work either.

Looking more into this functionality I'm confused how the optionsPair works. It looks like this situation is what it's used for? It allows to pass the ID to the linked datatable?

optionsPair: {
                    value: 'TicketID',
                },

Am I not understanding this correctly? Because even when I set it like this I'm not seeing anywhere to grab this value.

allan

By default Editor expected the options to contain label and value properties in an array of objects. Not all data is structured like that though, so the optionsPair let's you control the properties that it looks for - e.g.:

optionsPair: {
  value: 'TicketID',
  label: 'Name'
}

I'm not sure that is what you need in this case though! You probably want to reference the TicketID from the host table, rather than the editor form.

Consider the example here I've used d.site = rowData.id; where rowData is the selected row's data (from the parent table).

Is that more in keeping with that you are looking for? Perhaps you can link to your page so I can see the full picture?

Allan

ezdavis

best I could come up with is doing an onselect to get my data and store it to a global variable

ticketTable
        .on('select', function (e, dt, type, indexes) {
            selectedRowData = ticketTable.rows(indexes).data().toArray();
        });

Then in my nested table ajax call I use

ajax: {
                        url: "/ServiceDesk/LoadTicketDetailsDataTable",
                        type: "POST",
                        data: function (d) {
                            var ticketID = 3640;
                            if (typeof selectedRowData !== 'undefined') {
                                ticketID = selectedRowData[0].TicketID;
                            }
                            d.ticketID = ticketID;
                        }
                    },

I still don't like this solution though. Seems like there should be an easier way to pass a variable to a nested editor. I've went through the forums and found quite a few post of people trying to do this but it's not possible. Most of the solution suggest creating a child table rather than a nested editor but that's not what I'm looking for.

The solution I posted above has an issue on page load still. When the table is loaded it automatically tries loading the nested datatable data. Since I haven't selected a row yet it's undefined. Is there a way to not make this request until the editor window has been requested? Thanks

allan

Could you send me the full Javascript file you are using there please? It isn't quite clicking for me how all this fits together .

It should certainly be possible to do this without a global variable, but I'm wondering if we might be able to make it easier with some kind of field method .field('myTable').ajaxData( { ... } ) for example?

Allan

ezdavis

Alright here is my entire javascript file. I have a lot going on here. I do actually create a "child table" for my ticket details which can be viewed by hitting the green plus in the row. However, I don't want it editable here. I want everything to be editable in the "main editor" only.

    ticketTable = $('#servicedesk-datatable').DataTable({
        select: true,
        serverSide: true,
        processing: true,
        orderCellsTop: true,
        stateSave: true,
        fixedHeader: {
            header: true,
            headerOffset: $('#topbar').height()
        },
        buttons: [
        { extend: "create", className: 'btn-dark', editor: editorCreate },
        { extend: "edit", className: 'btn-dark', editor: editorEdit },
        ],
        initComplete: function (settings, json) {
            ticketTable.buttons().container()
                .appendTo($('.col-md-6:eq(0)', ticketTable.table().container()));
        },
        pageLength: 10,
        order: [[1, "desc"]],
        searchDelay: 500,
        deferRender: true,
        ajax: {
            url: "/ServiceDesk/ServiceDeskDataTable",
            type: "POST",
            data: function (d) {
                d.DepartmentId = $('#DepartmentId').val();
                d.StatusId = $('#StatusId').val();
                d.SeverityId = $('#SeverityId').val();
                d.ResolutionId = $('#ResolutionId').val();
                d.AssignedTo = $('#AssignedTo').val();
            }
        },
        columns: [
            {
                class: "dt-control px-3",
                orderable: false,
                searchable: false,
                data: null,
                defaultContent: ""
            },
            { data: "TicketID", title: 'ID' },
            { data: "StatusID", title: 'Status', visible: false },
            { data: "StatusTitle", title: 'Status' },
            { data: "SeverityID", title: 'Severity', visible: false },
            { data: "SeverityTitle", title: 'Severity' },
            { data: "Subject", title: 'Subject' },
            { data: "DepartmentID", title: 'Department', visible: false },
            { data: "DepartmentTitle", title: 'Department' },
            {
                data: "DateCreated", title: 'Date Created', type: "date",
                render: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                data: "DateExpected", title: 'Date Expected', type: "date",
                render: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                data: "DateModified", title: 'Date Modified', type: "date",
                render: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }, visible: false
            },
            {
                data: "DateCompleted", title: 'Date Completed', type: "date",
                render: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            { data: "CreatedBy", title: 'Created By' },
            { data: "AssignedTo", title: 'Assigned To' },
            {
                data: "AssignedToDate", title: 'Assigned Date', type: "date",
                render: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }, visible: false
            },
            { data: "ResolutionID", title: 'Resolution', visible: false },
            { data: "ResolutionTitle", title: 'Resolution' },
            { data: "Hours", title: 'Hours' },
            {
                title: 'Action',
                data: 'TicketID',
                orderable: false,
                searchable: false,
                render: function (data, type) {
                    if (type === 'display') {
                        let link = "/ServiceDesk/EditTicketBase?TicketID=" + data;

                        return '<a class="btn btn-primary btn-sm" href="' + link + '"  type="button"><span>View</span></a>';
                    }

                    return data;
                }
            }
        ]
    });

    ticketTable
        .on('select', function (e, dt, type, indexes) {
            selectedRowData = ticketTable.rows(indexes).data().toArray();
        });

    // Add event listener for opening and closing details
    $("#servicedesk-datatable tbody").on("click", "td.dt-control", function () {
        var tr = $(this).closest("tr");
        var row = ticketTable.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            destroyChild(row);
            tr.removeClass("shown");
        } else {
            // Open this row
            createChild(row);
            tr.addClass("shown");
        }
    });

ezdavis

And here are my 2 editors. I had to split everything up due to the length and I am still not showing everything but this should give you a better understanding.

ticketDetailsEditor = new $.fn.dataTable.Editor({
        ajax: {
            create: {
                type: 'POST',
                url: '/ServiceDesk/CreateTicketDetail',
                dataType: "JSON",
                data: function (d) {
                    return JSON.stringify(d);
                },
                error: function (errorData) {
                    OnError(errorData);
                }
            },
        },
        idSrc: 'TicketDetailID',
        fields: [
            {
                label: 'Detail',
                name: 'Details',
                type: "textarea",
                attr: {
                    placeholder: "Please enter a message here"
                }
            }
        ]
    });

    editorEdit = new $.fn.dataTable.Editor({
        ajax: {
            edit: {
                type: 'POST',
                url: '/ServiceDesk/UpdateTicket',
                dataType: "JSON",
                contentType: 'application/json; charset=utf-8',
                data: function (d) {
                    return JSON.stringify(d);
                },
                success: function (response) {
                    $('#servicedesk-datatable').DataTable().ajax.reload();
                },
                error: function (errorData) {
                    OnError(errorData);
                }
            },
        },
        idSrc: 'TicketID',
        table: "#servicedesk-datatable",
        formOptions: {
            main: {
                submit: 'changed'
            }
        },
        fields: [
            {
                name: "StatusID", label: 'Status',
                type: "select",
                options: [getStatusDropdownEdit()]},
            {
                name: "SeverityID", label: 'Severity',
                type: "select",
                options: [getSeverityDropdownEdit()] },
            { name: "Subject", label: 'Subject', type: "readonly" },
            {
                name: "DepartmentID", label: 'Department',
                type: "select",
                options: [getDepartmentDropdownEdit()] },
            {
                name: "DateCreated", label: 'Date Created', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "DateExpected", label: 'Date Expected', type: "datetime",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "DateModified", label: 'Date Modified', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }, visible: false
            },
            {
                name: "DateCompleted", label: 'Date Completed', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            { name: "CreatedBy", label: 'Created By', type: "readonly" },
            {
                name: "AssignedTo", label: 'Assigned To',
                type: "select",
                options: [getAssignedToDropdownEdit()] },
            {
                name: "AssignedToDate", label: 'Assigned Date', type: "readonly",
                data: function (d) {
                    if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                    else { return ''; }
                }
            },
            {
                name: "ResolutionID", label: 'Resolution',
                type: "select",
                options: [getResolutionDropdownEdit()] },
            { name: "Hours", label: 'Hours' },
            {
                label: 'Ticket Details:',
                name: 'TicketDetails',
                type: 'datatable',
                editor: ticketDetailsEditor,
                optionsPair: {
                    value: 'TicketID',
                    label: 'TestTicket'
                },
                config: {
                    searching: false,
                    info: false,
                    select: false,
                    serverSide: true,
                    processing: true,
                    pageLength: 10,
                    order: [[0, 'asc']],
                    ajax: {
                        url: "/ServiceDesk/LoadTicketDetailsDataTable",
                        type: "POST",
                        data: function (d) {
                            var ticketID = 3640;
                            if (typeof selectedRowData !== 'undefined') {
                                ticketID = selectedRowData[0].TicketID;
                            }
                            d.ticketID = ticketID;
                        }
                    },
                    columns: [
                        {
                            title: 'ID',
                            data: 'TicketDetailID',
                            type: "readonly",
                        },
                        {
                            title: 'Ticket ID',
                            data: 'TicketID',
                            type: "readonly",
                            visible: false
                        },
                        {
                            title: 'Created By',
                            data: 'CreatedBy',
                            type: "readonly",
                        },
                        {
                            data: "DateCreated", title: 'Created', type: "date",
                            render: function (d) {
                                if (d != null) { return moment(d).format("MM/DD/YYYY HH:mm:ss"); }
                                else { return ''; }
                            }
                        },
                        {
                            title: 'Details',
                            data: 'Details',
                            type: "readonly",
                            orderable: false,
                        }
                    ],
                    buttons: [
                        { extend: "create", className: 'btn-dark', editor: ticketDetailsEditor }
                    ]
                }
            }
        ]
    });

allan

Many thanks. I think this is what I would do in that case:

ticketTable.on("select", function (e, dt, type, indexes) {
  editorEdit.field("TicketDetails").dt().draw();
});

editorEdit
  .field("TicketDetails")
  .dt()
  .on("preXhr", function (e, s, d) {
    var selectedRowData = ticketTable.rows(indexes).data().toArray();

    d.ticketID = selectedRowData
      ? selectedRowData[0].TicketID
      : 3640;
  });

So when a row is selected in ticketTable it causes a redraw of the TicketDetails DataTable. That, since you have server-side processing enabled for it, will result in an Ajax request to the server, which will trigger the preXhr which gets the data for the selected row and appends it to the request.

Allan