I am looking for a way to identify accounts that don't have one specific Team. An account can have anywhere from 1 team to 10 teams, depends on many variables.
Sugar 6.7.13 Enterprise
I'm on 7.6, but I think this works the same way, I can do this by:
Reports > Create Report > Rows and Columns Report > Accounts
Select Operator is 'Or'.
Scroll down in Filters in the 'Related Modules' panel on the left to Teams.
In available fields click 'ID'. And then select ID 'is not' and add your first team.
Then click 'ID' again. And then select ID 'is not' and add your second team. And so on and so forth for all your teams (you could try 'Team ID is empty', it might be quicker, not sure if it'll work exactly right).
Then click 'Display Modules' and select 'name' in display columns (or whatever else you want to add).
This is important - when in 'Report Details' make sure you tick 'Optional Related Modules: Information Accounts > Teams'.
Then save your report.
Thank you for your reply.
Unfortunately, we have our user's private team added to their accounts, so any account, you will find the User's private team, Division Team, Office Team, and sometimes few others. I can't possibly exclude them one by one, it could be anywhere around 700 teams.
Thank you once again
If you just want to exclude one team, you can just follow the steps above - in available fields click 'ID'. And then select ID 'is not' and add the team you want to exclude. So you don't have to exclude them one by one - just the team you want to exclude. Or did I misunderstand you?
I am sorry if I didn't explain myself right.
Let's say we have a team called "XXX" that needs to be in all accounts.
We have over 700 Teams in total, linked to the accounts.
How to report on accounts that don't have team "XXX" in their team set? I can't possible add 700 conditions to exclude them team by team.
I contacted Sugar and this is their reply:
Case Number: 261767Case Name: How to create a report shows accounts that don't have a specific Team?Subject: Enhancement requestNote: Hi Ameed, This functionality is currently not available in the application. I have added your case to enhancement request # 58247 ( https://web.sugarcrm.com/support/issues/58247 ). One of our partners may be able to implement this functionality for you via code customization. If you are currently engaged with one of our partners, please follow up with them directly to see if they can provide a solution.
This is why I was looking here to see if anyone would have a solution or a work around.
In that case, you can do this in a mysql query. To describe the relationships, accounts has two relevant fields:
team_id - this is the primary team. If you just want to make sure there's a primary team you can just join team_id to teams.id
team_set_id - this is the relationship to any additional team on the account
The team_set_id relates to the team_sets_team table:
mysql> describe team_sets_teams;+---------------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------------+------------+------+-----+---------+-------+| id | char(36) | NO | PRI | NULL | || team_set_id | char(36) | YES | MUL | NULL | || team_id | char(36) | YES | MUL | NULL | || date_modified | datetime | YES | | NULL | || deleted | tinyint(1) | YES | | 0 | |+---------------+------------+------+-----+---------+-------+5 rows in set (0.00 sec)
So the team_set_id in the Accounts table will be the same as the team_set_id in Accounts. The team_id in team_sets_teams will be the team_id.
How to construct the mysql query will very much depend on:
a) Do you only want to know all accounts that don't have a primary team? Or any team at all? It shouldn't really be possible to not have a primary team
b) When you say they 'don't have a specific team' do you mean they can be assigned to the Global team (which usually has an ID of 1) or something else?
If you don't have mysql access, then all of this is moot anyway.
Retrieving data ...