HOW TO: Fetch Child Tables

[bedo]

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]

@Manu

customer_name,

try writing trigger here.

[vivek_ilexSquare]

@bedo

Trigger

write here gettimesheetdata

[bghayad]

@Jitendra_Khatri1

@Jitendra_Khatri1

for t in doc_a.get(“field_name_of_child_table_in_doctype_A”):

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

[adam26d]

@manu

In your case I see that in the lines below

@Manu

var tabletransfer= frappe.model.get_doc(“Medical Report”, frm.doc.customer_name)
$.each(qmtable.medical_h, function(index, row){

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]

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]

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]

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]

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

[Juri]

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]

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]

Thanks, it worked for me.

[ponyooooo]

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]

This guide might help

Community Developed Custom Scripts · frappe/erpnext Wiki

[ponyooooo]

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

hi @ponyooooo,

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

[Chibuzor_Derrick]

Hello @rmeyer

I tried your script and it worked perfect but i was wondering how best i could modify the scripts to add more columns and the respective data will be in the same row. Below is my script

[Chibuzor_Derrick]

on the custom scripts, i added the following column from the source table called “concreting” which are “job”, “description”, “unit”. it actually imported them but they are not on the same row as shown below

[ponyooooo]

Hi Juri,

No. I am not sure if the cause of the version of my ERPNext. My current version is

ERPNext: v12.4.2 (version-12)
Frappe Framework: v12.2.1 (version-12)

[ponyooooo]

Hi @Chibuzor_Derrick may I request if you could paste the code you have used please? So I can try the codes also? Thank you.