Inventory Help

[Michael Fox]

I know this is probably basic but I could sure use some help. I have a customer DB and supply DB linked one to many with supply id. I want to use a form with the customer information and be able to enter all of the supplies that the customer uses. This could be as many as twenty or thirty supplies so I don't want to use individual fields. I have been trying to use a browse table at the set level for this but when I go into record 2 (the second customer) or customer 3 (third record) the table shows all or most of the supplies that customer 1 (and customer 2) used. How do I get a blank browse table for each record in the customer form? Also, I have figured out how to post the number of each supply used to the Supply DB "used" field and then subtract that from the "instock" field but is there a way to do this and "automatically" run a post operation to keep a running master inventory? Finally, if I use another DB (i.e.product with product_id one to many link) as a lookup for the customer supply used, can I post that character data to the supply DB (add to it)from the customer form (browse table) so that I could set up a report of all supplies used for each customer and another report of all supplies used and in stock for the supply db? Thank you, I have been trying to figure this out for two months and I know I am missing something major.

[forskare]

RE: Inventory Help

Michael,

I don't have all your answers but thought I'd take a stab at this one:

>>I have figured out how to post the number of each supply used to the Supply DB "used" field and then subtract that from the "instock" field but is there a way to do this and "automatically" run a post operation to keep a running master inventory?

[Mike Konoff]

RE: Inventory Help

Michael,

I do something similiar to what your doing.

You say that you have the following fields: USED, INSTOCK.

I would change this to one field: STOCKAMNT. (Meaning, stock amount)

When you adding supplies to stock perform an Addition Post to this field and when your removing stock perform a Subtraction Post from the same field. This way you'll always have an accurate instock amount. I maintain a running stock by doing the following: Whenever we produce parts, we package the parts and put an identifying label on each package. We use a label table to enter a record and print a label. If we produce/package 500 of an item# 022-056 we enter a record in the label table for 500 PC's of that item #. I have a field rule defined to Add Post the label qty, in this case 500 PC's to the table->field PARTS->STOCKAMNT having an item# of 022-056. So, if before entering this record that field had a value of 1,500 then after the post the value would then be 2,000. And, if we make another 8,000 PC's that day we'll have added 16 labels at 500 PC's each. The record in PARTS->STOCKAMNT having a item# of 022-056 would now have a value of 10,000 PC's.

Now, assume that we only make 10,000 of those parts and our customer calls and ask's us to ship 7,500. We enter a record in the DELIVERY SET to ship those items. We enter all the Bill to & Ship to information in the DELIVERY table (part of DELIVERY SET), then we enter all items we ship into the DELLIN table (child of DELIVERY that is a browse on DELIVERY SET form). To ship those parts we enter a record in DELLIN for item# 022-056 shipping 7,500 PC's. The field rules "SUBTRACT" posts to PARTS->STOCKAMNT, reducing the 10,000 value in that field by the 7,500 we shipped to 2,500.

Does that help?

As far as displaying items that are supplies for customers...In your customer table, do you have a customer ID field, which is some identifyer that only refers to that customer. Also, in your supply table, do you also have the same customer number field, so that when entering supplies the supplies having the same customer ID can relate to the parent having the same customer ID?

Hope I've been a help.

Mike