What I learnt working on IT projects

Don’t build solutions on assumptions, ambiguity and ignorance #HoskWisdom

IT projects are a challenge, a good deal of fail and all of them are difficult. IT project can need lots of people, time, ideas and hard work.

Projects face obstacles, problems and changes of priorities, these changes can defeat you or you look at them as opportunities to make the solution better, the quote below sums it up.

“The obstacle becomes the path” Ryan Holiday

Challenging projects help you see the best in people, you can’t deliver a project by yourself and at certain times different people will shine, save the day and do something that helps you. Your team will be there to help you when you need it and you will be there to help them.

The better you can work as a team, the better the project will go.

Projects have taught me about myself, about technology and how to work with a group of people to make a team.

IT projects are about people not technology

There is a lot of noise and focus on technology on IT projects but ask yourself these questions

  • People builds the solution
  • People use the solution
  • People make decisions
  • People sign off requirements on the project

People are at the heart of the project and they are reason it for its success or failure.

Slow down to speed up

More people, working hard feels like it would speed up delivery but it can slow things down, when work is half finished, changes conflict.

Why adding more people to a project doesn’t make it go faster

Focus on quality rather than speed of output, fewer mistakes and consistent delivery will create releases, the successful process builds confidence and creates momentum.

Surprises on a project are never welcome because problems and delays follow. Focusing on quality, reducing mistakes and keeping momentum raises confidence in the team, it will reduce the chance of unexpected surprises..

Understand the business goals

Before you start with the technology and solution, understand the business, it’s goals, it’s language and how the business wins.

The purpose of the solution is to create a tool to support users do their job and the business to achieve its goals.

All requirements, features and user stories should focus on business needs and purpose. What is the user doing? why are they doing it? and how does this help the business.

Ask questions

Ask questions, to understand the business, clarify assumptions and work out the goals of the business.

If you have a question, ask it, it’s likely if you are thinking this then others in the room will be thinking it. Question help understand and clarify assumptions. Avoid building the solution on assumptions because these are weak foundations that could crumble later.

Don’t remake the old system

Everyone is influenced by the existing system and they will try and recreate the old system with new technology. People are use to the current system, they understand how it works and resisting change is the comfortable 

Focus on the business goals and what the purpose of the business. The old system will be aligned to the functionality of the old system. When creating a new system it’s a chance to align the process with the out of the box functionality of the software it’s being built with e.g. Dynamics 365 and Power Platform.

Don’t be constrained by the existing system, focus on the goals of the business and how best to deliver those and make it easier for people to do their jobs.

Warning — it will be difficult because people don’t like change and will keep referring back to how the current system works.

The best way to learn is through questions, reframe questions to look at work from the perspective of the teams and persona’s doing the work, understand the goals of the teams and how they fit together as a whole.

Clarify assumptions

Assumptions lead to problems, bugs and changes. You need to clarify the assumptions and get the facts otherwise you will be building a solution on ambiguity and ignorance.

Clarify the assumptions as soon as you can because these are logic bugs and can embed themselves in key parts of a process, which can be difficult to change later.

Focus on what matters

In a chronically leaking boat, energy devoted to changing vessels is more productive than energy devoted to patching leaks. — Warren Buffett

People devote energy to unimportant and unproductive areas. Work out the key areas and focus on getting those right, focus the energy and stop wasting time on the other areas.

Most areas on a project are unimportant and not worth arguing and discussing.

“You cannot overestimate the unimportance of practically everything.” Greg McKeown

People will try to create meetings for everything and demand your time. If you are not needed in a meeting, don’t attend and spend that time doing productive activities.

Keep it simple

Show the users how Dynamics 365 works, start with a simple solution and then get users to try it before thinking about any complex solutions.

When you give users a blank canvas and ask them what they would like, they will come up with ideas that sound good but aren’t what they need and aren’t easy to build or maintain.

Align the requirements to the out of the box functionality.

Don’t panic, don’t blame

Panicking causes you to make bad decisions, blaming doesn’t achieve anything and lowers morale.

Be prepared for things to go wrong, mistakes to be made. Projects involve getting out of your comfort zone and trying things we haven’t done before. Take this small knock backs on the chin, pick yourself up, dust yourself down and try again.

You can’t guarantee to get everything right, particularly when you do something for the first time. Trust the team, trust yourself, don’t lose faith and keep going.

