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