Good day Everyone
Please I need help with this thing I’ve been tryna figure out for days on how to go about it but couldn’t find out how so far so I decided to look for help from everyone on this forum. Thanks to everyone in anticipation.
Am working on an inventory manager and here are the tables I created with their various columns:
•
CATEGORIES(Category_ID, Category_Name, Description)
•
SUPPLIER (Supplier_ID, Supplier_Name, Supplier_Address, Supplier_Phone)
•
PRODUCTS_IN_STOCK (Product_ID, Product_Name, Supplier, Product_Category, Quantity_Per_Unit_Received, Units_in_Stock, Date_Of_Receipt, Entered_By)
•
PRODUCTS_OUTGOING(Product_Outgoing_ID, Category, Outgoing_Product_Name, Quantity_Or_Units_Issued, Movement_Or_Receipients, Date_Issued, Entered_BY)
•
USERS (User_ID, Full_Name, Username, Password, Phone, E-mail)
Now here is where am stock on this project:
- In PRODUCTS_IN_STOCK page, I need the system to automatically pick the ‘username’ in ‘USERS’ table and insert into the ‘Entered_By’ column
- In PRODUCTS_OUTGOING page, I need to be able to select a ‘category’ which will activate the lists under that ‘Category’ on ‘Outgoing_Product_Name’ and these lists should be the only valid lists in the table PRODUCTS_IN_STOCK.
- In PRODUCTS_OUTGOING page, I also need the ‘Quantity_Or_Units_Issued’ not to exceed the amount in ‘Quantity_Per_Unit_Received’ column in ‘PRODUCTS_IN_STOCK’ table.
- Lastly, on the ‘PRODUCTS_IN_STOCK’ List page, I need the system to automatically Check between ‘Units_in_Stock’ and ‘Quantity_Or_Units_Issued’ on the ‘PRODUCTS_OUTGOING’ table and display the balance in ‘Units_in_stock’ Column in ‘PRODUCTS_IN_STOCK’ List page
Please, Any help would really be a great breakthrough for me in this and am also greatful in anticipation.
Thank you
I've done something along those lines, I may have handled it differently than you have, I have the products ,incoming, in stock and products outgoing totals all in one table, so I have
- Products(product_code,details,InComing_total,InStock_total,OutGoing_total),
but then I have three other tables,
- InComing(product_id,category,supplier_id,qty,qty_recieved)
- OutGoing(product_id,customer_id,qty,qty_depatched)
- Product_transactions(product_id,customer_id,supplier_id,transaction_type,qty)
Then when I move a qty from incoming, to stock to OutGoing with MySql move into stock
UPDATE products SET incoming=incoming-qty,InStock=InStock+qty WHERE product_code='ABC123'
or move from stock
UPDATE products SET InStock=InStock-qty ,OutGoing=OutGoing+qty,WHERE product_code='ABC123'
and then insert into the product_transactions/incoming/outgoing if the product is coming into or going out of stock.
The one I wrote has a few more fields but we've been using it for good few years now and have added onto it with picking sales orders,allocation lists,picking list,goods inwards,filing lists etc
Hope this helps a bit
