Try ERPNext Buy Support Partners Foundation

HOW TO: Fetch Child Tables

Fetching Child Tables
DocTypeA = Source DocType (The document that holds the child table you are getting values from
DocTypeB = Target DocType (The document that the values are going into
ChildTableA= Source Child Table
ChildTableB=Target Child Table
Trigger = trigger/custom field = this is a link to DocTypeA. The fomula triggers when the field is modified. If you want to have the trigger be something other than the link field, just change the first trigger. Some examples are:

  • onload = when the document is loaded, the function runs.
  • validate = when the form is saved, the function runs.
  • refresh = when the form is refreshed, the function runs. I’m not sure how this is different than onload…
  • on_submit = the function runs when the document is submitted, but note that it only runs after the doc is considered submitted.

field1,2 = fields that are pulled from ChildTableA
fielda,b = ChildTableB equivalents of fields in ChildTableA

frappe.ui.form.on("DocTypeB", "Trigger", function(frm) {
frappe.model.with_doc("DocTypeA", frm.doc.trigger, function() {
var tabletransfer= frappe.model.get_doc("DocTypeA", frm.doc.Trigger)
$.each(qmtable.ChildTableA, function(index, row){
d = frm.add_child("ChildTableB");
d.field1 = row.fielda;
d.field2 = row.fieldb;
cur_frm.refresh_field("ChildTableB");
})
});
});
13 Likes
// for js side

frappe.ui.form.on("Doctype B","field_name of link field of doctype A",function(frm){
        	if(cur_frm.doc.field_name of link field of doctype A){
        		return frappe.call({
        			method: "get_child_table",
        			args:{
        			"doc":cur_frm.doc.(field_name of link field of doctype A)
        			},
        			callback: function(r) {
        			if(cur_frm.doc.child_table_field_name_in_doctype B){
        				cur_frm.doc.child_table_field_name_in_doctype B = ""
        				refresh_field("child_table_field_name_in_doctype B")
        			}
        			$.each(r.message, function(i, d) {
        				var row = frappe.model.add_child(cur_frm.doc, "Child Table Doctype Name(as you add in option in Doctype B)", "child_table_field_name_in_doctype B");
        				row.field_1 = d.field_1;
        				row.field_2 = d.field_2;
        				refresh_field("child_table_field_name_in_doctype B");
        				});	
        			}
        		})
        	}
        })

//for python side

        def get_child_table(doc):
        		doc_a = frappe.get_doc("Doctype Name OF A",doc)
        		list1 = []
        		for t in doc_a.get("field_name_of_child_table_in_doctype_A"):
        			list1.append({
        							'field_1'(b):t.field_1(a),
        							'field_2'(b):t.field_2(a)
        						})
        		return list1
6 Likes

Hi,
First of all , Thank you for the explanations on how this script works. It really helps beginners to understand.

i have followed your guidance on how to fetch Child Table but nothing is happening …

I have placed the custom script for Doctype A where:
Target Doctype: Customer*
Source Doctype: Medical Report
Target Childtable: list_of_allergies
Source Childtable: medical_h
Trigger:“customer_name” (is the fieldname of my link field to Customer Doctype)
Both childtables have similar layout and field names but are 2 different ChildDoctype (respectivly MedH C & MedH MR).<-- i don’t know if this matters

Here’s how the script looks:

frappe.ui.form.on(“Customer”, “customer_name”, function(frm) {
frappe.model.with_doc(“Medical Report”, frm.doc.customer_name, function() {
var tabletransfer= frappe.model.get_doc(“Medical Report”, frm.doc.customer_name)
$.each(qmtable.medical_h, function(index, row){
d = frm.add_child(“list_of_allergies”);
d.date = row.date;
d.description = row.description;
d.issue = row.issue;
cur_frm.refresh_field(“list_of_allergies”);
})
});
});

I am not getting any error message… did i make a syntax error ? or am i missing something ?

anyone, please … 4 days i m struggling on this :confused:

I’m trying to fetch data from timesheet detail into sales invoice timesheet, but with no luck :frowning:

frappe.ui.form.on("Sales Invoice", "gettimesheetdata", function(frm) {
frappe.model.with_doc("Timesheet", frm.doc.trigger, function() {
var tabletransfer= frappe.model.get_doc("Timesheet", frm.doc.Trigger)
$.each(qmtable.time_logs, function(index, row){
d = frm.add_child("timesheets");
d.task_name = row.task_name;

cur_frm.refresh_field("timesheets");
})
});
});

Script is saved under “Sales Invoice” custom script.
Both “Sales Invoice Timesheet” and “Timesheet Detail” have field calles “task_name”
gettimesheetdata is a button.

When I press the button I get the following error message.

try writing trigger here.

write here gettimesheetdata

@Jitendra_Khatri1

Do you the table field name or you mean a field name inside the child table?
Regards
Bilal

@manu

In your case I see that in the lines below

You fetched the result with tabletransfer but are trying to loop through it using qmtable, just replace the qmtable with tabletransfer.

Hope you managed!

Let me add a nicely formatted example.

  • 'Target DocType': The one you are currently working with, where the data is supposed to go.
  • 'Source DocType': The one that contains the data.
  • link_to_source: Fieldname of the Target DocType that contains the link to 'Source DocType'.
  • target_table: Fieldname of the table in 'Target DocType'.
  • source_table: Fieldname of the table in 'Source DocType'.
  • column_name: Fieldname of the column in Child Table.
frappe.ui.form.on('Target DocType', {
    link_to_source: function (frm) {
        if (frm.doc.link_to_source) {
            frm.clear_table('target_table');
            frappe.model.with_doc('Source DocType', frm.doc.link_to_source, function () {
                let source_doc = frappe.model.get_doc('Source DocType', frm.doc.link_to_source);
                $.each(source_doc.source_table, function (index, source_row) {
                    frm.add_child('target_table').column_name = source_row.column_name; // this table has only one column. You might want to fill more columns.
                    frm.refresh_field('target_table');
                });
            });
        }
    },
});

What this does: Whenever the link to 'Source DocType' changes, the above script clears the target table and adds the records from the source table.

By the way, this also works for TableMultiselect fields, as the underlying data structure is the same.

6 Likes