Help with Inventory Database Relationships/ VLs !!

[DoomedHostage]

Hello everyone. Im in a bit of a pickle at the moment and I'd love any assistance.

Heres the situation. I have 3 tables in one Filemaker Pro 7 database. This database is designed to manage an inventory, deduct products sold off of sales invoices from the inventory, and calculate comission given to employees. I have 3 tables. One table is the inventory outlining prouduct information, reference numbers, quantity on hand and price, another table called sales which I lay out a sales form to be used as an electronic invoice outlining the salesman or saleswoman, the method of payment, and then a product information fields (quantity, vendor, reference number, description, color, size, unit price)which are repeated to include multiple sales. There are no unique product indentifier codes (sadly), products can only be uniquely indentified through combining the info from all the invoice fields. The last table is simple; it hold the 4 employees names, their comission rates, amount each has sold, and the amount of comission they are to receive.

Now. What I've been crying about the past few days is the proper relationships between these tables. These salespeople are useless, and so I need the invoice to function purely off of value lists; when a vendor is selected, I need only that vendors products coming up in the reference number value list, and then that product numbers colors in the color list , etc...Dynamically reducing value lists I think they're called.After these fields have been entered, Filemaker needs to recognize the unique product combination, lookup the unit price of it, and then as another process add the quantity of that item purchased to the quantity sold field in the inventory table. Finally, the subtotal of the invoice record needs to be tallyed to that salespersons total of sales in the sales table, so I can calculate comission earned.

Ive tried making all the product criteria fields related to one another between inventory and sales, but then the value lists don't appear. As it stands, the vendor names are directly related, but that doesent identify a unique product. Nothing I do can get the quantity sold to increase when something is bought, and comission values arent adding up. I can email this or host it if any of you think you can work it out because Im stumped and if I dont have it by tomorrow..Im terminated too.

Somebody anybody, I beg and would greatly appreciate your assistance. Thank you for your time and assistance. Bryan Nagle

[The Shadow]

I think you're going to need multiple relationships (TO's) to do this, once a vendor is entered, you'll need a relationship based only on vendor to base your value list for products on.

Once a product is chosen, you'll have another relationship based on vendor and product to create a value list based on colors, etc.

The final relationship actually used for the Lookups will be based on a relationship with all the criteria you need for uniqueness.

[DoomedHostage]

I think I see what your saying, but I think Ive also tried to implement it to no avail. I was under the impression that relationships once defined exist everywhere, meaning that I cant just have a purely vendor name inventory table relationship with vendor name on sales invoice table for that value list. I first have to define all relationships , which in order to find a unique product, would be to every criteria on inventory to the matching field on sales invoice. When I do this, none of the value lists come up when you click on the field. In fact, nothing comes up, it wants you to enter in something.

So. Ok. If you could tell me how to make a single relationship apply to only one value list, and then 2 relationships to another value list, then 3 to another I would be happy doing it that way; at this point Im more than happy using any way that would work really.

To get an idea of my problem, let me explain our products (womens swimwear, and women are picky so I have to get this right)

Brand Code Type Description Size Color Price

Matta MT046 Thin Thong 10 Pink 100

Matta MT046 Thin Thong 12 Pink 110

Matta MT046 THin Thong 10 Blue 100

Matta MT110 Revealing 8 Blue 80

so what Im trying to show is that we have many repeated brands, many repeated code types and descriptions , but the old things which change are size and color, but they still have the same product number. However they are unique in inventory and most even have arbitrary unique prices so the lookup has to be exact.

I was thinking I could assign unique serial numbers in a hidden field to all items in the inventory table. Then in the sales table make the criteria fields a tool to narrow down to that product ID, but I wouldnt know what kind of script to write for that really. Any ideas??

cheers guys.

[The Shadow]

I did an example on this thread:

http://www.fmforums.com/threads/showflat.php?Number=106269

that shows the basic setup. You can't have just a single relationship that will do everything, you'll start with an "All Vendors" value list that isn't marked "related values only". Once the user has entered a vendor, they will move to the next field with a popup that can use a relationship based on the vendor for its value list, etc.

In FM7, you can now make multi-criteria relationships, so the next relationship will be that the vendor ids are equal AND the product ids are equal. You just draw another line between the same two tables in the graph.

Let me know if the example from the other thread doesn't clear it up for you.