Inventory problem!

[kryten]

Hi all,

I'm trying to build an inventory form that will automatically reduce the UnitsOnHand total by the quantity ordered.

Any tips on code to do this would be greatly appreciated!!

 

[ghudson]

You need two tables. On table to store the inventory "in" items. One table to store the inventory "out" items. Use a query or function to sum the items against each table for your inventory totals.

 

[Pat Hartman]

A single transaction table is best since you will have more than two types of transactions. Each transaction has a positive or negative value in quantity so that all transactions can simply be summed to attain a balance.

There are "good" ways to manage inventory and "bad" ways. Search for posts on inventory to see the opinions. Maintaining only a balance is the "bad" way.

I'm trying to build an inventory form that will automatically reduce the UnitsOnHand total by the quantity ordered.

- This is not as simple as it first appears. If an item is deleted from the order, it needs to be added back into inventory. If a quantity is changed, you need the old value in addition to the new value so you know whether to increase or decrease inventory. You also need to be able to handle a cancelled order where you process multiple items in a batch. And on and on. Believe me there is more. The transaction method is far simpler in the long run and is its own audit log so you can find errors.