Data Import Options

When discussing data imports to CRM there are 2 distinct scenarios:

Data Migration

Data Migration generally refers to a large scale conversion effort and is typically performed as part of a CRM Go Live effort where data is migrated from the legacy system and is usually quite an involved effort. This exercise is technical in nature, typically performed by IT professionals and requires a great attention to detail. Correspondingly the migration logic is also usually quite involved. Meaning that there can be quite a bit of data transformation as part of the migration exercise. And if migrating from another system, you typically want to be able to connect to the data in the legacy database rather than extracting the data to an intermediate CSV file. So as a general rule of thumb when doing this type of data migration you typically want to be using a tool like Scribe to make it happen.

Importing Data

Importing Data generally refers to a more specific one-time or ongoing requirement to import data into a live CRM environment. For example, the need to import new leads into the CRM database. Typically this is a more straight-forward "one-to-one" exercise (i.e. no data transformation required) and if this is the case, the out of the box "Data Import Wizard" can be used for this function.

One thing to note is that while the "Data Import Wizard" has advanced by leaps and bounds, it still has one chief limitation - it only can import new records. If you're looking to use the wizard to update existing records, I'm afraid you're fresh out of luck. And if that is a requirement you'll once again need to look for a 3rd party tool such as Scribe.

For an excellent general overview of the features and functions of the Data Import Wizard please refer to this post. At the end of the post it lists a number of 3rd party tools that can be used to fill the gap in functionality of the Data Import Wizard should you encounter it. Although none of these products have what I would term a "no-brainer" price. Below is a rough comparison in pricing of the various tools on the market (as of this writing):

1. Scribe: 
• $3000: 15 user license
• $5500: 100 user license
• $1900: 60 day migration license
2. Inaport: 
• $1799: standard (should be sufficient for import function)
• $3499: professional
• $1195: 30 day migration  "professional" version which is apples to apples comparison for the Scribe migrate license)
3. Import Manager (best option if end user interface is required)
• ~$2500
4. Import Tool (attractive pricing) 
1. ~$1300: Full Version
2. ~$325: 60 day migration license

The following tools don't seem to be realistic candidates based on their price point:

1. Starfish 
• $4800/year: Basic Integration
• $1495: 60 day migration license
2. eOne SmartConnect 
• $4500 (not sure if this is a one time or per year fee)
3. QuickBix 
• $7000: 100 user license ($70/user)
4. Jitterbit
• $800/month: Standard Edition
• $2000/month: Professional Edition
• $4000/month: Enterprise Edition

The following tools do not seem to fulfill the requirement:

1. CRM Migrate
• Tool specifically built for migrating SalesForce to CRM
2. CRM Sync: 
• Very little information and no pricing for this tool

object doesn't support property or method '$2b'

We encountered this strange error in a CRM online environment. The symptoms were as follows:

1. Only occurred on the Outlook client (not in IE)
2. Occurred even when form jscript was disabled indicating issue was not with the script
3. Appeared when closing out the contact form

The error message looked as follows:

After a bit of scratching around the solution presented in the following forum discussion seemed to work. Or in short as follows:

1. Close Outlook and IE
2. Open IE and delete temporary files (Make sure to uncheck “preserve favorites website data”)
3. Now if you open outlook, you might see that On the Grid's Ribbon is loading... WAIT for it to load
4. Open the Account, Lead, Appointment

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.