Trying to get nested Editor/DataTable to work

Trying to get nested Editor/DataTable to work

resqonlineresqonline Posts: 28Questions: 6Answers: 0

Following setup:
Customer table and customer editor
Bookings table and booking editor

Now I want to include the bookings of each customer in the customer editor, each booking obviously contains the customer_id.

I think I've got a knot in my head how to approach this, I've read through the Nested editing example and think I understand that I have to include the bookings for each customer inside the customer ajax call - right? So do I only include the booking ids in the customer data or the whole booking data? Or do I have to do a second ajax call to only get the bookings based on the customer_id when the customer editor loads? How do I access the bookings data the best way?

var contacteditor;
var bookingeditor;

        contacteditor = new $.fn.dataTable.Editor( {
            table: '#esicontactstable',
            template: '#contacts_form',
            idSrc: "customer_id",
            fields: [{
                label: "Customer",
                name: "customer_id",
            }, {
                label: "Bookings",
                name: "bookings",
                type: 'datatable',
                editor: bookingeditor,
                config: {
                                        ajax: ???
                    columns: [
                        {
                            title: 'ID',
                            data: 'id'
                        }, {
                            title: 'Amount',
                            data: 'amount',
                        }, {
                            title: 'Date',
                            data: 'add_date',
                        }, {
                            title: 'Status',
                            data: 'status',
                        }
                    ],
                },
            },
            ], ... });

var bookingeditor = new $.fn.dataTable.Editor( {
            template: '#bookings_form',
            idSrc: "id",
            fields: [{
                label: "ID",
                name: "id",
                type: 'hidden',
            },  {
                label: "Customer",
                name: "contact_id",
                type: 'select',
                options: contactselect.data,
                placeholder: "auswählen",
            }, {
                label: "Amount",
                name: "amount",
            }, {
                label: "Status",
                name: "status",
                type: "select",
                options: [
                    { label: 'unbestätigt', value: 'open' },
                    { label: 'bestätigt', value: 'confirmed' },
                    { label: 'Warteliste', value: 'waiting' },
                    { label: 'storniert', value: 'storno' },
                    { label: 'abgeschlossen', value: 'completed' },
                ],
            }, {
                label: "Date",
                name: "add_date",
                type: 'datetime',
            }, {
                label: "Notes",
                name: "booking_notes",
                type: "textarea",
            },
            ], 
});

var bookingstable = $("#bookingstable").DataTable({
            ajax: {
              url: datatablesajax.url + '?action=getbookingsstable'
            },
            columns: [
                { data: 'id' },
                { data: null, render: function ( data, type, row ) {
                    return data.contact_id+' | '+data.contact;
                } },
                { data: 'amount', render: DataTable.render.number( null, null, 2, '€' ) },
                { data: 'add_date' },
                { data: null, render: function ( data, type, row ){
                    var label;
                    switch( data.status ){
                        case 'open':
                            label = "unbestätigt";
                            break;
                        case 'confirmed':
                            label = "bestätigt";
                            break;
                        case 'waiting':
                            label = "Warteliste";
                            break;
                        case 'storno':
                            label = "storniert";
                            break;
                        case 'completed':
                            label = "abgeschlossen";
                            break;
                        default:
                            label = "";
                    }
                    return label;
                } },
                {
                    data: null,
                    defaultContent: '<span class="dashicons dashicons-edit"></span>',
                    className: 'row-edit dt-center',
                    orderable: false
                },
            ],
            columnDefs: [
                {
                }
            ],
            order: [[0, 'desc']],
            autoFill: {
                editor: bookingeditor,
            },
            select: true,
            responsive: true,
            rowID: 'id',
            dom: 'Bfrtip',
            buttons: [
                { extend: "create", editor: bookingeditor },
                { extend: "edit", editor: bookingeditor },
                'copy', 'excel', 'pdf'
            ],
});

The customer json data from the ajax currently returns:

{"data":[
{"id":"1",
"customer_id":"1",
"firstname":"Regina",
"lastname":"Gschladt",
...,
"bookings:[
{"id":"1","contact_id":"1","amount":"200","discount":"1","status":"open","booking_notes":"","add_by":"4","mod_by":"0","add_date":"2022-07-13 13:28:18","mod_date":null}
]
}
]}

but the bookings table in the contact editor is empty - so I believe I've missed something?