It will be difficult but you will overcome all the problems in your path. You will do it as a team.

Projects can be difficult but Don’t quit

IT projects are difficult, there will be tough periods but keep going and do not quit.

There will be times when you think the project won’t succeed, the deadlines are too tight, requirements aren’t defined, too many bugs, priorities changing, people leaving, technology failing, super bugs making everyone working from home.

Keep going, don’t quit, you will get there.

Relationships are key

Relationship with the customer and users is important because you want build trust. The customer pays the bills and makes the decisions, you have to influence as the technical export.

Build a good reputation and relationship with the client. This means listening, giving information and building trust. Avoid things which can reduce confidence in your ability, things like

  • Bugs
  • Not replying quickly
  • Hiding information
  • Talking in technical jargon they don’t understand
  • Raising problems without solutions

The customers in an IT Project is not the enemy, they are the partner. You will work together to create a solution

Conclusion

IT projects often start slowly which is fine, as long as you keep improving, fixing problems and build momentum.

Projects are not the same and there is no template to deliver a successful project. You have to adapt to situation, people and requirements. Build relationships, gain trust and find a way to progress.

As relationships grow, the project team can work quicker and the work becomes more rewarding.

Projects are about people, they will be the reason for success.

Management and leadership books I read in the last 5 years

Image for post

  • Leadership
  • Coaching
  • Presence
  • Communication
  • Scrum/Agile
  • DevOps
  • Persuasion

2016

  1. The Coaching Habit: Say Less, Ask More & Change the Way Your Lead Forever: Say Less, Ask More & Change the Way You Lead Forever
  2. Leading by Alex Ferguson, Michael Moritz
  3. Turn The Ship Around!: A True Story of Building Leaders by Breaking the Rules
  4. Insights Into Liberating Leadership: How to become a great leader and create a lasting legacy
  5. What Got You Here Won’t Get You There: How successful people become even more successful
  6. How to Lead:The definitive guide to effective leadership: The definitive guide to effective leadership (4th Edition)
  7. Peopleware: Productive Projects and Teams
  8. Extreme Ownership: How U.S. Navy SEALs Lead and Win
  9. Project Management Lite: Just Enough to Get the Job Done…Nothing More
  10. Scrum: The Art of Doing Twice the Work in Half the Time
  11. AGILE Project Management for Busy Managers
  12. Scrum and Xp from the Trenches 2nd Edition
  13. Talk Like Ted: The 9 Public-Speaking Secrets of the World’s Top Minds
  14. The 21 Irrefutable Laws of Leadership: Follow Them and People Will Follow You
  15. The Strategist: Be the Leader Your Business Needs
  16. Paisley: Smile On Me And Guide My Hand
  17. TED Talks: The official TED guide to public speaking
  18. Building Successful Communities of Practice: Discover How Connecting People Makes Better Organisations
  19. Playing to Win: How Strategy Really Works
  20. Inspiring Leadership
  21. Shankly: My Story by Bill Shankly
  22. Leadership:Plain and Simple: Plain and Simple (2nd Edition)
  23. Scrum: a Breathtakingly Brief and Agile Introduction
  24. The Strategy Book
  25. Originals: How Non-conformists Change the World
  26. Eleven Rings

