I'm in the process of trying to set up an inventory/order system for our company. I would like user to be able to enter the lot number and quantity of a product into the order main layout and that data draw into a portal for the Lot# and automatically deduct the shipped qty from the stock qty.
Currently I have two tables: Order_number and Lot_number. In the Order_number table main layout, users can enter the lot # from which the product is being pulled in up to 3 fields: Lot_ID1, Lot_ID2, Lot_ID3. Users then enter the qty of product in fields: Lot_qty1, Lot_qty2, Lot_qty3 respectively. I have a sum field that sums those three lot qty's for a total of product shipped.
Next, I have setup a multikey calc in the Order_number table that draws the order_ID fields into the appropriate Lot_number table main layout portals. That works fine. However, whenever it does that, it brings the total shipped qty with it, not just the itemized qty (presumably due to my incompetence with the multikey). As a a result, this is what the order and inventory is currently looking like:
Order_number table
Ord_ID 0001
Lot-0001 Qty: 500
Lot-0002 Qty: 600
Lot-0003 Qty: 100
Total: 1200
Lot_number table
(portal view Lot_ID Lot-0001)
Ord_ID 0001 1200
(portal view Lot_ID Lot-0002)
Ord_ID 0001 1200
Etc
I would like the following:
Order_number table
Ord_ID 0001
Lot-0001 Qty: 500
Lot-0002 Qty: 600
Lot-0003 Qty: 100
Total: 1200
Lot_number table
(portal view Lot_ID Lot-0001)
Ord_ID 0001 500
(portal view Lot_ID Lot-0002)
Ord_ID 0001 600
Etc
Am I not thinking straight by using multikey to achieve this instead of some other method?
Thanks in advance!
