Dynamics 365 and AD security groups

Dynamics 365 has some security features which integrate Dynamics 365 with AD Groups. AD Groups can be used to grant and restrict access to a Dynamics 365 environment and with AAD security teams you can automatically add users to teams.

I hate doing manual tasks (Dynamics 365 — The cost of manual deployments activities) and if it can be automated it should, so AAD security teams offer a great way to automate adding users and giving them security roles.

This blog post will discuss both security groups and AAD security teams.

Microsoft Documentation — Control user access to environments: security groups and licenses

AD security group

The first AD security group I recommend to setup is a group which grants people access to an environment. Microsoft cover it here

You create an AD group and then you go to Power Platform Admin and navigate to environments where you will see all environments you have access to.

If you add AD group in the security group setting, this allows users to access or see the Dynamics 365 environment if they are added to that group. This is an easy way to control which users can access different environments.

It acts as a layer before security roles and stops accidently giving access to users. For projects its common for developers and consultants to have access to most of the Dynamics 365 environments accept Preproduction and Production. 

Microsoft documentation — Control user access to environments: security groups and licenses

Group teams

Dynamics 365 lets you create a few different types of team

  • Owner teams
  • Access Teams
  • Group teams

Owner teams are the classic team in Dynamics and works like a user, they can own records and have security roles.

Access teams allow record access and give people access to a particular record, e.g. sales people working on an opportunity

Groups teams are different because they link an AD Group to a team in Dynamics 365 AAD team. AD groups can be of type office or security. The office AD group can be created with users with less privileges and as a way to create groups of users.

An AD security group will need an IT person to create them.

Both Azure AD groups will have an Azure AD Object Id for the group and we link that to a team in Dynamics by putting that guid in azureactivedirectoryobjectidazureactivedirectoryobjectid field

You can create these in Dynamics by creating a new team and selecting the AAD Security type or AAD Group type.

 A few things to note

  • You have to put Azure AD object ID on creation of the team
  • It will validate the it’s a valid guid
  • You cannot change this guid after creation

This page describes how group teams and owner teams work in Dynamics

The picture below shows how AD Groups work

The security gives access to the environment

The team AD group automatically adds a user. In the example above, you add a user into the Sales Manager AD team and it will automatically add that user to the Sales Manager team in Dynamics (as long as you have created the team and put in the correct Azure AD Object ID)

If you assign the team a security role then you don’t have to do any manual setup of the users for security roles or field level security. 

The diagram below show it without AD groups linked with teams.

Each AD group can only work with an Dynamics team in one Dynamics instance.

You can keep the guids the same but you need to change the AzureID field

Why aren’t people in the Dynamics groups?

I setup up my teams, put in the correct Azure AD Object Id for a group. The user was added to the correct group and then………nothing.

Where was the user? why wasn’t the user appearing in my team?

In the Microsoft documentation

The list of team members listed in each group team only displays the user members who have accessed the environment. This list doesn’t show all the group members of the Azure AD group. The team member’s privileges are derived dynamically at run-time when the team member accesses the application. The security role of the team is not assigned directly to the team member. Since team member’s privileges are derived dynamically at run-time, the team member’s Azure AD group memberships are cached upon the team member’s log-in. This means that any Azure AD group membership maintenance done on the team member in Azure AD will not be reflected until the next time the team member logs in or when the system refreshes the cache (after 8 hours of continuous log-in).

The members show in the team in Dynamics 365 only when users have accessed Dynamics 365 (logged in). If the user in the AD Group hasn’t logged in then they won’t show in the team members in Dynamics. 

Other potential gotcha’s from Microsoft documentation

You can only create one group team for each Azure AD group per environment, and the Azure AD ObjectId of the group team cannot be edited once the group team is created.
Team members are maintained in each group team at run-time and the operation is done at the database level; therefore, the update to group team event is not available for plugin.


If you have a release pipeline, you can keep the GUIDs of the AAD security group team the same but you have change the Azure AD Object ID to the AD group. 

It’s worth doing because you will have the teams with the same guids in each environment.


AD groups can be an effective way to add security and simplify adding users to a Dynamics 365 environment. 

I think all Dynamics 365 environment should have a security group, it stops accidently enabling users to environments.

AAD security groups are good if you have distinct security roles with no cross over. If you can do it, it will save time adding users and is worth looking into to.

picture from here

Dynamics 365 - MB-200 Microsoft Power Platform + Dynamics 365 Core study notes


I recently passed the the MB-200 certification and here are my study notes. The notes are best used once you have gone through all the skills required and our in the final week of studying for the exam and our trying to lodge the data in your mind.

They don’t cover all the parts of the exam but focus on the areas I needed a refresh.

I have notes which are for past Config and customisation exams, which are worth reading but some of the data might be out of date

MB-200 study notes

These study notes won’t help you learn Dynamics 365 but they will help test and verify your knowledge.

Starter portals

  • Community
  • Customer self service
  • Partner self service
  • Employee self service
  • Custom

Customer Self-Service portal

  • Starter portals are like templates for common scenarios

