Frappe Client Scriptjavascript

How to Dynamically Filter Link Fields in Frappe ERPNext

Field Filtration

Learn how to apply dynamic filters to Link and Child Table fields in Frappe ERPNext using the get_query method in custom Client Scripts for context-aware selections.

In ERP systems like Frappe and ERPNext, ensuring data integrity and improving user experience often involves guiding users to select the correct records. A common requirement is to filter the options in a 'Link' field based on other data entered on the form. For instance, you might want to show only the invoices belonging to a selected customer.

Frappe provides a powerful client-side API, `get_query`, to achieve this. By overriding this function for a specific field, you can dynamically pass filters to the backend query that populates the Link field's dropdown, creating a context-aware and intuitive user interface.

javascript
1// Example 1: Basic filter on a standard Link Field
2// This code filters the 'topic_name' Link Field based on the value in the 'subject_name' field.
3cur_frm.fields_dict.topic_name.get_query = function(doc) {
4	return {
5		filters: {
6			"subject": doc.subject_name
7		}
8	}
9};
10

11// Example 2: Filtering a Link Field within a Child Table
12// This script filters the 'batch_no' field inside the 'items' child table, based on the 'item_name' in the same row.
13cur_frm.fields_dict.items.grid.get_field("batch_no").get_query = function(doc, cdt, cdn) {
14	let row = locals[cdt][cdn];
15	return {
16		filters: {
17			"item": row.item_name,
18		}
19	}
20};

Understanding This Code

What It Does

These scripts dynamically filter the options available in a Link Field based on values from other fields in the current document, both on the main form and within child table rows.

When To Use

Use this in a Client Script, typically within the `onload` or a field's `onchange` event, whenever you need to restrict user selections in a Link Field to a context-specific subset of records.

Prerequisites

  • •Basic understanding of Frappe DocTypes and fields.
  • •Familiarity with creating and attaching Client Scripts in ERPNext.
  • •Access to a Frappe development environment to apply the script.

Key Concepts

Important ideas to understand in this code

cur_frm

A global object in Frappe's client-side scripting environment that represents the current form being viewed. It provides access to the form's data (doc), fields (fields_dict), and various methods to manipulate the form.

Learn more

get_query

A function property on a Link Field controller that can be overridden to customize the query used to fetch options for that field. It must return an object containing a 'filters' key.

Learn more

Link Field

A Frappe field type that creates a link to another DocType, allowing users to select a record from that DocType. It is rendered as a dropdown with a search-as-you-type functionality.

Learn more

Child Table (Grid)

A field type that allows you to embed a table of records from another DocType (a Child DocType) within a parent form. Accessing fields within a child table requires navigating through the grid object.

Learn more

Step-by-Step Tutorial

Follow along to understand how this code works

1

Create a New Client Script

Navigate to the 'Client Script' list in your Frappe/ERPNext desk. Create a new script and select the DocType you want to apply the filter to. For example, if you are filtering fields on a Sales Invoice, select 'Sales Invoice'.

bash
// Select 'Sales Invoice' in the 'Select DocType' field in the Client Script form
Next Step
2

Apply a Basic Filter to a Main Form Field

To filter a standard Link Field on the main form, access the field via `cur_frm.fields_dict`. In this example, we filter invoices to show only those matching the selected customer and that are submitted (`docstatus: 1`).

javascript
frappe.ui.form.on('Sales Order', {
    refresh: function(frm) {
        frm.fields_dict.invoice_no.get_query = function(doc) {
            return {
                filters: {
                    'docstatus': 1,
                    'customer': doc.customer
                }
            }
        };
    }
});
Next Step
3

Filter a Link Field Inside a Child Table

Filtering a field in a child table (grid) is slightly different. You need to access the child table field first, then the grid, and finally use `get_field()` to target the specific field inside the table. The function receives `cdt` and `cdn` to get the current row's data from the `locals` dictionary.

javascript
frappe.ui.form.on('Purchase Order', {
    refresh: function(frm) {
        frm.fields_dict.items.grid.get_field("batch_no").get_query = function(doc, cdt, cdn) {
            let item_row = locals[cdt][cdn];
            return {
                filters: {
                    'item': item_row.item_code, // Filter batches by the item in the current row
                    'warehouse': item_row.warehouse
                }
            };
        };
    }
});
Next Step
4

Using 'IN' Operator for Multiple Values

Instead of a key-value object, you can pass an array of arrays to the filters. This allows for more complex operators like 'in', 'not in', 'like', etc. This is useful for filtering against a predefined list of values.

javascript
frappe.ui.form.on('Task', {
    refresh: function(frm) {
        frm.fields_dict.project.get_query = function(doc) {
            return {
                filters: [
                    ['project_type', 'in', ['Internal', 'Maintenance', 'Support']]
                ]
            };
        };
    }
});

Common Issues & Solutions

Troubleshoot problems you might encounter