Cascading value lists from multiple tables...

[TWKArtist]

Hi All..!  

I'm new here as I just started in FileMaker (transiting over from MS Access) so I'm not familiar with everything. 
I'd like to ask about possible solutions to a problem I am having with value lists.  Here goes ...

 

Table 1 -- "look_Category"
    Field 1 -- <pkID>               (Serial Num.)

    Field 2 -- <Category>        (A list of financial categories: "Firm," "Product," etc.)

 

Table 2 -- "look_Product"

    Field 1 -- <pkID>                (Serial Num.)

    Field 2 -- <fkCategoryID>  (Relationship with pkID in Table 1)

    Field 3 -- <Product>           (A variable list of financial products: "All Cap Growth," "Focused Growth," etc.)

 

Table 3 -- "look_Vehicle"

    Field 1 -- <pkID>                (Serial Num.)

    Field 2 -- <fkCategoryID>  (Relationship with pkID in Table 1)

    Field 3 -- <Vehicle>           (A variable list of financial vehicles: "Separate Account," "Mutual Funds," etc.)

 

Table 4 -- "Main Data"

    Field 1 -- <pkID>                (Serial Num.)

    Field 2 -- <fkCategoryID>  (Relationship with pkID in Table 1)

    Field 3 -- <fkProductID>    (Relationship with pkID in Table 2)

    Field 4 -- <fkVehicleID>     (Relationship with pkID in Table 3)

    Field 5 -- Other data....

    Field 6 -- Other data....

    Etc..........

Now, the reason I have separate lookup tables is to accomodate future changes in terms and text -- if the entry in the 'Main Data' table is a foreign key lookup value, than the user can change the 'Category,' 'Product' and 'Vehicle' entries later if they need to and the changes will propogate throughout the other tables with no trouble.

On the 'Main Data' layout I have three pop-up menu boxes for each of the 'Category,' 'Product' and 'Vehicle' fields.

The first one is easy -- the 'Category' pop-up menu gets its data from Table 1 <look_Category> with a value list set to use both fields and "include all values."
 

The problem starts with the next two pop-up menu boxes -- the 'Product' box should be restricted to the 'Products' in Table 2 as related to the 'Category' picked from Table 1; likewise, the 'Vehicle' box should be limited to the 'Vehicles' in Table 3 as related to the 'Category' picked from Table 1.

 

Now, I have relationships set up between the Table 1 <pkID> field and Tables 2 and 3 fk fields. And those work for data entry on those lookup tables themselves. That's not the problem.

What I'm unfamiliar with in FileMaker world is how to set up the necessary relationships and value lists to make the pop-up menu boxes do their thing.

Thanks in advance for any suggestions. 

 

>>TimK

 

 

[bigtom]

I am having the same issue. I can get the first relationship dropdown to work, in my case "Make". I cannot figure out how to make the relationship and value list to get the 2nd, 3rd, and 4th tier dropdowns to work.

 

I have tried a number of things that did not work. Similarly I am starting with a database called Car and need to lead to Car:Make:Model:Engine with the field selections.

[TWKArtist]

OK, finally figured it out. For those who are having similar problems here's an image of the table relationships I worked out:

 

 

The solution I was looking for was the relationship join between the <value_Questions> table (a second copy of the <data_Questions> table) and the <look_Product> table.
This apparently provides the ability for the second and third tier of value lists to have access to the original <look_Category> table and can therefor sort the second and third tier lists by whatever choice is made in the first value list.

 

>>TimK