Conditional Value Lists

[kevybaby]

I'm not sure if I am missing something obvious or if I'm trying to do something that can't be done.

 

I have two tables: Contacts and Estimates

 

In the Contacts table, there can be multiple contacts with the same company name. The Contacts table use different records for different people to accommodate possible multiple detail differences (email for sure, but also possibly addresses and other details).

 

I have set up the Estimates table so that using a Conditional Value List, the selection of NAMES shows correctly whenever a company is shown, but I also want other fields to populate based on the name selected. For example, when I select Acme Test Company and tab to the Name field, I get the proper list of Goofy, Micky Mouse, and Minnie Mouse (my actual test data). When I select Minnie, HER email, address, etc. should populate, but I always get Mickey's because his record is the first (earliest) record in the Contacts table.

 

I have a Contact ID unique number, but I am not getting this to come over to Estimates (this is probably the key to my problem). I cannot use the person's name as the match in a relationship because the same person make be with two different companies (weird: I know).

 

What am I missing?

[comment]

If I follow this correctly, you need to have two relationships here, using two occurrences of the Contacts table:

 

Estimates::ContactID = Contacts::ContactID

 

and:

 

Estimates::CompanyName = Contacts 2::CompanyName

 

Your conditional value list should show related values from Contacts 2.  Once you have populated the ContactID field, you will see the selected contact's data through the relationship to Contacts.