2017

  1. Radical Candor: How to Get What You Want by Saying What You Mean
  2. The Everything Store: Jeff Bezos and the Age of Amazon
  3. The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win
  4. Make Your Bed: 10 Life Lessons from a Navy SEAL
  5. Legacy
  6. Parcells: A Football Life
  7. Win Forever: Live, Work, and Play Like a Champion
  8. Winning!: The path to Rugby World Cup glory
  9. A Practical Guide to Leadership: Be Inspired by Great Leaders
  10. The Coaching Mindset: 8 Ways to Think Like a Coach
  11. Steal the Show
  12. Jock Stein: The Definitive
  13. The Anatomy of Peace: How to Resolve the Heart of Conflict
  14. Gravitas: Communicate with Confidence, Influence and Authority
  15. The Team Building Strategies of Steve Kerr: How the NBA Head Coach of the Golden State Warriors Creates a Winning Culture
  16. How to Think Like Sir Alex Ferguson: The Business of Winning and Managing Success
  17. Jurgen Klopp
  18. Joe Fagan: Reluctant Champion — The Authorised Biography
  19. Quiet Genius: Bob Paisley, British football’s greatest manager
  20. Winners: And How They Succeed
  21. The Agile Samurai: How Agile Masters Deliver Great Software (Pragmatic Programmers)
  22. Scrum Mastery: From Good To Great Servant-Leadership
  23. The New Manager’s Handbook: 24 Lessons for Mastering Your New Role
  24. The Manager: Inside the Minds of Football’s Leaders
  25. How Good Do You Want to Be?: A Champion’s Tips on How to Lead and Succeed at Work and in Life
  26. Difficult Conversations: How to Discuss What Matters Most
  27. BOLD: 212 Charisma and Small Talk Tips to Engage, Charm and Leave a Lasting Impression
  28. Communication Skills Training: A Practical Guide to Improving Your Social Intelligence, Presentation, Persuasion and Public Speaking
  29. The Management Ideas of Nick Saban: A Leadership Case Study of the Alabama Crimson Tide Football Head Coach
  30. Bill Belichick: Ten Greatest Coaches of the NFL
  31. Sacred Hoops (Revised): Spiritual Lessons of a Hardwood Warrior
  32. Mastermind: How Dave Brailsford Reinvented the Wheel (90 Minutes Shorts Book 3)
  33. Strategy Concepts of Bill Belichick: A Leadership Case Study of the New England Patriots Head Coach
  34. Belichick and Brady: Two Men, the Patriots, and How They Revolutionized Football
  35. Patriot Reign: Bill Belichick, the Coaches, and the Players Who Built a Champion
  36. How to Manage People
  37. War Room: The Legacy of Bill Belichick and the Art of Building the Perfect Team
  38. Leadership and the One Minute Manager: Increasing Effectiveness through Situational Leadership
  39. The Education of a Coach
  40. Essential Scrum: A Practical Guide to the Most Popular Agile Process (Addison-Wesley Signature): A Practical Guide To The Most Popular Agile Process
  41. Louis van Gaal: Dutch Courage
  42. Smarter Faster Better: The Secrets of Being Productive in Life and Business

2018

  1. Way of the Wolf: Become a Master Closer with Straight Line Selling
  2. Humble Inquiry: The Gentle Art of Asking Instead of Telling
  3. Agile Retrospectives: Making Good Teams Great (Pragmatic Programmers)
  4. Exactly What to Say: The Magic Words for Influence and Impact
  5. How to Have Confidence and Power in Dealing With People
  6. Belichick: The Making of the Greatest Football Coach of All Time
  7. Stalling for Time: My Life as an FBI Hostage Negotiator
  8. When Pride Still Mattered: A Life of Vince Lombardi
  9. Never Split the Difference: Negotiating as if Your Life Depended on It
  10. The Charisma Myth: How to Engage, Influence and Motivate People
  11. Managing Humans: Biting and Humorous Tales of a Software Engineering Manager
  12. Pep Confidential: Inside Pep Guardiola’s First Season at Bayern Munich
  13. Finding a Way to Win: The Principles of Leadership, Teamwork, and Motivation
  14. Behind Closed Doors: Secrets of Great Management (Pragmatic Programmers)
  15. The Greatest Salesman in the World
  16. Big Sam: My Autobiography
  17. Single-Minded: My Life in Business
  18. The Coaches
  19. The Final Season