The customer Self-Service is for company to offer self-service support

  • Knowledge base articles
  • Submit Cases
  • forums
  • similar to community portal but blogs are not included
  • It’s focus is on assisted service more than self service
  • Forums operates in the same method as the Community Portal
  • Searching on a customer portal bring ups knowledge articles, forums posts and other information

Starter Portals — Employee Self-Service Portal

  • This portal is for employee staff to find human resource, company and other information
  • Each user (employee) accessing the portal will need a Dynamics 365 team licence

Employees can use

  • Knowledge base articles
  • Forum
  • Support cases

Start Portal — Partner portal

  • To collaborate with customers and partners
  • Partner Portal have the same functionality as in Community or Customer Self-Service Portals
  • Partner portals can add additional features if you have Field Service or Project Service installed

Partner portal features

  • Partner Management
  • Customer management
  • Opportunity management
  • Knowledge base articles
  • Forums
  • Support cases

Starter Portals — Custom Portal

If you don’t fit any of the starter portals use a custom portal

The custom portal contains core features

  • content management
  • security
  • extensibility

General portal info

  • You may be eligible for one free portal, check eligibility guidelines
  • You create knowledge base article in Dynamics 365 customer service
  • Community portal is the only starter portal to feature Blogs

Rollup fields

Rollup fields can do these aggregate functions

  • SUM
  • MIN
  • MAX
  • AVG
  • You need to select the data type first before rollup type will appear
  • You can rollup related activities e.g. appointments, emails, etc
  • Related entity can be added
  • Filter can be applied to related entity if hierarchy is no
  • Filter can be applied to source entity if hierarchy is yes
  • Filters are optional
  • If source hierarchy is no, you must specify a related entity
  • Rollups fields are asynchronous, they don’t run in real time
  • Rollup fields can be run by workflows or business rules

Rollup field states from here

0 => NotCalculated — The field value is yet to be calculated.

1 => Calculated-The field value has been calculated per the last update time in _date field.

2 => OverflowError-The field value calculation resulted in overflow error.

3 => OtherError-The field value calculation failed due to an internal error. The following run of the calculation job will likely fix it.

4 => RetryLimitExceeded-The field value calculation failed because the maximum number of retry attempts to calculate the value was exceeded due to high number of concurrency and locking conflicts.

5 => HierarchicalRecursionLimitReached-The field value calculation failed because the maximum hierarchy depth limit for the calculation was reached.

6 => LoopDetected-The field value calculation failed because a recursive loop was detected in the hierarchy of the record.

  • Rollup fields run asynchronously in the background
  • Dynamics creates a system job which you can see in Recurring System Jobs
  • By default it will rollup 12 hours after creation
  • Calculate Rollup Field is a recurring job that does incremental calculations of all rollup fields in the existing records for a specified entity.
  • There is only one Calculate Rollup Field job per entity
  • The default maximum recurrence setting is one hour
  • If you hover over a calculated/rollup field you can refresh the field
  • The maximum number of records during the rollup refresh is limited to 50,000 records
  • You can define a maximum of 100 rollup fields for the organization and up to 10 rollup fields per entity.
  • A workflow cannot be triggered by the rollup field updates.
  • A workflow wait condition cannot use a rollup field.
  • A rollup on a rollup field is not supported.
  • Data from Rollup field are not stored by auditing, so there is no real way to store historical values of a rollup field.

Calculated fields

NP — Calculated fields

Define calculated fields to automate manual calculations

  • Calculated value is set when a form is opened or a value viewed in a list
  • To create a calculated field you must have write privilege on Field Level security profile if the secure field is used
  • You can use lookup field by <LookupFieldName>.<RelatedFieldName> e.g ParentAccountId.AccountNumber

Calculated field considereations

  • A maximum number of chained calculated fields is 5
  • No Loops — A calculated field cannot refer to itself or have cyclic chains
  • The calculated field values are not displayed in the Customer Engagement Outlook Offline mode in the tile views or on entity main forms
  • Calculated fields can span only two entities
  • Calculated fields cannot trigger workflows or plugins
  • You cannot convert a simple field to a calculated field
  • A rollup field cannot reference a calculated field

Hierarchy security

  • You can use Manager or position but not both.
  • Manager is security is restricted by business unit
  • Position hierarchy allows data access across business units
  • The managers need read level access to entities (e.g. security role restrictions still apply)
  • manager has access to the records owned by the user or by the team that a user is a member of
  • Manager has access to records that are shared with the user or the team that a user is a member of
  • Manager has depth setting
  • Position hierarchy uses job roles to structure (you must set this up)
  • Position hierarchy security, a user at a higher position has access to the records owned by a lower position user or by the team that a user is a member of
  • Position hierarchy records that are directly shared to the user or the team that a user is a member of.

Admin roles

365 Admin Roles

Main roles

  • Dynamics 365 service administrator
  • Power Platform administrator
  • Global Admin
  • PowerBI service admin

