How to filter Purchase Invoice items to show only items with zero stock?

ERPNext
, , ,
1

Hi everyone,

I’m working on customizing the Purchase Invoice form in ERPNext, and I want to filter the items such that only items with zero stock are selectable in the child table (items grid).

Use case:
I want the Purchase Invoice to only allow selection of those items that currently have 0 stock in the selected warehouse (or default warehouse).

I tried using get_query in a client script and a custom server-side query, but I’m not sure how to correctly filter items based on stock levels using the Bin or stock ledger.

What I’ve tried:

frappe.ui.form.on('Purchase Invoice', {
    onload: function(frm) {
        frm.fields_dict.items.grid.get_field("item_code").get_query = function(doc, cdt, cdn) {
            return {
                query: "my_app.api.get_items_with_zero_stock"
            };
        };
    }
});

and this is is my python logic
@frappe.whitelist()
def get_items_with_zero_stock():
    
    in_stock = frappe.get_all('Bin',
        fields=['item_code'],
        filters={'actual_qty': ['>', 0]})
    in_stock_items = [d.item_code for d in in_stock]

    item = frappe.get_all('Item', filters={
        'item_code': ['not in', in_stock_items],},
        fields=['item_code', 'item_name'])

    return item
1 Like