Creating an Inventory Database

[MJG]

I am a FM novice, and am trying to create an inventory system that has three parts, Received, Inventory, and Shipped. I am not sure how to procede. I have created the three databases, but am not sure how to get the calculations to work, and how the relationships need to work. Also, I have read on some other posts that you need to use four files. Items contains one record for each item (what does that mean, and how is it done?), which I do not have, Orders (my Shipped), contains 1 record for each order, Restock (my Received), and Line Items (my Inventory), where most of the data is kept. I think I need some more clarification about how this is supposed to work. Any input would be greatly appreciated

Received Database.

This database contains a record of all deliveries. It lists quantities of each different line item. The fields are: Date, Order #, Vendor, Item #, Quantity, Manufacturer, Description, Cost, Purchased for, Category, Item total cost, Line Item Total, Shipping, Tax, Grand Total. Ideally, quantites entered here would update those listed in the Inventory Database. The Received database would allow us to look up and track where individual orders came from. We would like to add new products (one's we haven't entered into the inventory database yet) to the inventory database from here. Basically, a barcode would be scanned, or entered by hand, and the Description, Manufacturer, and Category, would be pulled from Inventory.

Inventory Database.

This is where most of the data actually resides. Fields include: Item #, Description, Manufacturer, Cost Each, Retail Price Each, Quantity on Hand, Remaining Stock Value, Total Quantity Received, Total Quantity Shipped, and History. Total Quantity Received would look at Received and find all of a particular item #, then add up all quantities for that item. Total Quantity Shipped would do the same thing, except from Shipped. Quantity on hand would be Total Received - Total Shipped. History would be a record of every Received order number that contains that item, along with date, as well as a record of every Shipped order number and date.

Shipped Database.

Just like the Received Database, except for items leaving.

I hope I have explained this clearly enough. I can attach samples if this would help.

Again, any input would be helpful

Version: v5.x

Platform: Mac OS X Panther

[Ender]

I think the file names you are using are confusing.

How about this:

"Purchase": a purchase request having one or more items.

"Product": an Inventory item.

"Order": an order having one or more items to ship.

Additionally, if more than one type of product could be purchased (received) at a time (usually this is the case) you will need a Purchase Line Item file, and if more than one type of product could be sold (shipped) at a time you will need an Order Line Item file (see attachment). Use the Product file for lookups into the line item files.

I think this is the basic idea of an inventory system. To add new products, you would need to add them to the Product file (you can do this with scripting).

Managing quantities in inventory is a bit more complicated. The first thing that comes to mind is this:

Items in Inventory = sum(Purchase Line Item::Quantity) - sum(Order Line Item::Quantity)

but this won't work work in the long run as items are sometimes lost or returned. A good inventory system needs a good way to update itself and a way to manually adjust counts when users make manual inventory counts.

I'd suggest either another table to track inventory adjustments, or using number fields for counts in the Product file (using scripts to change these counts as items are received and shipped.)

Happy FileMaking!

Version: v6.x

Platform: Mac OS X Panther

Inventory.pdf