Or am I completely wrong with this approach and need to do it differently?

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339

    Now I want to include the bookings of each customer in the customer editor, each booking obviously contains the customer_id.

    Looks like you want normal parent - child editing. The current field type "data table" doesn't really support this. It is rather focused on lookup tables to facilitate more complex option selection. But I created a work around for parent - child editing.

    Here is an example that works: https://datatables.net/forums/discussion/comment/210205

    Just take a look at my two replies at the bottom please. Good luck :smile:

  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin
    Answer ✓

    Just to add to that:

    I understand that I have to include the bookings for each customer inside the customer ajax call - right? So do I only include the booking ids in the customer data or the whole booking data?

    What to include in the JSON loaded for the host table (customers) will depend upon what you want to display in the customers DataTable. For something like this you might want to display only the number of bookings, or perhaps you want to be a bit more clever with it and show the number of bookings and their latest booking date, or something like that. That can be done with a renderer, but whatever data you want to display in the customer table about the bookings will need to be in the JSON, or at least derivable from it.

    At a minimum you need to have a list of the booking ids so they can be displayed / edited.

    Are you using our server-side libraries for this, or your own code? Can you also show me the database schema for these two tables and any link table you have for them (if there is one)?

    Allan

  • resqonlineresqonline Posts: 28Questions: 6Answers: 0

    Thanks @rf1234 for your answer and clarification on the datatables field - looking at the example again with that in mind, it is so obvious :D

    @allan so I could include the bookings either directly in the customer json and use maybe a display field (I already have this in place) and custom rendering for the bookings to be displayed or do a second ajax call on initEdit to get the bookings then. I also thought about using another lightbox for just showing the table of customer's bookings and use another editor in there.

    The customer db table only includes the customer data, the bookings each have a db field for the customer id to be referenced.

    I'm always conscious about the amount of data being loaded... the client has 20 years worth of data in his actual database, don't want to stress the system too much.

  • resqonlineresqonline Posts: 28Questions: 6Answers: 0

    @rf1234 did you mean parent child editing like in this example? If I can get this to work not underneath each other but in a lightbox it might be the right solution.

  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin
    Answer ✓

    Just to confirm, we are basically looking for this example in this style? That should be perfectly possible.

    In terms of data loading - how many rows are we talking about? Server-side processing might be the way to go here, which if you are using our PHP, .NET or Node.js libraries for Editor server-side is just a matter of enabling the server-side processing option.

    Allan

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339
    edited July 14

    Yes, I mean parent child editing like in the example that you quoted. But in the style of field type "datatable".

    Here is an example that works: https://datatables.net/forums/discussion/comment/210205

    So what does it look like in the example? I have three fields of type "datatable" in this Editor. The parent Editor only has 4 fields one "normal" field and 3 "datatable" fields with up to six columns. Works fine.

    Since field type "datatable" wasn't originally designed for parent - child editing but rather for working with lookup tables the parent editor wants to pass the field to the server. This is not adequate for real parent - child editing. Hence I need to delete those fields on "presubmit" of the parent editor like this:

    parentEditor
        .on('preSubmit', function( e, d, action) {
            //this crap has to be deleted to make field type "datatable" work:
            //we are not updating ANYTHING in the parent table for the "datatable" fields
            if ( typeof d.data !== 'undefined' ) {
                var key = Object.keys(d.data)[0];
                delete d.data[key].sub_exec_cashflow;
                delete d.data[key].sub_earmark;
                delete d.data[key].sub_proof_schedule;
            }
        })        
    
  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin
    Answer ✓

    That is actually possible with datatable. I've just created a little example that demonstrates how it might be done.

    @rf1234 Is that basically what you were looking for before?

    Allan

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339

    Hi Allan,

    it is only slightly different from what I have. Looks good!

    I don't have "optionsPair". What do you need it for in this case?

    And I don't have "submit: false". What is it doing? I noticed that it does not help buffer the new user entries because they are submitted and saved individually regardless of whether or not the parent Editor ist submitted.

    I don't see the server code. I had to delete the fields in the parent editor as shown above to make it work. You don't seem to have the necessity to do this?!

    Best
    Roland

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339

    I figured that "submit: false" just prevents the "datatable" field of the parent editor from being submitted. So it has the same effect as deleting the field in the "preSubmit" event handler. Is that correct? Didn't test this. And I didn't find "submit: false" in the docs either.

  • Loren MaxwellLoren Maxwell Posts: 338Questions: 78Answers: 9
    edited July 15

    @allan -- just a couple of bugs in the example when creating a new record:

    1

    • Select Los Angeles
    • Click Edit
    • Close the form without editing
    • Click New
    • The child records are from Los Angeles

    == Might need to completely refresh the page before duplicating the next bug ==

    2

    • Click New
    • Enter "Tokyo" in the Site name
    • Immediately click New for the datatable control
    • The Site select control is empty

    Because the Tokyo record is not yet saved by the time I try to add a new child record, I'm not sure the best way to address #2 except to perhaps not have the datatable control enabled unless the Tokyo record exists.

    Maybe the form should have two buttons, the regular save-and-close-editor button and a custom save-and-enable-databable-control button that doesn't close the form.

  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin

    @Loren Maxwell - Many thanks for the feedback! I'll get them fixed!

    @rf1234 - You are correct - it basically just doesn't submit that field giving the same effect as you delete approach you used. fields.submit is the doc for that property.

    Allan

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339

    Thanks Allan. I will use "fields.submit" now.

    But how about this?

    I don't have "optionsPair". What do you need it for in this case?

  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin

    It probably isn't required here to be honest. Since we don't care what the id is for the rows. I didn't actually try it without that option as I was so used to using it for the DataTable field type.

    Allan

  • rf1234rf1234 Posts: 2,355Questions: 76Answers: 339
    edited July 15

    I would drop it from the example because it isn't required for parent - child editing only for lookup tables, Allan. Otherwise it is confusing. I was coming from a different angle when I looked at field type "datatable" - and it confused me.

  • allanallan Posts: 56,837Questions: 1Answers: 9,033 Site admin

    Agreed - I'm travelling this week, but will check it and remove it if appropriate when I get back.

    Thanks for the feedback - I think this is a nice addition to the examples.

    Allan

  • resqonlineresqonline Posts: 28Questions: 6Answers: 0

    That is actually possible with datatable. I've just created a little example that demonstrates how it might be done.

    You're amazing @allan! That's exactly what I meant B)
    Trying to find the right way to implement this with all my custom form templates etc. I'll post my final solution when I got it.

  • resqonlineresqonline Posts: 28Questions: 6Answers: 0

    @allan just a little follow-up question regarding this:
    When I am in the parent editor and create a sub-item that while it's getting created changes a value of the parent item, do I need to re-initiate that field in the parent editor (it's a hidden one), so that if the parent editor gets submitted (the button is there, so users will click on it), the correct data is being submitted?

    In my example: if a payment (sub-item) gets submitted, it checks if its parent item (invoice) is fully payed and changes the status of the parent item in the database

  • resqonlineresqonline Posts: 28Questions: 6Answers: 0

    So this is my current setup:

    /* Payment Editor (child) */
    paymenteditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: datatablesajax.url + '?action=getpaymentstable',
            type: 'POST',
            data: function ( d ) {
                d.beleg_id = paysearch;
            }
        },
        table: "#payments",
        idSrc: "id",
        fields: [{
            label: "ID",
            name: "id",
            type: "hidden",
        }, {
            label: "Beleg",
            name: "beleg_id",
            type: "hidden",
        }, {
            label: "Datum",
            name: "paydate",
            type: 'datetime',
            displayFormat: 'DD.MM.YYYY',
            wireFormat: 'YYYY-MM-DD ',
        }, {
            label: "Betrag",
            name: "amount",
        }, {
            label: "Zahlungsmethode",
            name: "method",
            type: "select",
            options: [
                { label: 'Barzahlung', value: 'bar' },
                { label: 'Banküberweisung', value: 'bank' },
                { label: 'Guthaben', value: 'balance' },
            ],
            placeholder: 'auswählen',
        },
        ],
        ajax: {
            create: {
                type: 'POST',
                url: datatablesajax.url,
                data: {
                    action: 'createpayment',
                },
                dataType: "json",
                error: function(jqXHR, ajaxOptions, thrownError) {
                    alert(thrownError + "\r\n" + jqXHR.statusText + "\r\n" + jqXHR.responseText + "\r\n" + ajaxOptions.responseText);
                }
            },
            edit: {
                type: 'POST',
                url: datatablesajax.url,
                data: {
                    action: 'editpayment',
                },
                dataType: "json",
                error: function(jqXHR, ajaxOptions, thrownError) {
                    alert(thrownError + "\r\n" + jqXHR.statusText + "\r\n" + jqXHR.responseText + "\r\n" + ajaxOptions.responseText);
                }
            },
            remove: {
                type: 'POST',
                url: datatablesajax.url,
                data: {
                    action: 'deletepayment',
                },
                dataType: "json",
                error: function(jqXHR, ajaxOptions, thrownError) {
                    alert(thrownError + "\r\n" + jqXHR.statusText + "\r\n" + jqXHR.responseText + "\r\n" + ajaxOptions.responseText);
                }
            },
        }
    } );
    
    /* Accounting Editor (parent) */
    
    accounteditor = new $.fn.dataTable.Editor( {
        template: '#accounting_form',
        table: '#esiaccountingtable',
        idSrc: "id",
        fields: [{
            label: "ID",
            name: "id",
            type: 'hidden',
        }, {
            label: "Typ",
            name: "inex",
            type: "select",
            options: [
                { label: 'Einnahme', value: 1 },
                { label: 'Ausgabe', value: 2 },
            ],
            placeholder: "auswählen",
        }, {
            label: "Rechnung Nr.",
            name: "beleg_nr",
            fieldInfo: "Leer lassen für automatische Rechnungsnummer (bei Einnahme).",
        }, {
            label: "Rechnungsdatum",
            name: "beleg_datum",
            type: 'datetime',
            def: currentDate,
            displayFormat: 'DD.MM.YYYY',
            wireFormat: 'YYYY-MM-DD ',
        }, {
            label: "Kategorie",
            name: 'beleg_typ',
            type: 'select2',
            options: bookingcatselect.data,
            placeholder: "auswählen",
        }, {
            label: "Beleg Details",
            name: "beleg_details",
            type: "textarea",
        }, {
            label: "Kontakt",
            name: "contact_id",
            type: 'select2',
            options: contactselect.data,
            placeholder: "auswählen",
        }, {
            label: "Betrag",
            name: "amount",
        }, {
            label: "Status",
            name: "status",
            type: "select",
            options: [
                { label: 'offen', value: 'open' },
                { label: 'teilweise bezahlt', value: 'partly' },
                { label: 'bezahlt', value: 'payed' },
                { label: 'storniert', value: 'storno' },
            ],
            def: 'open',
        }, {
            label: "Notizen",
            name: "notes",
            type: "textarea",
        },
        {
            label: "Zahlungen",
            name: "payments",
            type: "datatable",
            editor: paymenteditor,
            submit: false,
            config: {
                ajax: {
                    url: datatablesajax.url + '?action=getpaymentstable',
                    type: 'POST',
                    data: function ( d ) {
                        d.beleg_id = paysearch;
                    }
                },
                buttons: [
                    { extend: 'create', editor: paymenteditor },
                    { extend: 'edit', editor: paymenteditor },
                    { extend: 'remove', editor: paymenteditor },
                ],
                columns: [
                    { data: 'id', title: 'ID' },
                    { data: 'beleg_id', title: 'Beleg', visible: false },
                    { data: 'paydate', title: 'Datum', render: function ( data, type, row ) {
                        return (data)
                            ? moment(data, "YYYY-MM-DD").format("DD.MM.YYYY")
                            : null;
                    },},
                    { data: 'amount', title: 'Betrag'},
                    { data: 'method', title: 'Zahlungsmethode', render: function ( data, type, row ){
                        var label;
                        switch( data ){
                            case 'bar':
                                label = "Barzahlung";
                                break;
                            case 'bank':
                                label = "Banküberweisung";
                                break;
                            case 'balance':
                                label = "Guthaben";
                                break;
                            default:
                                label = "";
                        }
                        return label;
                    } },
                ],
                searching: false,
            },
        },
        ],
        ajax: {
            create: {
                type: 'POST',
                url: datatablesajax.url,
                data: {
                    action: 'createinex',
                },
                dataType: "json",
                error: function(jqXHR, ajaxOptions, thrownError) {
                    alert(thrownError + "\r\n" + jqXHR.statusText + "\r\n" + jqXHR.responseText + "\r\n" + ajaxOptions.responseText);
                }
            },
            edit: {
                type: 'POST',
                url: datatablesajax.url,
                data: {
                    action: 'editinex',
                },
                dataType: "json",
                error: function(jqXHR, ajaxOptions, thrownError) {
                    alert(thrownError + "\r\n" + jqXHR.statusText + "\r\n" + jqXHR.responseText + "\r\n" + ajaxOptions.responseText);
                }
            },
        },
    } );
    

    there is also a global

    var paysearch;
    

    and some extra stuff for the parent editor:

    accounteditor.on( 'initCreate', function() {
        accounteditor.field('payments').hide();
        accounteditor.field('status').hide();
    });
    accounteditor.on( 'initEdit', function() {
        accounteditor.field('payments').show();
        accounteditor.field('status').hide();
        paysearch = accountstable.row({ selected: true }).data().id;
        accounteditor.field('payments').dt().ajax.reload(function (json) {
            paymenteditor.field('beleg_id').update(); // ???
        });
        paymenteditor.field('beleg_id').def(accountstable.row({ selected: true }).data().id);
    });
    

    I would welcome any hints on how to improve this! Absolutely not sure what that update function does... not sure, why I need to call the child ajax two times...

Sign In or Register to comment.