CRM 2013 & Chrome: Browser Upgrade Woes

October 23rd, 2014

Beginning with CRM 2011 and continuing with CRM 2013, Microsoft has made some major strides in supporting Dynamics with non-IE browsers, but a couple recent Chrome updates have thrown a wrench in the works.

With Google Chrome 37, an error displays when changing Status Reasons. As mentioned in this KB article, An error occurs in Microsoft Dynamics CRM when adding or editing Status Reasons using Google Chrome, the cause for this is because showModalDialog() has been turned off. Fortunately, the article lists the steps to re-enable the JavaScript function, providing a workaround for the issue.

However, with Google Chrome 38, Lookups began to have problems. As described in the KB article, Microsoft Dynamics CRM lookup fields fail to save or provide results when using Chrome 38, you receive “An error occurred” message in CRM 2013 when clicking on a Lookup and a different error in CRM 2011 when trying to save the form where a Lookup has been changed. While there is currently no fix or workaround for this problem, Aaron Richards has mentioned on his MS Dynamics CRM Community blog in the post Dynamics CRM lookup fields fail with Chrome 38 that CRM Online will be fixed in Service Update 6, while On-Premise should be addressed in the next few weeks.

CRM 2013 OData Queries With Related Entities and LINQPad

October 16th, 2014

I’ve been a huge user of LINQPad for several years now. It’s a wonderful product and if you haven’t used it I recommend that you do and also recommend that you get the pro or premium version.

And now, on to today’s topic. I needed to write some OData for a CRM 2013 project that I’ve been working on. In the past I’ve used a tool designed specifically for CRM OData queries. The tool I’ve used before has not been officially updated for CRM 2013 so I started looking for other options. As I searched I found something that said I could use LINQPad. What!? The app I love and use supports OData in MS CRM? Yes, please!

Connecting LINQPad to the CRM web service

Here’s how to get it working. In a new LINQPad query, click “Add connection”. Select “WCF Data Services 5.5 (OData 3). Click “Next”. Enter the URI for the CRM organization data service, something like: http://server/orgName/XRMServices/2011/OrganizationData.svc. Enter the user name as “domain\userName” and your password. You can test or just click “OK”.

Setting up the Necessary CRM Entities

To set up the example I’m going to work with in this post, create a new entity named new_widget, that has an N:1 relationship to contact.(Or, if it’s clearer, a 1:N from contact to new_widget.) I created the relationship name as “new_contact_widget”. For a contact, create a few associated new_widget records.

Basic OData Query – Single Entity

Let’s say that you want to get the OData for a basic query to get all widgets that contain the name “Drum Widget 1″. The query looks like this:

var widgets = (from w in new_widgetSet
               where w.new_name.Contains("Drum Widget 1")
               select w).Dump();

Here’s the output from LinqPad (click the image for a larger version):
Basic Query

What’s really great is that LINQPad outputs the actual OData query, making it easy to put in your CRM JavaScript. In the LINQPad results pane look at the “Request Log” tab. You’ll see output like this:

http://server/orgName/XRMServices/2011/OrganizationData.svc/new_widgetSet()?$filter=substringof(‘Drum Widget 1′,new_name)

Query and Return a Related Entity

Now, let’s say that in addition to the widget record, you want to get the related contact info. You can do that, but you can’t do a join as you normally would in LINQ to get the contact because OData won’t support that. This StackOverflow post was helpful to me in figuring that out. Instead of a join you have to use the expand OData function, but LINQPad makes it easy. Also, since you can’t do a join, you obviously can’t select from the joined entity. Instead, you have to use the expand functionality and projection in LINQ. So, the query would look like this:

var widgets = (from w in new_widgetSet
               where w.new_name.Contains("Drum Widget 1")
               select new {w.new_contact_widget}).Dump();

and the result looks like this:


One thing to keep in mind: you cannot filter based on attributes in the related entity, so something like this will not work:

var widgets = (from w in new_widgetSet
               where w.new_name.Contains("Drum Widget 1")
               && w.new_contact_widget.FirstName.Contains("mike")
               select new {w.new_contact_widget.FullName}).Dump();

If you try it, you’ll get an error “filter conditions of different entity types, in the same expression, are not supported. See exception below for more details.”

Returing Attributes from the Primary and Related Entity

If you want to bring back attributes from both the primary entity and the related entity, you again have to use projection. If I want the widget name and it’s associated contact, it looks like this:

var widgets = (from w in new_widgetSet
               where w.new_name.Contains("Drum Widget 1")
               select new {w.new_name,  w.new_contact_widget.FullName}).Dump();

and the result looks like this:

Hopefully this helps you understand how you can use LINQPad the CRM web service to generate OData web queries. It’s going to make my life a lot easier!

ASP.NET MVC Convert ViewModel to Client-Side ViewModel

October 9th, 2014

ASP.NET MVC allows a controller to return a model/viewmodel to the View for data-binding. That works just fine, but its one-way and I wanted to use Knockout two-way data binding to simplify making changes and posting them back to the server. Since I already had the model available on the View, I just needed a way to serialize it to JSON so that it could be used to create a client-side viewmodel and leverage Knockout to handle the bindings.

