Need to import complex data into CRM 2013? It may be easier than you think!

August 28th, 2014

While CRM 2013′s Import Wizard can provide a quick-and-dirty way to get data into the system, there’s a limit to what you can do and how much you can control how the wizard responds to missing data. Sometimes you just happen to have a huge table of historical data that you need in CRM, which may or may not be referencing records that already exist in the system, but will definitely need to be referencing something once they’re imported. Or maybe that historical data will need to reference something like a Contact or Account, but you’d like the lookup logic to be more complex than a simple “do any of these fields contain the value from this column?” condition.

The words “custom app” or “one-time use” tend to send up red flags, but that’s no excuse to try to force a square peg into a round hole. While you could always export data from CRM and import it into a database temporarily, allowing you to analyze it and craft a series of Excel files for import, it may actually be more cost-effective to go ahead and write that one-time use custom app. The truth is, as long as you know how to knock the logic out in C# and you have the connection info for both the source and the destination, it’s very easy to do.

MSDN provides a handy guide on how to set up a barebones version of such an app: Walkthrough: Build a console application that connects to Microsoft Dynamics CRM using developer extensions. All that’s left is to add code to pull your source data, logic to perform lookups, and populate CRM Entity objects to your heart’s content!

Maintaining Index Values While Paging with Knockout.js

August 18th, 2014

Recently I was working on a project that included a grid with large amounts of data. This grid not only displayed data but also allowed the user to edit, and the saving process contains a lot of validation and behind-the-scenes adjustments to data, so the usual knockout binding wasn’t always enough. In many cases, when the user updated a particular field, we wanted to default/disable/show/hide other fields within the same row. To help with this, we ended up using a custom binding handler that would add an attribute to each field with the index of the object in the observableArray. This process was adapted from this example by Ryan Niemeyer, and looks like this:

