Category Archives for "Business Applications"

Entering lists of Option Set values into MS CRM

A co-worker recently shared this project with me as we’ve had some experience with clients who had large number of options that needed to be added to Picklists.

List showing state abbreviations

Fifty US States, maybe some Canadian Provinces

Unfortunately, Dynamics CRM doesn’t offer a way to batch import these lists, rather it’s up to the administrator to add them one-by-one.  Adding in a dozen of these isn’t so bad…especially since you should only have to do it once…but when you start looking at two dozen, fifty, or more of these it becomes a real hassle (and a hotbed for fat-fingered garbage data).  That’s where the CRM Option Set Utility comes into play.

The goals of the project are simple enough:

  1. Provide a resource for the download and maintenance of the CRM 2011 Option Set Import tool which is available for download.
  2. Community resource library of contributed Options Sets that can be used by any Dynamics CRM Organization free of charge.
Application Screenshot

You mean all I need is an Excel spreadsheet with the values in it? Count me in!

After spending some time with the application, specifically testing it using on-premise CRM Organizations, there are a few things that I’ve found worth highlighting:

  • It not only imports the option set, it creates the field from scratch (so you can go straight from an Excel list without having to first create a placeholder in CRM). 
    • Once connected to a CRM Organization, it the UI presents a list of entities you want the Option Set created on.
    • You choose the entities, and whether the option set should be Local to each entity or Global.
    • Note, I had some issues during my testing when I tried to specify a global option set and selected entities at the same time.
      • My workaround for global option sets was to import the option set via the tool, then manually create the attribute on each entity linking it to the global option set.
      • I plan to seek assistance in the forum, but have not been able to get a forum account as of yet.
  • There is good flexibility with respect to setting both the “Label” and “Value” for each item
    • 3 options exist.
      • 1) Start with 1, 2, 3…..
      • 2) Use the defined numbering prefix from a selected Solution in the CRM Organization.
      • 3) Define in Column B of the source spreadsheet.
  • The tool is meant to Create these option sets, not to Update existing option sets.

The bit I like is the community aspect of hosting a repository of common option sets.  There’s about a dozen so far, but as this builds out it will hopefully be a first-stop when looking for a cleansed list like this.


CRM Report – Removing HTML tags from an Email Activity Detail

I’ve been working with Dynamics CRM reports quite a bit over the last few months.  Most of the work has been using the BI Dev Studio (BIDS) in Visual Studio.  It definitely falls into the artful science category for me, with flavors of Excel, SQL Server, and even Publisher when developing a report.

In the case of displaying Email activities, specifically the body of an HTML email, the art takes a decidedly sad turn.

Simple HTML-formatted Email

Yes, this seems like a nice basic email. Nothing fancy going on here…

Okay, let’s see what that looks like in a simple report that shows a summary of email messages….

HTML tags show up in report

Gah, what happened!? It seemed so harmless…Text and a little image.

This is useful for…nearly nobody.

Matrix Code

Mr. Anderson, maybe you understand this…

There are many ways to try to tackle this…ranging from CRM plug-ins, to complex report design, to, well, something a mentor of mine tipped me off to.


This gets used in the report design as an expression, replacing the normal field reference in the report design.  It uses a basic utility that strips out the HTML leaving only the plain text behind.  Okay, if that sounded clunky, a picture is worth a thousand words:

HTML vs Plain Text

Ahh, now that’s much better.

There are certainly more elegant ways of doing this, but if the key is to provide easy, readable email contents in a report this sure foots the bill!  From a user perspective, to see the full HTML version, I like to add in a clickable link to the email that pops the CRM Email Activity record.

I picked up a little tidbit while working through some applications of this:  If you try passing a null field using this utility  it returns an ugly little #Error instead of a blank string.  Also, SSRS will still throw the #Error even if you’ve included an IIF statement in the expression to make sure there’s a value before calling the utility.  Below is a little workaround that gets the job done.

Microsoft.Crm.Reporting.RdlHelper.HtmlUtility.RemoveHtml(field & “”)

If anyone knows an easier way to do that, please drop the details in the comments section.

Again, there’s surely a prettier way to handle this, rendering HTML in the report or including some basic formatting, but this is pretty lightweight and works well when printing a basic list of emails with the body text in a report.

As far as I can tell, this utility is an undocumented feature of CRM, so quite technically speaking it is not supported by Microsoft and is something that they could yank out in a future release without notice.  That said, this is the method used in the out-of-the-box CRM reports to strip out HTML tags.

Objective: Strip out HTML tags from an email message when displaying in a CRM report
CRM Version: Undocumented feature. I’ve tested with CRM 2011 through UR8
Valid as of: 9/10/2012
Permissions Required: Create/Write reports.

UPDATE 9/11/2012:

I received a question regarding a problem someone was having when importing a report that leveraged the expression above.  After a little troubleshooting, the answer was that the report lacked a reference to the following Microsoft assembly:

Microsoft.Crm.Reporting.RdlHelper, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Vanilla CRM reports and those reports created using the built-in CRM Report Wizard include this reference automatically.  However if you build your own report from scratch, you’ll need to add this manually.  With the report file open in Visual Studio BI Dev Studio, hit the menu item at Report->Report Properties…  Next, click Add and paste in the reference to the assembly and click OK.

Report Properties Window