The application I am using allows a Header record to be created with one or more Detail records and uses the two viewmodels below. The HeaderViewModel is what’s passed to the View.

public class HeaderViewModel
    public HeaderViewModel()
        Detail = new List<DetailViewModel>();
        DetailItemsToDelete = new List<int>();

    public int HeaderId { get; set; }
    public string Name { get; set; }
    public string Comments { get; set; }
    public List<DetailViewModel> Detail { get; set; }
    public List<int> DetailItemsToDelete { get; set; }

public class DetailViewModel
    public int DetailId { get; set; }
    public int HeaderId { get; set; }
    public string EmpId { get; set; }
    public string Name{ get; set; }
    public string Email { get; set; }
    public string Division { get; set; }
    public DateTime? HireDate { get; set; }
    public string Active { get; set; }

There are a few of ways handle the serialization such as, Json.Encode(model), new JavaScriptSerializer().Serialize(model) or JsonConvert.SerializeObject(Model). I found JsonConvert simpler and more flexible as I also needed to format the serialized dates in the mm/dd/yyyy format. The process is done in two steps with the first being the serialization of the server-side viewmmodel to JSON and then using resulting JSON to create the client-side viewmodel. The serialization logic can be added to the View like this.

@using Newtonsoft.Json
@using Newtonsoft.Json.Converters
@model HeaderViewModel

    ViewBag.Title = "Create Header";
    // Convert the model to JSON
    var data = JsonConvert.SerializeObject(Model, 
        new IsoDateTimeConverter { DateTimeFormat = "MM/dd/yyyy" });

@section scripts
    <script src="~/Scripts/knockout-3.1.0.js"></script>
    <script src="~/Scripts/knockout.mapping-latest.js"></script>
    <script src="~/Scripts/jquery.validate.js"></script>
    <script src="~/Scripts/app/membermodule.js"></script>
        // Convert the server-side viewmodel to a client-side viewmodel...
        var headerViewModel = new MemberModule.HeaderViewModel(@Html.Raw(data));
        // and bind it to the view


The call to MemberModule.HeaderViewModel is where the client-side viewmodel is created leveraging Knockout Mapping Plugin to simplify the creation of observable properties and is shown below. The Detail mapping, viewmodel and a some of the other methods were collapsed for brevity.

