Thread Details
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 371 subscribers
  • Views 877 views
  • Users 0 members are here
Forum Actions

report on multiple contacts

James Kinnersley

Hi.  We'd like to run a report to show Accounts with multiple Contacts (and exclude ones with a single Contact).  If someone could point me in the right direction to set this up it'd be a great help.

Many thanks

James

Parents
  • 0 Sugar Employee Badge

    Hello James, 

    If you would like to try an approach that doesn't require updating the account records an Advanced Report could do it. 
    Create the following custom query: 

    Fullscreen
    1
    2
    3
    4
    5
    SELECT a.id, a.name, COUNT(ac.contact_id) as contact_count
    FROM accounts_contacts ac
    JOIN accounts a ON ac.account_id = a.id
    GROUP BY a.id, a.name
    HAVING COUNT(ac.contact_id) > 1;
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    And use it to create an Advanced Report.
    This will list all the accounts with more than one contact related. 


    I hope this helps


    André

Reply
  • 0 Sugar Employee Badge

    Hello James, 

    If you would like to try an approach that doesn't require updating the account records an Advanced Report could do it. 
    Create the following custom query: 

    Fullscreen
    1
    2
    3
    4
    5
    SELECT a.id, a.name, COUNT(ac.contact_id) as contact_count
    FROM accounts_contacts ac
    JOIN accounts a ON ac.account_id = a.id
    GROUP BY a.id, a.name
    HAVING COUNT(ac.contact_id) > 1;
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    And use it to create an Advanced Report.
    This will list all the accounts with more than one contact related. 


    I hope this helps


    André

Children
No Data