Filtered dropdown in Link Field Type

priya_s · December 18, 2015, 12:46pm

@ninjas005 You can also use this -

cur_frm.fields_dict["child_table_fieldname"].grid.get_field("user").get_query = function(doc){
       return {
               filters:{
                       "gender": "Male"
               }
       }
}

revant_one · December 18, 2015, 12:49pm

Check out Mode of Payment in ERPNext.

There is a docfield “accounts” in Mode of Payment with type “Table”.
“account” field is a child table “Mode of Payment Account”.

default_account is the field in child table which is to be filtered.

ninjas005 · December 18, 2015, 12:57pm

Hi @revant_one
Hi @priya_s

I’ve tried both methods… Both Works

Thank you

ninjas005 · January 7, 2016, 4:24am

Hi @revant_one @priya_s

Need help with two things.

How can I change a value in the child form, when I change a value in the same form?
Also change the value in the parent form, when i change the value in the child form?

revant_one · January 8, 2016, 6:45am

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

github.com

frappe/erpnext/blob/develop/erpnext/stock/doctype/stock_entry/stock_entry.js#L191


      
          			callback: function() {
          				refresh_field('items');
          			}
          		});
          	},
          
          	refresh: function(frm) {
          		frm.trigger("get_items_from_transit_entry");
          
          		if(!frm.doc.docstatus) {
          			frm.trigger('validate_purpose_consumption');
          			frm.add_custom_button(__('Material Request'), function() {
          				frappe.model.with_doctype('Material Request', function() {
          					var mr = frappe.model.get_new_doc('Material Request');
          					var items = frm.get_field('items').grid.get_selected_children();
          					if(!items.length) {
          						items = frm.doc.items;
          					}
          
          					mr.work_order = frm.doc.work_order;
          					items.forEach(function(item) {

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

github.com

frappe/erpnext/blob/develop/erpnext/manufacturing/doctype/bom/bom.js#L120


      
          
          		if (!frm.is_new() && !frm.doc.docstatus == 0) {
          			frm.add_custom_button(__("New Version"), function() {
          				let new_bom = frappe.model.copy_doc(frm.doc);
          				frappe.set_route("Form", "BOM", new_bom.name);
          			});
          		}
          
          		if(frm.doc.docstatus==1) {
          			frm.add_custom_button(__("Work Order"), function() {
          				frm.trigger("make_work_order");
          			}, __("Create"));
          
          			if (frm.doc.has_variants) {
          				frm.add_custom_button(__("Variant BOM"), function() {
          					frm.trigger("make_variant_bom");
          				}, __("Create"));
          			}
          
          			if (frm.doc.inspection_required) {
          				frm.add_custom_button(__("Quality Inspection"), function() {

jyotinb · February 4, 2016, 6:00am

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

priya_s · February 4, 2016, 7:26am

@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]
		};
	});
});

jyotinb · February 4, 2016, 8:03am

Thanks for fast reply

Not working

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

jyotinb · February 5, 2016, 11:33am

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)

  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"
			
		}
	}
}

and how to refer dynamic filter field
‘brand_name’ instead of fixed value “brand1”.

ninjas005 · February 5, 2016, 2:06pm

hi @jyotinb,

if you need a dynamic filter, then you need to

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

refer Filter list view using callback data

kelscey90 · May 2, 2016, 8:16am

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

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,
}
}
}

priya_s · May 2, 2016, 9:34am

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

kelscey90 · May 2, 2016, 10:05am

@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,
}
}
}

priya_s · May 2, 2016, 10:15am

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

Thanks, Priya

kelscey90 · May 2, 2016, 10:18am

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

priya_s · May 2, 2016, 10:19am

Can you please share screen-shots?

kelscey90 · May 2, 2016, 10:27am

priya_s · May 2, 2016, 10:34am

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

kelscey90 · May 2, 2016, 10:43am

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’”)

kelscey90 · May 2, 2016, 10:47am

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?