Benefits of copying Dynamics CRM Online metadata to SQL

Dynamics CRM is, to a large extent, a metadata-driven platform, as illustrated here. What this means is that a lot of its functionality is derived by business entities, their relationships, fields, picklists, etc.

Given the importance of metadata in CRM, you'd expect that you'd be able to write a query against it, such as "List all account fields that are not on a form." or "List all web resources and the forms, if any, each one is used on.". Unfortunately, these types of queries aren't possible without writing a fair bit of SDK or Web API code.

Being a long time user of SQL Server and T-SQL, my instinct was to find a way to regularly copy CRM Online's metadata to SQL tables. To follow is an overview of what I have working so far.

My initial set of requirements are these:

• Make it quick and easy to run robust queries (joins, filters, sorting) against CRM Online metadata.
• Allow for enhancing CRM's metadata, such as listing all picklist items next to each picklist (OptionSet) field and showing whether a field is on any forms and which ones.
• Allow for diffs on the metadata, to be able to determine, for example, what new entities and fields were created in the past week.
• Provide a fast on-demand way to export CRM Online metadata to an Excel file.
• Allow for the service to run for multiple CRM Online organizations.

The approach I've taken (so far) is this:

• An Azure WebJob is responsible for querying CRM Online metadata (entities, fields, relationships, roles, global optionsets, views, forms and form structure, etc.) and copying this data to a set of SQL Server tables. Entity Framework, along with some CRM SDK code, makes this relatively easy to do. (The benefit of using a WebJob is that it can be scheduled or triggered on-demand.)
• A SQL Azure database stores the latest metadata and maintains copies of the previously extracted metadata. Stored procedures write differences to a table for easy reporting.
• Another WebJob (I might move it to REST service) allows for extraction of the metadata into various forms, such as Excel and Word templates. This allows for fast documentation of a CRM Online's org metadata.

This initial design has met all of my requirements except for handling multiple CRM orgs. That will take a bit more work and will probably deplete my Azure credits each month so I'm leaving it as a "nice to have".

I'll hopefully be able to put this out as open source but until then I hope you can glean some ideas from this information. And... hopefully Microsoft will provide the same types of functionality soon so that I don't need to maintain this solution. Although I have to admit, it was fun to build.

Using Microsoft Azure with Dynamics CRM

I've always been uncomfortable with the word "can't", especially when it comes to software. My manager at Onyx Software around 2002, John Hawk, once told his team that it's "just a matter of moving data from here to there" and that has always stuck with me. Sure, it can be difficult to move lots of data quickly, transform it for different systems, analyze it for meaning, present it clearly, etc. but there's usually a way to do all of those things. "Can't" should not be allowed in the room.

Every experienced Dynamics CRM user/admin/dev should spend some time each week answering questions on the Dynamics CRM forums. I try to answer a few questions each week. I especially like the questions or responses that use that word -- can't. My immediate response it, "oh yeah, let's see about that".

One cure for the Dynamics CRM can't's is Microsoft Azure. Azure means "world of possibilities"... in some language, I'm sure of it. These cloud services open up a myriad of possibilities for any company or organization to further automate the business, provide better customer service, make better decisions and lower costs.

So far, as I write this, I've listed 43 ways that those responsible for Dynamics CRM in their company can improve CRM with Azure. I came up with a lot of them in response to that word in the CRM forums. "I can't schedule a job to run against CRM data." Yes, you can, with a scheduled WebJob or other scheduled service. "Without writing code, I can't create an Excel file, populate it from CRM data and store it on SharePoint." Yes, you can, with an Azure Logic App or Microsoft Flow.

There are already several ways to link CRM and Azure (Service Endpoints, WebHooks, REST, etc.) and this will continue to expand. There will come a day when CRM administrators will be seamlessly using Azure services without seeing that it's "Azure". Perhaps CRM's workflows can start an "extended workflow" (really an Azure Flow workflow) or a CRM form can interact with a Node.js app (running as an Azure Function). Whatever Microsoft comes up with, the can't's won't stand a chance.

Azure Logic App to send email if other cloud app stops running

I created an Azure Logic App today that runs once per day and does the following:

1. Call a stored procedure in an Azure SQL database. The stored procedure returns the number of hours since an application last ran to completion.
2. If the return code from the stored procedure is greater than 6, send an email (using Office 365) to me.

If I were to create an app for this (e.g., a WebJob), I'd have to add in the SQL connectivity plumbing (or use Entity Framework) and add code to send an email. That's still an easy application to write and deploy in Azure, but being able to put together a small app (workflow) like this in just a few minutes is pretty great.

As of this post, Logic Apps is still in Preview, so it has lots of issues that the team will work out. One problem I ran into was trying to use the SQL connector's OutputParameters return object in a Logic Apps Condition step. I wanted to know if the OutputParameters value was greater than 6. When running the app, I ran into the following error:

{"code":"InvalidTemplate","message":"Unable to process template language expressions for action 'Send_Email' at line '1' and column '11': 'The template language function 'greater' expects exactly two parameter of matching types. The function was invoked with values of type 'Object' and 'Integer' that do not match.'."}

I then went into the "Code View" and wrapped the condition expression with an int() function call, but then the Logic App threw this error:

{"code":"InvalidTemplate","message":"Unable to process template language expressions for action 'Send_Email' at line '1' and column '11': 'The template language function 'int' was invoked with an invalid parameter. The value cannot be converted to the target type.'."}

Finally, I tried using a ReturnCode value from the stored procedure (as shown in the screenshot) and that worked; I was able to compare the integer return value to a specified integer value.

Logic Apps still has a ways to go to be ready for prime time but in this case it worked out well for what I was trying to achieve. And it's one less Visual Studio project to maintain!

Link Buddy for Dynamics CRM

On one of my Dynamics CRM (on-premises) projects, the company wanted CRM to calculate pricing for insurance-related services. We implemented the functionality in a plug-in and added tons of logging (log4net) statements to help us analyze CRM data during testing and troubleshooting. The pricing path went through dozens of steps, so being able to see the logic path taken was essential.

The log files contain mostly CRM record GUIDs -- the thought was that we could use those GUIDs to manually navigate to CRM records when needed. That turned out to be a pain.

So I worked late one night a created a Windows app that translates one or more CRM entity record GUIDs into CRM "addressable" form hyperlinks. Now, we (including end-users) can copy the log contents to the app, click a button and get hyperlinks that lead to the actual CRM records.

I named the app "Link Buddy for Dynamics CRM". It's up on CodePlex (source code and executable) if you'd like to put it to use. Please let me know if you run into any issues with it, have suggestions, etc.

-Tim

Export all entity field values to Excel file

In a previous post, I mentioned that it's possible to use the Open Source "ClosedXML" .NET library in a Dynamics CRM plug-in, even with CRM Online. In addition to using ClosedXML for importing data into CRM and executing formulas, it's also useful as a way to export data from CRM. For example, I recently created a solution for exporting all field values for an entity to an Excel file. The image below illustrates this functionality.

When developing forms, business processes or troubleshooting issues, it's often useful to see the value for all fields for an entity, not just the fields on the form. This tool (packaged as a managed solution) will provide a simple way to add this capability.

The "Export All Field Values" ribbon button creates a record of type "Excel Export" (a custom entity). A plug-in (that incorporates ClosedXML) fires, queries for the entity metadata, queries for all field values, creates the Excel file and attaches it to the new entity record. The ribbon button's JavaScript pops open the Excel Export entity for the user, so all the user needs to do is click the Excel file attachment to open the file.

It's expected that only CRM administrators and customizers will need this export functionality but I included a security role in the solution as well to allow an admin to assign the role to other users as needed. For example, someone testing functionality in CRM might want an easy way to see all field values.

Import data into Dynamics CRM from Excel files

On my CRM wiki, I added a page that describes how you can use the Open Source ClosedXML project to run Excel formulas from CRM workflows.

Another way to use ClosedXML in a CRM plug-in is for importing data directly from an Excel file.
I implemented this recently for a client who has several employees who need to import data on a daily basis. They wanted to build out the data in Excel (multiple entities, picklists, custom matching logic, etc.) and quickly get the data into CRM, without the need to export the data first before importing.

One possible design for this is to register a plug-in on the Create message for an Annotation (note) record. The user submits the Excel file as an attachment. The plug-in code gets the message body (Excel contents) from the attachment and passes the data as a Stream to ClosedXML, which loads the Excel contents into an easy-to-query object model. From there, it's simply a matter of getting the cell values and performing your CRUD operations in CRM. For the plug-in, you'll need to use ILMERGE to combine your plug-in assembly with ClosedXML.DLL and Microsoft's Open XML library and register that combined/merged assembly to CRM.

Note that this solution works in CRM Online as well as on-prem. Some .NET assemblies can't run in the Sandbox because of platform constraints in CRM Online but ClosedXML works fine.

(Note: After I wrote the above info, I found an interesting CRM forum post relating to importing data from Excel into CRM. The structure of the data the person presented in the question is not in rows and columns, but that's not a problem because the solution I outlined above can be used to import this type of "unstructured" data without too much effort. Once the Excel content is in the ClosedXML object model, you can reference any cell, range of cells, calculation, etc., so obtaining the data no matter how it's formatted is certainly possible.)

CRM 2015 SDK - Get organization details from Organization service

A few weeks ago, I was working on a plug-in for a Microsoft Dynamic CRM 2013 project and wanted to add organization-specific logic to it. In CRM version 2013, Microsoft did not provide a way to get organization details using the Organization.svc service endpoint but in CRM 2015 Microsoft added this capability with RetrieveCurrentOrganizationRequest and related classes. This allows for more adaptable code in plug-ins, workflow activities and other applications. For example, you might want to call a different Web API endpoint from a plug-in depending on whether the plug-in is running in production vs. running in a development sandbox instance.