Dynamics 365 service administrator

  • Cannot create new users or allocate licences
  • Can set AD groups on an instance
  • Backup, create dynamics instances
  • Enable or test mailboxes
  • Can backup or restore Dynamics environments
  • Can create a new environment
  • Can create support tickets

Global Admin

  • Can activate mailboxes
  • Can create new users and allocate licences
  • Can do practically everything, usually the customer IT
  • Can perform any management activity in Office 365 admin centre
  • By default has System Admin privileges in Dynamics 365
  • Global Admins can use dynamics without being assigned a licence but they cannot see any records
  • Global Admins can’t change their own roles (no one can)
  • Global Admins can reset passwords for all users
  • Manage domains

User Admin

  • Add users and groups
  • Assign licences
  • Update password expiration policies

Use service admin roles to manage your tenant

Security groups

Control user access to environments: security groups and licenses

  • When you add users to a security group it adds them to the Dynamics/CDS environment
  • When you remove users from the security group, it disables them in the Dynamics/CDS environment
  • If a security group is associated with an environment, only users with Dynamics/CDS licence that are members of the group will be visible in the users.
  • When a security group is associated to an existing environment, all existing user in the environment will be disabled.
  • If you do not assign a security group, all users with a licence will appear
  • You still have to assign security roles or the user won’t be able to open the environment

Security groups — office 365 groups

  • Collaborate with your colleagues using Office 365 Groups
  • Office 365 Group is a shared workspace for email, conversations, files and events. Where a team of people can collaborate.
  • Dynamics 365 users and non-Dynamics 365 users can collaborate in the group
  • You need to have an Office 365 subscription which includes Exchange Online and SharePoint Online
  • Before you can start using Office 365 Groups, the groups solution needs to be deployed and turned on for your Customer Engagement entities. For more information, see Deploy Office 365 Groups.
  • To use document storage with Office 365 Groups, you will need SharePoint Online and access to the group OneNote notebook.
  • Users must have an exchange Online mailbox

Manage access to apps by using security roles

  • Office 365 groups allow you to collaborate with people across your company, even if they don’t have access to Dynamics 365 CE.
  • The groups solution works with any entities, even custom ones
  • Marketing team group, invite office 365 users to join the group. You can then share documents, conversations, meeting notes and one note information for specific entities e.g. accounts, leads, opportunities, contacts.


  • You need to have an Office 365 subscription which includes Exchange Online and SharePoint Online.
  • Before you can start using Office 365 Groups, the groups solution needs to be deployed and turned on for your Customer Engagement entities. For more information, see Deploy Office 365 Groups.

Power Automate/Flows


  • 200+ connectors for Power Automate
  • To trigger a Flow, the entity must have change tracking enabled
  • A flow can take up to 2 hours to trigger!!!
  • The flow will trigger on the data that exists at the time the flow runs
  • You can export a flow, it will export as a package (.zip) file
  • You can import a flow, it will be a zip format
  • You can share your Flow with other users
  • If you let other users use your connection, they can’t access the credentials in your connection.
  • Users cannot user a shared connection in other Flows

Create team flows

  • You can share a flow by granting people or groups permission to run the flow
  • When you share a button, you can use your connections or require them to use their credentials

Button Flows/Instant Flow

  • The flow version of on-demand
  • You create a button to easily trigger the flow
  • You need an account with permission to use the connectors the flow will use
  • All Run history is only visible to the Flow creator

Share button flows in Power Automate


  • When you create a power platform environment, you can choose whether to make the DB or not
  • PowerApps canvas apps and Microsoft Flow support preview environments

Dynamics first party apps

  • Dynamics 365 Sales
  • Dynamics 365 Marketing
  • Dynamics 365 Customer Service
  • Dynamics 365 Field Service
  • Dynamics 365 Project Service Automation

You need to manually install updates to first party apps in Manage your solutions

Test updates in sandbox or test environments to make sure they don’t break anything

Install other apps at Microsoft Appsource

You must be an administrator to install apps


NP — Charts

  • System charts are visible to everyone
  • System charts can be included in solutions
  • User chart is a security role setting under core records
  • Tag and doughnut are new charts (they seem greyed out for personal charts). The color coding isn’t available for the tag charts and doughnut charts. These charts appear on the dashboard in white, gray, and black shades.
  • Some charts, such as bar charts or pie charts, render showing the data stored in the system.
  • The doughnut charts and tag charts load as static images and don’t show the preview of the actual data — interactive charts
  • Charts can be exported and imported using XML
  • You can export a user chart and import as a system chart (or vice versa)
  • It is possible to create system charts from make.powerapps.com.

Chart types

  • Column
  • Bar
  • Area
  • Line
  • Pie
  • Funnel
  • Tag
  • Doughnut

Business process flows

NP — business process flows

