Importing Contacts that are related to multiple accounts

Hello,

Your question will probably lead you far from a simple technical answer :) This starts by looking at the business use case. 

We've encountered this question several times, and each company has a different approach. For many, a contact is not, actually, linked to several companies. Why? because contacts often have different titles, phone numbers, email addresses etc. in the different companies they work for. 

While you can add more phone/email fields on the contact module, you will not be able to automatically determine which email is linked to which company. 

Until Sugar lets you add information to the relationship itself (title, phone, email...), multiple contact-account links will require either:

1. An intermediary module that carries specific information (title, phone, email...) and the relationships to the contact and the account. Database-wise it's clean, but it breaks some features (Sugar Connect for instance). Also the standard import feature will not let you import the relationships for this custom module. 
2. Pseudo duplicates, ie 1 contact record for each "contact context". Additionally you can create a contact-contact relationship if you want to map which contact is brother/sister to which and even create a "master contact" if you want to aggregate stats for instance. 

This may feel like one of those "tell me what you need, I'll tell you how to do without" moments, but I assure you that these questions are (often) vital.

So tell us more about your context, and we can look into this. Plus I'm sure others from the community will be happy to chime in. 

I totally agree to Damien Pochon's post. We often have the requirement that customers want to have such a many to many relationship between Contacts and Accounts. But mostly it is not only the relationship, there are special requirements for this relationship, like the type of employment, the function within that company, titles, even start and end dates or just the fact that somebody left a company. So it mostly depends on the usecases in the requirements how this could be solved.

The accounts_contacts relationship itself is a many to many relationship but Sugar code  brings it down to a one to many relationship. If a customer needs the many to many thing there are two possible ways to solve that:

1. extension of the existing relationship with new fields. This was done by partners already in some version 6 implementations and it is a very stony way as the out-of-the-box relatiosnhip has certain hard coded workflows which can change from version to version.

2. creation of a new realtionship module which holds the relationships itself as records which can be edited.  This was done by partners too and it requires a very exact design to make sure that the interaction with the existing relatonship does no harm to the new one and vice versa.

Both ways are doable, but both require detailed analysis and good developers for the implementation.

Damien Pochon, Harald Kuske

Thank you both for your replies. I'll try to summarize the need so you'll understand our business case:

We have several thousand customers (Accounts). Some of them are loosely affiliated with each other in a region - separate companies but sharing some services.  For Company 1 thru Company 10, John Smith is the I/T resource.

If one of those customers calls and our support team needs to visit with their I/T resource, we want John Smith's Contact record related to each of the 10 Account records.  We don't want the caller to say, "John Smith is our I/T guy", have the support person look at all the Contacts associated with the Account, not see John Smith, and add a second (duplicate) John Smith.  

We have those relationships in our existing legacy system.  As I mentioned, it's about 120-150 Contacts spread out across a few hundred Accounts.  It won't be the end of the world if we have to manually "Link an existing" Contact to each Account.  From what you both said, it sounds like that will be the easiest and least painful way to do this.  We don't want to go through the effort of custom modules, custom code, or other cases.  If this was several thousand contacts/accounts, we'd probably be more open to that.

Thank you both for your help!

Bob

We have addressed this requirement for a number of our customers. Taking your use case as an example, the Accounts Module will contain all your customers (I'd recommend an Account Type of customer or similar). Similarly the Contacts Module will contain all employees linked to those Accounts.

The IT Providers in the example are actually an entity that should be recorded in the Accounts Module - suggest an Account Type of say IT Provider unless they are a supplier to your organisation as well in which case you might set Account Type to Supplier. Again people who work for the IT Provider will be Contacts linked to those Accounts

Its easy to segregate Accounts and Contacts using the Account Type..

I then create a one to many relationship in the Accounts Module to the Accounts Module (would be nice if it could be filtered) and name the resulting Subpanel as needed - in this example - IT Provider.

As for Contacts being linked to multiple Accounts - we never recommend that for the reasons explained in earlier posts. We will duplicate the contact link the new Contact with updated Contact details to the appropriate Account (new or an existing one) and set the Status of the original Contact to Terminated, Resigned etc etc and invalidate emails, set to Do Not call etc. (We use BPM for those types of routine processes).  

That is an absolutely fantastic usecase which could be handled by a simple additional relationship between Accounts and Contacts. In your case I would create such a relationship as many-to-many because I think it can be possible that a customer has more than one IT-guy.

So when you create such a relationship "IT-support" you will have a subpanel in Accounts and Contacts where you can add each of the supporting guys to any account. The many-to-many relationship automatically shows such links in accounts and contacts as you see in the following screenshots.

Hi Harald,

This particular case is indeed one that would push for a many to many relationship. But won't a custom relationship break some of the out of the box features? I'm thinking Sugar Connect, importing (OK, you provided a workaround for that one in another thread), etc? 

" I'm thinking Sugar Connect": Yes, Damien Pochon, that's exactly the issue, as the OOTB relationship is used at so many places you never know which side effects can occur. So in most cases where additional fields are envolved an additional relationship-module would be the better choice. BUT with code to support the OOTB relationship, perhaps by copying the primary relationship to the OOTB relation tables. Detailed design must be done here.

Hi Bob Bundy 

Curious if you can share which solution you adopted?