Multiple Relationships

Use the same field in Company for the two links to Action. Then use the same field in company to link to the same field in contact in the company to contact and the company_partner to contact_partner links. That will give you these relationships:

Action::CompanyID_fk = Company::CompanyID

Action::ParternID_fk =  Company_Partner::CompanyID

Company::CompanyID = Contact::CompanyID_fk

Company_Partner::CompanyID = Contact_Partner::CompanyID_fk

This assumes only one contact for a given company and one contact for a given company partner. In some circumstances, it may make more sense to either link to contacts using ContactID (many contacts for one company) or even use a join table if the relationship is many contacts to many companies.

You can upload additional screen shots to a file sharing site and then post the link to that image here. That's a pain but best we can do here.

If you are trying to place the full name of the company contact next to the company ID field on the Action layout, you just add that field from contacts. For the partner, you'd add it from contact partner.

In both cases, your relationships suggest that it's possible to have more than one contact linked to the same company record. If this is the case, you'll get the full name of the first such contact--which might not be the contact you wanted here.

Sounds like you need to link directly to a contact ID value instead of using company ID.

Will it work to link your tables like this?

Action>------Contact>------Company

Action::ContactID = Contact::ContactID
Company::CompanyID = Contact::CompanyID

Use the same set up for partners, but start with a different field for the contact ID in Action.

"I need to be able to select the company first, then choose the contacts for that company."

Why? what problem does that solve for you?

You aren't selecting the company first if you use a calculation to supply the company name ( and this field isn't needed to display the company name either.)

Going back to my previous suggestion:

If you select or enter a customer ID in Action::customerID, you can add the name fields from customer and company to the action layout and they will appear when you enter/select this value.

The only reason I can see where you would need to select the company first is if you want to implement a conditional value list so that selecting a company narrows the list of contacts to just those contacts that are related to the selected company. That is possible here, but not something we've discussed and it requires an additional relationship.

Please note that with the right details in place, what I am suggesting will work and we can still come up with the needed conditional value list also. But before diving into those details, I think you need to confirm one of my conclusions here.

With a given action record, the critical detail is that you are linking this action record to a specific contact record in the contacts table. Once you've selected a contact, all the related company info is then available and visible, but the crucial selection is that you are choosing a contact, not a company. Does that make sense?

If so, then your original set up won't work because you are only choosing a company. you are not selecting a specfic contact associated with that company which is why you kept seeing the wrong contact info unless the desired contact happened to be the first such contact related to the seleted company record.

Try out these relationships:

Action::CompanyID = CompanyContact::CompanyID--this is the relationship to use for your conditional value list. Set it up to list values from CompanyContact, include only related values starting from Action. The value list can list contactID as field 1 and a name field as field 2, just make sure the fields are selected from CompanyContact, an occurrence of the Contacts table.

Action::ContactID = Contacts::ContactID -- this is the relaitonship that gives you access to the selected contact record's data. You format action::contactID with the above described conditional value list so that you can select a company in the companyID field then select a contact in the contactID field from a list limited just to contacts that are linked to the selected company. You can add any field you want from contacts to your action layout and they will display the data from contact once you have selected a contact.

You can repeat this pattern of table occurrences (but use different ones) for your partner link to contacts and to company. You'll also need to define a new conditional value list as it must be based on a different contact unless both contacts are always from the same company.

Once you have all that working, you might be interested in a demo file that utilizes some enhanced value selection methods to make working with such large value lists easier to manage: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

Please read my last post again. I spelled out exactly how to do this so that you select the company first, then the contact.

Every thing looks correct here in terms of the relationships used, though the calculation fields to return values from the related tables are not needed. You can, instead, add the fields from their respective table occurrences to your action layout. That doesn't explain why they aren't working for you, however.

In your example, I do not see a contact ID value selected in the Partner1_CtID_tk field. This field needs a different conditional value list than used with Customer_CtID_tk field. Without a value in that field the partner contact name calculation field cannot display a name.

Ok, just played with a demo file, and now I see what's missing.

For each contact, you need additional table occurrences of contacts so that you can link by company ID (for the conditional value lists) and then also a different table occurrence linked by contact ID (so you can access the contact's name)

This requires 4 occurrences of contacts and 2 of company.

Take a look at my demo file to see what I mean:  http://www.4shared.com/file/rhrC1RSY/DoubleContactCondValueListDemo.html