Try ERPNext Buy Support Partners Foundation

Filter by Item Group

Hello,

At Doctype “Item” exist “Item Group” link field.

At “Quotation” I placed a “Select” custom field named “Item Group” including only a couple of item groups. What I want is that the user can only select items (obviously at items table) that belong to the item group selected.

I tried addapting codes like these:

But just can’t make it work.

Also… I found that it’s not possible to unset as “Reading Only” the field “item group” at “Quotation Item” doctype. (I’m using Frappe Cloud).

Any thought would be really helpful!

@Francisco_Buendia

Please share your script.

You could try this script:

cur_frm.fields_dict['items'].grid.get_field('item_code').get_query = function(doc) {
    return {
        filters: [[
            'Item', 'item_group', '=', doc.item_group
        ]]
    }
};

Script would go on the Quotation doctype.

Where it says “doc.item_group” I made the assumption that your select field has the name “item_group”. You should change it to the actual name of your select field.

@Dbone no… it doesn’t work. But thanks!

@Sangram I tried…

Name of select field is “tipo_de_orden”.

frappe.ui.form.on("Quotation", {
	item: function(frm) {
		frappe.call({
			"method": "frappe.client.get_value",
			"args": {
			"doctype": "Item",
			"filters": {"item_group": cur_frm.doc.item},
			"fieldname": ["item_group"]
			}, 
		callback: function(r) { 
			cur_frm.set_query("item_group", "items", function(doc, cdt, cdn){
				return {
				"filters": {
				"tipo_de_orden": cur_frm.doc.tipo_de_orden,
				}
			}
			});
		}
		});
	}
});

`

cur_frm.fields_dict['items'].grid.get_field('item_group').get_query = function(doc, cdt, cdn)
 {
                var d = locals[cdt][cdn];
                return {
                    filters: [[
                            'Quotation', 'item_group', '=', doc.tipo_de_orden
                    ]]
                }
 };

`
Could be the problem that it’s not possible to unset ‘Read Only’ the “item_group” at Items table?

Try this, do not change anything:

cur_frm.fields_dict['items'].grid.get_field('item_code').get_query = function(doc) {
    return {
        filters: [[
            'Item', 'item_group', '=', doc.tipo_de_orden
        ]]
    }
};

This is how this script works:

cur_frm.fields_dict[‘items’].grid.get_field(‘item_code’) - points to the name of the table and then the fieldname on the current document that should pop up the filtered list

‘Item’, ‘item_group’, ‘=’, doc.tipo_de_orden - this gets a DIFFERENT doctype, and the fieldname and then compares it to a field on your current document and then returns only the matches.

By default, it’s the “item_code” field in the “Quotation Item” doctype that is linked to the “Item” doctype. So you want to filter that linked list. You would only change this if you added a different link field to the “Quotation Item” doctype.

@Dbone I tried your code on “Taxes and Charges” table and it works.

Maybe shouldn’t be as ‘Read Only’ the 'item_group" at “Quotation Item” so it could have effect on filter. I will try adding a new field at ‘Item’ and same at “Quotation Item”.

But the idea to have two fields for the same purpose… aaarg. Should be easier, more simple!

@Dbone this is the code I’m actually using to filter “Taxes and Charges” from custom field “Tipo de Orden” and works perfectly.

frappe.ui.form.on("Quotation", "refresh", function(frm) {
    cur_frm.set_query("taxes_and_charges", function() {
        return {
            "filters": {
                "tipo_de_orden": cur_frm.doc.tipo_de_orden
            }
        };
    });
});

But ‘taxes_and_charges’ field is out of taxes table.

Maybe I’m not being so clear to explain what I’m trying to achieve and there may be the issue:

After selecting “tipo_de_orden”…

Items must be filtered based on “tipo_de_orden” that its values matches with “item_group” catalogue.

Any ideas?

I finally made it!!! :grinning:

frappe.ui.form.on("Quotation", "refresh", function(frm) {cur_frm.fields_dict['items'].grid.get_field('item_code').get_query = function(doc) {
        return {
            filters: [[
                'Item', 'item_group', '=', doc.tipo_de_orden
            ]]
        }
    };
});
1 Like

Hi again!

Today I’m trying to filter the Customer based on Customer Group, using the same script, but it doesn’t make any changes.

frappe.ui.form.on("Quotation", "refresh", function(frm) {
    cur_frm.fields_dict['customer'].get_query = function(doc, cdt, cdn) {
    	return {
    		filters:[
    			['customer_group', '=', 'doc.tipo_de_orden']
    		]
    	}
    }
});

Also tried…

frappe.ui.form.on("Quotation", "refresh", function(frm) {
        	cur_frm.set_query("customer", function() {
                return {
                    "filters": {
                        "customer_group": cur_frm.doc.tipo_de_orden
                    }
                };
            });
        };
        });

I tried changing “refresh” for “tipo_de_orden”… but nothing.

Any thoughts?

I think you are not able to change query for Customer, because there exists query for Customer in ERPNext code and your custom query doesn’t work. Please check it