value list based on two other fields

[michaeldaly]

Forgive me if this is answered elsewhere but I've searched for a few days and been unable to find it on the forum.

I'm a beginner. My wife and I own a shop selling wedding dresses, bridesmaids' dresses, etc. I'm creating a database containing tables for stock items, customer details, invoices, and lines (individual items sold which will then be shown on an invoice). I also have a table called "make_item" with two fields; the designer name and the item that designer makes. Some designers make more than one type of thing. For example, Mori Lee makes both bridal gowns and bridesmaids' dresses.

The "line" table has fields for "make" "item" "style" "colour" and others. The "make" field is just a value list of all the manufacturers we deal with. "Item" is a value list from the "item" field in the stock list showing only those that match the "make_item" table. That works fine.

However, I'd be very grateful if somebody could kindly let me know what steps to take so that the "style" field in the "line" table displays only the styles from the stock list that match both "make" and "item" in the "line" table. That is, if the "make" is "Mori Lee", and the item is "bridal gown", I only get the styles that are bridal gowns. At present, I'm also getting bridesmaids dresses in the drop down list.

Sorry it's long-winded but hope it's clear what I'm trying to achieve.

Thanks in advance,

Michael

[mr_vodka]

You need what is called a conditional value list. In your case you will need if to fulfill two criteria.

There are tons of examples of conditional value lists on here. Here is one sample.

http://fmforums.com/forum/showtopic.php?tid/199224/post/308477

[michaeldaly]

Many thanks for your response. I've been going over it all for several hours now and still haven't resolved it. It's probably blindingly obvious - must be getting tired - will try again tomorrow. Regards, Michael

PS Morning now - problem solved. Woo-hoo! I don't know if what I've done is the most efficient solution but it seems to work. In case it's of any help to anyone else, I made a copy of the "stock" table in the relationships graph, called it "style_filter", and made a relationship between the "style_filter" table and the "line" table where both "make" AND "style" match. Yes, it was blindingly obvious but I live and learn! Thanks again.

Edited January 29, 2009 by Guest