Creating Sub Value Lists

[The Digital Man]

O.K. I've read all the posts and I still don't get it.

 

I have a table called PRODUCT. In this table I have 5 fields called Key1, Key2, Key3, Key4, and Key5.

 

Now I want to present a set of 5 pop-up lists. I've created a Value list based on the field Key1 in the PRODUCT table and it seems to work just fine. It shows one entry for each different Key1 value. Say for example: PAPER, BOARD, STYRENE, TILE.

 

Now after picking say BOARD I want the second pop-up list which is for Key2 to show only the values from the Key2 fields in the PRODUCT table that have BOARD in the Key1 fields.

 

I created a second table occurrence in the relationship diagram and named it PRODUCT KEY2. I then related the Key1 field in PRODUCT to the Key1 field in PRODUCT KEY2 and indicated they needed to be equal.

 

I then tried to create a value list using the PRODUCT KEY2 table and telling it to start with PRODUCT.

 

It doesn't seem to work. I am sure you gurus out there that read this will probably think I've fallen off the deep end. I am sure there are critical flaws in my structure. What are they?

 

I just want the user to be able to pick a PRODUCT TYPE (Key1) and then see in a second popup all the PRODUCT WEIGHTS for that type and then all the PRODUCT SIZES for a given WEIGHT and then all the PRODUCT COLORS for a given SIZE. I want to have 5 pop-ups next to each other and each subsequent pop-up will show the relative products based on the previous pop-up selection. Is that not too much to ask?

 

Thank you in advance for any help. I think I just need steered in the right direction.

[FileMakin' Tom]

Hi Randy . . .

 

Here is a sample file called "Cool Stuff" which has one example of how to do this along with other tricks. It is in FMP 5.5, but you can convert it as the tricks still apply to 7.0

 

I think the example you want is on screen 4 or 5.

 

Be well . . .

 

Tom

[The Digital Man]

Thanks Tom,

 

The cool stuff is pretty cool. However, I was unable to examine the database structure. Was this an oversight or kept locked down on purpose. Without being able to do that it makes it difficult for me to understand exactly how the menu was implemented.

 

I liked the secondary drop down menu although in my case it would be perfectly acceptable for the secondary menu to be a seperate field that when entered would contain the appropriate list. My menu will be a 5 tier affair and it may become difficult for a user to keep the mouse pointer steady enough to select 5 seperate menu items from 5 cascading menus all in one operation.

 

I am still not clear on how I should go about developing the unique value lists for the various menus in my operation. Some insight on this would be most appreciated.

[The Digital Man]

P.S.

 

I want these unique value lists to be dynamically drawn from the PRODUCT table. As additional products are added to the table they should show up in the appropriate value lists.

[Ender]

The value list for Key 1 can be just the field values from Product::Key 1, or you can use a Custom value list, if there's not too many items.

 

The value list for Key 2 should be the related values only from Product 2::Key 2 based on the relationship:

Product Product 2:

Product::Key 1 = Product 2::Key 1.

 

The value list for Key 3 should be the related values only from Product 3::Key 3 based on the relationship:

Product Product 3:

Product::Key 1 = Product 3::Key 1

AND Product::Key 2 = Product 3::Key 2

 

etc.

 

where Product 2, Product 3, etc, are additional TOs of the Product table. All value lists should show records starting from Product, where the value lists will be based.

[bikergeek]

You should be able to do this as a connected chain of self-join occurrences of PRODUCTS. Each extension of the chain only needs one set of match fields:

 

Define your value lists to use only related records starting from Main.

 

See attached sample.

[The Digital Man]

Thanks Ender,

 

I got it to work! My main problem was in creating the correct relationships and understanding the "Starting" table.

 

Your helpful comments are much appreciated. I was trying to relate the Key1 in the Product Table with Key1 in my first TO of the Product table named "Product Key2" But what I needed to be doing was relating the field where Key1 was actually stored (Which was in my Components table.) with the Key1 field in my first TO (Product Key2).

 

So what ended up working for me was to create 4 TOs of the Product table which I named "Product Key2"..."Product Key5". I created a relationship between "Key1" in my Components table and "Key1" in the "Product Key2" TO. The second relationship was between "Key2" in my Components table and "Key2" in the "Product Key3" TO. They all looked like:

 

Components SELECT::Key1 = Product Key2::Key1

Components SELECT::Key2 = Product Key3::Key2

Components SELECT::Key3 = Product Key4::Key3

Components SELECT::Key4 = Product Key5::Key4

 

My value lists were then built using these relationships. My first value list did not use a relationship. It simply used the Key1 field in the Product table. This value list was named "Key1". My other 4 value lists were named "Key2, Key3, Key4, and Key5". The "Key2" value list was based on the "Key2" field in the "Product Key2" TO and set to start from Product. The "Key3" value list was based on the "Key3" field in the "Product Key3" TO and set to start from Product. The Key4 & Key5 were created the same way.

 

It all works flawlessly! I am so tickled. I went into detail simply for anyone elses benefit that may be trying to do a similar thing. What I love about this is that it is totally dynamic. If a record is added to the Product table it will immediately show up in the appropriate pop up menu!

 

Thanks again for everyones help!

[The Digital Man]

Hey Biker Geek!

 

Where were you this morning!!!

 

From what I can tell I set it up just like your example pretty much. I think the only difference is where I actually stored the info. In my case the "Components SELECT" table.

 

Thanks for the sample.

[Ender]

[ QUOTE ]

I think the only difference is where I actually stored the info. In my case the "Components SELECT" table.

 

[/ QUOTE ]

 

In Bikergeek's defense (and mine), you never mentioned anything about a Components table.

[The Digital Man]

Oops!

 

Sorry about that. I was trying to keep the explanation as simple as possible. I was of course presenting this multi tiered menu for the user to select a particular stock to be used in the manufacture of a specific component attached to a job record. So each of the 5 keys were being stored in the component record.

 

However, I do want you to know that I appreciate your help and both yours and Biker Geeks comments did help me although I actually had it figured out before I read Biker Geeks post. But had I still been struggling his post certainly would have cleared it up.

 

Thanks again for all the advice you guys give! It is appreciated. At least by me.