Jump to content
Sign in to follow this  

value list based on two other fields

Recommended Posts

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,


Share this post

Link to post
Share on other sites

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.


Share this post

Link to post
Share on other sites

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 by Guest

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.