Inventory Quantities

[Joeybear]

I'm new to Filemaker, so please bear with me (no pun intended). Here's the situation...

I have an invoice file, and a parts entry (inventory entry) file.

I buy (say a qty. of 5) of a part and enter the information & qty. into the parts entry file.

When I need to write up an invoice for someone buying one of the parts, obviously the inventory should then update to show the "on hand" quantity as one less.

My question is this... is there a formula/relationship setup that will let me do this?

By all means feel free to e-mail me with as much info. as you can.

As I said, I'me very new to all of this.

Thanks!

[LiveOak]

Unfortunately, this is a real "how do I perform brain surgery" question. Inventorty systems range from complex to REALLY complex. This is a good appliation to tackle with at least a year of intensive FM experience. I can sketch an outline, but until you are very familiar with relationships, you may have to work a little at the concept.

The usual approach I use is to have an Invoice file where a record is a single invoice, an Invoice Transactions file where a record is an invoice line item, a Products file where a record is a product that may be in inventory (a catalog of products), a Inventory file which lists items in inventory (this allows for lot control), and an Inventory Transactions file in which a record is a stocking or draw transaction from inventory. The reason for the inventory transactions file is to protect the inventory count from being corrupted by a single incorrect transaction which changes the count. If the inventory amount is instead represented by a summation of all transactions (stocking operations, sales, adjustments, etc.) for a particular item, it is always possible to reconstruct the quantity in inventory. In some cases this on-the-fly related calulation of inventory quantity is too slow for use in an online system. In these cases a working number is kept for daily transactions and the summing up of transactions is performed as an offline operation each night.

In most systems the creation of a line item on an invoice reserves the item in inventory, but does not debit the actual quantity. This would give an incorrect physical inventory count, as the item has not really been pulled from inventory. The actual debit of inventory would occur in response to a pick list at the time the items is actually removed from stock and packed.

So much for a few of the high points of inventory systems, then again there are MRP systems which are far more complex! .

My best recommendations are to work hard on learning FM, especially relationships and to find someone to give you hands on help (or write the system) who has written one before.

-bd

[abkaplan]

As Live Oak says that is a little bit difficult job and need fmpro knowledge. But more than that if you want to do that you also need some detail information about stocking and the calculation of the stocked goods.

I can give you some clues about it if I understand you true.

You want to know the real-actual quantity of the goods when you want to sell it.

create a database and keep the records of your goods in that data base.

create a process database and keep the records of process being aware of it is sell or stocking action.

create a database to keep the records of your invoices

put a portal on that invoice layout and relate it to the process db.

since you have the id of of your good in every record of your process db ,you can see the latest results of the stocking so you can see in every row of your invice the portal counts of your goods coming form the process db.

may be that explanation is too more complex but if you really in need of it write me more specifically and I can prepare simple relational databases as an example for you to create more complex actions.

wish you luck and success.

abkaplan

[LiveOak]

I can't answer your question directly without a LOT more information. The general approach I use is to make Current Stock Level a sum() of related records in a Inventory Transactions file. I don't use a Start Stock Level in Inv DB, it is just the first entry for an inventory item in the Inventory Transactions file. A separate record is created in Inventory Transactions for each draw from or stocking operation to inventory (or correction). Any other file that changes inventory quantity posts a transaction to the Inventory Transactions file (creates a record).

This approach works fine to a point. This dynamic calculation can be too slow, in which case a static number in the Inv DB is used for stock on-hand. This number is updated by calculation after hours.

-bd

[Lee J]

I to are struggling to work with Stock levels/Inventory

We are a production company and currently I have fields in the Inv DB as follows

StartStockLevel

CurrentStockLevel

ReorderLevel

Our problem is that it's not at the invoice stage we need to add the items it is during the production process via the Time and Material capture.

I'm unsure on how I can dynamically return the results to the Inv DB as and when items are used with a project.

Other DB's are T-M Capture, JobTicket, all are related via a Job Number

thoughts please.