Conditional value lists
Hi - I'm stepping up from Bento, and climbing Filemaker Pro's learning curve.
I have a relatively simple database: a series of linked one-to-many tables to implement GTD. At the top, a table of my areas of responsibility, which links to a table of vendors, which links to projects, which links to to-dos and which links, separately, to notes (notes and to-dos are both children of projects). A "files" table is another child off the vendor table.
As I create a new to do or note, for example, I want the fields representing the area, vendor, and project to reflect what has been setup in those respective tables. So I setup value lists with pop-up menus, with the value lists pulling up the names from the other tables (actually, it keys on the ID and name/label and is set to only show the name/label, the second field). That way the ID number is hidden and I just work with names.
So far so good but since the project names are rather generic and therefore the same name is used for different projects for different vendors, it's impossible to tell just from the project name what vendor it's tied to.
For example, if my tables were instead states, cities and streets, when creating a "repair" record, selecting "main st." would hardly distinguish it from main st. in every other city.
So, I want to implement conditional values - selecting the area would limit the vendor list, which in turn would limit the project list.
In the value list setup, I changed it from all values to only values of the table one up on the stack, but it's not working. That is, in the vendor value list, I limit it to related values from the area table (one up the stack). In a new record, I get a <no values defined> message in the field one level down (e.g., after selecting an area, the vendor field gives that message). In other instances, changing the area does not change the available choices in the options in the field one level down (changing from area a to b doesn't change the list of vendors).