Title

Post

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).

There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

Thanks. I had already tried following the knowledgebase article. I'll try that again. Would "use values from first field" (ID) combined with "also display values from second field" (name) complicate this?

To make sure I have this right, if my vendor value list should list the vendors for whom there are already records, the "use values from" should refer to the vendor table and then the "include only related values from" should refer to the parent table (areas, in my case), correct?

Would "use values from first field" (ID) combined with "also display values from second field" (name) complicate this?

It shouldn't, but make sure that the first field value (ID) is what you use in your relationship to match records. Usually, the challenge is in correctly selecting the "Starting from" table occurrence as the link between the starting from table occurrence and the table occurrence from which you are listing values is what defines the relationship that then controls what values appear in the value list.

You may want to examine the demo file and note how it uses a portal to illustrate how the same relationship that displays records in a portal can show the same data in a conditional value list.

Value list "areas" is drawn from the area records and is set to all, and

Value list "vendors" is drawn from the vendor records and is set to related starting from areas, then

In a new project record, I should be able to select an area under the AreaID field and get just its related vendors in the pop up field for vendorID.

But it doesn't work. I'm so frustrated with my inability to figure this out.

I notice that in the examples, such as in the knowledgebase article, all the value data was contained in one table (e.g., meals and food). In my case, I have separate tables for such data (like have a meals table and food table). Should that make a difference?

I'll try to get around to doing that but I decided to use Bento. Bento is just so easy, so intuitive, I was able to whip up a database in minutes. Sure, sure, it doesn't have portals (only manually linking), actions buttons, and more, but I'm able to do what I need and do it easily.

If it turns out Bento is not compatible with Mavericks, I'll not upgrade or I'll keep an older Mac around just for Bento. It's that good (for my needs).