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"])

I want the item’s balance quantity in a custom field :point_down:

Please help in this.
Thank You.

@falah123 Did you manage to achieve this at all?

hi Bro , would you please help me to implement the same report in my Erpnext V14 ?

Pls. let me know

Regards

Hi, Did you manage to get it or still not? So here you want to display the stock balance quantity? I will check and post on how to achieve this.

Hi, What type of report are you referring to?

Hi dear , Thanks for your reply , i need a stock report with product images ,Please advise how to do that

Hi, are you ok with something I had customized but its more like a stock search page with images and quantity in stock. Or if you have some sample in mind can you share so I can try and figure out how we can achieve this.

yes , it will be good and helpful

Hi, for the one i have created you need to create a custom app first in the erpnext. You can install it using bench and then you need to create a python script which will fetch the required details like stock quantity. Then on the website section you can create a custom page with custom HTML and a context script which will do the job. If you follow this forum you can check my last post where i have posted the code and the method. If you still need assistance then we can connect on a teams/zoom call.

Many thanks dear , let me check it and if i need any help will contact you , again appreciate that