How to Dynamically Filter Link Fields in ERPNext using Server-Side Queries
Server Side QueryLearn to apply dynamic, context-aware filters to Link Fields in ERPNext Client Scripts using frm.set_query to call server-side Python methods.
In Frappe and ERPNext, Link Fields are powerful but often need to be filtered based on other data entered in the form. For example, you might want to show only contacts belonging to a selected customer, or items from a specific warehouse. Standard filters set in the DocType are static.
The `frm.set_query` method in Frappe's client-side API provides a robust solution. It allows you to override the default search behavior of a Link Field and delegate the filtering logic to a custom, whitelisted Python method on the server. This enables dynamic, real-time filtering that can significantly improve user experience and data integrity.
1 // Example 1: Filter Items in a Child Table based on a parent field 2 frm.set_query("item_code", "items", function(doc, cdt, cdn) { 3 return { 4 query: "erpnext.controllers.queries.item_query", 5 filters: frm.doc.enquiry_type === "Maintenance" ? 6 {"is_service_item": "Yes"} : {"is_sales_item": "Yes"} 7 }; 8 }); 9
10
11 // Example 2: Filter Contacts based on a linked Supplier (Broker) 12 cur_frm.set_query("broker_contact", function() { 13 if(cur_frm.doc.broker) { 14 return { 15 query: "frappe.contacts.doctype.contact.contact.contact_query", 16 filters: { link_doctype: "Supplier", link_name: cur_frm.doc.broker } 17 }; 18 } 19 else frappe.throw(__("Please set Broker")); 20 }); 21
22
23 // Example 3: Filter Contacts in a Child Table based on parent Customer 24 cur_frm.set_query("contact", "escalations", function(doc, cdt, cdn) { 25 return { 26 query: "frappe.contacts.doctype.contact.contact.contact_query", 27 filters: { link_doctype: "Customer", link_name: cur_frm.doc.customer } 28 }; 29 });
Understanding This Code
What It Does
This snippet demonstrates how to override the default query for a Link Field in a Frappe form. It allows you to apply dynamic filters by calling a server-side Python method that returns a filtered list of records based on other values in the current document.
When To Use
Use this in a Client Script, typically within the 'onload' or 'refresh' events, or on a field's 'change' event trigger. It's ideal when a Link Field's options need to be filtered based on real-time data from the form (e.g., filtering items by category, or contacts by linked customer).
Prerequisites
- •A basic understanding of Frappe Client Scripts.
- •A whitelisted Python method on the server to handle the query logic.
Key Concepts
Important ideas to understand in this code
frm.set_query
A client-side Frappe API method used to override the default query for a Link Field. It can be applied to fields in the parent DocType or within a Child Table. It tells the system to call a specific server-side Python method for fetching the list of options.
Learn moreServer-Side Query Method
A whitelisted Python function that receives the query request from the client. It must be located in a Python file within your Frappe app and is responsible for building and returning a query that Frappe's list view can execute. It receives filters and text from the client.
Learn moreWhitelisting (@frappe.whitelist)
A security decorator in Frappe that exposes a Python function to be callable from the client-side (e.g., via AJAX calls or set_query). Without this decorator, the server will reject the request.
Learn moreStep-by-Step Tutorial
Follow along to understand how this code works
Identify the Target Field and Trigger
First, determine which Link Field you want to filter and when the filter should be applied. In our example, we'll filter the 'item_code' field inside the 'items' child table. The filter logic depends on the 'enquiry_type' field in the parent document, so we'll trigger this on form load or refresh.
// Target field: 'item_code'
// Child Table (if applicable): 'items'
// Triggering field: 'enquiry_type' change or form refreshCreate the Client Script
In your DocType, create a new Client Script. Use the `frappe.ui.form.on` hook to attach your code to an event like 'refresh'. Inside, call `frm.set_query`. The first argument is the field name, the second is the child table name (if applicable), and the third is a function that returns the query object.
frappe.ui.form.on('Your DocType', {
refresh: function(frm) {
frm.set_query("item_code", "items", function(doc, cdt, cdn) {
// Query logic will go here
});
}
});Define the Server-Side Query Path and Filters
Inside the function, return an object containing the 'query' path to your Python method and a 'filters' object. The filters object is a dictionary of key-value pairs that will be passed to your server-side method.
frm.set_query("item_code", "items", function() {
return {
query: "erpnext.controllers.queries.item_query",
filters: {
'is_service_item': frm.doc.enquiry_type === "Maintenance" ? 1 : 0,
'is_sales_item': frm.doc.enquiry_type === "Sales" ? 1 : 0
}
};
});Implement the Python Method (Server-Side)
In the specified Python file (e.g., erpnext/controllers/queries.py), create the function. It must be decorated with @frappe.whitelist(). The function receives the 'filters' dictionary from the client. Use these filters to build your database query.
import frappe
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def item_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
if filters.get("is_service_item"):
conditions.append("is_service_item = 1")
if filters.get("is_sales_item"):
conditions.append("is_sales_item = 1")
# Example of a simple SQL query construction
# In production, use frappe.get_list for better permission handling
return frappe.db.sql(f"""SELECT name, item_name, description
FROM `tabItem`
WHERE {' AND '.join(conditions)} AND (`tabItem`.`{searchfield}` LIKE %(txt)s)
ORDER BY `tabItem`.`idx` DESC, `tabItem`.`name` ASC
LIMIT %(page_len)s OFFSET %(start)s""", {
'txt': f'%{txt}%',
'start': start,
'page_len': page_len,
})Common Issues & Solutions
Troubleshoot problems you might encounter