How to apply extra filter to two Child Tables with same data source?

Frappe Framework Customization
,
1

Hello,

Can we have two Child Tables in one DocType whose data is coming from same source but have different filter applied to them?

TIA

Yogi Yang

2

@YogiYang Yes. You can use frm.set_query to change the filter of Link fields in child table.

3

Hello @kid1194,

frm.set-query does not work in case of Child Table. Is there any other way I can achieve this.

Here is the code that I am using

frm.set_query('zone_1_batches', {'zone': 'Zone - 1'});
frm.set_query('zone_2_batches', {'zone': 'Zone - 2'});

TIA

Yogi Yang

4

@YogiYang Your code is wrong. It should be like the following.

frm.set_query('fieldname of link field in child table', 'fieldname of child table', () => {
    return {
        filters: {zone: 'Zone - 1'}
    };
});
5

Hello,

@kid1194 is this proper?

frm.set_query('zone', 'zone_1_batches', () => {
            return {
                filters: {zone: 'Zone - 1'}
            };
        });

TIA

Yogi Yang

6 
// Income Account in Details Table
// --------------------------------
cur_frm.set_query("income_account", "items", function(doc) {
	return{
		query: "erpnext.controllers.queries.get_income_account",
		filters: {'company': doc.company}
	}
});

// Cost Center in Details Table
// -----------------------------
cur_frm.fields_dict["items"].grid.get_field("cost_center").get_query = function(doc) {
	return {
		filters: {
			'company': doc.company,
			"is_group": 0
		}
	}
}
7

@magic-overflow thank you, for the code sample but as I am developing my own custom app I don’t think I will be able to follow as per your suggestion.

TIA

Yogi Yang

8

income_account is Select Link field in child table
items is child table name in the form

9

Use this

image
image740×313 38.6 KB

10

@YogiYang It looks right.

11

Hello,

But unfortunately it is not working.

Any other suggestions?

TIA

Yogi Yang

12

@YogiYang Let me explain further about set_query in case you misunderstood something:

  1. First param:
    It is the fieldname of the Link field in the child doctype

  2. Second param:
    It is the fieldname of the child table in the main doctype

For the filters object returned,

  • Keys:
    It must be a fieldname from the linked doctype set in the options of the Link field in the child doctype

  • Values:
    The value that the field represented by the key must hold so the the row from the linked doctype gets selected

Reference: The code from the documentation:

// set filters for Link field item_code in
// items field which is a Child Table
frm.set_query('item_code', 'items', () => {
    return {
        filters: {
            item_group: 'Products'
        }
    }
})

Important
The set_query must be placed in onload or before_load event and not in refresh.

13

Hello @kid1194,

Thanks for detailed explanation.

In my case the Child Table has a field which is of type Select and has two options.

  1. Zone - 1
  2. Zone - 2

TIA

Yogi Yang

14

@YogiYang And you want to filter the select field of child table?

If yes, then:

  1. set_query is only for Link and link based fields so it will not work for the select field
  2. Based on what field do you want to filter the select field?

If no, then:

  1. post a screenshot of the fields of main doctype
  2. post a screenshot of the fields of child doctype
  3. explain in details what you want to accomplish
15

Hello. This is not directly related but i believe it can be done with set_query. For the purpose of registering a sales return, i want the user to select the Customer, and the Sales Invoice Item, to narrow down the Sales Invoices which contain that item. Any help on how to do this would be greatly appreciated.
Thanks
Nick

16

@Nirmal_Balani You can do that using a set_query with a method param that points to a custom function in a custom Server Script (Python).
https://frappeframework.com/docs/v13/user/en/api/form#frmset_query

1 Like
17

I am on frappecloud.

python method signature

def custom_query(doctype, txt, searchfield, start, page_len, filters):
# your logic
return filtered_list

This script I should enter in a server script, with the right logic, correct?

18

@Nirmal_Balani Yes, that’s correct. And then you call the method using set_query in JavaScript.

19

Please, if you could help with the required client and server scripts, I’d much appreciate.
Thanks

20

Let me explain in detail what I am trying to accomplish. I have Made a doctype called Goods Return Form, for my sales rep to enter the details when the customer wants to make a return.
He selects the Customer, the item to be returned, and then finally the sales invoice from which it is to be returned. A few other fields fetch the Invoice Date, and according to its age, validate whether the Return can be submitted or not. The Use case is that We would not want to collect goods that are beyond the return period, and it is important to Select which invoice for the particular customer in case the item repeats in multiple invoices.
In this case, The customer filter can be applied to the parent doctype (Sales Invoice), and the item filter should be applied to the child doctype. (Sales Invoice Item) The problem I face is when it comes to populating the Sales Invoice field (Link) list in the Goods Return Form doctype.
for frm.set_query(),

customer(frm){
	    let cust= frm.doc.customer
	    console.log(cust)
	     frm.set_query('sales_invoice_number', () => {
    return {
        filters: {
            customer: cust
            // sales invoice item? 
        }
    }

I am not sure how to filter the child table with the help of a server script. Any help on how to get this working would be greatly appreciated.