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

I'm trying to find a way to list the Accounts to which a Contact is linked in a Sub-Panel of the  Contact Record .

When linking a Contact to an Account, the Contact appears in the Accounts Sub-Panel for that Account, and the Account field in the Contact Record is updated to show that link.

The "problem" we have is that when a given Contact is the Purchasing Authority for 5 to 15 locations of a Customer, the Contact Record contains the Account link for the last time the contact was linked.

I'm thinking I may need to create a new Many-to-Many Relationship for Contact-Accounts, but I'm fearful that doing that would disrupt the standard SugarCRM design.

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

Parents
  • 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!

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 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! 

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 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

Reply
  • 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

Children