Multiple Value Lists for a single field.

[niktemadur]

Greetings everybody! Sifting through this forum, I've read similar questions to mine, but I am not sure they are quite the same. I'd like to illustrate my question with a fictitious example, for the sake of simplicity and clarity (I hope).

Here goes:

I have three fields and I want them all to have Value Lists, sequentially related. What I choose in Field One affects my choices for Field Two, what I choose in Field Two affects my choices for Field Three.

My Pop-Up List for Field One: 1. Jazz; 2. Rock; 3. Classical.

If in Field One I choose Jazz, Field Two will give me four pop-up choices:

1. Ragtime; 2. Bebop; 3. Cool Jazz; 4. Fusion.

OR

If in Field One I choose Rock, Field Two will give me six pop-up choices:

1. Classic Rock; 2. Progressive Rock; 3. Glam Rock; 4. Punk Rock; 5. Grunge; 6. Indie Rock.

OR

If in Field One I choose Classical, Field Two will give me five choices:

1. Chamber; 2. Symphony; 3. Waltz; 4. Sonata; 5. Rhapsody.

Let's say I choose Rock.

If in Field Two I choose Classic Rock, Field Three will give me four pop-up choices:

1. The Beatles; 2. Rolling Stones; 3. Jimi Hendrix; 4. Led Zeppelin.

OR

If in Field Two I choose Progressive Rock, Field Three will give me four pop-up choices:

1. Genesis; 2. King Crimson; 3. Yes; 4. Gentle Giant.

And so on...

I've got all my Value Lists typed up. How do I make them work together like this?

Thanks to all for a fantastic forum.

[Anatoli]

From FM help:

If you are generating the values in a value list from a field, you can have FileMaker Pro dynamically change the value list's values based on the values in a second field. In other words, the value list will only display values in the first field from related records where the relationship definition is met by a value entered into the second field.

For example, imagine you have a database called Product that tracked a large number of inventory items. You could define a value list and assign it to the Product Name field to display only a subset of those items based on values in a second field, Category. During data entry, when you specify the value of the Category field (for example, Accessory), only the Product Name values where the Category equals Accessory show up in the value list. Similarly, if the value of Category changes to Clothing, the values for Product Name change to those records where Category matched Clothing.

In the above example, you would perform the following basic steps:

1 Define a value list for Product Name with the Use values from field and Only related values options.

2 Define a relationship to the same database file (a self-join) and specify Category as the match field. (See the help topic Defining relationships for relational databases and lookups)

3 Specify that the value list use values from the related field Product Name.

When you enter a value for Category, FileMaker Pro retrieves the related records where Category matches the value you entered, and displays the corresponding Product Name values in the value list.

Note Both the field with the value list values and the match field for the relationship must reside in the same file, for example, Category and Product Name from the example above.

Copyright

[niktemadur]

Thank you very much Anatoli, I never thought of doing a self-join. I'll try this first thing Monday, back at the office and post back with the result.

[niktemadur]

Help! It didn't work, since the FM manual, from which Anatoli quoted, is infuriatingly incomplete.

I will attach a file, hopefully somebody will see it and understand what my situation is.

[LaRetta]

Hi,

I admit I've been too busy to follow this thread or what you are trying to accomplish ... but I had a few minutes so quickly skimmed it and then opened your file. If you want dynamic Value Lists I see your problem immediately.

Your Value List "Post" should be based upon only RELATED values - not all values. Dynamic VLs must also (as indicated by Anatoli) be based upon values from field - not Custom VLs. Re-read Anatoli's post and it should get you moving back on track. It would have helped me if your 'examples' used in your first post matched what is in your database. I couldn't tell from you Value Lists what you really needed. You also need data in your file. Otherwise, how will you tell if it's working?

Sorry I don't have time to help you more right now!

Cheers!

LaRetta

[niktemadur]

Help please! I'm at my wit's end. Does anyone have a file with Dynamic Value Lists that I can dissect to understand how it works? If so, please post and attach.

I will attach a file with an example of what it is I need to do, using the music library example at the top of this thread (hope you're reading this, LaRetta).

[niktemadur]

Whoops! Hit the wrong button by mistake. Here's the attachment.

[andygaunt]

Hi --

I have attached a sample of a conditional value list file.

It uses a separate file for the values as you want to conditionally match 3 or more value lists.

Open the Value List.fp5 file

field 1's value list uses all values from the related file Field 1

Field 2's value list uses Only Related Values from the related Field 2. This is based on a relationship of Field 1::Field1

Field 3's value list uses Only Related Values from the related Field 3. This is based on two calculation fields that combine Field 1 & Field 2.

HTH

[niktemadur]

Calculation Field!

Andy Gaunt, you have done it. As they say in my neck of the woods, may God bless you with ten children (not to be taken literally).

Heck, in celebration, I may even wash my dirty dishes AND vacuum the cat hair off the sofa.

Seriously, thank all of you very much for your patience, know-how and time, even as it took me several days to state my problem clearly.

Have a wonderfull long weekend.

[zinneken]

To Andy : You solution works great, as long as there are no "spaces" involved. If one of the items that is being calculated has "spaces" they won't show up (Like for example "South Australia" - the space would prevent it from showing up in the list when coming from the calculation). How can I solve this?

Thanks.

[andygaunt]

zinneken -

Um......... there are spaces in the example files values. They seem to work just fine.

Can you clarify what you are doing so we can ascertain the problem?

[John Caballero]

Sounds like you calculation is formatted as a number. Is that the case?