2019

  1. How to Have Impossible Conversations: A Very Practical Guide
  2. The Last Couch: A Life of Paul “Bear” Bryant
  3. The Score Takes Care of Itself: My Philosophy of Leadership
  4. Do Improvise: Less Push. More Pause. Better Results. A New Approach to Work (and Life)
  5. The Education of a Coach
  6. Will It Make The Boat Go Faster?- Olympic-winning strategies for everyday success
  7. 12 Essential Skills for Software Architects
  8. A More Beautiful Question: The Power of Inquiry to Spark Breakthrough Idea
  9. Chuck Noll: His Life’s Work
  10. How to Own the Room: Including new HOW TO OWN THE ROOM
  11. The Captain Class: The Hidden Force Behind the World’s Greatest Teams
  12. Graham Taylor In His Own Words
  13. Give and Take: Why Helping Others Drives Our Success
  14. It’s Your Ship: Management Techniques from the Best Damn Ship in the Navy
  15. Essentialism: The Disciplined Pursuit of Less
  16. The Power of A Positive No
  17. Pre-Suasion: A Revolutionary Way to Influence and Persuade
  18. Pig Wrestling: The Brilliantly Simple Way to Solve Any Problem… and Create the Change You Need
  19. Utter Confidence: How what you say and do influences your effectiveness in business
  20. Powerful: Building a Culture of Freedom and Responsibility
  21. Barking Up the Wrong Tree: The Surprising Science Behind Why Everything You Know About Success Is (Mostly) Wrong
  22. Captivate: The Science of Succeeding with People
  23. The Cubs Way: The Zen of Building the Best Team in Baseball and Breaking the Curse
  24. Persuasion Point: Body Language and Speech for Influence
  25. Tribal Leadership: Leveraging Natural Groups to Build a Thriving Organization
  26. The Culture Code: The Secrets of Highly Successful Groups
  27. Dream Teams: Working Together Without Falling Apart
  28. Brave New World: Inside Pochettino’s Spurs
  29. Persuade: How to persuade anyone about anything
  30. Assertiveness: How to Stand Up for Yourself and Still Win the Respect of Other
  31. How to Sell Yourself, Revised Edition: Using Leadership, Likability, and Luck to Succeed
  32. Sales Genius 1: 19 top sales professionals share their sales secrets
  33. Grinding It Out: The Making Of McDonald’s
  34. Fearless: How an Underdog Becomes a Champion
  35. To Sell Is Human: The Surprising Truth about Moving Others
  36. Confidence: How to Overcome Your Limiting Beliefs and Achieve Your Goals
  37. Saban: The Making of a Coach

2020

  1. My Life and Rugby: The Autobiography
  2. Body Language in the Workplace
  3. Great on the Job: What to Say, How to Say It. The Secrets of Getting Ahead
  4. The Inner Game of Tennis: The Ultimate Guide to the Mental Side of Peak Performance
  5. What the Heck Is EOS?: A Complete Guide for Employees in Companies Running on EOS
  6. World’s Best: Coaching with the kookaburras and the hockeyroos
  7. The Inner Game of Work: Focus, Learning, Pleasure, and Mobility in the Workplace
  8. You’re Not Listening: What You’re Missing and Why It Matters
  9. Mastering Design Thinking: The Systematic Approach to Improve Considerably Your Business Success
  10. The Mythical Man-Month: Essays on Software Engineering
  11. What It Takes: Lessons in the Pursuit of Excellence
  12. Creating Great Choices: A Leader’s Guide to Integrative Thinking
  13. Accelerate: The Science of Lean Software and DevOps: Building and Scaling High Performing Technology
  14. The Devops Handbook: How to Create World-Class Agility, Reliability, and Security in Technology Organizations
  15. Leading the Transformation: Applying Agile and DevOps Principles at Scale
  16. Don Shula: A Biography of the Winningest Coach in NFL History

 

What the winningest NFL coach (Don Shula) can teach us about IT projects

 

I read Don Shula: A Biography of the Winningest Coach in NFL History, Don Shula coached in the NFL from 1958 to 1995 and won more games than any other NFL coach and the super bowl twice.

Shula was successful over a long period, he evolved and adapted to different environments, people and situations.

Adaptable

Environments, customers, people and technology changes. The actions that brought you success in the past might not bring success in the future.

Don Shula success in his earlier career came with a great quarterback at the Colts. He then moved to the Miami Dolphins and had 3 great running backs. Later the NFL changed the rules to encourage more attacking play and make it easier for quarterbacks. Shula adapted and drafted Dan Marino, a hall of fame quarterback. Shula changed his strategy to get the best of his team and play to it’s strengths.

I think what coaching is all about, is taking players and analyzing there ability, put them in a position where they can excel within the framework of the team winning. And I hope that I’ve done that in my 33 years as a head coach. Don Shula

Dynamics 365 has changed, moving from On-Premise to Online, C# or JavaScript code to no code functionality with the Power Platform.

Flows can deliver functionality which previous could only be done with a plugin, the difference is creating and maintaining plugins is more costly. This has changed the solutions created. No code/Low code changes the skills needed in delivery team and empowers consultants to deliver more.

