Overriding Link Query By Custom Script

You can override the standard link query by using set_query via the Client Script DocType from the desk.

The set_query method takes one of two formats: set_query(field_name, options_function()) for regular fields, or set_query(field_name, child_table_name, options_function()) for fields in child tables.

1. Adding Filters

You can add filters to the query:

frappe.ui.form.on("Bank Reconciliation", "onload", function(frm) {
    frm.set_query("bank_account", function() {
        return {
            "filters": {
                "account_type": "Bank",
                "group_or_ledger": "Ledger"
            }
        };
    });
});

A more complex query:

frappe.ui.form.on("Bank Reconciliation", "onload", function(frm){
    frm.set_query("bank_account", function(){
        return {
            "filters": [
                ["Bank Account", "account_type", "=", "Bank"],
                ["Bank Account", "group_or_ledger", "!=", "Group"]
            ]
        }
    });
});

2. Calling a Different Method to Generate Results

You can also set a server side method to be called on the query:

frm.set_query("item_code", "items", function() {
    return {
        query: "erpnext.controllers.queries.item_query",
        filters: frm.doc.enquiry_type === "Maintenance" ?
            {"is_service_item": "Yes"} : {"is_sales_item": "Yes"}
    };
});

Custom Method

The custom method should return a list of items for auto select. If you want to send additional data, you can send multiple columns in the list.

Parameters to the custom method are:

def custom_query(doctype, txt, searchfield, start, page_len, filters)

Example:

# searches for leads which are not converted
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def lead_query(doctype, txt, searchfield, start, page_len, filters):
    return frappe.db.sql("""
        SELECT name, lead_name, company_name
        FROM `tabLead`
        WHERE docstatus < 2
            AND ifnull(status, '') != 'Converted'
            AND ({key} LIKE %(txt)s
                OR lead_name LIKE %(txt)s
                OR company_name LIKE %(txt)s)
            {mcond}
        ORDER BY
            IF(LOCATE(%(_txt)s, name), LOCATE(%(_txt)s, name), 99999),
            IF(LOCATE(%(_txt)s, lead_name), LOCATE(%(_txt)s, lead_name), 99999),
            IF(LOCATE(%(_txt)s, company_name), LOCATE(%(_txt)s, company_name), 99999),
            name, lead_name
        LIMIT %(start)s, %(page_len)s
    """.format(**{
            'key': searchfield,
            'mcond':get_match_cond(doctype)
        }), {
        'txt': "%{}%".format(txt),
        '_txt': txt.replace("%", ""),
        'start': start,
        'page_len': page_len
    })

Note: @frappe.whitelist() is used to expose lead_query to the client-side and @frappe.validate_and_sanitize_search_inputs decorator is used to validate and sanitize user inputs sent through API or client-side request to avoid possible SQLi.

For more examples see:

https://github.com/frappe/erpnext/blob/develop/erpnext/controllers/queries.py