Business process flows overview

  • You have to enable an entity for business flows
  • Once enabled you cannot disable it (but you don’t have to create any business process flows)
  • When enabled it creates additional fields Process Session, Process Stage
  • Business process flows have their own Security tab
  • System customizer and System Admin automatically have all BPF security roles
  • You can add business rules
  • You can add workflows — trigger stage entry or stage exit
  • BFP can call on-demand workflows
  • You can edit Business Process flows in https://make.powerapps.com/ and the classic editor (as of June 2020)
  • One entity can have multiple business process flows (security roles can control who sees what)
  • If you multiple BPF’s then user the Order Process Flow to select the order
  • You enable the business process flow for security roles — Enable Security roles button in the BPF
  • Action step — prompt the user to fill in a value
  • Limit of 10 active business rules per entity
  • A business process flow can have no more than 30 stages
  • Multi-entity business process flow has a maximum of 5 entities
  • You can include custom controls (PCF)


Export to an Excel dynamic worksheet

NP — Excel Templates

  • You can export up to 100,000 records at a time
  • Exporting data as static, creates a local copy on your computer with no connection to Dynamics
  • When you export a dynamic worksheet or pivotable, a link is maintained between Excel worksheet and Dynamics. The Excel spreadsheet will be refreshed and you will need to authenticate with your credentials
  • PivotTable data checkboxes
  • Save source data with file
  • Enable show details
  • Refresh data when opening the file

Neil Parkhurst — Excel Templates

  • You can open a Excel template in Excel Online or download it
  • If you use a pivot table you want to refresh the data when Excel is opened. In Excel, right-click the pivot chart, and then click PivotChart Options > Refresh data when opening the file.
  • When you change the template you will need to upload it as document template

Microsoft Teams

  • You can connect a record to a team channe
  • Enable teams in Settings à system settings à general
  • Download the Dynamics 365 teams app


NP — Languages

  • Base language is defined when you create the environment and cannot be changed or deleted.
  • You can enable additional languages
  • Settings →languages
  • You can check the additional languages you want
  • These additional languages will then be available in your personal settings
  • You will need to export translations which will create a file called CrmTranslations.xml
  • The Translation file will be a zip, inside the zip there are xml files you can open in Excel


Neil P — Searching

Different searches

  • Default search
  • Category search
  • Relevance search
  • Advanced find

Searching functionality

  • Category and Relevance search need to be enabled
  • You select what entities will be used for category search
  • You can filter by the entities selected
  • Category results are in separate entities as different columns
  • Relevance search is configured in System Settings →General
  • You select up to 10 entities for Category search
  • Relevance search shows all results in one lists
  • Relevance search is done in Azure with your data copied
  • Relevance search uses Azure Search
  • Relevance search does not need to use wildcards

Relevance search shows results for text in documents stored in

  • Notes
  • Email attachments
  • Appointments


NP — App designer

Document Management

Neil Parkhurst — Document management


  • You choose which entities document management is enabled
  • SharePoint uses the Document Location entity to store SharePoint URLS
  • SharePoint site holds the SharePoint site
  • Both entities can be added to security roles under Core Records
  • Files stored in SharePoint are visible to everyone by default


Set up OneNote integration

  • Before you can enable OneNote integration, you need to turn on server-based SharePoint integration.
  • OneNote is dependent on SharePoint being configured because the OneNote notebook is stored in SharePoint
  • Document management for SharePoint/OneNote and OneDrive is all done in Settings →Document Management
  • When you have Enabled Server-Based SharePoint integration, OneNote integration button will be visible in Settings →Document Management
  • You can turn off OneNote integration later if you want but you have to manually delete the notebook in SharePoint
  • OneNote uses notebooks
  • You can open OneNote from the Timeline control
  • OneNote integration stores OneNote notebooks in SharePoint
  • You enable OneNote integration for selected entities

OneDrive for business

Microsoft — OneDrive for business

  • Before you can use OneDrive you have to setup SharePoint
  • OneDrive for business is included in Office 365 Subscriptions
  • Files stored in OneDrive are private (not shared) with other users by default
  • Before configuring OneDrive for business make sure you have the correct licence
  • Default OneDrive for business storage is 1TB
  • OneDrive for business can work with Dynamics 365 on premise and Online
  • OneDrive for business is enabled in Document management in settings
  • OneDrive for business is a miscellaneous privilege in Core Records
  • You have to Setup SharePoint integration with Dynamics 365 CE and have at least one team site
  • Users will need permission on root SharePoint site


Everything You Need to Know About Task Flows

  • Tasks flows a user level approach
  • Task flows are for mobile devices
  • Task flows are common tasks (follow up to a meeting, change password, update a contact)
  • Task Flows are depreciated

Email and Outlook

Configure synchronization for appointments, contacts, and tasks

  • You can sync emails, Appointments, contacts and tasks
  • You can sync appointments and attachments using Dynamics 365 for outlook and config
  • Sync between Dynamics 365 and Out for Dynamics 365 apps is disabled by default
  • Sync settings can be found System settings →Synchronization tab
  • You can sync contacts from outlook to Dynamics (with Dynamics 365 outlook app)
  • You can choose to sync one email address or choose business, home and other
  • Tasks can be synced
  • You can sync using server side sync, Microsoft Dynamics 365 for Outlook or forward mailbox — this is setup on the users Mailbox
  • Queues can only sync using Server side sync
  • Mailboxes are automatically created when you create a user or queue

