Details about this thread
  • State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 319 subscribers
  • Views 221 views
  • Users 0 members are here

How to Export All Teams Associated with Records in SugarCRM?

Phuong Trinh

Hello everyone,

I'm trying to export all the teams associated with records in SugarCRM. For example, one of my accounts is linked to two teams: Global and QMob-13.

When I try to export this kind of information using standard reports, I only get the primary team or the private one, which usually just gives me the Global team.

I’ve also tried using advanced reports, but without success.

Here’s the SQL query I used:

Fullscreen
1
SELECT team_set_id, acl_team_set_id, team_set_id FROM accounts WHERE billing_address_postalcode LIKE '13%';
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Unfortunately, this doesn’t return all the teams associated with each account as I was hoping. (yeah, i know that i did choose only the fields available on "accounts" tables so that's why my query doesn't work)...

Has anyone encountered this before or have any suggestions on how to retrieve all associated teams for each record?
 Thanks in advance for your help, and have a great evening! Blush

  • 0

    The Team Set is comprised of teams, the IDs of those teams are found in the teams_sets_teams and then you have to look up the teams names in the teams table to make sense of it.

    Fullscreen
    1
    2
    3
    4
    5
    6
    select accounts.team_set_id, teams.name, teams.private
    from acounts
    join team_sets_teams on team_sets_teams.team_set_id = accounts.team_set_id and team_sets_teams.deleted = 0
    join teams on teams.id = team_sets_teams.team_id and teams.deleted = 0
    where accounts.deleted = 0
    and ...
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    To get the primary Team's name

    Fullscreen
    1
    2
    3
    4
    5
    select accounts.team_id, teams.name
    from accounts
    join teams on teams.id = accounts.team_id and teams.deleted = 0
    where accounts.deleted = 0
    and ...
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Hope this helps.

    FrancescaS

  • 0 in reply to Francesca Shiekh

    Hi Francesca, 

    Thanks for your help ! 

    But unfortunately, i can run the first query and have this message instead : 

    Do you have any idea why it doesn't work ? Your query seems ok to me Slight smile

    Sincery yours

  • 0 in reply to Phuong Trinh

    Hi  ,

    If you copied the query exactly as written above, there is a typo in the 'FROM' portion of the query where the table name should be 'accounts'. If that doesn't resolve the issue, please provide the last line from your sugarcrm.log file as it should contain a reference to the specific SQL error you are encountering.

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

  • 0 in reply to Chris Raffle

    Thanks, Chris! The query works great after the changes FROM accounts !

    Now, I’m trying to figure out how to remove one of the teams from my list of accounts. For exemple, i have a liste of accounts having teams QMOB-13, and i want to delete this teams for all these accounts.
    Could anyone help me tweak the query for that? :) 

    Thanks for your help Slight smile