Different value lists from one related table?

[Gareth]

Hi,

I am trying to find out if there is a way to make a series of different value list pop-ups in a single record be drawn from just one related table using a conditional argument.

Here's the scenario: Table 1 stores student records, including courses taken, which fall under different requirement headings, such as Environmental Biology, Chemistry, etc. Table 2 lists the courses, and one of the fields is a text code that represents the requirement that that course satisfies ("EnvBio", "Chem", etc.) What I would like is for the Environmental Biology Course field in Table 1 to be filled in a by a pop-up list that includes only those courses from Table 2 that have the code "EnvBio", the Chemistry Course field in Table 1 to be filled in a by a pop-up list that includes only those courses from Table 2 that have the code "Chem", and so on. As far as I can tell this 'multiple list' scenario doesn't work using matching records, because you have to specify just one field from Table 1 to do the match against the code field from Table 2.

I realize there are some long-winded ways to do this: for example, I could maintain separate tables for the courses in each topic area, which would easily allow me to make the value lists different. The reason I don't want to do this unless I have to is that some courses may satisfy more than one requirement, and so would need to be included in more than one table, which makes keeping the course tables up-do-date rather more tricky. It would be better (more efficient) to just have one course table with a code field that allows multiple values.

Any suggestions? One possibility that occurred to me is to mantain a single course table, but have other, subset tables (that the value lists in Table 1 refer to) generated automatically by a script (which would be run once each time the course table was updated). But I realize that duplicating tables via a script may be rather "un-Filemaker", even if it is possible. Plus it's rather beyond my current scripting capability!

Thanks for any suggestions...

Gareth

[The Shadow]

Here's a sample file.

You want multiple versions of your "course" table in the graph, each having a different relationship. Unfortunately, you can't create a relationship to a constant like "Chem", so you'll need to create global fields to hold those values.

Then you can create your related valuelists using these relationships, and add your popups.

[Gareth]

Shadow,

Thanks -- that's perfect. What I didn't realize was that you can add multiple instances of the same table to the relationships graph, and thereby define different relationships to it from different fields in the same second table.

Gareth