Author: Yuri Gee
5 minute read time
In this article, I'll provide examples of custom queries that can be used to track changes in Teams on a record, specifically focusing on Accounts and Opportunities records.
Custom Queries in Advanced Reports
With custom queries, you can execute read-only queries on the SugarCRM database via the Advanced Reports module. The appearance of these reports can be customized, and they can be scheduled to be sent via email to users who have access to these queries.
Team fields on a SugarCRM record determine the visibility of the records to users who belong to the respective team.
Tracking Updates in the team_sets_teams Table
This query will produce opportunity IDs, along with the primary (first in the list) and additional teams that had access to the record changed within the last 24 hours (UTC time):
select t.tid, "Opportunity" as module, concat (teams, ", ", group_concat(tset separator ', ') ) as teams from ( select r.id as tid, concat(team.name, IFNULL (concat(" ",team.name_2), "")) as 'teams', concat(sets.name, IFNULL (concat(" ",sets.name_2), "")) as tset from opportunities r join team_sets_teams s on s.team_set_id=r.team_set_id join teams sets on sets.id=s.team_id join teams team on team.id=r.team_id where sets.id <> team.id and s.deleted = 0 and sets.deleted = 0 and team.deleted = 0 and r.deleted = 0 and s.date_modified >= DATE_SUB(CAST(NOW() AS DATE), INTERVAL 1 DAY) ) as t group by t.tid;
The query may not reflect changes when a record is assigned to standard team sets, such as Global, East, West, and their combinations, but it will capture other changes. The list of standard teams can be retrieved using a query similar to the following:
select count(*), team_set_id from accounts group by team_set_id having count(*)>1 limit 20;
Combining Queries for Multiple Record Types Using UNION
To produce changes in the Accounts and Opportunities modules, the query can be formulated as follows:
select t.tid, "Opportunity" as module, concat (teams, ", ", group_concat(tset separator ', ') ) as teams from (
select r.id as tid, concat(team.name, IFNULL (concat(" ",team.name_2), "")) as 'teams', concat(sets.name, IFNULL (concat(" ",sets.name_2), "")) as tset from opportunities r join team_sets_teams s on s.team_set_id=r.team_set_id join teams sets on sets.id=s.team_id join teams team on team.id=r.team_id where sets.id <> team.id and s.deleted = 0 and sets.deleted = 0 and team.deleted = 0 and r.deleted = 0 and s.date_modified >= DATE_SUB(CAST(NOW() AS DATE), INTERVAL 1 DAY) ) as t group by t.tid
Union
select t.tid, "Account" as module, concat (teams, ", ", group_concat(tset separator ', ') ) as teams from (
select r.id as tid, concat(team.name, IFNULL (concat(" ",team.name_2), "")) as 'teams', concat(sets.name, IFNULL (concat(" ",sets.name_2), "")) as tset from accounts r join team_sets_teams s on s.team_set_id=r.team_set_id join teams sets on sets.id=s.team_id join teams team on team.id=r.team_id where sets.id <> team.id and s.deleted = 0 and sets.deleted = 0 and team.deleted = 0 and r.deleted = 0 and s.date_modified >= DATE_SUB(CAST(NOW() AS DATE), INTERVAL 1 DAY) ) as t group by t.tid;
Alternative Approach Using Audit Tables
A more advanced method to detect changes is examining the recent audit tables for modifications in the team_id and team_set_id fields. This approach will display all team changes recorded in the audit log for Opportunities and Account records within the past day (UTC time):
select parent_id,group_concat(field_name separator ', '), group_concat(after_value_string separator ', ') from opportunities_audit where field_name like "%team_%" and date_created >= DATE_SUB(CAST(NOW() AS DATE), INTERVAL 1 DAY) group by parent_id
Union
select parent_id,group_concat(field_name separator ', '), group_concat(after_value_string separator ', ') from accounts_audit where field_name like "%team_%" and date_created >= DATE_SUB(CAST(NOW() AS DATE), INTERVAL 1 DAY) group by parent_id;
Additional notes
At times, queries may take a long time to execute and can put extra load on the database, or may require additional indexing. Therefore, it is advisable to limit the query size and test it on a test instance before applying it to production. Enjoy using custom queries to enhance CRM administration and reporting!