JS: set_query - ashish-greycube/help GitHub Wiki

Note :

1. filter from direct doctype and filter from coding(set query from js file) are not working together.

Case 1 :

filter conditions are from only one doctype

i.e doctype: Stock Entry field : item_code conditions: is_stock_item = 1 & is_customer_provided_item = 1

Solution 1 :

stock_entry.js

frappe.ui.form.on("Stock Entry", {

    onload: function(frm){
        frm.set_query("item_code","items", function (doc,cdt,cdn){
            if (frm.doc.stock_entry_type=="Handling Loss" || frm.doc.stock_entry_type=="Audit Shortage" || frm.doc.stock_entry_type=="Cargo Received") {
                return {
                    filters: {
                        is_stock_item: 1,
                        is_customer_provided_item: 1
                    }
                };       
            }else{
                return {
                    filters: {
                        is_stock_item: 1                 
                    }  
                } 
            }
        })
    }
})

Solution 2 :

    refresh: function(frm) {
        frm.fields_dict['boq_items_cf'].grid.get_field('item_code').get_query = function(doc, cdt, cdn) {
            var child = locals[cdt][cdn];
            return {    
                filters:[
                    ['Item', 'item_group', '=', child.item_group]
                ]
            };
        };        
    },

Case 2 :

filter conditions are from different doctype

i.e doctype : Rake Dispatch field : grade condition: show list of commidity which is mentioned in vessel's child table

Solution 1 :

rake_dispatch.js

frappe.ui.form.on("Rake Dispatch", {
    setup(frm) {
        frm.set_query("grade", "rake_prelim_entry", function (doc, cdt, cdn) {
            let row = locals[cdt][cdn];
            if (row.vcn_no) {
                return {
                    query: "kict.kict.doctype.rake_dispatch.rake_dispatch.get_unique_grade_list",
                    filters: {
                        vessel_name: row.vcn_no
                    },
                };
            }
        });
    },
});

rake_dispatch.py

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def get_unique_grade_list(doctype, txt, searchfield, start, page_len, filters):
	vcn_no = filters.get("vessel_name")
	return frappe.get_all(
		"Vessel Details",
		parent_doctype="Vessel",
		filters={"parent": vcn_no,"grade": ("like", f"{txt}%")},
		fields=["distinct grade"],
		as_list=1,
	)

or

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def get_civil_employee(doctype, txt, searchfield, start, page_len, filters):
	employee = frappe.db.get_all("Employee", filters={"status":"Active","name": ("like", f"{txt}%")}, fields=["name", "custom_contract_type"])

	civil_employees = []
	for emp in employee:
		contract_type = frappe.db.get_value("Contract Type ST", emp.custom_contract_type, "contract")
		if contract_type == "Civil":
			employee_name = (emp.get('name'),)
			civil_employees.append(employee_name)
		else:
			continue
		
	return civil_employees

Solution 2 :

after set query, if do not able to filter values then..

journal_entry.js

frappe.ui.form.on("Journal Entry", {
  onload: function (frm) {
    frm.set_query("party", "accounts", function (doc, cdt, cdn) {
      let query = "",
        party_type = locals[cdt][cdn]["party_type"];
      if (party_type == "Customer")
        query = "happay.api.customer_query";
      else if (party_type == "Supplier")
        query = "happay.api.supplier_query";
      return {
        query: query,
        filters: {
          company: frm.doc.company,
        },
      };
    });
  },
});

journal_entry.py

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def supplier_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
	doctype = "Supplier"
	supp_master_name = frappe.defaults.get_user_default("supp_master_name")

	fields = ["name"]
	if supp_master_name != "Supplier Name":
		fields.append("supplier_name")

	fields = get_fields(doctype, fields)

	return frappe.db.sql(
		"""select {field} from `tabSupplier`
		where docstatus < 2
			and ({key} like %(txt)s
			or supplier_name like %(txt)s) and disabled=0
			and (on_hold = 0 or (on_hold = 1 and CURRENT_DATE > release_date))
			{mcond}
		order by
			(case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
			(case when locate(%(_txt)s, supplier_name) > 0 then locate(%(_txt)s, supplier_name) else 99999 end),
			idx desc,
			name, supplier_name
		limit %(page_len)s offset %(start)s""".format(
			**{"field": ", ".join(fields), "key": searchfield, "mcond": get_match_cond(doctype)}
		),
		{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
		as_dict=as_dict,
	)

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def customer_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
	doctype = "Customer"
	conditions = []
	cust_master_name = frappe.defaults.get_user_default("cust_master_name")

	fields = ["name"]
	if cust_master_name != "Customer Name":
		fields.append("customer_name")

	fields = get_fields(doctype, fields)
	searchfields = frappe.get_meta(doctype).get_search_fields()
	searchfields = " or ".join(field + " like %(txt)s" for field in searchfields)

	return frappe.db.sql(
		"""select {fields} from `tabCustomer`
		where docstatus < 2
			and ({scond}) and disabled=0
			{fcond} {mcond}
		order by
			(case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
			(case when locate(%(_txt)s, customer_name) > 0 then locate(%(_txt)s, customer_name) else 99999 end),
			idx desc,
			name, customer_name
		limit %(page_len)s offset %(start)s""".format(
			**{
				"fields": ", ".join(fields),
				"scond": searchfields,
				"mcond": get_match_cond(doctype),
				"fcond": get_filters_cond(doctype, filters, conditions).replace("%", "%%"),
			}
		),
		{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
		as_dict=as_dict,
	)