Dynamic Value Lists - multiples

[Charles Henebry]

Here's the problem:

One table full of items, each assigned to a category.

Another which has (20+) fields that get populated by items from the first table (via a value list). That is, each record has 20+ slots for these items.

Each of these 20+ fields is paired individually with a category field. I'd like to use these category fields to limit the value list that appears in the popup menu for each of the 20+ item fields.

So far, I can't figure out a way to use a second field (the value in the category field that corresponds to each of the 20+ fields) to limit a value list drawn from another table.

If instead of 20+ fields (each with a different category) I had just one item field and one category field in each record, creating a dynamic value list drawn from a separate table would be simple. But unfortunately I need to have a lot more than just one of these item fields in each record.

I hope the parameters of the problem are clear from this summary.

Best,

Chuck Henebry

[Charles Henebry]

Okay, figured it out for myself.

Turns out you can define multiple relationships between two tables. You do this by creating additional instances of one of the two tables in the relationships pane of "Define Database" (this is all FM 7). In my case, I create multiple instances of the "Items" table. And then I create a separate relationship between my main invoice table to each of these instances of the "Items" table, keyed by the 20+ category fields in my invoice table.

Each of these separate relationships can then be used to generate separate valuelists, keyed by category, in "Define Value Lists". Overall it's a lot of labor (I've got 20+ slots for items on my invoice table, so that means 20+ relationships and hence 20+ value lists). But it works.

Hope these directions help someone else in need of assistance.