Details about this thread
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 12 replies
  • Subscribers 308 subscribers
  • Views 3064 views
  • Users 0 members are here
Related content from around SugarClub

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

Bud Hartley

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

    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)

  • 0 in reply to Bud Hartley

    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

  • 0 in reply to Francesca Shiekh

    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)

  • +1 in reply to Bud Hartley

    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'

Reply
  • +1 in reply to Bud Hartley

    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'

Children
No Data