var MemberModule = (function() {
    // Mapping definition for the child records
    var detailMapping = {};

    // Child View Model
    var detailViewModel = function(data) {};

    // Parent View Model
    var headerViewModel = function(data) {
        var self = this;
        ko.mapping.fromJS(data, detailMapping, self); = function() {
                url: "/Member/Save",
                type: "POST",
                data: ko.toJSON(self),
                contentType: "application/json",
                success: function(result) {
                    if (result.viewModel != null) {
                        ko.mapping.fromJS(result.viewModel, {}, self);
        self.flagHeaderAsEdited = function() {},
        self.addDetail = function() {},
        self.deleteDetail = function(detail) {};

    return {
        HeaderViewModel: headerViewModel

The View can then use the client-side viewmodel for two-way binding as shown below.


    <div class="form-group">
        <label for="Name" class="control-label">Name:</label>
        <input name="Name" id="Name" class="form-control" data-bind="value: Name, event: {change: flagHeaderAsEdited}, hasfocus: true" />
    <div class="form-group">
        <label for="Comments" class="control-label">Comments:</label>
        <input name="Comments" id="Comments" class="form-control" data-bind="value: Comments, event: {change: flagHeaderAsEdited}" />
    <table class="table table-striped">
            <th>Hire Date</th>
            <th><button data-bind="click: addDetail" class="btn btn-info btn-xs">Add</button></th>
        <tbody data-bind="foreach: Detail">
                <td class="form-group"><input name="EmpId" class="form-control" data-bind="attr: {'id': 'EmpId_' + $index()}, value: EmpId, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><input name="Name" class="form-control" data-bind="attr: {'id': 'Name_' + $index()}, value: Name, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><input name="Email" class="form-control" data-bind="attr: {'id': 'Email_' + $index()}, value: Email, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><input name="Division" class="form-control" data-bind="attr: {'id': 'Division_' + $index()}, value: Division, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><input name="HireDate" class="form-control" data-bind="attr: {'id': 'HireDate_' + $index()}, value: HireDate, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><input name="Active" class="form-control" data-bind="attr: {'id': 'Active_' + $index()}, value: Active, event: {change: flagDetailAsEdited}" /></td>
                <td class="form-group"><button data-bind="click: $parent.deleteDetail" class="btn btn-danger btn-xs">Delete</button></td>
    <p><a href="/" class="btn btn-default btn-sm">&laquo;Back to List</a></p>

    <p><button type="submit" class="btn btn-primary">Save</button></p>

Here are few shots of the UI.

Header listing with options to Create, Edit, view Details and Delete.

Create a new Header.

Edit a Header showing the client-side viewmodel in Chrome’s developer tools.

The data for the View can certainly be retrieved and return as JSON via Ajax, but in this case the viewmodel was already being passed, so it was easy enough to convert it to a client-side viewmodel. In addition, leveraging Knockout’s two-way data-binding synchronizes the changes made via the UI with the underlying viewmodel and makes posting changes back to the server a snap.

Generate and Export a String-only CSV File from WebAPI

September 29th, 2014

Recently one of my tasks was to allow part of my model to be exported as a downloadable CSV attachment. On top of that, the CSV files were likely to be opened in Excel for modification, and sometimes depending on data types, Excel would apply formats to the data that we did not want. So, we also needed to be able to force the format of the values to be pure strings when opened in Excel.

In WebAPI, we are going to create a method that returns a HttpResponseMessage type. I am also passing an id parameter that I use to get my data.

public HttpResponseMessage GetCsv(string id)
	// ...

The model I will be using looks like this (various data types):

public class Record
	public string Name { get; set; }
	public string Phone { get; set; }
	public string Email { get; set; }
	public DateTime BirthDate { get; set; }
	public int Tickets { get; set; }

First I’ll retrieve my data. Then, to generate my CSV contents, I use the StringBuilder class. To start, we’ll need to append our header row (includes new line):

var records = GetRecords(id); //irrelevant
var sb = new StringBuilder();

sb.Append("Name,Phone,Email,Birth Date,Tickets\r\n");

In order to force string values in Excel, we need to wrap the contents. For example, for a value of John Smith we will actually be saving as =”John Smith”. The AppendFormat method is helpful for this part:

foreach (var record in records)
	sb.AppendFormat("=\"{0}\",", record.Name);
	sb.AppendFormat("=\"{0}\",", record.Phone);
	sb.AppendFormat("=\"{0}\",", record.Email);
	sb.AppendFormat("=\"{0}\",", detail.BirthDate.ToShortDateString());
	sb.AppendFormat("=\"{0}\"\r\n", detail.Tickets.ToString()); //no comma for the last item, but a new line

That’s it for building our CSV file, all that’s left is to return the object for download:

HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);

result.Content = new StringContent(sb.ToString());
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); //attachment will force download
result.Content.Headers.ContentDisposition.FileName = "RecordExport.csv";

return result;

To start the download just add a link with the path to your method as the url:

<a href='../api/Tools/GetCsv/12345'>Export</a>

Now when our file is opened in Excel, you can see that even integer values are wrapped and no additional formatting was automatically applied.

Excel Screenshot

Microsoft Dynamics CRM 2015: The Exciting Bits

September 25th, 2014

Well, to be honest, your mileage may vary, but there were parts of the recently-released Microsoft Dynamics CRM 2015 Release Preview Guide that made my ears perk up a bit. We knew that we’d be seeing improvements with the tablet client, so the announcement of an enhanced “offline” mode is no surprise, but I’m glad to see that the Business Rules functionality is getting some love.

One of the handiest additions to Microsoft Dynamics CRM 2013 was the ability to create simple UI rules without needing to hack out some JavaScript. Granted, the functionality was limited, since the conditions you could assign only allowed you to use the “and” logical operator (much like what you see when adding conditions to a CRM Workflow), but the Business Rules provided a cool, quick way to define and organize how a form would behave. With Microsoft Dynamics CRM 2015 though, not only are we getting access to the “or” operator and simple grouping, but we’re also getting If/If Else/Else support, practically making the Business Rules a full-fledged client-side version of the CRM Workflow functionality.

Not only that, but we’re also getting Calculated Fields, which have to have been a very popular request for Microsoft to implement for years. JavaScript will still have its place in CRM 2015, especially if it’s necessary to query for other records or manipulate strings, but we’re definitely moving closer to a system where we can whip up some pretty complicated behavior without resorting to custom code.

Properties vs. AllProperties

September 18th, 2014

There is a great article that I recently found about Properties vs. AllProperties. It describes the way that AllProperties is meant to replace Properties, but Properties was left in place for backwards compatibility. The unconventional PropertyBag data type stores its keys in all lowercase, thus not supporting case-sensitive keys, while the conventional Hashtable does support case-sensitive keys. On top of that, while entries added to Properties get propagated to AllProperties with a lowercase key, entries added to AllProperties do not get propagated to Properties.

To read more of the article, please click here.

Troubleshooting tabindex in IE

September 4th, 2014

Recently I was working on a UI project, and started to have issues with tabindex – but only in IE (go figure). The UI was essentially a large grid with custom tabbing, and the user could also add new rows/fields at any time, so the tabindex values were always being adjusted and had the potential to get pretty large. At the very bottom of the form was a textarea that would also need focus, but it needed to be the very last item in the tab order.

As some of us end up doing with things like z-index, I decided to take what I thought was the easy solution and assign the textarea a tabindex of some random large number like 99999.

<textarea id="notes" tabindex="99999"> </textarea>

While testing I realized that the textarea would never actually get focus in IE, so I began to wonder if there were actually limitations to tabindex values – turns out there are! The IE documentation for the tabindex property states:

“For Internet Explorer 5.01 or above, the attribute may be set to any value in the valid range of -32767 to 32767.”

…so there was my answer. While other browsers handled larger integers, IE was once again a bit different, and after adjusting my tabindex my field was once again added to the tab order!

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.