Cannot add more picklist or bit fields

I came across this issue today in a 4.0 environment that still uses SQL 2005. Time to upgrade, huh? Happily that's soon to be the case - so this hopefully is just for the record books.

Anyway the symptom was that while the user was able to add nvarchar fields to the accounts entity, it would error out each time the user wanted to add a new bit field (or for that matter a picklist) it would fail with an error message.

Using the trace tool it took pretty quick to identify the underlying cause. The following exception appeared in the trace file:

Exception: System.Data.SqlClient.SqlException: Too many table names in the query. The maximum allowable is 256.

So what is causing the "too many table names" in query?

Simple. Every time an attribute is added to an entity, the entity views (regular entity view and filtered view) are updated. The filtered view in particular joins to the StringMap view for picklists and bit fields to obtain the corresponding friendly "name" field. For example, for an account field called "new_flag" it will join to StringMap view to create a new virtual field in the FilteredAccount called "new_flagname".

One only needed to look up a little higher in the trace file to see the view being constructed with many joins for the bit and picklist fields. Such that if the number of these two types of field combined exceeds 256 (or thereabouts given other joins that may already exist) it will cause this join limitation to occur. Which is only a limitation on SQL 2005. This is no longer a limitation from SQL 2008 and up.

The options for resolving this issue are therefore:

1. Upgrade. Really. The technology you are using is around 7 years old (at least) and there does come a point where the compelling reason to upgrade is just the combined benefits of all the various enhancements that have been introduced over time (that is, if you cannot find a single compelling reason).
2. Clean up your system and review whether you actually do need all those fields in your environment. This actually is relevant whether you upgrade or not. I'm a big proponent of keeping the environment as clean as possible as my first post on this blog will attest to (disclosure: the above environment is extended directly by the client as we like to encourage our clients to do so they are not reliant on us for every little change required).

Passing Execution Context to Onchange Events

In a previous posting I provided some jscript that can be used to validate phone number formats. In order to invoke the validation for a phone number field I mentioned that you need to create an "on change" event that would pass in the attribute name and attribute description i.e.:

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

I thought this would provide a good example for demonstrating the ability to use the execution context because you can obtain the field name and label (by extension) via the execution context which on the surface is a good thing since you avoid hard-coding as in the example above (and you can apply this to all phone number fields in the system). And therefore in theory you could simplify that example as follows:

function PhoneNumberValidation(context) {
var phone = context.getEventSource().getName();
var phoneDesc = Xrm.Page.getControl(context.getEventSource().getName()).getLabel();
 var 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.");
  Xrm.Page.ui.controls.get(phone).setFocus();
  ret = false;
 } else {
  if (stripPhone.length == 10) {
   phone2 = "(" + stripPhone.substring(0,3) + ") " + stripPhone.substring(3,6) + "-" + stripPhone.substring(6,10);
  } else {
   phone2 = stripPhone;
  }
 
 }
 Xrm.Page.getAttribute(phone).setValue(phone2);
 return ret;
}

The only difference is that instead of the "phone" and "phoneDesc" parameters being passed into the validation function, the execution context is instead passed in and the phone attribute and its corresponding phoneDesc label are obtained via the context as local variables. The rest stays the same.

In order for this to work, you would update the "on change" event to call the PhoneNumberValidation function directly and check off the "pass execution context as first parameter" as shown:

So that's the theory and I think it demonstrates quite nicely how the execution context can be used. 

Having said that, in this particular example, I prefer using the explicit technique referenced in the original posting. The reason for this is because the on change event in this validation example (and probably relevant for most data validation cases) has a dual function - 

The first is to provide the necessary validation as part of the field on change event as the example above will accomplish quite well. 

The second is to be called from the on save event to make sure that even if users ignore the message from the on change event they will not be able to save the form via the validation from the on save event (the PhoneNumberValidation function returns a true or false value to indicate whether validation was passed or not). And when the function is called from the on change event the specific field context is not going to be there anyway making it necessary to put in some additional logic in order to handle correctly. Therefore what you gain from using the execution context in this example is likely to be offset by requirements for special handling required by the on save event. 

Dynamics CRM Connectivity and Firewall Requirements

The Dynamics CRM team released a new white paper providing guidance for the connectivity requirements for typical Dynamics CRM on premise installations. It covers both regular on premise and IFD scenarios.

I don't think there's any earth shattering revelations made in this white paper and given that it is choc-full of riveting port settings requirements it is a real page turner (thankfully relatively short at 12 pages). All kidding aside this document is a good reference to file somewhere in your brain that you can pull out especially for those cases where the IT department has very rigid controls over their internal IT infrastructure.

I thought the following diagram (page 6) to be especially helpful as a visual guide although I'm not sure the dotted line between the client machine and the SQL Server is entirely accurate. I would think that it's necessary for all reporting requirements and not just for the Excel export function. That's unless you're not planning on having any custom reports or that all report development will be performed via RDP on the SQL server. Either way, I think it is unlikely in most installations for this to be a "dotted" line.

Other than that I also think the table on the following page provides a good summary that helps explain the overall picture to system administrators unfamiliar with the Dynamics CRM deployment footprint.

The white paper can be downloaded from here.

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

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:

<person>
  <firstName>John</firstName>
  <lastName>Smith</lastName>
  <age>25</age>
  <address>
    <streetAddress>21 2nd Street</streetAddress>
    <city>New York</city>
    <state>NY</state>
    <postalCode>10021</postalCode>
  </address>
  <phoneNumbers>
    <phoneNumber type="home"> 212 555-1234</phoneNumber>
    <phoneNumber type="fax">646 555-4567</phoneNumber>
  </phoneNumbers>
</person>

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 

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

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.

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.

Interesting.

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.

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.

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.