Inventory Count

[JesseL]

I am trying to get some help on a database that I have separate tables called Product Inventory (PI) and Main Database (MD). I am using these tables, as well as many other tables that are related to each other by Client ID, to keep track of my product quantity in PI and to see who purchased what item in MD. The inventory is the only thing that is not related to the MD because I didn’t think that it needed it.

What I am trying to have happen is when a customer purchases an item in the MD table, to automatically reduce the count of inventory I have for that single item in the PI table. As of right now I have the following fields in each table that I think are necessary:

MD- "Item Purchased" with a drop down list of all products available

PI- Individual field names for each product with a number that has the count available

I think I need to create a script for this, but I am not sure if I can get the same thing accomplished in some form of equation with certain fields. I am a novice at FMP with just tinkering with it on my own, so I understand some lingo, but I may need some assistance on understanding other concepts or knowing what other options I may have. I am willing to hear any suggestions people may have.

Thanks,

Jesse

[bcooney]

You may wish to rethink your tables. A typical inventory system has the following tables:

Clients

Parts

Vendors

Invoice

Inv_LineItems

Receipts

Receipt_LineItems

PurchaseOrders

POLineItems

That being said, when you create an invoice for a client, you will specify the Parts purchased in the Invoice Line Items table. From Parts, you can calculate the quantity of the part in the InvLineItems table to determine the QtySold.

In Parts you'd need:

__kP_Part_ID

Desc

InitialQty

Cost (calculated as a wtg avg from receipt line items)

Price

How does this sound so far?