Value List based on another value list

[erostratus]

Hi everyone,

First, I'm sorry if this is too simple of a question to ask. I really don't know how to proceed.

I have a database that is basically a rolodex of companies, with addresses, phone numbers, and contact people as fields. Everything has a 1-1 ratio except that a company may have up to five contacts, each with his/her own phone number. These are all separate fields as part of the company's record, meaning there's a contact #1 field, contact #2 field, etc.

There's a separate database for creating job postings that uses the employer information from the first database. So, under the employer name field, a user can select an employer from a drop down menu, using a defined value list, which is just the equivalent employer name field from the first database. Then, address, city, state, zip, etc. gets filled in automatically.

Now the problem is that, although the company can have up to 5 contacts, only one of the five will be the contact for any particular job. I want a drop down menu in a field that lists the five possible contacts for the company. I do not know how to make this drop down menu that is taking the names from five fields and only from the record of the company already selected. Does anyone know how to do this? Thank you!

Matt

[Genx]

Search "+Conditional +Valuelist" in the quick search box under search, you will get plenty of responses that suit what you want - take your pick.

But, basically the concept is creating a value list from fields over your relationship, if you have a look at any of the sample files of which i'm sure you will find many.. you'll get the idea. If you still have trouble after looking at these, come back and yell.

~Genx