Cond. Value List over 3 related files

[jrp]

I am updating a solution created by someone else. Here's my problem: I have three related files:

Departments

Staff

Projects

Departments and Staff are related such that for each Department there are multiple staff members, and each staff member is associated with more than one department.

For each Project, A staff member and a Department need to be selected from a pop-up value list. I would like to use a conditional value list, so that once a Staff member is selected, only that person's associated Departments would appear in the Departments pop-up list. This seems elementary - but since there are three files involved, I haven't managed to make it work.

Can someone help me? Is there a trick I'm missing?

Many many thanks!

[The Bridge]

In the case of a many-to-many relationship, such as between Departments and Staff, you may want to create a go-between file called, say, Staff_Department or somesuch thing. This file only needs two fields: the unique id of the staff member and the unique id of the department.

A one-to-many relationship between Staff and Staff_Department will show all departments a staff member belongs to. A one-to-many relationship between Departments and Staff_Department will show all the staff members in a department.

As for your Projects file, you can do this:

Define a field, Staff_ID, which will look-up the unique Staff ID from Staff based on the name selected in the Staff valuelist. Note that Staff will require unique names for this to work properly.

Define a relationship from Projects to Staff_Department, matching Staff_ID.

Define your Department valuelist to draw values from Staff_Department using that relationship.

Hope this helps!

[danjacoby]

The key is a file with a list of staff members and the departments to which they belong. Create a relationship in the "Projects" file between the Staff field and the proper field in the list file. Then set the value list to include related items -- the departments field from the list file.

[The Bridge]

Don't you just love simultaneous posts?

Nice, succinct answer, Dan! I'm never sure just how much detail to include.

[jrp]

Thank you both so much!

*hard at work*