ERPNext CustomizationJavaScript

How to Dynamically Filter Link Fields in a Frappe/ERPNext Child Table

Table Field Filteration

Learn how to apply dynamic filters to Link Fields within a child table (Grid) in Frappe ERPNext using the get_query client script method. This snippet demonstrates filtering based on parent document fields and static values.

In Frappe and ERPNext development, a common requirement is to filter the options available in a Link Field within a child table (Grid). This ensures data integrity and improves the user experience by only showing relevant choices. For example, when selecting invoices in a payment entry, you should only see invoices belonging to the selected supplier.

This is achieved using a Client Script and the `get_query` method. By assigning a function to this method, you can dynamically pass filters to the backend query that populates the Link Field, often using values from the parent document.

javascript
1// Example 1: Filtering based on parent document fields
2// Filters 'invoice_no' in the 'invoices' child table
3cur_frm.fields_dict.invoices.grid.get_field("invoice_no").get_query = function(doc) {
4	return {
5		filters: {
6			"docstatus": 1, // Only show submitted invoices
7			"supplier": doc.supplier, // Match parent's supplier
8			"company": doc.company, // Match parent's company
9		}
10	}
11};
12

13// Example 2: Filtering based on a static value
14// Filters 'item' in 'container_details' table to only show 'Spares'
15cur_frm.fields_dict.container_details.grid.get_field("item").get_query = function(doc) {
16	return {
17		filters: {
18			"item_group": "Spares"
19		}
20	}
21};
22

23// Example 3: Filtering using an 'IN' condition with an array
24// Filters 'main_item' in the 'items' table based on a list of allowed items
25cur_frm.fields_dict.items.grid.get_field("main_item").get_query = function(doc) {
26    // item_list would be defined elsewhere in your script
27    let item_list = ["ITEM-001", "ITEM-002", "ITEM-003"];
28	return {
29		"filters": [
30		  	["Item", "item_code", "in", item_list]
31		]
32	};
33};

Understanding This Code

What It Does

This script applies dynamic server-side filters to Link Fields within various child tables (Grids) of a Frappe DocType. It uses the `get_query` method to restrict the selectable options based on values from the parent document or predefined conditions.

When To Use

Use this script within a Client Script that is attached to a DocType. It's typically placed within the `onload` or `refresh` events to ensure the filters are applied as soon as the form loads or is refreshed.

Prerequisites

  • •Basic understanding of Frappe DocTypes and Child Tables.
  • •Familiarity with Frappe Client Scripting.

Key Concepts

Important ideas to understand in this code

cur_frm Object

The "cur_frm" (current form) object is a global client-side object in Frappe that provides access to the current form's data (doc), metadata (meta), and methods. It's the primary way to interact with a form's fields and triggers.

Learn more

get_query Method

The `get_query` method allows you to override the default query for a Link Field. By defining a function for it, you can return a dictionary of filters that will be passed to the server-side call that fetches the list of options.

Learn more

Child Tables (Grids)

In Frappe, a Child Table (rendered as a Grid) allows you to store multiple records linked to a parent document. Accessing fields within a grid from a Client Script is done via `cur_frm.fields_dict.[child_table_fieldname].grid`.

Learn more

Step-by-Step Tutorial

Follow along to understand how this code works

1

Identify Target DocType and Fields

First, determine the parent DocType you are working on. Then, identify the fieldname of the child table and the fieldname of the Link Field inside that child table that you want to filter.

javascript
// Parent DocType: e.g., 'Payment Entry'
// Child Table Fieldname: e.g., 'invoices'
// Link Field in Child Table: e.g., 'invoice_no'
Next Step
2

Create a New Client Script

Navigate to 'Client Script' from the Awesome Bar. Click 'New' and select the DocType you identified in Step 1. This script will contain your custom filter logic.

javascript
// In Frappe Desk:
// 1. Go to Client Script List
// 2. Click 'New'
// 3. Select DocType: 'Payment Entry'
Next Step
3

Implement the get_query Logic

Inside the `onload` or `refresh` event of the Client Script, write the `get_query` function. This example filters the 'invoice_no' field in the 'invoices' child table based on the parent document's 'supplier' and 'company'.

javascript
frappe.ui.form.on('Payment Entry', {
    refresh: function(frm) {
        frm.fields_dict.invoices.grid.get_field("invoice_no").get_query = function(doc) {
            // Check if supplier is set on the parent doc
            if (doc.supplier) {
                return {
                    filters: {
                        "supplier": doc.supplier,
                        "company": doc.company,
                        "docstatus": 1
                    }
                };
            }
        };
    }
});
Next Step
4

Advanced Filtering with 'IN' Clause

For more complex scenarios, you can use a list of values. This example shows how to filter the 'main_item' field to only show items whose 'item_code' is present in a pre-defined list.

javascript
frappe.ui.form.on('Your DocType', {
    refresh: function(frm) {
        // Assume item_list is populated elsewhere in your script
        let item_list = ["ITEM-001", "ITEM-002", "ITEM-003"];

        frm.fields_dict.items.grid.get_field("main_item").get_query = function(doc) {
            return {
                "filters": [
                      ["Item", "item_code", "in", item_list]
                ]
            };
        };
    }
});

Common Issues & Solutions

Troubleshoot problems you might encounter