Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Multiple Relationships

Question asked by smallbusiness on Aug 7, 2011
Latest reply on Aug 10, 2011 by smallbusiness

Like • Show 0 Likes0
Comment • 15

Title

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.

philmodjunk Aug 8, 2011 12:01 PM

Correct Answer

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.

See the reply in context

No one else had this question

Outcomes

15 Replies

philmodjunk Aug 8, 2011 12:01 PM
Unmark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
smallbusiness Aug 8, 2011 1:09 PM
Mark Correct
Correct Answer
Dear PhilModjunk,

Many thanks! Simplified the relationships a lot. However, now I can't get the Full name to show properly beside the Customer Contact ID, and I can't seem to get the value list right for the Partner Contact ID (no matter what I do it selects the Customer Contact IDs).

Help much appreciated - I'm afraid I'm just an amateur at this...

Wish I could send another screenshot...
Like • Show 0 Likes0
Actions
philmodjunk Aug 8, 2011 1:17 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
smallbusiness Aug 8, 2011 1:30 PM
Mark Correct
Correct Answer
Again, many thanks. You have hit the nail on the head. I usually have several contacts for each Customer and Partner company, and need to be able to select and display the appropriate one, not just the first one! How to I do that?

Then I can also set up buttons to email them, Skype them, copy their contact information (which I paste into emails) etc...
Like • Show 0 Likes0
Actions
philmodjunk Aug 8, 2011 1:37 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
smallbusiness Aug 8, 2011 2:58 PM
Mark Correct
Correct Answer
Nope, now the only field that works properly is the Company ID for both Customer and Partner.

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

Fields are as follow:

Customer Company ID (from ACTION: Company ID which is an Indexed number - with a lookup table that (should) select COMPANY: Company ID and display COMPANY: Company_Full (but I get an error message that Company_Full can't be indexed)

Customer Company Name (from ACTION: Action_Company_Full which is a calculation = COMPANY: Company_Full

Customer Contact ID (from ACTION: Contact ID which is an Indexed number - with a lookup table that (should) select CONTACT: Contact ID and display CONTACT: Contact_Full and limits the choices to related IDs from COMPANY. (but I get an error message that Contact_Full can't be indexed)

Customer Contact Name (from ACTION: Action_Contact_Full which is a calcuation =CONTACT: Contact_Full

---

Partner fields are analogous.

Where am I going astray here???
Like • Show 0 Likes0
Actions
philmodjunk Aug 8, 2011 3:26 PM
Mark Correct
Correct Answer
"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.
Like • Show 0 Likes0
Actions
smallbusiness Aug 8, 2011 4:15 PM
Mark Correct
Correct Answer
Yes, that's exactly why I need to select the COMPANY first - I only want to be offered a choice of the CONTACTs related to that COMPANY, for both the customers and partners. i have hundreds of companies and thousands of contacts.

See my origina screenshot. Ed Grabover is one of two contacts at NaNaMed, and I would have two or three at XL Precision if I could get them to show.

When I just had customers in my ACTIONS layout everything worked fine, but adding the partners (which as you know take their COMPANY and CONTACT values from the same table, since there are companies that are both customers and partners/suppliers) has proved impossible for my limited knowledge (I have been playing with the fields, relationships, and value lists for a week).

This is my CRM system, and I input quite a few new actions (business inquiries) every day, so selecting the CONTACTS without the COMPANY would not be efficient. Also, doing it this way avoids errors (there may be two John Smiths, but only one working for XYZ Corp, and I don't want to call or email the wrong one.

By the way, I do not really need to display the ID numbers for the COMPANY or the CONTACT, so the selection might be possible with one field instead of two...
Like • Show 0 Likes0
Actions
philmodjunk Aug 8, 2011 4:19 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
smallbusiness Aug 8, 2011 4:30 PM
Mark Correct
Correct Answer
Sorry to be such a pain, but it is important to me to select the company first. Reason is that for a given action/project the contact person sometimes changes (the purchasing agent hands me over to the engineer, or whatever). Also, with thousands of contacts it is much easier to remember the company.

Any way to do this?
Like • Show 0 Likes0
Actions
philmodjunk Aug 9, 2011 10:05 AM
Mark Correct
Correct Answer
Please read my last post again. I spelled out exactly how to do this so that you select the company first, then the contact.
Like • Show 0 Likes0
Actions
smallbusiness Aug 10, 2011 5:49 AM
Mark Correct
Correct Answer
Still no joy - have played around with all variations (contacts first, etc.) using your instructions. Everything works except being able to select and display the partner contact. Partner fields are set up the same as the Customer ones, but...

see screenshots:
Like • Show 0 Likes0
Actions
philmodjunk Aug 10, 2011 11:05 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk Aug 10, 2011 11:27 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
smallbusiness Aug 10, 2011 12:50 PM
Mark Correct
Correct Answer
Y E S ! That did it! Thank you so very much for your persistent hand-holding and efforts. I was in over my head...
Like • Show 0 Likes0
Actions

Multiple Relationships

Title

Post

Outcomes

Related Content

Recommended Content