3 Level deep Valuelists based on Records

[overrider]

Hello all,

edit: seems i did not explain clearly, thats why nobody looks at my post. i know how to make a value list based on another field, the question i have is how to make a valuelist minimum 3 level (possibly unlimited) levels deep?

im stumbling to find out how to make Valuelists displaying only Values i selected in a prior Valuelist. It seems i have a mistake in my Relationships, but i cant fins out where. The first one Valuelist is easy and displays as it shoud. All Field Contents from the type table. The second one works as well, but the third one wont function. It picks up Beer, even though i selected Dark Spirits. Can anyone help me fix this file i posted?

Thanks a lot,

Overrider

boggle.fp7.zip

[overrider]

A little update, after playing around all afternoon with it, i finally worked out a way it works. see the attached file. i dont know what the problem was exactly though. There are three tables, one for each category (type, category, sub_category) to use my value list go to the item table and select some values.

however, i find somehow i dont like how i did it, it takes too many relationships to archive. can someone optimize it?

regards,

Overrider

boggle.zip

[Søren Dyhr]

Yes some simplification is posible - check the upload here!

--sd

Boggle2.fp7.zip

[Fenton]

Here's my version. In version 7 you don't need the self-relationships on the foreign key to filter the next table occurrence, if you have the correct relationship already. You can use the "Start from" option, starting from the TO before (if you have an existing path to it).

We have a relationship from Items to Type, on TypeID; then from Type to Category, on TypeID. Both relationships are on TypeID; so the Category Value List will work whether we start from Item (where we are) or from Type (so big deal :-).

But the next one, Subcategory, shows it better. The relationship from Category to SubCategory is on CategoryID. That is the filter we want; but if we "Start from" Items or Type, we get all for that Type.

However, if we "Start from" Category, then we get only those matching that category, which is what we want. Our "filter" path is valid:

Items-->Type-->Category-->SubCategory

Mine uses the real IDs. Yours is kind of a mix up. My opinion is, if it's just descriptive words you want, don't use IDs at all. If it's important data (used for keys, etc.), then use IDs. But don't mix them together like that (unless your client puts a gun to your head; then use Lookups, or quit).

I don't see the purpose of the Type field. If it's supposed to be with Type Description, then concatenate them (put together in a calculation).

You need a few "extra" TO relationships, as you found, so you can reference the "names", from the Items table. Otherwise it doesn't have a clear path to the Category or SubCategory.

boggle_fej.zip

[overrider]

S

[Søren Dyhr]

S

[overrider]

well my idea behind it was to just simplify the process of categorization. i could have one table with all the categories, but since filemaker lets me have up to a million (?) tables, why not take advantage of it? dont you think it is easier when you can make an interface where for example on the top is the categorie, and in a portal you type all the sub categories? i somehow feel it is cleaner that way, even though i might be mistaken. how i came accross this was i read an article and solution file in the filemakermagazine ( www.filemakermagazine.com ) "Infinite Valuelists". I personally did not like the way he had done it at all, so i tried to make my own. It could be used to draw out all components on a pc board for example.

[Søren Dyhr]

don't you think it is easier when you can make an interface where for example on the top is the categorie, and in a portal you type all the sub categories? i somehow feel it is cleaner that way, even though i might be mistaken.

First off, I can

[Fenton]

I would submit that there's more than one way to do a value list, and that a value list of "existing" items in a data table is quite different from "choose items from other values list tables."

In all cases you'd be either using IDs or just names. That's a subjective choice depending on the data.

If you're looking for existing items in data, then you wouldn't use any other tables: 1. You only want what's in the table, to build a calculated global relationship key or for Finds I imagine, 2. The hierarchy exists (or should exist) in the data table, no need to reference elsewhere. 3. You don't need extra TOs to show the names, unless they're in other tables; you probably already have those TOs in that case.

A "flat" file for the choices is probably the best choice if it's just names you want to pick from; because you can't easily generate IDs for each.

Separate tables is probably best if you want IDs. It's easier for a user to create, edit and delete values if they're in their own table, with a real ID (so they can edit the name slightly without breaking existing data relationships).