Fixing the frustrating search of items

1

Users were frustrated with how Item search was behaving.

Let’s say item name is ‘GRANDMA WF456 S2000’
You type ‘WF456 GRAN’, you get nothing
You type ‘GRAN WF45’, still nothing

So we fixed it for most used Doctypes by calling this function from the client script:

@frappe.whitelist()
def custom_search(doctype, txt, searchfield, start, page_len, filters):
    # Split the search text into individual words
    terms = txt.split()

    # Construct the SQL query with OR conditions for item_code, item_name, and barcode
    conditions = []
    for term in terms:
        conditions.append(f"(item.item_code LIKE '%{term}%' OR item.item_name LIKE '%{term}%' OR barcode.barcode LIKE '%{term}%')")

    where_clause = " AND ".join(conditions) if conditions else "1=1"

    # Execute the query and get results as tuples
    query = f"""
        SELECT DISTINCT item.name, item.item_name
        FROM `tabItem` item
        LEFT JOIN `tabItem Barcode` barcode ON barcode.parent = item.name
        WHERE {where_clause}
        LIMIT {start}, {page_len}
    """
   
    results = frappe.db.sql(query)
    return results

And called it from this script:

const parentDoctypes = ['Stock Entry', 'Delivery Note', 'Material Request'];

parentDoctypes.forEach(parentDoctype => {
    frappe.ui.form.on(parentDoctype, {
        refresh: function(frm) {
            applyCustomSearchToChildTable(frm);
        }
    });
});

function applyCustomSearchToChildTable(frm) {
    // Assume 'items' is the field name for the child table in the parent doctype
    frm.fields_dict['items'].grid.get_field('item_code').get_query = function() {
        return {
            query: "bth.bth.bth_utils.custom_search",
            filters: {
                // Additional filters can be specified here if needed
            }
        };
    };
}

But I would very much like to make this logic for search global. By this logic I mean:

  • Each term can be part of words and searched link must contain that term