Filtered dropdown in Link Field Type

Frappe Framework App Development
11

Something like how “source warehouse” in the row in items of Stock Entry is selected?
Checkout stock_entry.js

Something like how BOM calculates raw material cost from rows in items? Checkout bom.js

12

I want to filter item_code in child table based on custom field brand_name

My parent doc name: Delivery Note
child table : Delivery Note Item ( field_name : items)

i had added following script in “Delivery Note” custom script but not working. Please help

cur_frm.fields_dict[‘items’].grid.get_field(‘item_code’).get_query = function(doc) {
return{
filters:[
[‘brand’, ‘=’, brand_name]
]
}
}

13

@jyotinb , Please try with following code :

frappe.ui.form.on("Delivery Note", "onload", function(frm, cdt, cdn){
	frm.set_query("item_code", "items", function(cdt, cdn) {
		var c_doc = locals[cdt][cdn];
		return {
		       "filters": ['brand', '=', c_doc.brand_name]
		};
	});
});
2 Likes
14

Thanks for fast reply

Not working

copy script in custom script of “Delivery Note” custom script is ok?

15

My query again with clear documentation.

I want to filter item_code in Delivery note based on field brand_name
(both item_code & brand_name are in child table Delivery Note Item (field name items))

I had created extra field ‘i1’ type link field and options Item (same as item_code)

  1.   My following code works pefectly for 'i1' , but not for 'item_code'.

cur_frm.fields_dict[‘items’].grid.get_field(‘i1’).get_query = function(doc) {
return {

		filters: {
			 "brand": "brand1"
			
		}
	}
}
  1. and how to refer dynamic filter field
    ‘brand_name’ instead of fixed value “brand1”.
16

hi @jyotinb,

if you need a dynamic filter, then you need to

  1. get the value with which you need to filter (use get_value) in onload
  2. then you apply the filter with that value.
    3 then you refresh listview

refer Filter list view using callback data

17

@priya_s Hi, I’ve tried the code but it won’t categorize our list??

9.png956×756 47 KB

8.png932×984 59.4 KB

Here’s the code:

cur_frm.fields_dict[‘requested_by’].get_query = function(doc) {
return {
filters: {
“Operations”: doc.department,
“Admin”: doc.department,
“Sales”: doc.department,
“Engineering”: doc.department,
“Legal”: doc.department,
“Purchase”: doc.department,
“Marketing”: doc.department,
“Human Resources”: doc.department,
}
}
}

18

@kelscey90, something is incorrect in code.
By seeing screenshots,as per my understanding you have to show only those employee in drop down which having department “Engineering”.
so in filter you have write only department value,in filter try with this-
filters: {
“department”: doc.department
}

19

@priya_s Yes I only need to show those people who are engineers only when engineering is chosen. But it kept showing everyone. I’ve tried your custom script but is still wont categorize it?

This is my overall script:

frappe.ui.form.on(“Stock Request”, “onload”, function(frm, cdt, cdn, doc, dt, dn){
frm.add_fetch(“item_code”, “description”, “description”);
frm.add_fetch(“item_code”, “item_name”, “item_name”);
frm.add_fetch(“item_code”, “stock_uom”, “uom”);
frm.add_fetch(“item_code”, “item_group”, “item_group”);
frm.add_fetch(“item_code”, “brand”, “brand”);
frm.add_fetch(“requested_by”, “Non-Alcon Personnel”, “non_alcon_personnel”);
frm.add_fetch(“tc_name”, “terms”, “terms”);
frm.set_value(“date_requested”, frappe.datetime.add_days(frappe.datetime.nowdate()));

})

cur_frm.fields_dict[‘requested_by’].get_query = function(doc) {
return {
filters: {
“Department”: doc.department,
}
}
}

20

If you are using default Department form try with this in filter-
“department_name”: doc.department

Thanks, Priya

21

@priya_s still won’t work. Btw I have 2 field which is a link type and a select type. I copied the name on the employee module list and inserted it on the select type field. Is it correct?

22

Can you please share screen-shots?

23

1.png1416×952 82.1 KB
2.png1064×1128 99.2 KB

3.png1396×820 85.4 KB

24

@kelscey90, This is not correct way to listout employees. For Requested By field set field type is Link and in Option set Employee as like department. Correct this and try again.

Another question is that why are you write frm.add_fetch code on onload of form.? add_fetch method use when you select any value from dropdown then related to that form value fetched and set automatically in current forms field.

1 Like
25

We need those fetch codes for our other fields to insert automatically when item code is inserted. Is it not possible to write a very long and different script at on module?

I tried it and this error appeared when I tried the department field.

Traceback (innermost last):
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/handler.py”, line 36, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/init.py”, line 806, in call
return fn(*args, **newargs)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/desk/search.py”, line 13, in search_link
search_widget(doctype, txt, query, searchfield=searchfield, page_len=page_len, filters=filters)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/desk/search.py”, line 92, in search_widget
as_list=True)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/desk/reportview.py”, line 17, in execute
return DatabaseQuery(doctype).execute(*args, **kwargs)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/model/db_query.py”, line 66, in execute
result = self.build_and_run()
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/model/db_query.py”, line 83, in build_and_run
return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug)
File “/home/frappe/press/benches/1605021224/apps/frappe/frappe/database.py”, line 147, in sql
self._cursor.execute(query)
File “/home/frappe/press/benches/1605021224/env/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/press/benches/1605021224/env/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘tabEmployee.department_name’ in ‘where clause’”)

26

Oh it got fixed now! Thank you @priya_s. It just my mistake with the scripts! Sorry!

Btw does our choices have to be emp/0001 then under it is the name of the employee. Is there anyway that we can see only the name? and can we hide the field and appears only when department is chosen?

27

@kelscey90, Ohhkk…Try with your old code in filter, repace department instead of department_name
i.e. “department”:doc.department

1 Like
28

Employee get saved by EMP/001 so you are not see only name. But you can also search employee by name. For that go to customize_form,select form Employee and set employee_name in Search Fields. Then you can see employee list like this -

1 Like
29

if one field means it’s working if i retrieve multiple field in same doctype means

30

Hi @priya_s I had wrote set query for this link field to display employee_name alone in drop down and i am getting the result but problem after selecting the employee_name from drop down it is not setting in that field it gets disappeared is there any way to set the value which i am selecting from drop down?