Main forms in Dynamics

  • Main Form — the main entity forms typically used in browser. But can also be seen in outlook and CRM mobile clients.
  • Quick View Form — These offer a simplified view of parent record information that can be added to child forms. (And also used in hierarchy views.)
  • Quick Create Form — Short versions of the form used for record creation, typically used when creating a child record in context of a parent.
  • Card Form — These are compact forms used in views for Unified Interface apps

You can have multiple forms of the same type

Business Rules

Create business rules and recommendations to apply logic in a form

  • You need to deactivate a business rule before you can modify it
  • Business rules are triggered when a record is opened or when a field is changed which the rule conditions are set to check e.g Form OnLoad and Field OnChange
  • Business rules are not triggered when you save a record
  • Set scope to Entity if you want a business rule to run on the server, not just on the client side
  • Business rules action will run only if condition is true
  • If a field isn’t on the form the business rule will not run
  • A business rule can unlock fields and edit actions on a read only form
  • Business rules run before the onload JavaScript runs

Business rule scope

  • Entity — All forms and server based
  • All Forms — All forms but client based
  • Specific form — business rule runs only on that form

business rules can

  • Set field values
  • Clear field values
  • Set field requirement levels
  • Show or hide fields
  • Enable or disable fields
  • Validate data and show error messages
  • Create business recommendations based on business intelligence.

How to Use Recommendations in Dynamics 365 Business Rules

  • Business rule recommendation puts a value in field that you recommend
  • It pops up with an Auto-Fill asking the user to Apply your recommendation
  • If a recommendation is dismissed no value is copied


  • Overwrite another environment with a copy of this one. Its existing resources including backups will be deleted
  • You can copy one environment and then choose a target environment
  • You can copy everything (customisations and data)
  • You can copy customisations (no data)
  • You cannot copy to a production instance
  • You can convert an instance to a sandbox, then copy and then change back to production
  • When you create a power platform environment, you can choose whether to make the DB or not
  • PowerApps canvas apps and Microsoft Flow support preview environments

Forms Pro

Get started with Forms Pro

  • Forms Pro has replaced voice of the customer (voice of the customer is depreciated)
  • Forms Pro is built on Microsoft forms

Forms Pro — Send Survey

Surveys can be sent in these 5 ways.

  • Email: Send the survey link by using the built-in email capability.
  • Power Automate: Configure a business trigger by using Power Automate to send the email.
  • Embed: Embed the survey in a webpage.
  • Link: Copy a link to the survey that you created, and paste it into a shared area.
  • QR code: Send a QR code for your survey.


  • You cannot delete the default currency

Understand decimal precision in Dynamics 365

  • A currency record has a currency precision field — this sets currency precision
  • Settings System settings à currency precision field used throughout the system — this sets non currency precision numbers
  • A field of type money has a currency precision value

Database indexes

  • Need to be created by a Microsoft service engineer, create a support request


  • You cannot export a managed solution
  • If you delete an unmanaged solution, the customisations are not deleted
  • You have to manually delete unmanaged customisations
  • Deleting a managed solution, deletes the data too.

Other Study notes

Study guide for MB-230 Dynamics 365 for Customer Service

picture from here

Hosk’s top articles of the week - 17th June


I want problems to punch me in the face, not sneak up behind me and kick me on the butt #HoskWisdom

Articles of the week

on being lost — strategy and context

Long and interesting article on business strategy

Top 10 articles of the week

  1. Inversion — James Clear
  2. Richard Feynman and mental models
  3. How I’m learning Machine Learning (without being a math genius)
  4. habits of high performing teams
  5. The best way to exercise self-control is not to exercise it at all
  6. America Is Unified Only in Its Outrage
  7. How to Negotiate…with Your Kids
  8. Peter Thiel
  9. CDC response
  10. The Sickness in Our Food Supply

Hosk articles

Dynamics 365 + Software engineering


Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose. You are already naked. There is no reason not to follow your heart. — Steve Jobs

The best time to plant a tree was 20 years ago. The second best time is now. — Chinese Proverb

“I like to say, “Experience is what you got when you didn’t get what you wanted.” — Howard Marks, The Most Important Thing

Selected HoskWisdom

  • You come up with ideas collectively, you wouldn’t think of individually #HoskWisdom
  • Sometimes importing a managed solution without getting an unhelpful dependency error can feel like skiing down a black run blindfolded, on one leg and going backwards #HoskWisdom
  • If you’ve never eaten while writing code, you don’t know what development tastes like. #HoskWisdom
  • Just because you might fail, is no reason not to try #HoskWisdom
  • A developer who has never suffered, has never written a line of code #HoskWisdom
  • A bad choice look good when there is lack of alternatives #HoskWisdom
  • A stupid developer is the most dangerous type of developer #HoskWisdom
  • Against the stupidity of some gifted users, even the best developers fight in vain #HoskWisdom
  • Time is a tool, use it to think about the problem when you have time, so you can react quickly when you don’t #HoskWisdom
  • If you can’t win the game you are playing, change the game #HoskWisdom
  • Creating a solution in Dynamics 365 is simple in principle but complex in practice #HoskWisdom
  • A report is never finished, only paused whilst users think of changes #HoskWisdom

