Trying to get nested Editor/DataTable to work
Trying to get nested Editor/DataTable to work
resqonline
Posts: 58Questions: 14Answers: 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
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
Just to add to that:
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
Thanks @rf1234 for your answer and clarification on the datatables field - looking at the example again with that in mind, it is so obvious
@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.
@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.
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
Yes, I mean parent child editing like in the example that you quoted. But in the style of field type "datatable".
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:
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
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
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.
@allan -- just a couple of bugs in the example when creating a new record:
1
== Might need to completely refresh the page before duplicating the next bug ==
2
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.
@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.submitis the doc for that property.Allan
Thanks Allan. I will use "fields.submit" now.
But how about this?
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
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.
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
You're amazing @allan! That's exactly what I meant
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.
@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
So this is my current setup:
there is also a global
and some extra stuff for the parent editor:
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...