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