Follow the hastag #HoskWisdom or follow me on twitter BenHosk

Quote from current book(s)

Simple Habits for Complex Times: Powerful Practices for Leaders

taking multiple perspectives also helps widen your scope to improve your problem solving

The Hosk — last 5 good books

Don’t deny yourself a book, it could change your life #HoskWisdom

Last top 10 article

Check out last weeks top articles — Last top 10 article

picture from here

Dynamics 365 tool you should use - SQL 4 CDS


SQL 4 CDS is a XRMToolBox tool from Mark Carrington that will change the way you query data in Dynamics and can speed up query multiple environments. You can create scripts which you can quickly run in any environment.


When you are an experienced Dynamics 365 user the GUI can slow you down, when maintaining the system you want to view certain data that highlights problems.

The first method to view data with the GUI is using Advanced find, creating views — Why advanced find is an awesome tool in Dynamics 365 . You don’t need to see all the data, just the data you are interested in and filter out the rest.

Another recommended tool is the FetchXML Builder by Jonas Rapp, which works with the XRMToolbox . This allows you to query Dynamics data and query data not visible in advanced find. You can save and run queries from advanced find by saving the fetchxml and running it.

Dynamics 365 and SQL

Then whilst attending the Birmingham CRMUG virtual meeting we were talking about the endpoint added to Dynamics 365 to allow SQL queries with Dynamics 365. I wrote about it a few days before — Woohoo — You can query Dynamics 365 (CDS) with SQL….again. When Dynamics 365 was on-premise it was useful to query the database directly to see what was really happening and find problems. There are lots of on-premise developers who miss querying the Dynamics SQL database.

If you want to learn about the new CDS T-SQL endpoint , Mark Carrington creator of the SQL 4 CDS tool has done a step by step guide to setting it up.

CDS T-SQL endpoint stole some of thunder from the SQL 4 CDS but after the investigation Mark has enabled the T-SQL endpoint in SQL 4 CDS in this version SQL 4 CDS 2.1.0 — the T-SQL edition. Personally I’m quite happy to use CDS 4 SQL rather than use the SQL tools on my laptop.


SQL 4 CDS is an XRMToolbox plugin and you can run it from the XRMToolbox and don’t have to open Dynamics 365. The source code is in Github, so if you want to read or contribute you can — here

SQL 4 CDS allows you to write these SQL statements agaisn’t a Dynamics 365 environment


Yes, you read that correctly, it allows you to delete, insert and update records as well as querying.

I have been using the SQL 4 CDS XRMToolbox tool more often and find it a great tool that allows me to get information from Dynamics faster than using the GUI.

SQL 4 CDS will need you to invest some time to create the queries, which was initially slower for me because I hadn’t written any SQL queries for many years.

What do I like

  • You can connect to multiple environments in one tab
  • autocomplete for fields
  • Its easy and quick to use
  • You can save queries and load them in seconds
  • It can save the queries into FetchXML, so you can use them in plugin queries, integrations or the FetchXml builder
  • Create, update and delete records
  • You can open records

What have I used it for

Querying Dynamics 365

It took me a while to refresh myself with writing SQL, not something I have done for 15 years.

The common reason I use the CDS 4 SQL is to query data. It’s fast and easy to use. I can use the query agaisnt multiple environments, which is useful for comparing environments and finding problems.

Creating queries is fast and the autocomplete makes it easy

Exporting data

The tool is great for exporting data and creating files for exporting and importing in different environments. You can select the fields, create a filter and then copy the data and headings to an excel file.

Here is a query I used to query for teams

select teamid, name, description, teamtype, businessunitid, azureactivedirectoryobjectid

from team

where teamtype = 2

This query selects where teams which are AAD teams, this is where you create an AD group and link to a team in Dynamics, when you add a user to the team it automatically adds those users to the teams in Dynamics.

I use it to create date for the my release, so I can keep the guids synchronised between environments and avoid the cost of manual deployments activities

Draft workflows and draft business rules

After I do a release to an environment, I like to check if the there are any draft workflows or business rules because sometimes data is missing in the target environment and sometimes I need to manually activate the workflows/Business rules on the first import into an environment.

SELECT w.name,
FROM workflow AS w
solution AS s
ON w.solutionid = s.solutionid AND (s.friendlyname like ‘%Hosk%’)
WHERE w.type = ‘1’
AND (w.category = ‘0’ — category 0 = workflow
OR w.category = ‘3’) — category 3 = action
AND w.statecode = ‘0’ — statecode 0 = draft

This query has a couple of cool things.

  • You can join tables to get more information.
  • The join is here is with the solution table and filter by solutions whats in the text e.g. Hosk
  • it looks for Workflows and actions in a draft state
  • you can add comments on a line by adding —

Solution versions

You can quickly query the solution versions where the name is like something, usually you name your solutions. The reason I have to use friendly name is because the solutions have different guids in different environments and I want these queries to be run on different environments.

