Limit Group Item list to only Children Choices / How to query a Link Field

Frappe Framework Customization
1

In our case we have create a Group Item like:

-- This Parent
   -- this children1 
   -- this children2 
-- This Parent 2 
  -- this children 3
  -- this children 4

How we can display only children then user data entries a new Item ?

Also consider that Item is core ERPnext DocType, so we need the process can be done with Custom Form or Custom Script in order to version control it with export-fixtures.

2

do you mean something like selecting account in mode of payment? (is_group == 0)

3

Enter only children leaf node items as item record. And other group Items should be entered as Item Group record. Item and Item Group are separate doctype in erpnext.

1 Like
4

@revant_one Ty, you give me a great hint to figure out how this might be done.

From your example, I decided to create a whitelist function with frappe.db.sql call.

Using filters etc it has not possible as I couldn’t figure out how to add OR operator.

5

Yes, but that doesn’t hold for variants right? For some reason, the filter for templates got removed in some of the doctypes.

6

https://frappe.github.io/frappe/user/en/guides/app-development/overriding-link-query-by-custom-script.html

If you are doing custom sql query refer lead_query example from above link.

you have to use all arguments viz doctype, txt, searchfield, start, page_len, filters. Only then the auto-complete and filtering as you type in link field will work.

7

@revant_one Ty, I was reading that documentation resource and got it.

8

My solution based on the comments:

The first task was to limit in Selling / Item and Item Group to list only children of the tree:
So,

  1. Go to Setup > Custom Script
  2. Select Item Doc
  3. Add the following
frappe.ui.form.on("Item", "onload", function(frm) {
    cur_frm.set_query("item_group", function() {
    return {
            "filters": {
                "is_group": "0",
            }
        };
    });
});

The second task was how to limit the Item Group but to have also parent Item Group with name “(3) This a parent I want” or “(2) This a parent I also want”.

So,

  1. On your app, create: yourapp/yourapp/controllers/init.py
    2.On your app, create: yourapp/yourapp/controllers/queries.py
  2. in queries.py
import frappe

# Limit Item Group to List only leaf tree or (3) or (2)
def group_item_query(doctype, txt, searchfield, start, page_len, filters):
    return frappe.db.sql("""select name From `tabItem Group`
                            where
                                is_group=0
                                or name like '(3)%'
                                or name like '(2)%'""")
  1. Go to Setup > Custom Script
  2. Select Item
frappe.ui.form.on("Item", "onload", function(frm) {
    cur_frm.set_query("item_group", function() {
        return {
          query: "yourapp.controllers.queries.group_item_query",
        }
    });
});

Please be aware, that the above solution is an over simplification, meaning you have add some more logic in your database query in order to have the default Link Field functionality like on the fly search. Also, alter query will not be valid if you Quick Entry as True, meaning that in Quick Entry your user will still see the whole list

1 Like