Way to go, Bobby!

Thanks for the question!  Other comments and questions are welcome.

CRM 2011 UR 10 released

I’ve taken some time to sift through the MS KB article on the UR 10 release that MS rolled out a couple of weeks back.  That’s right, 10, not 9.  9 won’t be released on its own, in case you hadn’t heard (it was combined with 10).

With UR10, here are a few of the things I’m excited about:

  • Support for Office 2013 (Office 15 :))
  • Support for Windows 8 (makes sense since Windows 8 has hit RTM)
  • Fixes for some nagging bugs

That something, but it’s not a ton.  I’d be lying if I didn’t say I was disappointed about the continued slowness on the cross browser functionality.  That was supposed to come with UR9, which was “rolled into UR10”.  Great…except for the cross browser stuff that’s not in 9 or 10.  I know a lot of folks who are looking forward to using their browser of choice, and we’re knocking on the door of 2013 at this rate.

Pity party aside, the update is much needed after the long gap since UR8.  Full release details and downloads can be found at  Release history can be found here.


Importing a list of records into MS CRM

In the last post, we downloaded a Dynamics CRM Import Template.  Now, it’s time to get some data INTO the system…since that’ll be much more useful.

Contacts are a pretty common import, being something that most sales users will want to do early on.  Fortunately, it’s a super simple process to do this!

Objective: Download the import templates specific to your organization from the CRM application.
CRM Version: CRM 2011, any version
Valid as of: 8/27/2012
Permissions Required: Read/Write Import Job and at least User level Create permissions for the Contact entity.


  1. In the navigation pane, click File->Tools->Import Job
  2. Browse to the .xml/.csv/.zip file that has been prepared
  3. Click Next
    Select A File
  4. In this example, since we’ve used the templates that came from CRM, there’s no need to modify the data map (We’ll cover data maps in another post), so click Next
    Select Data Map
  5. Accept the default duplicate setting (do not allow) and choose yourself  as the record owner.
  6. Note, the Owner field can be set in the import file, so this is really a means to set the fallback owner if there’s not an exact match
  7. You’re ready to Submit the Import Job
  8. The import will be queued up, and the CRM Async Service will do the rest

Download Data Templates for Importing Data into MS CRM

Whether I’m just starting a CRM project, or there’s a list of [something] that someone needs imported into Dynamics CRM.  One of the easiest ways to get a list of records imported is using a file import…and the biggest hassle of that is making sure the field mappings are set up.

The vanilla product has an easy way of handling this, providing a way to download a template, specific to the record type you want, which includes all of your customized fields as well.  It’s pretty slick not having to set up this field mapping on your own, and if you have some Excel data that you can copy/paste into the appropriate columns, you’re in business.

Objective: Download the import templates specific to your organization from the CRM application.
CRM Version: CRM 2011, all versions
Valid as of: 8/20/2012
Permissions Required: All default security roles.


    1. In the navigation pane, click Settings.
    2. In the System section, click Data Management
    3. In the main window, click Templates for Data Import
    4. A window will pop up.  In the dropdown, select the type of record you will be importing.
    5. Once selected, click the Download button.
    6. Save the file to the desired location.
    7. Repeat steps 4 , 5, and 6 for each record type template.
    8. Click Close when complete.

Identify the version and update rollup of MS CRM 2011

When I’m working with a client who is currently running Microsoft Dynamics CRM, one of the early steps is to validate their current version of the product.  Below are the steps to look this information up and a table to cross reference the version number.  Hope this is helpful if you’ve been looking for it.

Objective: Identify the version and rollup level of a Dynamics CRM environment.
CRM Version: CRM 2011
Valid as of: 10/14/2012
Permissions Required: Any default security role.


  1. In the main CRM window, click File->Help->About Dynamics CRM
  2. In the window that pops up, identify the version number in the blue bar.
  3. Compare the version number with the table below.

Dynamics CRM Version Reference Table

Version Release Date Build Number KB Article
Release Candidate 5.0.9688.53 2461082
Beta (On Premise) 5.0.9585.106
Beta (Online) 5.0.9585.107
RTM 2011/02/16 5.0.9688.583
Update Rollup 1 2011/04/07 5.0.9688.1045 2466084
Update Rollup 2 2011/06/02 5.0.9688.1155 2466086
Update Rollup 3 2011/07/28 5.0.9688.1244 2547347
Update Rollup 4 2011/09/19 5.0.9688.1450 2556167
Update Rollup 5 2011/10/20 5.0.9688.1533 2567454
Update Rollup 6 2012/01/20 5.0.9690.1992 2600640
Update Rollup 7 2012/03/23 5.0.9690.2165 2600643
Update Rollup 8 2012/05/03 5.0.9690.2243 2600644
Update Rollup 9 No Release details
Update Rollup 10 2012/08/16 5.0.9690.2730 2710577
Update Rollup 10v2 2012/10/04 5.0.9690.2730 2710577
Update Rollup 11 2012/10/10 5.0.9690.2835 2739504
Update Rollup 12 2013/01/29 5.0.9690.3233 2795627
Update Rollup 13 2013/03/28 5.0.9690.3448 2791312
Update Rollup 14 2013/08/05 5.0.9690.3557 2389019
Update Rollup 15 2013/10/28 5.0.9690.3731 2843571
1 3 4 5