Value list based on table related to another table

[Bagel]

I know that the topic of dynamic value lists is a frequent one, but I haven't been able to find an answer to my specific situation:

I have a table called "MainContactInfo" that contains names and contact information for individuals. Each individual can be assigned an employer that the user selects from a pop-up list via a value list generated from the contents of a field in a related "Employers" table. (The tables are related by a PersonID.) The "Employers" table in turn is related to an "Offices" table (by an EmployerID). Each record in the "Employers" table can have multiple offices.

What I want to happen is for the user, while entering a record in the "MainContactInfo" table, to be able to select an employer via the pop-up list and then to be able to select an office via a second pop-up list, with the office pop-up list only displaying the list of offices relevant to that particular employer.

I hope that this makes sense and that someone will be able to suggest a solution.

[transpower]

Good question and it does make sense. Set up every possible combination of offices in a separate value list in the Employer table. Then flag each record of the Employer table with the corresponding value list. Then use that code in a calculation with valuelistitems to display the correct value list for that employer in the Employee table.

[Bagel]

Thanks for a quick reply, transpower!

I have a sense of what you are suggesting, but remain a bit confused as to the specifics. Are you saying that I should manually create a different value list in the Employer table for each employer and then somehow designate in the employer's record which value list is appropriate? I'm probably completely misconstruing your advice.

I've attached a sample file. Would you be able to show me a bit more exactly what you mean? Thanks for your help.

Set up every possible combination of offices in a separate value list in the Employer table. Then flag each record of the Employer table with the corresponding value list. Then use that code in a calculation with valuelistitems to display the correct value list for that employer in the Employee table.

EmployeesDatabase.fp7.zip

[transpower]

Not quite. Set up all possible value lists for the various combination of offices you have (not one for each employer). Assign a code for each value list (like v1, v2, v3, etc.); put that code as a field in each record in Employer. Then, in the Employee table, use that code and the valuelistitems function to display the correct value list for that employer.