Is it possible to Update value in table2 on addition of entry in table1

Ask for Help
1

As part of Inventory Management application, I have a use case to select a product first in order to have purchase/sales. Also, On successful purchase/sales addition, the quantity in Inventory table should be updated/reduced accordingly. Which component should I use to filter a product in order to add purchase/sale entry and which action to be used to update/reduce the quantities in inventory based on purchase/sales.

Tables:

  1. Products (Name, Description, Image, Category)
  2. Purchases( Item Name, Quantity, Price, Date and Supplier Name)
  3. Sales( Item Name, Quantity, Price, Date and Customer Name)
  4. Inventory ( Item Name, Quantity, Price, Min Order Level)
2

I think you would have two different forms to write to those Purchases/Sales table, and in there you would be able to have a choice component to select the right product. You would also want to write the product/item’s ID instead of its name to make sure it’s unique and won’t change over time.

You can also consider using only one table for both purchases and sales, and have a “Transaction Type” column to indicate whether it’s Purchase or Sales.

Regarding the inventory amount, I would suggest using a relation using the item ID to the purchases/sales table, and then a rollup column to sum the quantity. If you use only one table, Purchases would be kept as is, whilst Sales would need to be stored as negative numbers.

I’m not sure what’s the difference between the Products and Inventory tables though. Are they both storing the same items?

1 Like
3

Thank you for your guidance. I will give it a try and keep you updated. The purpose of having separate Products and Inventory tables is that the Products table serves as a master table for product definitions, which remain unchanged with each purchase or sale. Instead, updates like (quantity, price, sale price, sold quantity and min. order level) are made in the Inventory table.

1 Like