In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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.
Understanding CRM data structure is vital in this case. From a data structure, the following tables would be utilized:
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!
Hi All,
If you are looking for Technical Blogs or want to read about vTiger, Odoo, OpenERP, Microsoft Apps, Google Apps, CRM, ERPs, Mobile Apps and New things in IT industry then read and subscribe on http://www.knowledgement.ie
OR go to http://www.targetintegration.com/ for CRM and ERP purchase consultation.
Hi Joe,
We have added members to a team using the CRM 2011 interface and SQL. Next, we shared a custom view with the team. The members that were added to the team using the interface could see the view. However, the member added to the team using SQL could not. I have verified that this user was, in fact, added to the team. Am I missing a step somewhere?
Nevermind. we figured it out. There was another table in play that we had not considered. Thanks anyway.
I need to get the team members under one account, how can i get them using Advanced find in CRM.?
I just wanted to say Thanks for the post. Even though this was written a while ago it is still useful to those still on-prem. I have code that is adding and removing team members and now we have a way to ensure the code is running correct.