Editing in Related Tables

TomBajzek

I'm trying to port an existing web app into DataTables/Editor. Any record in the table being edited might have zero to many related records in another table. Is there any way from within an Editor form to invoke the editor again on the related table record in the related table? I'd like to be able to create a record in the related table when necessary, as well as to display the related records in that table. So far, I haven't found a way to do this within the DataTables/Editor framework.

Since this is a feasibility question, I don't have a problem pagers debug or link to. However, I can provide more detailed information if that would help someone who wants to respond.

allan

Hi,

Do you have a second Editor instance for the child table? If so, you could use postCreate, or perhaps better yet submitComplete on the parent Editor to trigger whatever action is required on the child Editor.

I suspect that what you are looking for probably can be done with events and the API, but the exact setup would depend upon exactly what you are looking to do.

Allan

TomBajzek

Allan,

I appreciate your answer, but it doesn't quite address my issue here. The app I'm porting is a trouble-ticket manager. In this app, any ticket may have zero or more notes related to it, serving as a means of communications between people who work to resolve the ticket issue. The workflow of the existing app displays notes related to the current ticket only in the context of editing that ticket. And, in this context, notes are read-only, although a new note may be added. So, it seems that what I need is, in the context of editing a ticket, to be able to display the list of notes (if any) related to that ticket, and allow creation of a new note related to that ticket.

I occurs to me that I may be overestimating what I can (or should) be doing directly in DataTables/Editor, and that I should begin to think of embedding that in the existing structure of the workflow. I've been extremely impressed by how quickly and easily I've been able to create another app with DataTables/Editor, but that one is pretty much just CRUD for a single, large and complex table, which is a simpler architecture than the trouble-ticket app.

Am I missing how to implement what I need in DataTables/Editor, or should I be thinking of using DataTables/Editor as a component of a port of the existing app architecture?

Thanks,
Tom

allan

Hi Tom,

Thanks for the additional explanation. To frame it in terms of Editor, you want to have a single field input which can have items inside it added, removed and edited? That would be possible with a custom field type plug-in that would provide those options to add, remove and edit items inside the form. It would return an array of values (one for each item).

That's not something I've done yet myself (its on my todo list!), but, if I've understood what you are looking for correctly, then it should be possible with a custom plug-in.

Allan

TomBajzek

Allan,

Not quite, although I could imagine doing it the way I think you are suggesting. What I'm thinking of is being inside an Editor form for an item in the tickets table, and being able to see the existing notes for that ticket and create a new note. I can send you an email with a screenshot to show what I mean if you are willing for me to do that.

Thanks,
Tom

allan

Hi Tom,

If you could send me a screenshot that would be useful. You can send me a PM by clicking my name above and then "Send message".

You might also be interested in looking over this blog post which sounds like it might be applicable.

Allan

TomBajzek

Allan,

I've gotten the individual issues related to my tickets and notes tables figured out, and now have a working editor for each of these, and I'm starting on the integration along the lines of the blog post you referred me to.

The approach in the blog post allows me to bring the tickets and notes onto the same page, as the sites and users are in the blog post. That suggests to me that I need to combine the tickets and notes into the same source file, which would contain the editor for each of these tables as well as the DataTable definition for each. It seems that the PHP files for each table would remain separated as they are now. For reasons of screen real estate, I think that I will eventually need to make the notes table invisible until the user selects a ticket that has notes attached, but I won't start out that way.

Before I go too far along this line, does that seem to be the right approach to this? Are there other things you would advise me to keep in mind as I get started?

Thanks,
Tom

allan

It seems that the PHP files for each table would remain separated as they are now

Generally yes, that is how the Editor demos are presented. But there is no reason why you can't combine them together and just send an extra parameter to the server telling it which table is being operated on.

Overall, I think your approach sounds good.

Allan

TomBajzek

Allan,

I've now combined the tickets table and notes table into one file leaving the PHP files separate. I now have the tickets table loading correctly, but something is wrong with the JOIN operation, because nothing happens when I click to select a ticket. The intent is to load the notes related to the selected ticket, but that does not happen. I've studied the documentation and examples, and tried to replicate the code used in the example of a left join, but I've missed something and have not been able to see what that is. I would appreciate it if you would have a chance to look at this and provide guidance.

I've trimmed my tables to a few tickets and notes for efficiency.

For some reason, the file tableITEditor.js is rejected by the uploader, so I've pasted the source here:

