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

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

Children