I have seen examples of companies not adapting, Dynamics professionals are still creating solutions using workflows and plugins instead of creating Power Automate/Flows. You would create large Dynamics solutions but now we have to consider several smaller focused PowerApps can be used.

Each project is different, a successful approach on one project could fail with a different customer and team on another. The ability for critical thinking and adapting to situations is key.

Work hard

The sign outside Shula’s office reads — “I’m just a guy who rolls up his sleeves and goes to work”

I don’t know any other way to lead but by example. Don Shula

Shula made sure his team worked hard and prepared well. You won’t win if you are outworked by the opposition and you won’t succeed without being as well prepared as you can.

“Leave as little to chance as possible. Preparation is the key to success” Paul Brown

The better you prepare, the smaller the chance of unforeseen problems causing problems. In IT projects you need to think, design and problem solve before you start building. This starts with understanding the business goals and the purpose of functionality, understand the requirements from a business and technical perspective. Finding problems in design or build makes it easier it is to resolve them and reduces the impact.

Game of errors

“Football is a game of errors. The team that makes the fewest errors in a game usually wins” Don Shula

IT Projects are a game of errors, the focus should be on creating quality, creating best practices to reduce self inflected problems. Testing and automated deployments give fast feedback, helping to find and fix problems in your dev and test environments. Fixing problems closer to creation speeds up the process.

The fewer errors, mistakes and wrong moves made, the smoother and quicker the project goes. Speed up a project with quality not speed. The common method of speeding up is by adding more people but this can slow the project down — Why adding more people to a project doesn’t make it go faster

Complexity slows down projects, you can combat this by reducing complexity and sticking to out of the box. To deliver a project on time, stick to out of the box

Why IT projects estimates are wrong

Team game

NFL games and IT projects are a team game, you need good people and leadership to be successful. There is pressure and expectation, you can’t let these affect performance or decisions, don’t let the highs be to high or the lows be to low.

Success is not forever and failure isn’t fatal. Don Shula

Don Shula spent time with his family and tried to attend his sons matches and have dinner with his wife. It’s important to recharge your batteries, enjoy life outside of your profession and work. To give your maximum at work you need to rest and enjoy life outside of work.

Summary

Don Shula worked hard, adapted to the strengths the people of his team and was more prepared than the opposition. 

The NFL and IT Projects are a game of errors, the few errors you make the smoother and fewer late deliveries. The more you can avoid problems from your execution of work the more trust you gain with the customer. You can control the quality of your work and your execution, do this and reduce the mistakes.

The Difference Between Amateurs and Professionals — Professionals win points by doing good shots, amateurs lose points by making mistakes. Set the standards, minimise mistakes and create quality, in the long run it’s the fastest way to deliver a project.

ps. That is a picture of my cat Rex as a general, I am a fan of animals dressed as general 🙂

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,

tr.teamid,

‘TRUE’ AS associate,

t.name,

r.name

FROM teamroles AS tr

INNER JOIN

role AS r

ON tr.roleid = r.roleid

INNER JOIN

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

INNER JOIN

team AS t

ON t.teamid = tr.teamid

where t.teamtype = ‘2’

Group by t.name

result

SELECT t.name,

r.name

FROM teamroles AS tr

INNER JOIN

role AS r

ON tr.roleid = r.roleid

INNER JOIN

team AS t

ON t.teamid = tr.teamid

WHERE r.name <> ‘System Administrator’

and teamtype = ‘2’

ORDER BY t.name, r.name;

Result

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

 

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.

Data

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.

Conclusion

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
  • COUNT
  • 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.

Prerequisites

  • 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

https://docs.microsoft.com/en-us/power-automate/connection-dynamics365

  • 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

Environment

  • 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

Charts

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)

EXCEL

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

Languages

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

Searching

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

Apps

NP — App designer

Document Management

Neil Parkhurst — Document management

SharePoint

  • 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

OneNote

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

TaskFlows

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

Environments

  • 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.

Currency

  • 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

Solutions

  • 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

Quotes

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.

Data

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

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

– SELECT
– INSERT
– UPDATE
– DELETE

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,
w.workflowid,
w.activeworkflowid,
w.statecode,
w.category,
w.primaryentity,
w.type,
w.ownerid,
s.friendlyname
FROM workflow AS w
INNER JOIN
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

Limitations

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.

Conclusion

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

Conclusion