/*
 * Editor client script for DB table tickets
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    
    $('.moreF').more({length: 80, wordBreak: true});

    var ticketEditor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.tickets.php',
        table: '#tickets',
        formOptions: {
            main: {
                submit: 'changed'
            }
        },
        fields: [
            {
                label: "updated:",
                name: "updated",
                type: "readonly",
            },
            {
                label: "status:",
                name: "status",
                type: "select",
                placeholder: "- Select status -",
                options: [
                    "closed",
                    "holding",
                    "in progress",
                    "in  queue",
                    "unseen",
                    "urgent"
                ]
            },
            {
                label: "responder:",
                name: "responder",
                type: "select",
                placeholder: "- Select responder -",
                options: [
                    "CB",
                    "CZ",
                    "ES",
                    "KD",
                    "LM",
                    "TB"
                ]
            },
            {
                label: "originator:",
                name: "email"
            },
            {
                label: "category:",
                name: "category",
                type: "select",
                placeholder: "- Select category -",
                options: [
                    "computer",
                    "software",
                    "disk",
                    "network",
                    "server"
                ]
            },
            {
                label: "description:",
                name: "description",
                type: "textarea"
            },
            {
                label: "device:",
                name: "device",
                type: "select",
                placeholder: "- Select device -",
                options: [
                    "PC",
                    "router",
                    "switch",
                    "printer",
                    "copier",
                    "monitor",
                    "server"
                ]
            },
            {
                label: "devstatus:",
                name: "devstatus",
                type: "select",
                placeholder: "- Select devStatus -",
                options: [
                    "usable",
                    " unusable",
                    "intermittent",
                    "unknown"
                ]
            },
            {
                label: "severity:",
                name: "severity",
                type: "select",
                placeholder: "- Select severity -",
                options: [
                    "critical",
                    " normal",
                    "when convenient",
                    "ex-critical"
                ]
            }
        ]
    } );

    var ticketTable = $('#tickets').DataTable( {
        dom: 'Bfrtip',
        order: [[0,"desc"]],
        ajax: 'php/table.tickets.php',
        columns: [
            {
                data: "id"
            },
            {
                data: "tickettime"
            },
            {
                data: "updated"
            },
            {
                data: "status"
            },
            {
                data: "responder"
            },
            {
                data: "email"
            },
            {
                data: "category"
            },
            {
                data: "description",
                className: " moreF"
            }
//          {
//              data: "device"
//          },
//          {
//              data: "devstatus"
//          },
//          {
//              data: "severity"
//          }
        ],
        select: {
            style: 'single'
        },
        lengthChange: false
    } );
    
    new $.fn.dataTable.Buttons( ticketTable, [
        { extend: "create", editor: ticketEditor },
        { extend: "edit",   editor: ticketEditor },
        { extend: "remove", editor: ticketEditor }
    ] );

    ticketTable.buttons().container()
        .appendTo( $('.col-sm-6:eq(0)', ticketTable.table().container() ) );
} );

    var noteEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'php/table.notes.php',
            data: function(d) {
                var selected = ticketTable.row( { selected : true} );
                if (selected.any()) {
                    d.ticket= selected.data().id;
                }
            }
        },
        table: '#notes',
        fields: [
//          {
//              label: "created:",
//              name: "created",
//              type: "datetime",
//              def: function () { return new Date(); },
//              format: "YYYY-MM-DD HH:mm:ss"
//          },
            {
                label: "commenter:",
                name: "notes.email"
            },
            {
                label: "content:",
                name: "notes.content",
                type: "textarea"
            },
            {
                label: "ticket #",
                name: "notes.ticket_id",
                type: "select",
                placeholder: "Select a ticket Id"
                
            },
            {
                label: "resolved?:",
                name: "notes.resolved",
                type: "select",
                placeholder: "yes/no",
                options: [
                    "no",
                    "yes"
                ],
                default: "no"
            }
        ]
    } );

    var noteTable = $('#notes').DataTable( {
        ajax: {
            url: 'php/table.notes.php',
            type: "post",
            data: function(d) {
                var selected = noteTable.row( {selected: true} );
                if (selected.any()) {
                    d.ticket = selected.data()id;
                }
            }
        }
        order: [[0,"desc"]],
        columns: [
//          {
//              data: "id"
//          },
            {
                data: "notes.created"
            },
            {
                data: "notes.email"
            },
            {
                data: "notes.resolved"
            },
            {
                data: "tickets.id"
            },
            {
                data: "notes.content",
                className:" moreF"
            }
        ],
        select: true
    } );

    new $.fn.dataTable.Buttons( noteTable, [
        { extend: "create", editor: noteEditor }    // ,
//      { extend: "edit",   editor: noteEditor },
//      { extend: "remove", editor: noteEditor }
    ] );

    noteTable.buttons().container()
        .appendTo( $('.col-sm-6:eq(0)', noteTable.table().container() ) );

    ticketTable.on( 'select', function () {
        noteTable.ajax.reload();

        noteEditor
            .field( 'notes.ticket' )
            .def( ticketTable.row( { selected: true } ).data().id );
    } );
    
    ticketTable.on( 'deselect', function () {
        noteTable.ajax.reload();
    } );

    noteEditor.on( 'submitSuccess', function () {
        ticketTable.ajax.reload();
    } );

    ticketEditor.on( 'submitSuccess', function () {
        noteTable.ajax.reload();
    } );


}(jQuery));

The debugger code for my debug file is 'adamob'.

Thanks in advance for your help,
Tom

allan

Interesting - I don't see the issue in the above code I'm afraid.

First thing I'd do is put a debug statement into the ticketTable.on( 'select', function () { function. Does it actually get executed when a row is selected in the ticket table?

Allan

TomBajzek

Hmmm. I put an alert into the ticketTable.on( 'select', function () and there is no alert when I select a row, even thought the row becomes highlighted. The dataTables definition for ticketTable contains: select: { style: 'single' }

I'll study this next.

Tom

TomBajzek

Aha! I found that the itEditor.html page was loading the original table.tickets.js file, which does not contain the notes table code. Let me move on from here.

I'm sorry I bothered you with this red herring!

Thanks,
Tom

TomBajzek

Allan,

I found my problems. The first was that the document.ready() function was closed in the wrong place, which orphaned the notes table definition. Because of the length of the code, it was very hard to see this. I just thought I should tell you this.

Thanks,
Tom

allan

Hi Tom,

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

Allan