ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Custom Filter Sorting Issue in Link field


#1

Hi,

We applied custom query through set_query function on quotation item, and its working fine for filtering data. But the SQL query sorting is getting applied from the second value of link list. We are not able to understand how to apply sorting from the first item of the link list.

In below example, the first Item should be “HOT AIR OVEN-DIGITAL-90X60X60 CM-250°C-INNER S.S. CHAMBER” as per the code, given below.

Below is the query being called from PY file

def new_item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
	conditions = []
	
	return frappe.db.sql("""
		select
			tabItem.name, tabItem.item_group
		from
			tabItem
		where 
			tabItem.docstatus < 2
			and tabItem.has_variants=0
			and tabItem.disabled=0
			and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
			and (tabItem.`{key}` LIKE %(txt)s
				or tabItem.item_name LIKE %(txt)s
				or tabItem.item_group LIKE %(txt)s
				or tabItem.barcode LIKE %(txt)s)
			{fcond} {mcond}
		order by
			default_selection desc,
			if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
			if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
			idx desc,
			item_name
		limit %(start)s, %(page_len)s """.format(
			key=searchfield,
			fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
			mcond=get_match_cond(doctype).replace('%', '%%')),
			{
				"today": nowdate(),
				"txt": "%s%%" % txt,
				"_txt": txt.replace("%", ""),
				"start": start,
				"page_len": page_len
			}, as_dict=as_dict)

and here is the js file of Sales Order to call above query

this.frm.cscript.onload = function(frm) {
	this.frm.set_query("item_code", "items", function() {
		return {
		      	query: "forex.api.new_item_query",
			filters: {'is_sales_item': 1}
		}
	});
}

Would highly appreciate if someone can point a place to look for the solution.