Try ERPNext Buy Support Partners Foundation

Create a child table from doctype list

hi!
needed some help. lets say i want to populate a child table of a certain doctype with the details from another doctype list. how would i go about it? for example for a specific customer, if i want to create a child table of all the sales invoice and amount, how would i go through the Sales invoice list to search for the specific customer?
TIA

Hi @Yash_Agarwal,

I don’t really follow why you would like to do this as this is normally done with the dashboard. However, your use case should be easily covered with a custom script like this:

frappe.ui.form.on("Customer", {
  refresh: function(frm) {
    frappe.call({
      method: "frappe.client.get_list",
      args:{
 	doctype:"Sales Order",
 	filters: [ ["customer","=", frm.doc.name] ],
        fields: ["name"]
      },
      callback: function(response) {
        if (response.message) {
          response.message.forEach(function(entry) {
            //frappe.msgprint(entry.name);
            var child = cur_frm.add_child('sales_orders');
            frappe.model.set_value(child.doctype, child.name, 'sales_order', entry.name);
	  }); 
        }
      }
    }) 
  }
}); 

Make sure to have a suitable child table “sales_orders” that this can fill into. And make sure to clean the list before filling, something like this should do the job:

 // remove all rows
 var tbl = frm.doc.sales_orders|| [];
 var i = tbl.length;
 while (i--)
 {
     cur_frm.get_field("sales_orders").grid.grid_rows[i].remove();
 }
cur_frm.refresh();

Hope this helps.

1 Like

Hi,
Thanks a lot for your response. the function worked pretty much as i desired. i used the sales invoice as an example. However, two issuse:

  1. the child table isn’t populating automaticatically. i have to click on add rows after which it happens. also, there is an extra row getting added in the end.

  2. one more question. from the doctype i wish to extract the information from, there is a child table from which i need to take out the weight total. How should i go about that.

i have atrached my code below. kindly take a look and any suggestions regarding how i should go about it?

frappe.ui.form.on(“Packing List Record”, “lc_number”, function(frm) {
frappe.call({
method: “frappe.client.get_list”,
args:{
doctype:“TR-Truck Receipt”,
filters: [ [“lc_no”,"=", frm.doc.lc_number] ],
fields: [“posting_date”,“naming_series”,“data_29”,“vehicle_no”]
},
callback: function(response) {
if (response.message) {
response.message.forEach(function(entry) {
//frappe.msgprint(entry.name);
var child = cur_frm.add_child(‘packing_child’);
//var d=cur_frm.add_child(entry.data_29);
frappe.model.set_value(child.doctype, child.name, ‘vehicle_no’, entry.vehicle_no);
//frappe.model.set_value(child.doctype, child.name, ‘nt_weight’, entry.data_29.qty);
frappe.model.set_value(child.doctype, child.name, ‘packing_date’, entry.posting_date);
frappe.model.set_value(child.doctype, child.name, ‘tr_no’, entry.naming_series);
});
}
}
})
});

1 Like

Hi @Yash_Agarwal,

glad that it worked.

Your code above looks fine; it only triggers on field changes of “lc_number”. I guess this is intended. Is your child table called “packing_child”? Have you checked with debug triggers like the message box or console.log("..."); whether the code is executed? Also, note that you can make the code more readable in this board when indenting it with 4 spaces (or hit CTRL-SHIFT-C on the selected block).

To get data from a child doctype, simply run another frappe.client.get_list and specify the name of the child table doctype. The “parent” field will contain a reference to the record that contains the child table.

Hope this helps.

Hi @lasalesi ,

Your Code works great! but it populates only 20 rows on hitting add row, how to add parameter in functions that can add more than 20 rows? and is there any solution to extract the exact value from select field, which is used as naming series. There should be solution :sleepy:

Try this (using the parameter limit_page_length, which is 20 by default):

frappe.ui.form.on("Customer", {
  refresh: function(frm) {
    frappe.call({
      method: "frappe.client.get_list",
      args:{
 	doctype:"Sales Order",
 	filters: [ ["customer","=", frm.doc.name] ],
        fields: ["name"],
        limit_page_length: 100
      },
      callback: function(response) {
        if (response.message) {
          response.message.forEach(function(entry) {
            //frappe.msgprint(entry.name);
            var child = cur_frm.add_child('sales_orders');
            frappe.model.set_value(child.doctype, child.name, 'sales_order', entry.name);
	  }); 
        }
      }
    }) 
  }
});
3 Likes

I am trying similar for the project Doctype where I can show the user all the list of Quotations that are make for this projects. I am not sure on what Fields the child table to have for this purpose. I very new to ERPNext and I am trying to set it for my company. Please help me.

Below is the Code I have edited as per your code:
frappe.ui.form.on(‘Project’, {
refresh(frm) {

frappe.call({
  method: "frappe.client.get_list",
  args:{
doctype:"Quotation",
filters: [ ["project_name","=", frm.doc.name] ],
    fields: ["name"],
    limit_page_length: 100
  },
  callback: function(response) {
    if (response.message) {
      response.message.forEach(function(entry) {
       var child = cur_frm.add_child('Quotations');
        frappe.model.set_value(child.doctype, child.name, 'Quotations', entry.name);
  }); 
    }
  }
}) 

}

})