Fetching Available Quantity of the stock and displaying it on customfield in Items page

Hi,

I am trying to fetch the Available Stock quantity based in the Item code and display it in a custom field on the item page. This is already available in the Sales Invoice Item doc and Delivery note Item doc. The reason I want to display the quantity is because I have created a custom webpage with item search functionality using the context script. The search displays item based on the Item code/ description or any related value.

Any help or suggestion would be great.

Hi @bilalparkar,

Get stock qty in the custom field in item master for all items, first set up a scheduler_events in the custom app.

Add the Python function in your custom app of hooks.py file:

scheduler_events = {
    "hourly": [
        "custom_app.custom_app.api.sync_stock"
    ]
}

and then apply the method:

# custom_app/custom_app/api.py
import frappe

@frappe.whitelist()
def sync_stock():
    # Get a list of all Item codes
    item_codes = frappe.get_all('Item', fields=['item_code'])

    for item_code in item_codes:
        # Get a list of Bin documents for the specific item
        bins = frappe.get_all('Bin', filters={'item_code': item_code.item_code}, fields=['actual_qty'])

        # Calculate the total quantity
        total_qty = sum(bin['actual_qty'] for bin in bins)

        # Update the Item document with the calculated quantity
        item = frappe.get_doc('Item', item_code.item_code)
        item.custom_field = total_qty
        item.save()

Please check your file path and custom_field name according.

Stock will update every hour in the Item master.

Set your scenario according.

Thank You!

1 Like

Hi @NCP , I hope you are doing well. Thank you for quick response. I tried the method you suggested but after i execute the app manually to test if it is working, nothing happens. The app executes fine but nothing shows up, also there are not log entries in the frappe.log. I am not sure if I am doing something wrong. Below is my code

# Scheduled Tasks
# ---------------

scheduler_events = {
#	"all": [
#		"stock_quantity.tasks.all"
#	],
#	"daily": [
#		"stock_quantity.tasks.daily"
#	],
	"hourly": [
		"stock_quantity.stock_quantity.api.sync_stock"
	],
#	"weekly": [
#		"stock_quantity.tasks.weekly"
#	],
#	"monthly": [
#		"stock_quantity.tasks.monthly"
#	],
 }

Mehod:

import frappe

@frappe.whitelist()
def sync_stock():
    frappe.log("Syncing stock quantities...")
    
    # Get a list of all Item codes
    item_codes = frappe.get_all('Item', fields=['item_code'])

    for item_code in item_codes:
        frappe.log(f"Updating stock quantity for item: {item_code.item_code}")
        
        # Get a list of Bin documents for the specific item
        bins = frappe.get_all('Bin', filters={'item_code': item_code.item_code}, fields=['actual_qty'])

        # Calculate the total quantity
        total_quantity = sum(bin['actual_qty'] for bin in bins)
        
        frappe.log(f"Total quantity for {item_code.item_code}: {total_quantity}")

        # Update the Item document with the calculated quantity
        item = frappe.get_doc('Item', item_code.item_code)
        item.custom_field = total_quantity
        item.save()

    frappe.log("Stock quantity sync complete.")

Hi @NCP , Just to update, I did manage to get it working by modifying the api.py script but I am not sure if that is the right way. Your expertise will be really helpful on this.

Below is the updated code I used to get the total stock value

import frappe

def update_actual_quantity():
    items = frappe.get_all("Item", fields=["name"])
    
    for item in items:
        bin_entry = frappe.get_all("Bin", filters={"item_code": item["name"]}, fields=["actual_qty"])
        total_quantity = bin_entry[0]["actual_qty"] if bin_entry else 0
        
        frappe.db.set_value("Item", item["name"], "total_quantity", total_quantity)

# Call the function to update the actual quantity
update_actual_quantity()

Context Script for the webpage:

context.items = frappe.get_all("Item", fields=["item_code", "item_name", "item_group", "description", "standard_rate", "image", "total_quantity"])