I'm building a solution that includes (among others) the following tables:

1. Vendor_Info (companies that we do business with)

2. Contacts (individuals that work at the companies we do business with)

3. Campaign_Info (advertising campaigns, which we run co-operatively with Vendors in the first table)

4. Agreements (co-operative agreements with Vendors to run in a given advertising campaign)

Each contact is associated with a given company, and there may be a number of contacts for any single company, each with their own job function (budget responsibility, budget and materials, materials creation, sales rep, etc). The database user will speak with the specific contact at a given Vendor who has budget responsibility or budget and materials responsibility to secure the agreement to run in a given campaign. It is worth noting that if there is only one contact at a given vendor with budget responsibility, and that contact may or may not also be responsible for material creation. That designation is handled with a value list that includes "Budget Responsibility" and "Budget & Materials", among other things.

Now, here's the question: I have a campaign worksheet layout that basically consists of a portal to campaign participants. That portal includes a pull down with a list of all vendors, and once the vendor is selected, I want the appropriate related contact info to populate contact name and contact phone number fields in the portal. The "appropriate related contact" is the one with budget responsibility.

The tables are related to one another as follows:

Vendor_Info is related to Contacts using a Vendor ID field

Vendor_Info is related to Agreements using a Vendor Name field

Campaign_Info is related to Agreements using a Campaign ID field

Thanks in advance for any ideas anyone can provide!

Adam,

Looks like you need to have a look at what Ralph has done with my file :

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=157178&an=0&page=0#157178

Darrin.