Title
Multiple Relationships
Post
I have tables for Companies and Contacts (companies can have multiple contacts). Companies can be customers, partners (suppliers), or both, so they are in the same table. I have an "Action" layout and table, and want to be able to select both a customer and a supplier with a contact for each from a dropdown list (lookup table keyed to the ID but showing name), with other fields showing the name of the company and contact.
Just can't get the relationship working for the second relationship (the contact) for the supplier. Have added table occurrences Company Partner and Contact Partner and additional keys, but can't figure it out.
Please see screenshots of the layout and relationships.
Any help appreciated.
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.