Is there anyway I can add child table inside a child table?
@avc
I don’t just need for data output so I can’t use the second solution.
The first solution was never merged so how can I use that in my custom app?
Yes, child within a child would be very helpful & has been discussed a few times, but I don’t think it is on the current roadmap.
The only 2 workarounds I’m familiar with are:
- On the first child doctype, instead of storing all the data in the child table, make a field in the child doctype be a linked field to another (standard) doctype, and then you can put the 2nd child doctype on that standard doctype form that the first child doctype field links to.
Or,
- On the first child doctype form, in an HTML field create a custom HTML table that shows data for the 2nd child doctype. This second option obviously requires more custom development, especially if you want in-grid editing etc.
Neither solution is very elegant. It would be great if Frappe eventually supported nested child doctypes.
1 Like
Hmm 
,
@Abhiraj_Tulsyan and @DCRob,
Child tables are used to represent one-to-many relationships between two DocTypes. Child tables allow you to store multiple records related to a parent document in a structured way. Each row in a child table represents a record related to the parent document.
The reason why nesting child tables (child tables within a child table) is not supported is related to the underlying database structure and the way relationships are defined. The current design of the data model doesn’t allow for direct nesting of child tables.
Here are some reasons why nesting child tables might not be supported:
-
Database Structure: The database schema used may not be designed to handle nested child tables efficiently. Implementing nested child tables could complicate the database structure and may lead to performance issues.
-
Data Integrity: Nesting child tables could potentially introduce complexities in maintaining data integrity and consistency. It might make it more challenging to manage and update nested data relationships.
-
User Interface: The user interface is designed around the concept of child tables, and introducing nested child tables could complicate the user experience and UI design.
-
Development and Maintenance: Supporting nested child tables would require significant changes to the ERPNext codebase. It might also introduce challenges in terms of maintaining and upgrading the system.
While nested child tables might provide a more flexible data structure in some cases, the current design choices prioritize simplicity, performance, and ease of use. The workarounds you mentioned, such as using linked fields or custom HTML tables, are ways users can achieve a similar outcome within the existing framework, although they may not be as elegant as having native support for nested child tables.
I hope this helps.
Thank You!
Hi DCRob, i am looking for solution for same problem.
I am new in this Frappe Framework, option one looking good solution for me.
Further question, after make a linked field in the child doctype, can we take data (one value of one field in this child doctype) and store automaticaly to one field of another (standard) doctype which is opened from that linked field?
Yes, I think you could do that. I’m not a programmer, but using a script you should be able to, if the standard ‘fetch’ feature of the Frappe framework isn’t sufficient.
There are at least two ways I know of for “doing” this, realistically, both using the built-in availability of frappe.DataTable, which provides a drop-in table element and data manager wherever you want one, including in child table forms.
The first “way” circumvents the need for an additional nested child doctype, by simply loading and saving structured data to a dedicated JSON field on your existing child doctype. This can be done elegantly and with little code, but presents some challenges to querying the data. The more recent addition of native JSON functions to MariaDB and PostgresSQL do help to close the gap here significantly. You can, for example, load, query, and join JSON data as native SQL data using JSON_TABLE. The advantage of this method is its elegance: it can be implemented entirely as a fairly minimal frontend script, and augmented with backend scripts, as necessary.
The second way is similar, but allows “true” nested doctypes by querying and saving data to/from an external child doctype/table into a frappe.DataTable. This preserves all the conventional doc and database API functionality for your nested doctype, but requires implementing backend methods to handle the data transfers on load and save… so a bit more scripting.
I’ve implemented both of these, and I hope to extrapolate and demonstrate a more general-purpose implementation of both approaches in the future. I personally feel that the strict child/parent designation for Doctypes is a weakness of the Framework, and deserves to be re-engineered, but that’s another topic for another time. For now, hopefully that spurs some thinking about what’s possible and can garner some scripting ideas.
4 Likes
Please share, as I also think that this is a weakness in the Framework that would be greatly benefited by a solution.
Here’s a start, using a combination of conventions across a few projects. I haven’t tested this particular code, however, so please let me know how you make out.
The idea here is that you have a doctype “Parent”, with child table Doctype “Child” linked to Table field children. The “Child” Doctype contains a JSON field grandchild_data used to store structured data as if it was a nested child table.
With these conventions in mind, the following code should work inside parent.js to manage the “grandchild” widget and data in each row, visible within the child table form:
// parent.js
frappe.ui.form.on('Child', {
form_render: function(frm, cdt, cdn) {
var cur_row = locals[cdt][cdn];
const dialog_form = frm.fields_dict.children.grid.open_grid_row;
if (dialog_form) {
setupGrandchildDatatable(frm, cur_row, dialog_form);
}
}
});
function setupGrandchildDatatable(frm, child_row, dialog_form) {
var grandchild_wrapper = dialog_form.fields_dict.grandchild_data.wrapper;
grandchild_wrapper.innerHTML = '';
var grandchild_fields = grandchildFields;
var grandchild_data = JSON.parse(child_row.grandchild_data || '[{}]');
var datatable = new frappe.DataTable(grandchild_wrapper, {
columns: grandchild_fields.map(df => ({
id: df.fieldname,
name: df.label,
editable: !df.read_only,
focusable: true,
dropdown: false,
df: df,
format: (value) => frappe.format(value, df)
})),
data: grandchild_data,
layout: 'fluid',
checkboxColumn: true,
serialNoColumn: false,
inlineFilters: false,
pasteFromClipboard: false,
cellHeight: 40,
getEditor: createEditor(frm, child_row, dialog_form),
events: {
onRemoveColumn: () => updateGrandchildData(frm, child_row, dialog_form),
onRemoveRow: () => updateGrandchildData(frm, child_row, dialog_form)
}
});
dialog_form.fields_dict.grandchild_data.datatable = datatable;
// allow overflow
datatable.datatableWrapper.style.overflow = 'visible';
datatable.bodyScrollable.style.overflow = 'visible';
// button to add new row
var add_row_button = document.createElement('button');
add_row_button.className = 'btn btn-xs btn-default';
add_row_button.textContent = 'Add Row';
add_row_button.addEventListener('click', function() {
datatable.datamanager.data.push({});
datatable.refresh();
});
grandchild_wrapper.appendChild(add_row_button);
// button to remove selected rows
var remove_row_button = document.createElement('button');
remove_row_button.className = 'btn btn-xs btn-danger';
remove_row_button.textContent = 'Remove Rows';
remove_row_button.addEventListener('click', function() {
const selectedRows = datatable.rowmanager.getCheckedRows();
selectedRows.forEach(index => {
datatable.datamanager.data.splice(index, 1);
});
datatable.refresh();
updateGrandchildData(frm, child_row, dialog_form);
});
grandchild_wrapper.appendChild(remove_row_button);
}
const grandchildFields = [
{
fieldname: 'field1',
label: 'Field 1',
fieldtype: 'Data',
reqd: 1
},
{
fieldname: 'field2',
label: 'Field 2',
fieldtype: 'Select',
options: ['Option 1', 'Option 2', 'Option 3'],
reqd: 1
},
{
fieldname: 'field3',
label: 'Field 3',
fieldtype: 'Date'
}
];
function createEditor(frm, child_row, dialog_form) {
return (colIndex, rowIndex, value, parent, column, row, data) => {
let df = grandchildFields.find(f => f.fieldname === column.id);
if (!df) return false;
let control = frappe.ui.form.make_control({
df: { ...df, default: value },
parent: parent,
render_input: true
});
if (df.fieldtype !== "Check") {
control.toggle_label(false);
control.toggle_description(false);
}
// allow overflow
control.wrapper.parentElement.style.overflow = 'visible';
return {
initValue: (value) => control.set_value(value),
setValue: (value) => {
control.set_value(value);
data[column.id] = value;
updateGrandchildData(frm, child_row, dialog_form);
const datatable = dialog_form.fields_dict.grandchild_data.datatable;
if (rowIndex == datatable.datamanager.data.length - 1) {
datatable.datamanager.data.push({});
datatable.refresh();
}
},
getValue: () => control.get_value()
};
};
}
function updateGrandchildData(frm, child_row, dialog_form) {
const datatable = dialog_form.fields_dict.grandchild_data.datatable;
const data = datatable.datamanager.data
.filter(row => {
// Check if at least one field in the row has a non-empty value
return Object.values(row).some(value =>
value !== undefined &&
value !== null &&
value !== ''
);
});
child_row.grandchild_data = JSON.stringify(data);
frm.dirty();
}
Since this approach utilizes the Frappe field conventions for grandchildFields, it shouldn’t be too hard to envision how this could be adapted to use an external Doctype for the “Granchild” table, and push and pull the field definitions and data accordingly. The grandchild_data approach as demonstrated would require manually-coded JSON_TABLE functions to incorporate the “Grandchild” data into reports.
Now… someone can take it from here and publish an app template that incorporates these approaches. I’m out of time 
6 Likes
Huzzah! I just discovered a much simpler way to accomplish this, by utilizing frappe.ui.FieldGroup and it’s native fields parameter rather than declaring a new frappe.DataTable directly. Not only is the code cleaner, but the user experience is also currently better with the frappe.ui.form.ControlTable control vs the frappe.DataTable. Here’s code that replicates the above approach, but this time utilizing frappe.ui.FieldGroup:
// parent.js
frappe.ui.form.on('Child', {
form_render: function (frm, cdt, cdn) {
let child_row = locals[cdt][cdn];
let dialog = frm.fields_dict.children.grid.open_grid_row;
if (!dialog) return;
let wrapper = dialog.fields_dict.grandchild_data.wrapper;
wrapper.replaceChildren();
let field_group = new frappe.ui.FieldGroup({
fields: [{
fieldtype: 'Table',
fieldname: 'grandchild_table',
in_place_edit: true,
data: JSON.parse(child_row.grandchild_data || '[]'),
fields: [
{
fieldname: 'field1',
label: 'Field 1',
fieldtype: 'Data',
in_list_view: 1,
reqd: 1
},
{
fieldname: 'field2',
label: 'Field 2',
fieldtype: 'Select',
options: ['Option 1', 'Option 2', 'Option 3'],
in_list_view: 1,
reqd: 1
},
{
fieldname: 'field3',
label: 'Field 3',
fieldtype: 'Date',
in_list_view: 1
}],
get_data: () => field_group.get_value('grandchild_table')
}],
body: wrapper
});
field_group.make();
// Update the JSON field whenever the table changes
field_group.fields_dict.grandchild_table.grid.wrapper.on('change', () => {
child_row.grandchild_data = JSON.stringify(
field_group.get_value('grandchild_table')
);
frm.dirty();
});
}
});
The only quirky thing I’ve encountered so far is that deleting a row borks some of the rendering in the child row form dialog box. Specifically, it breaks the darked background around the dialog box, removing the easiest target for closing the dialog, forcing the user to click on the close (∨) button instead.
Merry Christmas! ~
5 Likes
what a smart solution.
