Inventory Relationship

[Kathy]

I would like to create an system to track inventory. I already have a products table with ID's and cost, etc. which is linked to my invoices table. What I am wondering is how to I set things up so that I can track inventory on hand as things get sold? If I define a field that is units on hand for each product, how do I set up the relationship so that if I sell something via an invoice, the the units on hand number will decrease by the number sold?

 

I appreciate the help.

 

Kathy

[Inky Phil]

Hello Kathy,

 

 

A relationship is not the tool used to do what you want. You will use a script to do this. The script might well use a relationship in order to decide which records you want to be amended but in itself a relationship cannot do what you want.

 

Might I suggest you look up the 'Set Field' script step in your help files to get an overview on how to achieve the required result.

 

Once you have done that by all means come back for more help.

 

Regards

Phil

[quintana-roo]

I happen to have a similar problem. In an online bookstore inventory db, I currently have the quantity updated by a "set field" by a script set in motion when a purchase order is changes status to "received." It updates the quantity and records the date and quantity in a text field.

 

So far, so good. Except the management of these inventory items requires keeping track of individual items, since some, but not all, are shipped to trade shows to sell, labels printed per item, and box numbers for shipping need to be recorded.

 

I have been thinking of keeping an Inventory Items db in addition to the original db, and manage it through the original Inventory db. The individual items could change status to track trade show shipments and when sold, a script could export the item to the Invoice Items db (holding all sold items) and delete it from inventory.

 

This might be a great improvement in the clunky system I have now - a Puchase Order Items db, a separate Inventory db and a separate Invoice items db - the numbers never seem to jibe, and it's hard to avoid human error since the process is hard for a non-geek to follow - it's just a number in a box. If an item is an actual record that moves along the process, it might be easier for the user to manage.

 

The main downside that I see is that there are some items they sell (like magazines) that they keep in large quantities (100+) and listing items one by one in the Invoice (which is the parent of the Invoice Items db) is clumsy - better to use a quantity field there. I can avoid that in the Purchase Order db (parent of Purchase Order Items) by creating the Inventory Item record after it is received.

 

What do you think? What am I missing? Is there a better system? Any other suggestions would be welcome.

[Robert Schaub]

Have either of you looked at Business Tracker?

[FileMakin' Tom]

Anyone who wishes an example of an auto-reducing and increasing inventory, please email me at fmguru@tampabay.rr.com and request "Basic Inventory Relationship". It is an easy to apply relationship file which reduces the inventory when things are sold by invoice and increases inventory when things are received against a purchase order.

 

Be well . . .

 

Tom

[shoebox]

i am curious as to what the help requestees have done to solve their problems. i just joined here and am wanting to make a simple inventory mgt system in fm (fm4 if possible).