SQL 4 CDS - querying teamroles


In my previous post on the SQL 4 CDS XRMToolbox tool, Dynamics 365 tool you should use — SQL 4 CDS, I used it to check for deactivated workflows, get versions and export data.

I have been using it to verify my releases have worked correctly.

After I run a release I check for deactivated workflows and business rules.

I am using AD Group and AAD Security group teams (Dynamics 365 and AD groups). A quick recap is you link AD groups to AAD security groups teams in Dynamics 365 and when you add a user into an AD group it automatically adds it the team in Dynamics. The key to setting this is up is to make sure you set the the correct security roles.

The PowerObjects data tool allows to import data into different environments, this means I can keep the same team but change the Azure ID to the team and link to a different AD group for each environment.

So after a release and moving data I wanted to check the right number of roles were assigned to teams. When you link a a role to a team it updates the teamroles table, this is hidden from advanced find but you can see it using SQL 4 CDS.

The PowerObjects data tool can also assign the roles to teams which is great, so I want to make sure they are setup correctly in all environments. If you non production environments are the same as prod, you can find problems quicker and before customisations get into productions, so keep you environments in sync and fix problems when they are small.

This is how I made the file and checked the values in the teamroles table. Interesting things to note, the format button works well and tidied up my query. This query is my first double join!!! The query joins the teamrole to teams and roles. I only need the roleid, teamid but it’s easier for a human to read actual roles and team names

SELECT tr.roleid,


‘TRUE’ AS associate,



FROM teamroles AS tr


role AS r

ON tr.roleid = r.roleid


team AS t

ON t.teamid = tr.teamid

WHERE r.name <> ‘System Administrator’

ORDER BY t.name, r.name;

I exclude the System Administrator because I can’t set that between environments. The System Administrator role has a different guid in each environment!

first query gets all the team roles, in my case it should equal 129 or something is wrong

SELECT count(teamroleid)

FROM teamroles;

I wanted to check the different AAD teams had the right number of roles, my first attempt at grouping and counts, Yes that works with SQL 4 CDS

this produce this result

This query did some grouping and counting

select t.name, count(tr.teamid) as RoleCount

from teamroles as tr


team AS t

ON t.teamid = tr.teamid

where t.teamtype = ‘2’

Group by t.name


SELECT t.name,


FROM teamroles AS tr


role AS r

ON tr.roleid = r.roleid


team AS t

ON t.teamid = tr.teamid

WHERE r.name <> ‘System Administrator’

and teamtype = ‘2’

ORDER BY t.name, r.name;


The reason I ignore ‘System Administrator’ is you can’t move this role between environments

Dynamics 365 - mobile offline profile error


I have a love/hate relationship with Mobile offline and today it threw an error in one environment that wasn’t occuring in the any of the other environments with the same Mobile offline profile setup.

Exporting Mobile Offline

When you add a mobile offline profile to a solution and export, it will automatically add any entities that are linked by Metadata. In my mobile offline profline, it has 18 entities in the Mobile Offline Profile Item Details.

When you export, it explodes the number of entities in my solution to 108.

adding entities such as

  • Web File Log
  • Blog Post
  • Case
  • Forum Alert
  • Idea
  • IoT Device
  • Phone call

None of those are needed in mobile offline, they exist in the system on Microsoft solutions, why must Microsoft add them to my solution.

There are 18 entities that I choose to be available offline. Dynamics 365 adds any other entities referenced in the metadata. Microsoft say this is by design but the problem is we can’t add all these entities to our solutions because it would increase dependencies and export/import times.

Due to Dynamics 365 automatically added these entities on export, it means you can move the Mobile offline profile between environments and have to manually create them.

Publish error

I manually recreated the Mobile offline profile in each environment and published without error, until today where in one environment, publishing caused this message to appear

The entity ‘account’ in profile ‘Mobile Offline Profile name’ is configured with the filter download related data only, however there are no relationships specified for this entity in profile item associations. If an entity is set to download related data only you must specify a profile item association to this entity

After checking the mobile offline profile was the same. I checked the version of Dynamics

Server version: 9.1.0000.18647Client version: 1.4.838–2005.4

The same in all environments.

Sherlock Hosk time

So why is it complaining about the account

Download related data only?

I read the Microsoft documentation for clues — Set up mobile offline synchronization to allow users to work in offline mode on their mobile device

The page has this text

“Download related data only — Make related data for this entity available offline. If you don’t set any relationships, no records for this entity will be available.”

The page or in Dynamics does it explain what relationships are or importantly how to set them.

In classic Dynamics 365 terms, setting a relationship is linking two entities together, this I need to set what other entities is the account related two by updating the Mobile offline

I assumed I needed to link the account with Mobile Offline profile. Below you can see that the account profile item has no item association.

I add some relationships but I still get the error. Useful the GUI shows what avaiable relationships are available

I then add the item into the Application (opportunity) it worked but when I added the application to account, it did not. I’m not sure of the difference.

Warning if you add the same relationship in both sides you will get a circular error

has a circular reference which will prevent your data download. Please review the circular reference chain: opportunity =&gt; account =&gt; opportunity and remove the profile item association that causes the circular