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.