Get list of child table

Frappe Framework Custom Script
1

I want to show selling rate history for particular customer while user entering item_code in Sales Invoice Item

frappe.call({ method:"frappe.client.get_list",
		args:{
			doctype: "Sales Invoice",
			fields: ["items.item_code", "items.rate"],
			filters:[
					["customer","=",frm.doc.customer]
				],
			limit_page_length: 5
		},
    		callback: function(res){ .... }
});

but items.item_code is not recognized…how to get list of child table?

2 
frappe.call({ method:"frappe.client.get_list",
		args:{
			doctype: "Sales Invoice",
			fields: ["items"],
			filters:[
				["customer","=",frm.doc.customer]
			],
			limit_page_length: 5
		},
    		callback: function(res){ .... }
});

Just query the items

3

got this error
OperationalError: (1054, "Unknown column 'tabSales Invoice.items' in 'field list'")

4

Sorry for my previous answer. Here is the correct way to do it

frappe.call({ method:"frappe.client.get_list",
		args:{
			doctype: "Sales Invoice Item",
			fields: ["item_code", "rate"],
			filters:[
				["parent","=", name_of_parent_doc]
			],
			limit_page_length: 5
		},
    		callback: function(res){ .... }
});
2 Likes
5

@netchampfaris I want to get all history rates for particular item and selected customer…regardless its parent.

The use case is to ease the user to get quick view of previous rates of selected customer…without going to another page/report…

So I want to pass both parent & child parameters to get bot parent & child table details…but I think the method cannot do this

6

@netchampfaris this is my workaround…I created custom field ‘customer’ and ‘docstatus’ inside items child table upon save and submit of Sales Invoice. Therefore I can retrieve the items history…

do you know better way to do this without creating another customer field in child table…below function didn’t work if we pass parent parameter…can we pass parent parameter to get child table? which function to be used here?

frappe.call({ 
		method:"frappe.client.get_list",
		args:{
			doctype: "Sales Invoice Item",
			fields: ["*"],
			filters: [
					["item_code", "=", d.item_code],
					["customer", "=", frm.doc.customer],					
					["docstatus", "=", 1]
				],
			limit_page_length: 5
		},
    		callback: function(res){
		if (res.message){ .... }
1 Like
7

trying the same but i’m getting error You do not have enough permissions to access this resource. Please contact your manager to get access although i’m logged in as administrator

2 Likes
8

Even i’m getting the same error, if anyone can help in solving this error.

9

Is there anybody got the solution?

10

Solution:

11 
await frappe.db.get_list('CHILD TABLE DOCTYPE',//Ex:Item Customer Details
{
	parent: 'PARENT DOCTYPE',//Ex:Item
	fields: ['*'],
	filters: ['parent = "PARENT DOCTYPE NAME"'],//Ex:Pencil
	limit: 99,
	order_by: 'idx asc'
}).then((result) => { console.log(result); });
12

For JS

await frappe.db.get_list("BOM Operation", { 
	filters: {
		"parent": "BOM-Onur-009", 
		"idx": 1
	}, 
	fields:["*"]
});

For Jinja

{% set bom_operations = frappe.db.get_list("BOM Operation", 
	filters = { "parent": doc.bom, "idx": operation.idx }, 
	fields=["*"] ) 
%}
13

At least in v15, I don’t believe it’s possible to get child table results using frappe.db.get_list. Instead, it can be done using frappe.call on the whitelisted method frappe.client.get_list.

const operations_list = frappe.call('frappe.client.get_list', {
   doctype: 'BOM Operation',
   parent: 'BOM',
   fields: ['parent', 'name']
}).then(r => r.message)
2 Likes