HOW TO: Fetch Child Tables

cpurbaugh · May 4, 2016, 11:28pm

Everyone: I’ve updated this tutorial on my website, which is much better than what is below. I’m leaving the old one as is here, as it is referenced in subsequent posts. You can click this sentence to go to the article.

Expand for original post:

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");
})
});
});

Jitendra_Khatri1 · May 5, 2016, 6:11am

// 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

Manu · June 1, 2017, 9:01pm

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 ?

Manu · June 5, 2017, 3:31pm

anyone, please … 4 days i m struggling on this

bedo · June 26, 2017, 11:30am

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

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.

vivek_ilexSquare · July 15, 2017, 10:18am

try writing trigger here.

vivek_ilexSquare · July 15, 2017, 10:22am

write here gettimesheetdata

bghayad · September 4, 2017, 2:40pm

@Jitendra_Khatri1

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

adam26d · September 19, 2017, 12:35pm

@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!

rmeyer · October 30, 2020, 5:06pm

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.

Juri · December 7, 2020, 8:47pm

hello @rmeyer,

do you know if your source code is also valid for subordinate DocTypes that are not marked as Child Doctype in the ERPNext system?

Example:

I have created a DocType “Offer”. Here I would like to suggest a list of required components for the quotation.

The list should contain information like name, brand, supplier and so on.

I take the components from the DocType “Item”, which in turn contains the DocType “Supplier”. However, the DocType “Supplier” is not listed as a child table in the DocType configuration.

I ask therefore, because I can get no supplier information of my component by means of your source code, into my field intended for it of my offer Doctype.

In addition, can be that I do something wrong. Therefore I post my source code in the hope to get help.

frappe.ui.form.on('Offer', {
    link_to_source: function (frm) {
        if (frm.doc.get_component) {
            frm.clear_table('supplier_name_offer_bom');
            frappe.model.with_doc('Item', frm.doc.get_component, function () {
                let source_doc = frappe.model.get_doc('Item', frm.doc.get_component);
                $.each(source_doc.supplier_items, function (index, source_row) {
                    frm.add_child('components_offer_bom').supplier_name_offer_bom = source_row.supplier_name_offer_bom; // this table has only one column. You might want to fill more columns.
                    frm.refresh_field('components_off_bom');
                });
            });
        }
    },
});

Here the corresponding, previously determined supplier should appear automatically.

New Offer (test)

Offer DocType settings

Offer Bill of Materials Doctype settings

Item DocType settings

Supplier Items DocType settings

and Supplier DocType settings

hope 4 help and best regards!

rmeyer · December 8, 2020, 8:49am

Hi @Juri, my code snippet expects your get_component field to be in your Offer doctyoe, not in a child table. What you want to do is possible but needs to be triggered from the child table.

I couldn’t test if it works, but maybe something like this:

// located in custom script for doctype Offer
frappe.ui.form.on('Offer BOM', 'get_component', function(frm, cdt, cdn) {
    const target_row = locals[cdt][cdn];
    const item_name = target_row.get_component;
    if (item_name) {
        frappe.model.with_doc('Item', item_name, function () {
            const item_doc = frappe.model.get_doc('Item', item_name);
            const source_row = item_doc.supplier_items[0]; // first row of Item's supplier table
            target_row.supplier_name_offer_bom = source_row.supplier;
            frm.refresh_field('components_offer_bom');
        });
    }
});

Juri · December 8, 2020, 1:22pm

Thank you for your timely feedback @rmeyer and in the meanwhile, i´ll try to find an alternative solution.

Juri · December 8, 2020, 1:30pm

Oh I only see now that you have already posted a possible solution. I have replied to the old version of your post

Thanks, I’ll try it out right now

Juri · December 8, 2020, 10:59pm

Ok so first of all, @rmeyer thanks for your help.

I sat a little while now to understand your code snippet, because one line made me work properly to understand it, because at some point it turned out that there was the problem.

To understand your code snippet I added some jscript alarm functions, which show me the value of the respective constant/variable.

I wanted to add at this point, that my javascript knowledge is pretty much the same as a beginner.

Nevertheless I approximately know how to start.

Let’s go through the lines.

If I have understood this correctly

locals[cdt][cdn]

already a provided function of the, Frappe Framework.

const target_row = locals[cdt][cdn];
window.alert('target_row: ' + target_row);

Here it is only confirmed that I will get a constant of the datatype object.

I also know (because of the alarm) that JScript has already recognized that the field get_component has been activated.

const item_name = target_row.get_component;
window.alert('item_name: ' + item_name);

Here I get the respective number of the component, which I have selected in the field get_component at this moment.

if (item_name)

If I interpret the if line correctly, then it says here If the constant item_name exists or a value is stored for it, then…

const item_doc = frappe.model.get_doc('item', item_name);
window.alert('item_doc: ' + item_doc);

Here the same as in the first lines.Only the information that I will get a constant of the data type object.

item_doc: [object Object]

The next line brought me difficulties, because I was constantly told that source_row is undefined, which I didn’t understand.

const source_row = item_doc.supplier_items[0]; 
window.alert('source_row: ' + source_row)

In addition to that the compiler did not compile anything else (at least the last lines of the overall function). I think so, because I placed an alarm function afterwards and it didn’t appear anymore.

By coincidence I found out that I really had no value in the field supplier. This was because last night, due to server problems, I did a rollback of the virtual server. Afterwards, as I thought of course, I brought the DocTypes back to the correct status and as it looks like I forgot to enter the supplier in the item again.

Now that everything is working, it is of course much easier to understand the code!

I would say let’s finish the line description for the other users anyway.

target_row.supplier_name_offer_bom = source_row.supplier;
window.alert('target_row.supplier_name_offer_bom: ' + target_row.supplier_name_offer_bom)

This is where the content of the supplier field from the Item Supplier table is stored.

and then the command
frm.refresh_field('components_offer_bom');
refreshes the whole table.

I hope I have interpreted the lines correctly and would be happy (if I am wrong) to be corrected here.

You and the other users from this thread were a great help to me to get closer to the principle of ERPNext! From there I say thanks again!

aquinot · January 27, 2021, 9:30pm

Thanks, it worked for me.

ponyooooo · February 4, 2021, 4:20am

Hi @Juri How can I integrate these codes if I need to fetch data on a parent doctype and encode it on the child table using a dynamic link?

I was trying to fetch the data on a custom field on the parent doctype of the Sales Invoice then encode it on the child table payment entry references.

This is same on how the Supplier Invoice No. works. When the dynamic link is a Purchase Invoice, a supplier invoice no. field will be visible.

Vesper_Solutions · February 4, 2021, 9:35am

This guide might help

ponyooooo · February 4, 2021, 4:33pm

@Vesper_Solutions thank you for sharing the link. However, I had a hard time making the codes work with a dynamic link as a trigger or to fetch the source doctype of it

Juri · February 10, 2021, 11:56am

hi @ponyooooo,

Excuse me, I’ve been too busy with other construction sites. Did the code from Vesper_Solutions help?