Selecting availability of data in a table

[brownt]

This is Filemaker 11 on MacOS X.6

My client has a table of items that we are using as a lookup table to populate some value lists

The fields in this table are:

item_key - primary key for each item, a sequential number

item_category - the category of the item, a text field

item - the item, a text field

How the lookup works is they first choose a category, then they choose the items within that category. The relationships to make that happen look like this:

and I have two value lists set up using those two instances of the tables to fill in the popup menus

This all works great... Except...

Now my client wants to be able to disable entire categories, ie, make them NOT appear in the popup menu...

How can I make table data unavailable using a relationship? I've been beating this one up for 2 days now and I just can't figure it out...

Thanx

[comment]

they first choose a category

From what exactly? That is, how is the categories value list defined?

[brownt]

The Category value list is taken from the category field in the Items table

Attached is a simplified example of how this currently works...

test_of_categories.zip

So, there are two tables, Items and Collection.... the Items table contains the information about what each item is, including the item and category for the item.

There are two table instances of the items table. The first instance, items_category, is related to the Collection table. items_category::category = Collection:Category

The second instance, items_items, is related to the Collections table this way

items::item = Collection::item

and

items::category=Collection::category

I have 2 value lists defined... The first, Category is from the field items_category:category, and takes all items

The second value list is from the field items_category::items and takes ONLY related values from Collection

So, what happens is the users create a new record in Collections, click on the Category popup menu, then they choose a Category. Based on their choice of category, when they click on the Items field they ONLY see in the popup menu those items in the Category they selected previously...

NOW what I'm being asked to do is to provide the ability to make unavailable an entire category of items... So, for example, I have Dog and Cat and Bird as categories. WITHOUT deleting anything from the Items table, I need to be able to make the Dog category unavailable when you click on Category in Collections....

This is really making my brain hurt.... I SUSPECT I will need to move to 2 related tables, one for items and one for categories, but I STILL can't figure out how to make that work....

(I'm not very good at relationships, ask my ex-wife!)

[comment]

How about this way?

test_of_categories2.zip

[brownt]

How about this way?

That is an amazingly elegant piece of coding! Thanks so much! So elegant, I'm still struggling to understand it!

And time pressures have kept me from implementing it yet... I'll get there, tho... You'll probably know when I do, 'cause I'll likely have questions!