Contact linked to multiple accounts - How do I see all of the accounts to which a contact is linked?

Hi Bud,

When you say "see," are you focused on the recordView specifically? I ask because my intuition tells me you can report on this. But, knowing your extensive experience with Sugar, my instinct is that you are thinking more along the lines of how to see this at the Contact record level.

There is not a way for the core app to do this in the Contacts RecordView, but I suspect it could be customized at a code level to do it.

I wonder if it is possible to add an array for an accounts subpanel to the Contact modules subpaneldefs. I saw an old reference to someone adding the following to modules/Contacts/metadata/subpaneldefs.php : 

$layout_defs['Contacts'] = array(
    // list of what Subpanels to show in the DetailView
    'subpanel_setup' => array(

        'accounts' => array(
            'order' => 30,
            'module' => 'Accounts',
            'sort_order' => 'asc',
            'sort_by' => 'name',
            'subpanel_name' => 'default',
            'get_subpanel_data' => 'accounts',
            'add_subpanel_data' => 'account_id',
            'title_key' => 'LBL_ACCOUNTS_SUBPANEL_TITLE',
            'top_buttons' => array(
                array('widget_class' => 'SubPanelTopCreateAccountNameButton'),
                array('widget_class' => 'SubPanelTopSelectButton', 'mode' => 'MultiSelect')
            ),
        ),

I have never personally tried this. When I get a chance, I will experiment with it.

Regards,
Patrick McQueen
Director, SugarCRM Support

We have had a M-M Contacts/Accounts whereby we have a Contacts Subpanel on the Account and an Accounts Subpanel on the Contact.

It was implemented by the Sugar Partner who did our original v6.0 Pro deployment back in 2011/2012.
Our production system is now on Pro v11.0.5 and I just tested the conversion to Ent v 11.0.5 and it's still working.

I admit I have never looked into how they set this up, but... looking at ti now it seems that they defined a new link on the same relationship as the original...

In my Contactvardefs.php in the cache folder for the Contacts module I can see the original relationship with link name "accounts":

    'accounts' => 
    array (
      'name' => 'accounts',
      'type' => 'link',
      'relationship' => 'accounts_contacts',
      'link_type' => 'one',
      'source' => 'non-db',
      'vname' => 'LBL_ACCOUNT',
      'duplicate_merge' => 'disabled',
      'primary_only' => true,
    ),

and the new relationship with link name "accounts_contacts"

    'accounts_contacts' => 
    array (
      'name' => 'accounts_contacts',
      'type' => 'link',
      'relationship' => 'accounts_contacts',
      'source' => 'non-db',
      'vname' => 'LBL_ACCOUNTS_SUBPANEL_TITLE',
    ),

Notice that the "relationship"=>"accounts_contacts" is the same in both.

The link with name accounts_contacts is defined in the Contacts Vardef Extension framework 

custom/Extension/modules/Contacts/Ext/Vardefs/accounts_Contacts.php

We then have a subpanel for Contacts on Accounts:

custom/modules/Contacts/clients/base/views/subpanel-for-accounts-contacts/subpanel-for-accounts-contacts.php

and a subpanel for Accounts on Contacts

custom/modules/Accounts/clients/base/views/subpanel-for-contacts-accounts_contacts/subpanel-for-contacts-accounts_contacts.php

In the DB we still just have the accounts_contacts table that contains these relationships.

In the Contacts module the original account field is removed from the custom view, and not required. So a contact can be created without any accounts, then accounts can be related from the subpanel.

Originally we showed the account in the Create Contact view and called it "primary Account", but that was creating confusion as we don't really have the concept of primary Account.

Is this the best way to implement this? I am not sure...
But it seems to be working... for now ;)

FrancescaS

Thanks Patrick! You are correct, I'm trying to have the list of Accounts in a sub-panel of the Contact Record.  I'm sure it could be coded as you suggest, but as you know, we're in the on-demand environment and we're trying to not have any custom code.

Hi Francesca, this looks like what I'm trying to do, and I appreciate you jumping in on this.

It is the exception where we have a single Contact that needs to be linked to several accounts.  It's a one-to-one relationship in most cases except where a given contact crosses multiple sales territories or multiple companies (all four of our companies share a single installation and are separated with a "Company Code".  The Account name in the Contact record is used in BPM to synchronize data like the Territory Code, Assigned Sales Rep, and Teams.  The mess that's been created is when a Contact is linked to a new account, the Account Name is changed, and if the person isn't paying attention they might even accept the semi-automatic change of address for the contact.

I debated about setting up additional relate fields for multiple account records in the contact record, but that would create an unholy mess with assignments, processes, and would undoubtedly create confusion in the Service Department.

It may be time to start looking at customization of the code, and what you have would probably do what's desired.  We're in the on-demand environment and I don't think we want to do customization outside of Studio and BPM for now.

Thanks again for the suggestion.

Hi Patrick and Francesca,

While looking into this, one of the Sales Managers said he'd be happy with a report that could be sent to the territory reps and Customer Service.

The standard reports module doesn't accommodate pulling more than the related account, and I don't have a lot of experience with Advanced Reports.

What I'd like to pull is a report that would show the sales territory, contact name, office phone, email, and account names, and a way to know which of the account is the one in the related account field.

I'll start working on this, but if you wrote the SQL to create the report I'd be very thankful!

I don't know where your sales territory is stored but this should give you a start.

Here I am only pulling the primary email address, and only contacts that have an email address

select contacts.first_name, contacts.last_name, contacts.phone_work, email_address as primary_email_address
from contacts
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
  and email_addr_bean_rel.primary_address = 1
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'

This will get you contacts even if they don't have an email address (outer join), and all the email addresses if any (I removed the primary email flag check in the email_addr_bean_rel join condition).

select contacts.first_name, contacts.last_name, contacts.phone_work, email_address
from contacts
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
left outer join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
left outer join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'

You will need to join in custom tables for the contact and account if you need custom fields in your result set or filters.

Hope this helps,
Francesca

WOW Francesca! This is getting me close to what's needed.  I changed the Contact ID to one that I know appears in multiple Account records and it pulled all four records...  The Account Name is needed, and the custom field in the contact record is "sales_territory_code_c".  I'm working on this to see if I can get it finished.  THANK YOU! 

Here you go. With account name and sales_territory_code_c

select accounts.name,
contacts.first_name, 
contacts.last_name, 
contacts.phone_work, 
email_addresses.email_address, 
contacts_cstm.sales_territory_code_c
from contacts
join contacts_cstm on contacts_cstm.id_c = contacts.id
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
left outer join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
left outer join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'

If you want ALL contacts with more than one account, this will count the number of accounts for each contact, return only the ones with more than one (having account_count > 1)

It also groups the account names and email addresses into one field for each contact so you should get only one line per contact.

You will need to review your results, make sure it's working for your scenarios, my instance is too customized to test thoroughly.

select 
group_concat(accounts.name SEPARATOR '; ') as account_names_semicolon_separated,
contacts.first_name, 
contacts.last_name, 
contacts.phone_work, 
group_concat(distinct(email_addresses.email_address) SEPARATOR '; ') as email_addresses_semicolon_separated, 
contacts_cstm.sales_territory_code_c,
count(distinct(accounts.id)) account_count
from contacts
join contacts_cstm on contacts_cstm.id_c = contacts.id
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
left outer join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
left outer join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
group by contacts.id 
having account_count > 1

Let me know how it goes.

Francesca

WOW Again Francesca! You are SO VERY talented and helpful.  I'll be working on this later today - the SQL is VERY close to exactly what I need.