Relating three drop down boxes to each other

[JoniS]

I am having trouble relating three drop-down boxes to each other correctly.

There are three related tables:

Item Categories (Category_ID)

Item Makes (Make_ID, Category_ID)

Item Models (Model_ID, Make_ID)

The Item table has a field called Model_ID from which I can draw the name values for Model, Make, and Category based on their related fields, and concatenate them all together (example: CPU PC - Dell - Optiplex GX620). I have no trouble displaying that information if the item already has a Model_ID, and, in fact, that is how I'm allowing the user to select the Model_ID for a new item now--one HUGE dropdown box (because there are a LOT of models).

I need to make the dropdown box more usable. The list is just too long and unwieldy, so I wanted to break it into a series of three dropdown boxes for users.

When a user creates a new item in the database,

I want them to be able to choose the item type through a series of dropdown boxes. First, they need to select the Category of the item, which in turn would filter the Make dropdown and, once the Make has been chosen, filter the Model dropdown. Once they choose a value from the Model dropdown, I need that model number to be saved in the new item record.

I don't even know where to start. I tried putting three dropdown boxes on the Item form, but they don't relate correctly.

I will be grateful for any help you can give me.

Thank you!

[mr_vodka]

Do an advanced search for +conditional +valuelist and you should come across many posts and examples of this.

[JoniS]

Well, I'm getting closer... I've attached the file I've been messing with in the hopes I can get someone else's solution to work for me.

Unfortunately, I tried to add a third drop-down to the working solution and it doesn't work right. The third dropdown is relating to the first dropdown instead of the second...

Any ideas on how to fix it?

ConditionalVLs_ClearFieldsToo.zip

[comment]

Try it this way?

ConditionalVLs_ClearFieldsToo2.fp7.zip

[JoniS]

Try it this way?

Whoa, my little heart just skipped a beat! It works just like I imagined. It's wonderful! Thank you, thank you, thank you! I was even able to get it working in my real FileMaker file.

You're a real life saver.

Now I just have to figure out how to make them dropdowns (instead of popups) that can be typed into and shrink the list with every letter....... That's for another day.

[comment]

I was asked in a PM about the calculations in the file, so I better make it clear here as well:

That is not my file. I only fixed the relationships, and didn't even look at the field definitions. The calculations seem to me more complex than they need to be and they use Evaluate() to trigger the change, which IMHO is not good practice.

The original demo of conditional value lists with self-clearing fields is posted here:

http://www.fmforums.com/forum/showpost.php?post/195641/