select friendlyname, version from solution

where friendlyname like ‘%solutionname%’

If you want examples of more complex queries, there are some on this page below

D365UG UK: Data Integrity & Quality — Tips & Tools


One limitation I have found is when you create a record you can’t specify the guid of that record. This is fine for contacts and records to use but for any records you want to reference from workflows/plugins/flows then you want to keep the guids the same.


SQL 4 CDS is fast and easy to use and you can run one query on multiple environments in seconds. I am starting to build up more queries which I run regularly after releases (hopefully I can make this into PowerShell scripts at some point).

Add the SQL 4 CDS and give it a go and you will soon find yourself using it more and more, particularly if you query multiple environments.

Other interesting articles

Remove unnecessary requirements and deliver the project on time

If functionality isn’t a clear yes then make it a clear no #HoskWisdom

Wish lists

What’s the goal

Be strong


The two skills of modern developers are creating solutions without code and deploying them automatically

The two skills of modern developers. Creating solutions without code and deploying them automatically.

The driving force of successful projects is being able to deliver solutions into production, to get projects over the finish line. To deliver a project on time, stick to out of the box (if you can), is a good in theory but in reality companies processes can’t always align with out of the box functionality. The benefit of an IT system is it helps people do their jobs by assisting, automating, validating and helping people collaborate.

Creating a solution is one part but moving it into environments quickly, without creating any overhead keep everyone on the project motivated and generates feedback from the business.

It’s only when you get a solution into production that the business starts to get a return on project investment, before then it’s all cost with no benefit.

When I mention No code solutions I’m referring to Power Automate/Flow, Logic Apps and other GUI customisations that need code. Using services such a Azure cognitive services will also decrease work needed because there reduced maintenance.

Slow projects

A unsuccessful project has these recognisable signs

  • Growing requirements and no control of scope
  • Project deadlines are constantly missed
  • Moving solutions between environments is time consuming, difficult and error prone
  • The quality is low and complexity of the solution is high
  • Delivery is slow

The effort of the people on the project is often greater on badly run projects than it is on well run projects because the people try to out work with the problems.

Requirements and scope are significant, the complexity of the solution (e.g. code and overall solution) takes longer to create, harder to maintain and more difficult to find lots of developers to create them. Code will contain more bugs in comparison to out of the box or no code solutions.

A project which uses out of the box and no code solutions will deliver faster, cheaper and the team needed doesn’t need to have programming knowledge. The maintenance of the no code solution should be cheaper.

No code solutions can have a different problems because the creators can lack the disipline of software engineers and create inconsitent and poorly named customisations. This can create technical debt and hard to maintain customisations but this will still be less complex than code.

ALM and DevOps

ALM tools are common place and Azure Devops makes the process repeatable and maintainable. The improvement in ALM tools increases the use and adoption.

I remember the 15 years ago when building software took hours and releasing it could take a whole day. The tasks were manual, error prone and boring. ALM and release pipelines can now be triggered after a check-in to source control, take minutes and can link automated tests and other validation checks. Environments can now be automatically created in minutes or hours.

Know what what tools, services and no code do and how they can be used to create solutions, understand how to deploy that solution automatically without creating more tasks and consuming more time.

Dynamics 365 and ALM/DevOps Information to get you started

Dynamics 365 — The cost of manual deployments activities

If you don’t have ALM setup then the feedback of the solution will be slow. Users, SME’s need to see and try the solution, to identify where it works and where it needs to change. Delivering a solution raises morale, increases motivation and engagement because people are making progress.

Changing environment

It’s not impossible to create a solution using code but it is more difficult and the chances of failure are higher. It increases the complexity, time to deliver and chances of failure.

Tools, services, out of the the box functionality and no code solutions will upgrade without problems. The significant change is the out of the box/no code solutions will be delivered cheaper, faster and with a higher degree of success. Those who don’t adapt to the new environment will lose out to those who do.

The people who will thrive are those who adapt to the changing environment and embrace No code solutions and DevOps.

Dynamics 365 - Different environment, different country label

Whilst setting up a new environment I ran into an odd and annoying currency problem.

The Problem

  1. Created new environment
  2. Imported base solution (entities, views, fields, security roles etc) — worked
  3. Imported processes (workflows, Flows, Plugins, etc)- mostly worked

After the import I saw that 30 workflows were in a draft state (always check for workflows in a draft state, this can be a silent error that causes bugs when parts of the functionality are missing)

I opened one of the workflows and saw a red X and this message

The entity referenced by this process includes a currency value that does not exist in your organisation. Select a different currency and try again

The step was a send email and the currency field on the email!!??! was showing it couldn’t find Pound Sterling

Why am I getting this problem?

I hadn’t seen this problem in the other environments. Dynamics 365 error messages point you in the right direction, This was odd because I was using GBP in all environments with no other currencies and I hadn’t seen this error in any other environment.

Look at the currency values in I went to look at the currency.

Settings →Business →Currency

New environment currency

British Sterling — GBP

