Conditional value list from value list from confusion

[clevelandprintwear]

I've got a table "Products" that has a field called "Colors". (This is a t-shirt business so I've got hundreds of products and hundreds of colors) The field "Colors" is set to create a value list based on all the colors entered for all products. So in short 1 product could have multiple colors (i.e. T-shirt comes in blue, green, purple, yellow.)

In my "Orders" table when I select T-shirt I want only [blue, green, purple, yellow] to show up in my "Colors" field. However, every way I try to set this up, I'm getting every single color in the drop down list to show up.

I've seen some conditional value list posts around here and have a few examples, but I don't really see how they would apply to this situation. Any help please!!! Thanks.

[doughemi]

Assuming your Products table has a separate record for each color of t-shirts (e.g. a record for t-shirt blue; another for t-shirt purple) and all t-shirts have a common ProductType ID:

Create a new Table Occurrence of your Products table. Let's call it ProductsColors. Relate it to Orders by ProductType ID.

Create a new Value List ProductColorVL consisting of values from ProductsColors::Color (only related values starting from Orders).

Format the Color field in the Orders table as a dropdown list using value list ProductColorVL.

[clevelandprintwear]

Well, no there is only one product for "T-shirts" the ID or primary key being, "T-Shirts."

I've attached some pics , in this case, Product name is G8000. Colors on right right hand side, green, yellow, blue.

I want to be able to pick G8000 on 'order' and have only green, yellow, blue show up under the color....

Products_SC.tiff

Orders_SC.tiff

[clevelandprintwear]

Got it! Didn't matter that it wasn't a seperate record, thanks a million, been on that for like 2 days.

[doughemi]

Got it! Didn't matter that it wasn't a seperate record.

That's because the color field in the Products table was already a return-delimited list. It wouldn't have worked if the items in the field were separated by commas, or some other character.

[clevelandprintwear]

Okay, but now I can't do it again? If you see the attached picture, I want the next line down to be another product. I've created another TO Named ProductsColorsL2 and related it to another TO of Orders for the ProductNamesL2 field. However, this is not working. Is it just not possible? Would a pic of the Relationships help...

[comment]

Try it this way.

ProductColorVL.zip