ko.bindingHandlers.setIndex = {
	init: function (element, valueAccessor, allBindings, data, context) {
		var prop = valueAccessor();
		element.setAttribute(prop, context.$index());

To use this in your binding, you would add the following (with “arrayIndex” being the attribute name):

<input type='text' data-bind="setIndex: 'arrayIndex'" />

Now, whenever we wanted to retrieve the index of the item in our script, we could access the value of the arrayIndex attribute. This worked awesome, until the day came where we wanted to introduce paging to the grid. To page the observableArray that was binding to the grid, I used a slightly modified (for our needs) version of this paging extender on jsFiddle. The problem was that when I navigated to any of the pages, we’ll say page three, and updated the record in row 1, when I navigated back to the first page it had actually updated data in row 1 of the first page (and the row on page three was now blank).While troubleshooting I noticed that the knockout $index() value was not actually taking pagination into consideration, and instead was only relative to the particular page that I was viewing. So rather than page one having indexes of 0-9 and page two having 10-19, each page would always only be 0-9. This wouldn’t normally be an issue, except that all of our behind-the-scenes changes were happening based on the arrayIndex attribute (derived from $index()), so no matter what page you were on, you would always be updating the first page of data.

To fix this, all we had to do was update our binding handler to accomodate our new paging:

ko.bindingHandlers.setIndex = {
	init: function (element, valueAccessor, allBindings, data, context) {
		var prop = valueAccessor();
		//$parent.DETAIL references our observableArray
		element.setAttribute(prop, ((context.$parent.DETAIL.currentPage() - 1) * 10) + context.$index());

With this, each page would set the arrayIndex to the correct value, and our edits once again worked properly. Reiterating what I touched on above, this isn’t normally something you would encounter unless you are making custom index-based changes, but in the event that you are, hopefully this helps prevent some of the headaches!

Database Table Compare Using Excel VBA

August 11th, 2014

One of several integration points on a current project is to an SAP Sybase ASE database (v16.0). The Sybase database was ported from the 12.5 version, and part of the testing was to verify that the data being pushed to the new version matched what’s being pushed to old version.

I couldn’t find any decent [free] tools that would do a data compare against tables in two different Sybase databases, and I ran into some issues trying to use Entity Framework against Sybase: 1) the old Sybase database didn’t have primary keys set on the tables and there wasn’t a .NET Provider for it, and 2) the new Sybase v16.0 didn’t have any decent documentation on the topic. So I decided to roll my own simple comparison leveraging ODBC, Excel 2013 and VBA.

Since the tables in both database were identical in terms of the schema, and all I needed to do was to compare the data on a field by field basis, Excel was an option that could handle it pretty easily and it was a tool the users were familiar with.

To start with, I setup two User DSN ODBC connections, one to each of the Sybase databases (Figure 1) and configured them (Figure 2).
Figure 1. ODBC Data Source Administrator

Figure 2. DSN Configure, ASE

Next I created an Excel Macro-Enabled Workbook (.xlsm) with a worksheet for each of the tables I needed to compare: CMC_PAGR_PARENT_GR, CMC_GRGR_GROUP, etc. (Figure 3).
Figure 3. Excel Workbook

And lastly, added some VBA code with logic to connect to the databases using the ODBC connections and also added a reference to the Microsoft ActiveX Data Objects 6.1 Library latest version (Figure 4). Older versions of the data object library works as well.

Sub Main()
    Dim groupId As String
    Dim sqlGroup As String
    groupId = "3005"
    sqlGroup = "SELECT * FROM dbo.CMC_GRGR_GROUP WHERE GRGR_ID = '" & _
                groupId & "'"

    PopulateWorksheet "CMC_GRGR_GROUP", sqlGroup
End Sub

Private Sub PopulateWorksheet(worksheet As String, sql As String)
    Dim col As Integer
    Dim rs As ADODB.Recordset

    ' Get the records from the old database
    Set rs = QueryDatabase("FACETS v12.5", sql)

    ' Copy field names to the first row of the worksheet
    For col = 1 To rs.Fields.Count
        ActiveWorkbook.Worksheets(worksheet).Cells(1, col).Value = _
            rs.Fields(col - 1).Name

    ' Copy the recordset to the worksheet, starting in cell A2
    ActiveWorkbook.Worksheets(worksheet).Range("A2").CopyFromRecordset rs

    ' Get the records from the new database, and copy the recordset to cell A3
    Set rs = QueryDatabase("FACETS v16.0", sql)
    ActiveWorkbook.Worksheets(worksheet).Range("A3").CopyFromRecordset rs

    HighlightDifferences (worksheet)
End Sub

' Format differences in the data between databases
Private Sub HighlightDifferences(worksheet As String)
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Style = "Bad"
End Sub

' Query a given Sybase database based on the ODBC name and SQL statement
Private Function QueryDatabase(odbcName As String, sql As String) _
    As ADODB.Recordset
    On Error GoTo ErrorHandler

    ' Declare our variables
    Dim conn, rs

    ' Initialization
    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection

    conn.Open odbcName, "userid", "password"
    rs.CursorLocation = adUseClient
    rs.Open sql, conn, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic

    ' Clear the connection: only a disconnected recordset can
    ' be passed around and used later.
    Set rs.ActiveConnection = Nothing

    ' Return the recordset
    Set QueryDatabase = rs
    GoTo ExitHere

    MsgBox "Error " & Err.Number & ": " & Err.Description & " in Main()", _
        vbOKOnly, "Error"

    Set conn = Nothing
End Function

Figure 4. References VBA Project

The logic retrieves the data from each database using the connections and SQL statement provided, populates the worksheet and highlights the cells where the values are different. Also, there are any number of re-factoring that could be done such as, additional formatting, storing the parameters for the SQL statement in a ‘Settings’ worksheet and adding a button to launch the process. However, this basic process was all I needed to verify that the data posted to the new database matched up with what was being posted to the original database. In this example, I am retrieving data from a Sybase database, but it’s even easier from a SQL Server database because you don’t need to setup ODBC connections. In addition, Excel and VBA can be quite powerful and can be used in a variety of ways to provide solutions that’s often overlooked.

Dynamics CRM 2013 Upgrade Gotcha: Sitemap Pointing to the Wrong Help?

July 31st, 2014

We’ve performed several sitemap customizations for clients in the past, so a recent MSDN article, Is your CRM 2013 pointing to CRM 2011 help?, really caught my eye. I admit that I don’t frequently use the online help for development, so learning that there are instances that a CRM upgrade can leave the help link pointing to the CRM 2011 page was a surprise.

The article has a great infographic that covers how to correct the issue, but it boils down to:

  1. Export a solution with the sitemap.
  2. Extract the files from the solution and open up the XML for the site map.
  3. Locate the “Help_Resource_Center” SubArea node and update the “Url” value to “″

And, while you’re in there, check if there are any other changes you should be making based on this TechNet article: Verify new areas are available in the navigation bar.

Generating Selected Entities for MS CRM Early Bound Entities

July 24th, 2014

On a recent project I had to create a web service that returned data from a MS CRM system based on provided parameters (account name and contact name). I prefer to work with the early bound, strongly typed XRM entities as opposed to using the web service calls, QueryExpression, ConditionExpression, etc., because the code is much shorter and easier to read – especially for anyone that has worked with Linq.

It takes a little more overhead to set up and create the XRM entities, though, and by default when you use crmsvcutil.exe it generates the early bound classes for every entity in the system. That’s a lot of overhead when you only need a small subset of entities like I did. In this case, I needed two entities: account and contact. I’ve had to do this before and always have to look up exactly how to do it. I have two blog posts that I’ve referenced when doing this. Both are very helpful, have actual code as well as a good explanation and wanted to share them. First is this post by Erik Pool. The second is this one by “Busy xRM Architects”. They both use a similar idea – read the entities to generate classes for from a file. One uses a text file and the other uses an XML file.

Hopefully this helps get the information out there and allows people to generate smaller classes!

Dynamics CRM 2013 Spring Release – Timer Control

July 3rd, 2014

One of the neater additions to CRM 2013 in the Spring ’14 Release is the ability to add a Timer control to a form. While it’s specifically mentioned that this new control can be used for service level agreements, the timer is generic enough that it can be used with any DateTime field in conjunction with a workflow to develop some handy time-dependent functionality. You just pick a DateTime field for it to look at, specify the Success/Waiting/Cancel Condition fields and values, then position it on the form.

There’s a great tutorial on how to add a Timer to the Lead form at Adding Timer Control to Lead Entity in Dynamics CRM 2013 Spring release, but I’d like to call out some things to keep in mind right here:

  1. The Success/Waiting/Cancel Condition fields can all be different.
  2. Only Option Set controls are available to select for the Condition fields.
  3. If you cannot add a Timer to the form because the Timer button is disabled, make sure that you’ve installed the latest product updates via the “Install Product Updates” link on the Administration page.

Getting the Current User Information in a Plugin

June 26th, 2014

I was working with MS CRM 2011 and I had a situation where I needed to get the information about the current user in the middle of plugin execution. When you start digging around the documentation in MS CRM you’ll find that there are two properties that look like they might have the information you need:

  • InitiatingUserId
  • UserId

Here’s an example of the code you’ll see in a plugin:

public void Execute(IServiceProvider serviceProvider)
    IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

    // Which one is correct?
    var initiatingUserId = context.InitiatingUserId;
    var userId = context.UserId;

How do you know which one you need? What’s the difference? The difference is that the UserId property may or may not be the same as the InitiatingUserId. If in your plugin step registration “Run in User’s Context” field has the value “Calling User” then the UserId and InitiatingUserId will match. If you specify a user in the “Run in User’s Context” field, then the UserId field will contain the user ID of the person you specify and the InitiatingUserId will be the actual CRM user whose action triggered the plugin.

My bet is that most people are usually looking for the InitiatingUserId, so it probably makes sense to use that property even if you specify “Calling User” for the “Run in User’s Context” field. That way, if the “Run in User’s Context” value ever changes, InitiatingUserId will still have the correct value. If you use “UserID”, it’s possible you’ll get a different value than what you expect.

Hopefully this helps someone figure out which value is the correct one for your situation.

Knockout Select Value Binding

June 20th, 2014

I’ve used KnockoutJS with a few different projects and really like its two-way binding. However, one quirk I’ve ran into was binding to a select element. The select was bound to an array of Github repository objects and I wanted the selected item value to be the current repository object, not just one of its properties. For example, Listing 1 shows a sample repository and instead of the select value being say “id”, I wanted it to be the entire object.

	"id": 12015056,
	"name": "ApiRouter",
	"full_name": "OdeToCode/ApiRouter",
	"owner": {...},
	"private": false,
	"html_url": "",
	"description": "Web API Message Handler, routes requests based segments",
	"fork": true,
	"created_at": "2013-08-10T03:38:36Z",
	"updated_at": "2014-03-25T02:13:06Z",
	"pushed_at": "2013-08-13T00:41:53Z",
	"size": 472,
	"stargazers_count": 0,
	"watchers_count": 0,
	"language": "C#",
	"has_issues": false,
	"has_downloads": true,
	"has_wiki": true,
	"forks_count": 0,
	"open_issues_count": 0,
	"forks": 0,
	"open_issues": 0,
	"watchers": 0,
	"default_branch": "master"

Listing 1. Sample Github repository object with selected properties

It’s a simple fix once you know what to do, but it wasn’t clear from the documentation how to do it.

When binding a select element to a Knockout view model, you can bind the “options”, “optionsText”, “optionsValue” and “value” attributes. Or you can bind the “options”, “optionsText” and “value” attributes. There are a few other attributes, but they don’t applied in this case.

If you bind a property to the the optionsValue attribute (which is what one would typically do), then the value attribute is also set to the same value as “optionsValue”. This is what’s not clear and can be confusing if both attributes are bound.

If your select is bound to an array of objects with just name and id properties, then setting “optionsText” to the name property and the “optionsValue” to the id property works just fine as “value” will also be set to the id property (see Listing 2).

<select data-bind="options: repos,
    optionsText: 'name',
    optionsValue: 'id',
    value: currentRepo">

Listing 2. Select element with only the optionsText, optionsValue and value attributes set

However, when your select element is bound to a array of objects with 3 more properties, it’s more likely that you want the current object when a selection is made, not just a particular property of the object. To get the current object as the value, only bind the “value” attribute, don’t use the “optionsValue” attribute as shown in Listing 3.

<select data-bind="options: repos,
    optionsText: 'name',
    value: currentRepo">

Listing 3. Select element with only the optionsText and value attributes set

With the simple change in Listing 3 in place, making a selection will now stored the current object as the “value” instead of just a particular property. Take at look at this Plunk to see an example of both scenarios.

For more info on Knockout’s binding, see the value binding and the options binding in the online documentation.

CRM 2013 Online and On-Premise: Organization Settings Editor

June 5th, 2014

While experimenting with CRM 2013′s new CRM app for Windows 8 and the iPad, we found that there were some limitations in place that were a bit restrictive for what we would like to see for some custom forms. For one of our clients, the Account form is particularly complex due to all the information they capture, so the default limit of 75 fields and 5 tabs wasn’t sufficient without significantly pruning the form. Of course, the tablet app uses the first available form for the logged in user, so removing fields/tabs from the form they would see by default in the browser was out of the question, even if we did want to display less information on the tablet.

The max number of fields and tabs on the tablet is configurable, though, and Sean McNellis’ Dynamics CRM Organization Settings Editor makes getting to these settings extremely painless. After importing the solution, it’s as easy as opening up the configuration page, clicking “Add” for the setting you wish to change, then editing the value for the setting. While caution should be used when changing any organization setting, this is a fantastic way for you to do it.

Click on the picture below to increase the size.


More 2013 Client API Enhancements

May 29th, 2014

My co-worker Rod mentioned a nice CRM 2013 client SDK API change in his last post. Keeping up with the latest changes in various products can be quite a task given the number of tools we all have to work with as well as the rapid pace of changes to those tools. Sometimes a short video or document helps sort through what’s new. There’s short but very helpful video that outlines the new CRM 2013 client SDK features. Here are a few that I found interesting.

Those are just a few that jumped out at me. Watch the linked video for other items that have been added to the CRM 2013 client side SDK.