Use server script in set_query

ERPNext Customize ERPNext
, , ,
1

Hi,
I want to filter the Item Group in Request Site Expense Item depending on the child table in the selected Customer Agreement child table included Item Group

image
image1448×832 43.5 KB

Customer Contract

image
image1771×403 44.4 KB

I want to show the item group that I saved in Estimate Item CC child table in Customer Contract

3

Any help ?

4

Hi @Omar_Mohammed:

You will need a custom query, see this doc:

https://frappeframework.com/docs/user/en/guides/app-development/overriding-link-query-by-custom-script#1-adding-filters

Hope this helps.

1 Like
5

Thank you @avc, it’s good resource but it’s not explain how can pass parameter from js to python file (query) as shown in example

image
image842×878 108 KB

6

This is my code
doctype.js

	refresh:function(frm){
		frm.set_query('item_group', 'request_site_expense_items', function(doc, cdt, cdn) {
			var d = locals[cdt][cdn];
			return {
				query: "construction.construction.doctype.request_site_expense.request_site_expense.get_item_group",
				filters: {
					'customer_contract': frm.doc.customer_contract
				}
			};
		});
};

Python file

@frappe.whitelist()
def get_item_group(doctype, txt, searchfield, start, page_len, filters):	
	frappe.msgprint(str(filters))
	return frappe.db.sql("""
		SELECT
			ig.name
		FROM
			`tabItem Group` ig
		INNER JOIN
			`tabEstimate Item CC` icc on ig.name = icc.item_group
		INNER JOIN
			`tabCustomer Agreement` ca on ca.name = icc.parent
		INNER JOIN
			`tabWorker Agreement` wa on ca.name = wa.contract
		INNER JOIN
			`tabWorker Agreement Items` wai on wai.parent = wa.name
			and wai.item_group = icc.item_group
		WHERE
			ca.party_one like %s
			
	""", ("%" + txt + "%"))

I need to pass customer_contract to compare it with

ca.party_one = customer_contract

7

I solved

1 Like
8

Hello Omar . I want to ask you if you can help me with a problem similar to yours, which is that I want to filter the options based on the section name.