Table-to-table relationships for Inventory ERP

The way I've traditionally seen this done is that the items coming in from Purchase Orders (or Transfer Orders or whatever signals someone else to send material to you) go either to a project or into inventory. If they go to a project, it is pretty straightforward and it sounds like you are set up for that. If they go into inventory, you start to run into a lot of decisions to make, most of them dealing with accounting practices instead of “real world” tracking of items.

The simplest is to just keep track of how many of each item are in inventory, and the Purchase Orders make the quantity go up and transferring inventory to different Projects make the quantity go down. From an accounting perspective (since the same item may cost something different depending on how many you buy or when you buy, like steel and wood products!), you may need to keep track of which Purchase Order an item came in on and which specific item gets transferred to a Project. Sure, one WidgetXYZ is exactly the same as a different WidgetXYZ, but if they had different costs when they came in, there will be a different cost assigned to the Project.

There are other ways to keep track of inventory (inventory cost more than inventory), like FIFO or LIFO which can get complicated, too.

For just generally keeping track of how many items you have, though, having a bucket called inventory should work. If you want to just have a Project called Inventory, that should work too, if there is a way in your system to transfer items from one Project to another.

------------------------------
Edward Hefter
www.Sutubra.com
------------------------------

Annie,

Inventory gets complicated.  Here is a model I have used


There is a fair amount going on, but not by the standards of Off the Shelf Inventory Packages.

This tracks the Manufacturer and the Vendors.

Titanium Screws are made by a handful of Manufacturers but sold by many dealers at very different price points and with different part numbers.

The Transaction Type Table is where I have Into Inventory, Out of Inventory and Inventory Adjustments which happen in the Inventory Transaction Table.

I have not explained everything, but hopefully this will help.

------------------------------
Don Larson
------------------------------