LinkedIn
Twitter
-
I am developing an inventory management app, which wants to show what items are put in each and every location.
-
I created a transaction table which records all the items, in and out qty, with their respective locations.
-
When I try to link the location table with the transaction table, the relevant transactions were retrieved. However, the system fail to add the sum of the same items available in the same location. Therefore, i cannot check the total quantity in the location form.
-
In this example, product โS10150โ were added to location โ1L1Aโ โfor
โ2 times each of 100pcs. I wish to show that the available products in location โ1L1Aโ are:
- S10150 = 200pcs
- S10161 = 100pcs
- S10184 = 100pcs
May I ask how to do it?
Tables in this app (attributes):
i. Product (ProductID, Product name)
ii. Location (Location)
iii. Transaction (DateTime, ProductID, Location, Quantity_change, User)
I am actually thinking if i should add a virtual column in โTransactionโ table, namely โtotal_qtyโ. Therefore, the โtotal_qtyโ should equal the sum of โQuantity_changeโ if the productID and Location match the row. But I donโt know how to code this.
I would be very grateful if anyone can advise me ><
