currently i am working on pharmacy software, and here i am facing one confusion in stock management. Below is my stock form. and here is the situation. for example I entered stock for medicine name “ABC” with 50 quantities and purchase price of 50 USD for each unit .
Now after 10 days i purchase “ABC” medicine of 20 quantity but with purchase price of 60 USD. ( price increase due to inflation) , now the question is how can i handled this price difference while entering new stock
I guess you database table for the medicine contains the price. That’s the fault. You table should contain only name and type etc. then you need a table with invoices where you store the articles you bought with amount and price.
What @Thallius said. You need one table with only base product information, and another one with transactions of that product. Product ID, quantity (positive for bought, negative for sold) and price per item. Using that table you can calculate the current stock (sum of all quantities for that product) and the profit (sum of quantity * price per item for a product).
In essence this process is called event sourcing, where you don’t keep track of it the current state, but instead keep all the events that lead up to the current state, and you can deduce the current state by reapplying all the events up until the now.
Going full blown event sourcing is probably a bridge too far for now, but a transaction table is certainly a good idea. I’m sure the company’s accountant will appreciate it as well.
2 Likes
Thanks mate
ok now i have table with invoices, in one invoice price is 10 dollar of item A, and then price of same item become 12 dollar in new invoice, then how to fetch that in billing form
I assume you mean stock/inventory? Invoices concern billing and payments.
The id in the stock/inventory table establishes a stock_id for the items. When you fill an order for an item, you would get the stock_ids for that item that have a quantity > 0, ORDERed BY stock_id (oldest to newest.) You would insert row(s) into the order items table, with the stock_id and quantity, where the quantity is <= the available quantity for each stock_id, until you have satisfied the quantity needed for the order.
1 Like
What is the error says? Perhaps a screenshot of the error will definitely help.
What error? No one has said anything about an error!
My bad.