Dev — environment currency

Pound Sterling — GBP

So when the currency lookup was trying to match it couldn’t find it.

Out of interest I looked for other GBP and found there is 3 and you can’t add more than one in an environment.


You can change the currency name (phew) to be the same as Dev. Then I could import the process solution and activate the workflows and not have errors.

I don’t understand how the label of the GBP currency was different in this environment than the others. Why doesn’t Microsoft import it’s data with the same guids in each environment. I had a simliar problem where I couldn’t automate the setting of the System Administrator role because it gets created with a different guid in each environment (a bit of me did agree with not making it easy to assign the system admin role).

The question I had was why were was the workflow setting the currency with an email that didn’t send any financial figures?

If you still have problems you can either not set it the currency field because it’s not mandatory or you can look it up Dynamically from another currency field on an entity.

Dynamics 365 solution error - Components are missing, import the managed solution with these Components (Active)!

Importing managed solution is more of an art than a science #HoskWisdom

Sometimes importing a managed solution without getting an unhelpful dependency error can feel like skiing down a black run blindfolded, on one leg and with the sun in your eyes. Even though I have experienced different errors whilst, there is always a new one to baffle me.

You can get error exporting solutions — Dynamics 365 — Error when exporting a solution

There is definitely a full time role on the Dynamics 365 team to come up with new and interesting errors and create cryptic error messages which don’t help identify what the problem is.

Solution import error

I was importing a managed solution on a new environment. I have installed the Microsoft solutions and other managed 3rd party solution that my custom managed solution depends on.

Then this error pops up

“The import of the solution HoskSolutionName failed. The following components are missing in your system and are not included in the solution. Import the managed solutions that contains these components (Active) and then try importing this solution again.”

Microsoft then gives me a url to a different error — Import of solution fails due to missing components

Frustratingly there was no log file or any other details. The screen then shows half of the entities saying those components are missing. Yes I know they are missing I am trying to import them.

The error message is saying I can’t import those entities because the solution depends on those entities.

So Microsoft are saying, somewhere in the 40 entities of your solution, there is a dependency issue. It’s like your child losing your car keys on the beach and saying it’s somewhere in the sand.

Remove dependencies

As this was a fresh instance and I know Microsoft back up the Dynamics 365 instance every hour for the last week, I edit the solution file.

If you remove the dependencies from a solution file, you can import it and the import will error and tell you what the problem is (e.g. if you are missing some dependencies)

I then get this error

Failed to create entity with logical name new_entityname and object type code -1. Exception:
Microsoft.Crm.CrmException: Entity Display Collection Name for id: d31c2b64-b6f8–4f64–9962-b019dcb596fe, objectcolumn:
LocalizedCollectionName and labelTypeCode: Entity not specified at Microsoft.Crm.Metadata.LocalizedLabelHelper.ValidateLabelCollectionContents(LabelCollection labelCollection, Guid objectId, String objectColumnName, LabelTypeCode labelTypeCode, String parameterName, ISqlExecutionContext context, Boolean throwIfCollectionIsNullOrEmpty) at Microsoft.Crm.Metadata.EntityService.ValidateEntityNameAndLabels(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ISqlExecutionContext sqlContext) at Microsoft.Crm.Metadata.EntityService.ValidateForCreate(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context) at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass75_0.<CreateInternal>b__0() at Microsoft.Crm.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName) at Microsoft.Crm.Metadata.EntityService.CreateInternal(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context) at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass27_1.<CreateInternalHelper>b__3() at Microsoft.Crm.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName) at Microsoft.Crm.Metadata.EntityService.CreateInternalHelper(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context)

Another error I have never seen and have no idea what it means (the entity isn’t really called new_entityname :-))

I went to the entity to see if it would give me any clues. What I found was the include metadata checkbox was not checked (Dynamics 365 — What does Include entity metadata do?). So maybe this error is complaining there is no metadata.

I add tick the metadata flag, ALM the process and create a new managed solution. I try importing this with the dependencies in place and it fails.

I take off the dependencies and then 40 minutes later it has successfully imported, leaving me totally confused why Dynamics 365 was complaining.

I am worried that there is something not right. I bump up the version and import again. This time the standard import is working until 20 minutes and then it throws a dependency error for some missing relationships.

I add the relationships and try again. This time it works. If the first error had given me some information I could have resolved the problems in an hour, instead of 5 hours.

I delete the managed solution and the import my new managed solution and it works after fixing my dependencies issues.

To take my frustration to comic levels, Dynamics decided to log me out of all 20 of my tabs!!!


Don’t let solution errors beat you. You need to push past the obscure errors Dynamics 365 throws up and look in the errors that are causing the problem (Dynamics 365 error messages point you in the right direction). There is usually a reason for solution errors, it takes time to find them.

The most effective way to avoid dependency errors is to have a ALM/Devops process (Dynamics 365 and ALM/DevOps Information to get you started) in place and release often, this way you find the problems in a small changeset, which is quicker and easier. It stops you wasting your life doing a manual task which you can automate

picture from here