Monday, October 29, 2012

JSON vs. Ajax vs. jQuery - layman's guide

JSON, Ajax, and jQuery are all technologies that are frequently referenced and implemented to provide a great deal of flexibility and data manipulation options when customizing Dynamics CRM - in particular as it relates to Jscript customization. This post is therefore dedicated to providing a very brief layman's guide to each of these technologies and what the essential difference and function of each is.


JSON is simply a data format much like XML, CSV,  etc. Its primary function is to provide an alternative to the XML standard. For example (borrowed from Wikipedia), an XML format for a "person" entity might be as follows:

    <streetAddress>21 2nd Street</streetAddress>
    <city>New York</city>
    <phoneNumber type="home"> 212 555-1234</phoneNumber>
    <phoneNumber type="fax">646 555-4567</phoneNumber>

Whereas the JSON equivalent would be as follows:

    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    "phoneNumber": [
            "type": "home",
            "number": "212 555-1234"
            "type": "fax",
            "number": "646 555-4567"

The benefit cited for JSON over the XML standard is that JSON is generally considered to be lighter weight and easier to process programmatically while maintaining all the other "aesthetic" benefits of the XML standard. For a more detailed analysis of these benefits, refer to the JSON web site.


Ajax is used for asynchronous processing of web pages. Meaning that once the web page has been loaded Ajax can be used to interact with the server without interfering with the already rendered web page i.e. such requests happen as part of background processing. Ajax is therefore typically used to make web pages highly interactive without having to reload the web page every time a new server request is made to retrieve data based on user interaction. A classic example is when you start typing an airport name at one of the online reservation sites and the drop down list shows relevant options based on what you are entering.

Ajax interacts with the server by means of an XMLHttpRequest object. And although the results that are retrieved can be in XML format (as is indicated by the object name) it is typically more common to retrieve the results in JSON format as that is easily consumed by JScript.


jQuery is meant to simplify JScript programming by making it easier to to navigate, handle events, animate, and develop Ajax for web pages. The latter being most relevant for this summary. And therefore as far as
Ajax is concerned - jQuery leverages Ajax for performing server requests and simplifies interaction with the Ajax layer. Put simply, Ajax is the tool that jQuery will use for handling asynchronous server requests. So when you see the "$.ajax" method in your JScript code it means that jQuery ($.) is being used to execute an Ajax request (ajax).

At this point in time, only the jQuery ajax method is supported by Dynamics CRM. Or to quote from the SDK:

The only supported use of jQuery in the Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online web application is to use the jQuery.ajax method to retrieve data from the REST endpoint. Using jQuery to modify Microsoft Dynamics CRM 2011 application pages or forms is not supported. You may use jQuery within your own HTML web resource pages.

Thursday, October 25, 2012

Dynamics CRM 2011 Instance Adapter Released

Yesterday the Dynamics CRM team announced the release of the Microsoft Dynamics CRM 2011 Instance Adapter. The following is the description that was provided for this tool:

This is an additional adapter for use with Connector for Microsoft Dynamics that allows for the synchronization of data between two Microsoft Dynamics CRM 2011 organizations. The adapter allows for communication between two endpoints that exist on any authentication/hosting environment (on-premise, online, IFD, etc.) This means that you can leverage this new adapter to move Microsoft Dynamics CRM 2011 data between test and production servers or from on-premises to the cloud.


This would seemingly provide a solution to a situation that is encountered all too frequently. Namely that the data in the test or staging environment becomes stale and consequently does not provide a good testing ground without performing a refresh. Until now, the only way to do that has been to backup the production database and then import it to refresh the test environment. Something that can take a while to accomplish especially with the larger CRM databases out there. Not to mention that such a refresh would also overwrite development deltas made in those environments that would need to be re-applied. Now it would seem that you can keep the test or staging environment in sync in a much more seamless fashion.

In addition to this, this tool also would seem to offer a good fail over solution for the CRM deployment. That is, by keeping a redundant CRM deployment (on site or in a remote location) that is kept in sync via the Instance Adapter, you now have the option of switching over to this redundant server as part of a disaster recovery plan. This is a feature that certain business (typically financial institutions) require (which seems particularly relevant right now as the East Coast braces for the onslaught of Hurricane Sandy).

What is not yet clear to me is how it will handle the following scenarios:

  1. Deltas - If an update is made in the test environment will this be overwritten? Or will it require that the mirrored environment to be "read only"?
  2. Frequency - How frequently the syncing occurs. Will this be configurable? Real time? Or only on demand? Such options factor into the tool being used as an effective disaster recovery option and also into requirements for bandwidth and such like.
  3. Two-way - Can syncs go both ways? Almost certainly not going to be the case.

I am quite sure that this tool will not have the robustness of dedicated disaster recovery products such as  Double-Take (e.g. High Availability options). Or at least initially... But it definitely seems to be a tool that can be leveraged for optimizing test environments and perhaps to satisfy low to medium-end disaster recovery requirements without having to resort to a 3rd party (restoring a database is considered a very low end disaster recovery plan).

Time of course will tell as we learn more about this tool. To get a jump start on the installation and configuration of this tool you can review the detailed instructions provided in this PowerObjects blog posting.

Wednesday, October 24, 2012

CRM Relationships: Part 2

In my previous post I concluded by saying that, in my experience, Connections tends to cause a degree of consternation in terms of relationship design. This is a fairly difficult topic to approach but in this post we'll review why that is and suggest an approach for determining whether using Connections is an appropriate choice.

The main reason why Connections can be confusing is that it can - by definition - be used to relate any two entities in the system together. And therefore you always have the option of using Connections instead of a custom relationship to set up the relationship between two entities.

I think that the use of Connections often can relate to something more fundamental in terms of relationship design. For instance, Connections come into play most frequently for businesses who use CRM first and foremost as a contact management solution. And in such a scenario Connections are typically used to relate contact and account records together to record current and past associations with one another. And very often you will have many different account and contact types (company, vendor, supplier etc.) being represented within the account and contact forms. The more fundamental question in this case is whether  you should be using the account and/or contact entity to represent all these incarnations or whether perhaps you should create a custom entity to represent a specific type of account. For example, if you're an investment capital firm, should a Bank be an account of type "bank" or should it be it's own separate entity?

The answer to that question (which is not really the topic of this post) will depend on various factors but ultimately should be driven by whether it's really necessary to make the Bank a type of account. Is the Bank a customer in this example? Are you likely to send marketing initiatives to the Bank? Or is it just a piece of compound information that is related to your actual customers i.e. your investors? I think the answer to the custom vs. account "flavor" decision lies within these questions.

Coming back to Connections - well more often than not once you're dealing with a custom entity then you're more likely to want to use a custom relation to define the relationship than using the Connections. Because that relationship is likely to be highly defined. Using the Bank/Investor example - if you need to record the bank that the investor uses then simply define the Bank as a lookup attribute of the Investor. There is simply no need to define that relationship via a Connection.

The above example uses a custom relationship for the sake of illustration but it is by no means limited to relationships to a custom entity. It can just as well be the case when relating an account to a contact by means of a custom relationship. The crux of the decision of whether to use a Connection should be based on how tightly the relationship is defined. I think the rule of thumb is that Connections should be used when there is not a highly defined relationship between entities.

For example, the relationship between a Contact and a Lawyer might be represented by a custom relationship if a Contact only can have a single Lawyer. If however the Contact can have multiple Lawyers and each Lawyer could be related to other Contacts in the system (N:N), it would probably be better represented as a Connection. You could of course also define a custom N:N relationship for this. The decision between these two options might be determined by the "centrality" of that relationship. That is, if a Lawyer is just one of the many "useful" client/vendor relationships that you want to store for the Contact (among say, doctors, plumbers, electricians etc.) then you could easily see that you'd have to start defining a whole lot of custom relationships for each one. This will result in your data being a lot more distributed i.e. click here to view lawyers, click there for doctors etc. (and the same is true for views, reporting etc.). However if the Lawyer is a critical piece of information that needs to be stored for your Contacts, then you probably should be separating it out into its own custom relationship.

Perhaps we can therefore summarize as follows:

Connections is not an essential part of the CRM database as in theory it can be replaced through the use of custom relationships. Therefore Connections should not be used in cases where the relationship being represented is important or definitive for your data. Separating out those relationships in terms of navigation via the use of custom relationships is probably a good thing. For other interesting, miscellaneous or viral type relationships that might be perused by a sales rep prior to heading into a meeting or for other similar less defined ad-hoc business procedures - using Connections as a general centralized repository is probably the way to go.

Tuesday, October 23, 2012

CRM Relationships: Part 1

There are many different ways to set up relationships in Dynamics CRM. The good thing about this is that you can accurately and economically model CRM relationships to mirror any real-world counterpart. The challenging thing about this is that inevitably when you have so many choices it can be confusing knowing which option you should go about choosing. And of course that decision has long term design implications so you ideally want to get it right or you'll most likely end up with a design that is not optimal. You could of course change this representation down the line but such a decision will involve data migration, modifying reports etc. etc. and therefore it is more than likely that the design you "go live" with will be a fairly long term proposition.

So the question becomes when deciding to relate 2 CRM entities - which relationship type should I use? At a high level you have the following options available:

  1. 1-Many
  2. Many-1
  3. Many-Many
  4. Nested
  5. Relationship
  6. Connection

I think 1-Many and Many-1 options are in general fairly obvious and we don't need to spend much time discussing those.

The Many-Many relationship can also be obvious but I think it also can be deceptively involved. That is because there are a number of different types of many to many relationships. Without delving into the detail right now we can summarize many to many relationships into the following 3 categories:
  • Classic N:N
  • Proxy N:N
  • Complex N:N
We'll explore the many to many relationship options in a separate blog posting. For now, we can summarize that the classic N:N relationship as it appears in the CRM entity customization should be use to model any "simple" N:N relationship i.e. where entities are related to one another without having to "explain" the nature of their relationship.

The Nested relationship is just an extension of the 1:N and N:1 relationships where an entity is related to itself. The biggest decision when it comes to nested relationships is whether it makes sense to relate an entity to itself rather than creating a custom entity to house that relationship. For example, tickets in a professional services organization that deal with break/fix type issues related to a supported product - it is often a requirement to bundle related tickets that address the same fix or release together. This can be done by designating a single parent ticket and linking all other related tickets underneath it or it can be done by creating a custom entity (call it "Ticket Group") that functions as the umbrella for grouping the related tickets. Both approaches can be valid depending on the requirement but I personally have found the need to created a nested relationship to be few and far between. Therefore as a rule of thumb I would venture to say that unless you can find a good justification not to go the way of creating another custom entity then that is what you should be doing.

Relationships and Connections are pretty much the same thing. In fact Connections came to fully replace the Relationships option in CRM 2011 so making a decision as far as Relationships go is pretty easy - do not use this option. Pretend it's not there and hide it via the security model so it doesn't confuse. If you have inherited Relationships from your 4.0 upgrade then consider migrating to Connections as part of the upgrade exercise. If that is not an option then hide Connections until you're ready to transition because having them both is just confusing.

That leaves us with Connections. I personally find that Connections (and its precursor Relationships) causes a degree of consternation in terms of relationship design. But we'll delve into that in the next blog posting.

Tuesday, October 16, 2012

Simplifying Navigation: Nav Link Tweaks

This post is a continuation of the Simplifying Navigation theme in Dynamics CRM. The most recent post regarding this topic discussed making Site Map changes to optimize the CRM Areas or "wunderbar" experience.  This post is an extension of that topic i.e. making changes to the menu items in each of these areas.

When creating a custom entity, CRM by default adds the entity into a new group called "Extensions" as illustrated below.

The issues with this default configuration is that the navigation can be kind of haphazard. That is, it is quite likely that "Banks" in the example given more naturally belongs under the "Customer" grouping. Like so:

Or perhaps in one of the other groupings or even in a more aptly named custom grouping. Assuming this is the case, this default configuration makes the user have to "hunt" when navigating between the different areas. So although this is nothing more than a minor tweak it is an important one that should not be overlooked. Kind of similar to updating the widget icon that custom entities have by default to a more relevant uniquely tailored icon (as also illustrated above) i.e. the application will work fine without these small tweaks but that a conscientious solution integrator should be sure to take the time to tweak. This is the proverbial crossing of the i's and dotting of the t's - if these type tweaks are not made then it likely reflects other more fundamental design shortcuts that are being implemented elsewhere in the application due to the general lack of attention to detail. This is something akin to this post in the Harvard Business Review about grammar - the principle of which I generally subscribe to (ok - who is going to be the first person to point out the mistakes in grammar that I have made? Principle or principal? :).

This change can be implemented quite simply by exporting the Site Map and removing the SubArea from the Extensions group (the Extensions group can be removed as well).

Subsequently copy the SubArea into the Group in which you want it to appear. The order of its appearance will match the order in which it is inserted into the Site Map.

Import and publish and you should be all set.

Monday, October 15, 2012

Ribbon: Get View ID (Context Sensitive)

Let's say you want to execute an action that will act on the view that is currently being viewed i.e. the objective is that the action will be context sensitive to the current view. How can this be achieved?

The first thing to do is of course to add the a button to the application ribbon and you'd be well advised to use the Ribbon Workbench Tool to do this. As we are adding a button that will appear in the grid views you'll need to be sure to select the HomePage option.

As illustrated in the screenshot above, the action for the ribbon button references a JavaScript function, so you will of course need to create a JScript web resource and a custom function to match what you define for the ribbon action command.

The JScript function will then take care of launching the custom action and passing in the View ID of the view currently being viewed - which is the crux of this post. The script below provides the method for obtaining the view ID:

function GetViewID() {
    try {
        if (document.getElementById('crmGrid_SavedNewQuerySelector')) {
            var view = document.getElementById('crmGrid_SavedNewQuerySelector');

            var firstChild = view.firstChild;

            var currentview = firstChild.currentview;
            if (currentview) {
                var viewId = currentview.valueOf();
        else {
            alert("No Element");
    catch (e) {

The result of the above exercise will be a button on the ribbon that when clicked will pop up with the ID of the current view:

You can of course tailor this jscript to pass in the retrieved View ID to whatever custom action you wish to execute.

Friday, October 12, 2012

context.getServerUrl() not getting correct context

Whenever you need to run a web service or fetch query from within the jscript of the form you need to first obtain the server URL of your environment. Typically that is performed by running the following set of commands:

var context = Xrm.Page.context;
var serverUrl = context.getServerUrl();

However there are a few issues with the context that this returns. For example, if I open up a contact form that has some fetch logic on the CRM server, then form opens up and renders cleanly. However opening the same contact form remotely will result in an error such as the one shown below:

The reason for this is quite simple:

When running CRM from the server, the URL does not need to include the domain name. For example: http://crm/org1. However when running from a client machine you need to specify (in certain scenarios) the full domain in order for CRM to open e.g.

The problem with the context.getServerUrl() command is that in both of the above cases it will return http://crm/org1 (without the which is going to be valid when running from the server but not when running from a remote client.

Although I have not confirmed I believe there will be similar issues if you're running CRM over https i.e. it will cause browser security warning popups to be issued as the command will try and execute over the http context rather than https.

The solution that we found (thanks to this post) was instead to use the following syntax in favor of the more standard formula listed above:

var context = Xrm.Page.context;
var serverUrl = document.location.protocol + "//" + + "/" + context.getOrgUniqueName();

The result is that when running CRM using the http://crm/org1 URL, serverUrl will return http://crm/org1. And when running CRM using the more fully qualified, serverUrl will correspondingly return Which of course is what you should expect.

And this small tweak results in the experience being the same no matter which machine CRM is accessed from.

Thursday, October 11, 2012

Phone Number Formatting

It is a fairly common and understandable requirement that telephone numbers be formatted when entering via the CRM front end. For example, if a user enters "5551234123" or "555-1234123" or "555-123-4123" or any other variation thereof it should be formatted into the standard format of  "(555) 123-4123".

The  jscript logic mentioned below can be used to this end. Specifically this logic will work as follows:
  • Strip any non-numeric values from the phone number field
  • If the phone number is less than 10 digits issue a warning 
  • If the phone number is equal to 10 digits, format as in the example above
  • If the phone number exceeds 10 digits assume it to be an international number and do not perform any additional formatting (besides stripping non-numeric values)
  • Prevent the form from being saved unless the phone numbers adheres to the above rules

The steps to achieve this are as follows --

First, define the PhoneNumberValidation function that performs all the heavy lifting:

function PhoneNumberValidation(phone,phoneDesc) {
 ret = true;
 var phone1 = Xrm.Page.getAttribute(phone).getValue();
 var phone2 = phone1;
 if (phone1 == null)
  return true;
 // First trim the phone number
 var stripPhone = phone1.replace(/[^0-9]/g, '');

 if ( stripPhone.length < 10 ) {
  alert("The " + phoneDesc + " you entered must be at 10 digits. Please correct the entry.");
  ret = false;
 } else {
  if (stripPhone.length == 10) {
   phone2 = "(" + stripPhone.substring(0,3) + ") " + stripPhone.substring(3,6) + "-" + stripPhone.substring(6,10);
  } else {
   phone2 = stripPhone;

 return ret;

Then for each attribute on the form you wish to format, create an "on change" function as follows:

function Attribute_OnChange() {
PhoneNumberValidation("attributeName", "attributeDescription");

  • attributeName is the name of the attribute the "on change" function is acting on e.g. telephone1
  • attributeDescription is a the friendly name for the attribute (used for the warning error messages)

Finally, reference the "on change" event from the form "on save" event to prevent the form from saving unless the phone number has the correct format. For example, the following will work:
function Form_onsave(executionObj) {

 var val = true;
 if (!Attribute_OnChange()) 
  val = false;
 if (!val) {
      return false;


Don't forget to check the "pass execution context as first parameter" in the "on save" event or otherwise it will not work!

Wednesday, October 10, 2012

Reports: Excel Export Formatting

When exporting an SSRS report to Excel all kinds of interesting things can happen from a formatting point of view. The report which might otherwise render quite nicely in SSRS may appear to have been attacked by a bunch of gremlins in its exported Excel format. Several things might contribute to this haphazard effect:

  1. The cells of data on the spreadsheet might be scattered all over the place
  2. Some elements of data may occupy several excel cells having been auto-merged in the export
  3. The report might export into multiple different sheets within the spreadsheet with no apparent rhyme or reason

The report below illustrates this showing cells of data being spread way out and multiple sheets being created from a single export file.

The main reason for this is that Excel is a highly structured document and it needs to fit the results into neat rows and columns. If the cells or rows are not aligned then Excel makes automatic decisions in terms or rendering the export. For example, merge cells may bring 2 sets of non-aligned data elements into alignment; expanding a column width or height may similarly bring a non-aligned element into alignment with other report elements.

Therefore it goes without saying that one of the keys to ensuring that a report renders well when exporting to Excel is to ensure alignment. This might seem obvious and is fairly easy when it comes to grid controls which are fairly structured elements to begin with. However if you look at the report above, you will notice that many of the data elements are text fields and you will need to ensure that these all align quite nicely. It might be difficult to discern with the eye - for example, a location of a cell might be 2.51322 vs. 2.5 and that small discrepancy can throw things off in the export to Excel. The following guidelines should therefore be used when creating SSRS reports (in general in terms of overall quality but obviously with extra emphasis when you know the report will be exported to Excel):

  • Ensure alignment - you should use the text box properties to ensure that cells or aligned exactly
  • Use rectangles extensively for grouping. It is quite common for different parts of a report to have different formatting structures. Using rectangles for grouping helps excel cope with changes to the format.
  • If you are using sub-reports in your main report - ensure the page size of your sub-report adheres to the size of the parent report and once again - I'd recommend using a rectangle to enclose the sub-report
  • Be careful with page breaks before or after sections. When excel encounters a page break it will create a new sheet.

One other important feature to be aware of is that you can also control the name that Excel assigns when creating a new sheet. As illustrated in the screenshot above - the export created default Sheet1, Sheet2 etc. sheet names. You can refer to the following post for a detailed write up of this feature (only available in RS 2008 R2). In my case, I just entered the page details as follows:

The end result is much cleaner looking export: