MSCRM vs Normalization?

Today I ran into a silly discussion about Microsoft Dynamics CRM and normalization in a meeting. A senior consultant in my team made a statement that "MSCRM database is not normalized" during the meeting. I was astonished and shocked when I heard this statement. I challenged him and asked him to prove where his point stands. He claimed that CRM picklist metadata is not normalized. Well, that's sort of true, we cannot re-use a picklist across entities in CRM 4.0, and I told him that's not going to be the case in CRM 2011 which has the support of global option set so that you can define reusable picklist options. He didn't give in to that because the project we were discussing is still using CRM 4.0. The silly part of the discussion was, I firmly believed that MSCRM platform supports and encourages normalization in every possible aspect, but his point was that MSCRM is totally the opposite of database normalization (just because of the picklist options), which was so wrong.

Let me have a quick summary about my points:

• Microsoft Dynamics CRM database itself has respected database normalization principle in general. But some CRM metadata tables and some few business entities are not fully normalized for either performance reasons, or due to constraints of the platform.
• Microsoft Dynamics CRM fully utilizes relational data model which is the foundation of the platform.
• Microsoft Dynamics CRM fully encourages normalization through its flexible entity relationship models including one to many, and many to many. Those relationships are not only available to two different entities, but also can be applied to the same entity through self-referential relationship. What level of normalization that your custom application can achieve on CRM platform really depends on how you design your CRM entities and their relationships. You use the relationships between entities to normalize your data, which is an almost identical approach to what you would do in any traditional data modeling practice, where a CRM entity can be seen as a database table, while a CRM field (or attribute) can be seen as a table column. In this sense, CRM entity & database table are pretty much a pair of interchangeable terms, so are CRM field & table column.
• If you are currently using CRM 4.0, and you want to reuse some picklists. An alternative solution would be using a lookup entity, which contains all the options, so they can be re-used and referenced by different entities. If you want to provide better usability, you can use a script from one of my other blog posts to convert the lookups into picklists. 

When it comes to the discussion about the level of database normalization that we should be actually pursuing in the real-world project, I have seen some people really become religious simply because that's what they have been told from the textbook. I don't want to start a war here, but I do want to make my points quickly, my take or solution to this issue is, normalize to the point that your data model looks logic enough so that it reflects and satisfies your business requirements, while not sacrificing the scalability and maintainability of your application.

Cheers!

Solution: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Today, after I uploaded one of my ETL data integration components (written in C#) from local 32-bit Windows System to our server environment which is running on Windows 2008 R2 64-bit system, my application blew up with the following error message:

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

The reason for this error is, I used Jet database engine in my ETL component to read Excel file, but the Jet library was not designed for 64-bit system. The community has been suggesting to compile the code to target to x86 platform in order to cope with this issue, which is something I was reluctant to do for the obvious reasons. It's also the primary motivation that I am blogging about this issue.

Further Google search indicates that Microsoft has released a 64-bit compatible Jet database engine last year. The following is the procedure that you may use to fix this issue if you have:

1. Download Microsoft Access Database Engine 2010 Redistributable (of course you'll need to choose the right bit for your server), and install it on your server
   
   
2. Change your connection string in your code or configuration file from
   Provider=Microsoft.Jet.OLEDB.4.0;
   to
   Provider=Microsoft.ACE.OLEDB.12.0;

UPDATE: Microsoft has released a service pack for the Database Engine which is available at  http://www.microsoft.com/en-us/download/details.aspx?id=26605.

After I have done the above, my ETL component worked fine on the 64-bit system.

Hope this helps if you ever run into this issue.

MSCRM 4.0: Filtered Lookup for "Add Existing..." Button of a CRM N:N View

There has been a question on CRM Development Forum today about whether it's possible to implement a many to many filter lookup functionality.

About one and half years ago, I blogged an approach to implement filtering functionality for lookup fields on CRM form, using George Doubinski's invention. Today, I am going to take this approach one step further, and show you how to apply the same technique to achieve our purpose for a CRM associated view that represents a many-to-many relationship. The CRM form and its associated view could be something similar to the following screenshot.

In the above scrren, I made up a custom entity called Project, which has a N:N relationship with Account entity. My requirement is to only show the account records that have an industry code of "Service Retail" (Again, this is what I came up, your story could be entirely different).

Since I have previously documented how to load CRM associated view in an iframe, I am not going to repeat the same code here.

The following is the procedure that you may follow to implement the aforementioned N:N filtering lookup, which is fairly similar to the filtering functionality for lookup fields on CRM form:

1. Create a subfolder called CustomLookup under your CRMWeb's ISV folder.
2. Copy <crmweb folder>\_controls\lookup\lookupsingle.aspx page to your ISV\CustomLookup folder which you have just created, and renamed the file to lookupmulti.aspx.
3. Add the following script tag to <crmweb folder>\ISV\CustomLookup\lookupmulti.aspx (Please thank George for his code, this is basically a copy from his snippet with one extra line that I added to enable multi-select).   

   <%--
   ********************************** BEGIN: Custom Changes **********************************
   This page is a copy of <crmweb folder>\_controls\lookup\lookupsingle.aspx file 
   with the following custom change. 
   --%>
   <script runat="server"> 
   protected override void OnLoad( EventArgs e ) 
   { 
         base.OnLoad(e); 
         crmGrid.PreRender += new EventHandler( crmgrid_PreRender ); 
   } 
   
   void crmgrid_PreRender( object sender , EventArgs e ) 
   {
       // As we don't want to break any other lookups, ensure that we use workaround only if
       // search parameter set to fetch xml.
       if (crmGrid.Parameters["search"] != null && crmGrid.Parameters["search"].StartsWith("<fetch")) 
       { 
           crmGrid.Parameters.Add("fetchxml", crmGrid.Parameters["search"]);  
   
           // searchvalue needs to be removed as it's typically set to a wildcard '*' 
           crmGrid.Parameters.Remove("searchvalue");
   
           // Allow multi-select
           crmGrid.MaximumSelectableItems = -1;
   
           // Icing on a cake - ensure that user cannot create new new record from the lookup window
           this._showNewButton = false; 
       } 
   }
   </script> 
   <%--
   ********************************** END: Custom Changes **********************************
   --%>

   
   
4. Use the script in my previous blog post to load the associated view in iframe. The script should be added to your CRM form's onload event.
5. Then you are ready to add filtering criteria to your many-to-many associated view lookup button. In my case, my requirement is to only show the account records that have an industry code of "Service Retail" (its picklist integer value is 25) as I have just mentioned above.
   

   getFilteredUrlForAddExistingAccountButton = function(lookupUrl) {
       var iframe = crmForm.all.IFRAME_Accounts; // Change IFRAME_Accounts to your iframe's ID
       var crmGrid = iframe.contentWindow.document.all['crmGrid'];
       
       // If it's not N:N lookup dialog, we skip it. 
       if (lookupUrl.toLowerCase().match(/\/_controls\/lookup\/lookupmulti.aspx/i) == null)
           return lookupUrl;
       
       // If the lookup window is not concerned with the entity that we are interested in, we skip as well
       if (GetQueryString(lookupUrl, 'objecttypes') !== crmGrid.GetParameter('otc'))
           return lookupUrl;
   
       lookupUrl = lookupUrl.replace(/\/_controls\/lookup\/lookupmulti.aspx/i, '/ISV/CustomLookup/lookupmulti.aspx'); 
       
       var filterXml =
   '<fetch mapping="logical">' +
      '<entity name="account">' +
         '<filter>' +
            '<condition attribute="industrycode" operator="eq" value="25" />' + // Industry: Service Retail
         '</filter>' +
      '</entity>' +
   '</fetch>';
   
       // Ensure that search box is not available in the lookup dialog
       lookupUrl = SetQueryString(lookupUrl, 'browse', 1);
       lookupUrl = SetQueryString(lookupUrl, 'ShowNewButton', 0);
       lookupUrl = SetQueryString(lookupUrl, 'search', filterXml);
   
       return lookupUrl;
   };

   
   
6. Add the following immediate JavaScript function to your form's onload event, following the above script
   

   (function replaceCrmLookups() {
       window.oldOpenStdDlg = window.oldOpenStdDlg || window.openStdDlg;
       window.openStdDlg = function() {
           arguments[0] = getFilteredUrlForAddExistingAccountButton(arguments[0]);
           return oldOpenStdDlg.apply(this, arguments);
       };
   })();

   
   
7. Save your form and publish your entity, you are good to go now.
   
   In my case, after I have everything published, my "Add Existing Account" now prompts me the following screen. As you can tell, I intentionally changed the default many-to-may lookup dialog to the single lookup dialog, which is usually considered to be much more user friendly. I believe we have just shot two birds with one stone, isn't that something beautiful?
   

Note that you should change the iframe ID in the first line of getFilteredUrlForAddExistingAccountButton() function, also ensure that you have constructed filterXml string correctly in the same function.

I hope this helps.

Error When a Picklist Option Deleted but with Data Referenced

Today I ran into a strange error when I was trying to open a CRM view. I got a screen shown as below (In case you are curious, I have CRM DevErrors option turned On in my web.config file in order to show CRM error in the following fashion):

Basically CRM was complaining with the following message: 

Microsoft CRM Error Report: 
Error Description: 
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Error Details: 
Exception of type 'System.Web.HttpUnhandledException' was thrown.

Full Stack: 
[KeyNotFoundException: The given key was not present in the dictionary.]
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.Crm.BusinessEntities.PagingHelper.GetValueAsString(AttributeInfo attribute, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.PagingHelper.Serialize(OrderExpressionCollection orderCollection, BusinessEntityCollection entities, Int32 pageNum, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.PagingHelper.CreatePagingCookie(BusinessEntityCollection entities, EntityExpression entityExpression, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.QueryProcessObject.AppendPagingCookie(XmlTextWriter xmlWriter, BusinessEntityCollection entities, EntityExpression entityExpression, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.QueryProcessObject.CreateResultSetXmlFromResultEntities(List`1 resultEntities, EntityExpression entityExpression, Boolean moreRecords, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.QueryProcessObject.RetrieveDataQueryAsResultXml(EntityExpression entityExpression, QueryStrategyType strategyType, ExecutionContext context)
at Microsoft.Crm.BusinessEntities.QueryProcessObject.RetrieveQueryAsResultXml(EntityExpression entityExpression, QueryStrategyType strategyType, ExecutionContext context)
at Microsoft.Crm.ObjectModel.SavedQueryService.Execute(String fetchXml, ExecutionContext context)

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IPluginExecutionContext context)
at Microsoft.Crm.Extensibility.PluginStep.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.Pipeline.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.MessageProcessor.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.InternalMessageDispatcher.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.ExternalMessageDispatcher.Execute(String messageName, Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, PropertyBag fields, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
at Microsoft.Crm.Sdk.RequestBase.Process(Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
at Microsoft.Crm.Sdk.RequestBase.Process(CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
at Microsoft.Crm.Sdk.CrmServiceInternal.Execute(RequestBase request, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
at Microsoft.Crm.Sdk.InProcessCrmService.Execute(Object request)
at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.ExecuteInternal()
at Microsoft.Crm.Application.Platform.ServiceCommands.ExecuteFetchCommand.Execute()
at Microsoft.Crm.ApplicationQuery.GetViewData()
at Microsoft.Crm.Application.Controls.AppGridDataProvider.GetDataXml(QueryBuilder qb)
at Microsoft.Crm.Application.Controls.AppGridDataProvider.Execute()
at Microsoft.Crm.Application.Controls.AppGridUIProvider.Render(HtmlTextWriter output)
at Microsoft.Crm.Application.Components.UI.DataGrid.RenderData(HtmlTextWriter output)
at Microsoft.Crm.Application.Components.UI.DataGrid.RenderInnerHtml(HtmlTextWriter output)
at Microsoft.Crm.Application.Components.UI.DataGrid.Render(HtmlTextWriter output)
at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
at Microsoft.Crm.Application.Components.UI.CrmUIControlBase.RenderControl(HtmlTextWriter writer)
at ASP.cmis__root_homepage_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)
at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
at System.Web.UI.Page.Render(HtmlTextWriter writer)
at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown.]
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.cmis__root_homepage_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The problem was almost impossible to identify. Even I tried to turn on trace log, I cannot see any helpful message. But fortunately I quickly realized that our business analyst folks have recently deleted an option from a picklist field of this entity. However, there are some data in the system that are still referencing that option. As soon as I realized the cause, it wasn't too hard to fix this issue. What I did was, add back the option that we previously deleted using the same integer option value, publish the entity customization change, and then delete all data referencing that picklist option, and then I can safely delete the same picklist option.

There could be a hundred of different scenarios that could lead to "The given key was not present in the dictionary", the key to determine whether the solution applies to you, is to check that you have "Microsoft.Crm.BusinessEntities.PagingHelper.GetValueAsString(AttributeInfo attribute, ExecutionContext context)" message in the error log, and the error happens when you click to open a CRM view.

I was hoping that CRM platform could have handled this more gracefully, but not a big deal if you know the solution.

Hope this helps if you ever come across the same issue.

Cheers!