POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

A frequently asked question we get from users using Access Teams is: How do you join or link from the account record to the users on the account team in a query? The Access Team functionality is great, but it is not easily queried from the user interface – such as through Advanced Find. So, let’s talk about how you would do this from SQL.

crm-101

Understanding CRM data structure is vital in this case. From a data structure, the following tables would be utilized:

  • PrincipalObjectAccess
  • TeamBase
  • TeamMembership
  • TeamTemplate
  • SystemUser

Use this SQL Query to retrieve all Access Team Members for Account(s):

SELECT    a.Name AS AccountName, t.Name AS TeamName, u.FirstName, u.LastName, tt.TeamTemplateName
FROM        Account AS a INNER JOIN
PrincipalObjectAccess AS poa ON a.AccountId = poa.ObjectId INNER JOIN
TeamBase AS t ON poa.PrincipalId = t.TeamId INNER JOIN
TeamMembership AS tm ON t.TeamId = tm.TeamId INNER JOIN
SystemUser AS u ON tm.SystemUserId = u.SystemUserId INNER JOIN
TeamTemplate AS tt ON t.TeamTemplateId = tt.TeamTemplateId
WHERE    (a.Name = ‘Test Account’)

Note: To retrieve all accounts, remove the Where Clause

AccountName

TeamName

FirstName

LastName

TeamTemplateName

Test Account

10fd1a1b-95e6-e311-a6da-005056a51e53+f433e998-39dc-e311-b4cc-005056a51e53

Paul

West

Account Access Team

Test Account

10fd1a1b-95e6-e311-a6da-005056a51e53+f433e998-39dc-e311-b4cc-005056a51e53

John

Mayer

Account Access Team

 

Hope this comes in handy! If you need additional tips and tricks, please feel free to check our multitude of Dynamics CRM blogs.

Happy CRM’ing!

Avatar for JoeCRM

JoeCRM

Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more businesses and organizations around the world.