Wednesday, July 07, 2010

CRM Export Customization Error and Workflow "Query Builder Error - No Entity"

I was recently promoting some significant customization changes from one environment (DEV) to another (UAT). Since the changes involved some physical name changes of a few CRM entities and attributes, so I have to delete all those involved entities and import the customizations that I have exported from DEV. The import on UAT was successful, and the application simply runs fine until I was trying to export all customizations on UAT, which gives me the following stunning error message - "The entity with ObjectTypeCode = 100xx was not found in the MetadataCache":
<?xml version="1.0" ?>
<error xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<code>0x80041102</code>
<displaytitle>Query Builder Error</displaytitle>
<displaytext>No Entity</displaytext>
<description>The entity with ObjectTypeCode = 10088 was not found in the MetadataCache.</description>
<file>Not available</file>
<line>Not available</line>
<details>The entity with ObjectTypeCode = 10088 was not found in the MetadataCache.</details>
<requesturl>http://MyCrmServer/CrmOrgName/_grid/cmds/dlg_exportcustomizations.aspx</requesturl>
<pathAndQuery>/CMIS-UAT/_grid/cmds/dlg_exportcustomizations.aspx</pathAndQuery>
<source>XML</source>
<stacktrace />
</error>
ObjetTypeCode Not Found
The story doesn't just end here. When I tried to click "Workflows" link in my CRM's Settings area, I got the following beautiful "Query Builder Error – No Entity" CRM screen.
Query Builder Error
After a little search on Internet, I found that CRM MVP David Yack has documented this error, which pointed me to the right direction. Based on his information, I was able to fix the problem by using the following procedures.
  1. Launch SQL Management Studio and connect to CRM database.
  2. Determine which workflow is causing the problem by using the following SQL script.
    SELECT * FROM WorkflowBase 
    WHERE PrimaryEntity='10088' -- The entity code that caused the problem
    By looking at the returned record and its Name column, which is the workflow's name, you should know which workflow is causing the problem, at the same time you should be able to figure out which entity is actually causing the problem. For instance, you have figured that 'new_myentity' is the culprit.

  3. Determine the offending entity's ObjectTypeCode by issuing the following SQL script.
    SELECT ObjectTypeCode FROM MetadataSchema.Entity
    WHERE Name='new_myentity' -- The entity's name that caused the problem
    You should now get an integer code, so that we can use next. For instance, we got a number of 10095. 

  4. Run the following SQL script to correct the issue. 
    UPDATE WorkflowBase
    SET PrimaryEntity='10095' -- The correct entity code (The code that you got from step 3)
    WHERE PrimaryEntity='10088' -- The entity code that caused the problem
    You should expect a few records to be updated depending on how many workflows were involved. 
After you have done the above procedures, you should be able to do full customization export, and also you should be able to manage your workflows again.

BE ADVISED, any direct change made to CRM database could cause potential problem to the application, make sure to have a full database backup before doing so.

The cause of the problem might be that as soon as I finished deleting old CRM entities on the UAT environment, I immediately imported the customizations. CRM server might not have actually cleaned up the metadata cache at the point of the import, which ends up the orphan workflow records in CRM database.

Hope this helps if you ever run into the same error.

Sunday, July 04, 2010

Release: MSCRM4 Web Service Toolkit for JavaScript v2.1

Today I am pleased to announce the release of CRM Web Service Toolkit for JavaScript v2.1. The new release includes the following enhancements:
  • All the major functions now support asynchronous calls through an optional callback function parameter. When the callback function is provided, the toolkit will perform an asynchronous service call, otherwise it would be a synchronous call. 
  • A new function setState has been added to the toolkit, which allows you to update a CRM record's status.
  • Two functions (associate, disassociate) have been added to the toolkit, which you can use to associate or disassociate two CRM records that have an N:N relationship. 
  • The signature of queryByAttribute function has been changed, so it takes one parameters now, instead of a bunch of optional parameters in previous version.
In order to make use of the toolkit, you can refer to my previous release page for sample code of all major functions.

Here are a few additional samples that can help you get up to speed with the new release.
  1. Use the optional asynchronous callback function. As just mentioned, all major functions now support asynchronous calls. The asynchronous callback function should take single parameter which is whatever you are expecting to get by using the synchronous call. For instance, if you make an asynchronous call to CrmServiceToolkit.Fetch() method, your callback function should be dealing with the fetch result which is an array of BusinessEntity as the single parameter. Here is a quick sample.
    // callback function
    function fetchCallback(fetchedContacts) {
        alert(fetchedContacts.length);
        alert(fetchedContacts[0].getValue('lastname'));        
        alert(fetchedContacts[0].getValue('creditlimit'));
        alert(fetchedContacts[0].getValue('creditlimit', 'formattedvalue'));
        alert(fetchedContacts[0].getValue('birthdate'));
    };
    
    // Fetch all contact records whose first name is John using FetchXML query
    var firstname = 'John';
    var fetchXml = [
    "<fetch mapping='logical'>",
       "<entity name='contact'>",
          "<attribute name='contactid' />",
          "<attribute name='firstname' />",
          "<attribute name='lastname' />",
          "<attribute name='creditlimit' />",
          "<attribute name='birthdate' />",
          "<filter>",
             "<condition attribute='firstname' operator='eq' value='", firstname, "' />",
          "</filter>",
       "</entity>",
    "</fetch>"
    ].join("");
    
    // Use CrmServiceToolkit.Fetch() to make an asynchronous call.
    var fetchedContacts = CrmServiceToolkit.Fetch(fetchXml, fetchCallback);
    
  2. Use CrmServiceToolkit.setState() to update a CRM record's status.
    // Use CrmServiceToolkit.setState() to update a CRM record's status. 
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C';
    var response = CrmServiceToolkit.setState('contact', contactId, 'Inactive', 2);
    alert(response);
  3. Use CrmServiceToolkit.associate() to associate two CRM records that have an N:N relationship.
    // Use CrmServiceToolkit.associate() to associate two CRM records that have an N:N relationship. 
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C';
    var orderId = '3210F2AF-1630-EB11-8AB1-0003AAA0126A';
    var response = CrmServiceToolkit.associate('contactorders_association', 'contact', contactId, 'salesorder', orderId);
    alert(response);
  4. Use CrmServiceToolkit.disassociate() to disassociate two CRM records that have an N:N relationship.
    // Use CrmServiceToolkit.disassociate() to disassociate two CRM records that have an N:N relationship. 
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C';
    var orderId = '3210F2AF-1630-EB11-8AB1-0003AAA0126A';
    var response = CrmServiceToolkit.disassociate('contactorders_association', 'contact', contactId, 'salesorder', orderId);
    alert(response);
  5. Use CrmServiceToolkit.queryByAttribute() to retrieve all CRM records that match the query criteria.
    // Use CrmServiceToolkit.queryByAttribute() to retrieve all CRM records that match the query criteria. 
    var queryOptions = {
        entityName : "contact",
        attributes : ["firstname", "lastname"], // Search by firstname and lastname
        values : ["John", "Smith"], // Find all contacts whose firstname is John, lastname is Smith
        columnSet : ["familystatuscode", "creditlimit", "birthdate"],
        orderby : ["creditlimit", "birthdate"]
    };
    
    var fetchedContacts = CrmServiceToolkit.queryByAttribute(queryOptions);

For all the rest functions, you should be able to find related sample code from my previous release page.

[CREDITS]
  • The idea behind CrmServiceToolkit.BusinessEntity was inspired by Ascentium CrmService JavaScript Library, after I have finished most of version 1.0 coding. Hats off to Ascentium CRM practice team.
  • Thanks to Daniel René Thul for his contribution on the implementation of asynchronous support in this release.
P.S. Please excuse me that I have to break my word in the previous release page, guess this would be the last release before CRM5. :-)

[Update - Oct 16, 2010] A bug fix has been included in the release so that the BusinessEntity can work properly with null value now.

Thursday, July 01, 2010

MSCRM 4.0: Recover "Create New" Button for the Associated View of an Invoiced/Active Contract Record or an Inactive Record of CRM Custom Entity

If you have worked with CRM 4.0 long enough, you might have noticed a behavior designed by CRM team, which is when a CRM record has been deemed to be read only, all its associated views (except the activity ones) will not have any "Create New" or "Add Existing ..." button. For instance, my Contract entity has a one-to-many relationship to a custom entity called Payment. The Payment entity is designed to collect payments from my client for the contract record. When a CRM contract is invoiced or activated, the associated views for the payment entity will not have any "Create New" or "Add Existing ..." button (shown below) as CRM platform has determined that this contract record is no longer a draft one, CRM users are not supposed to create any new child records.

Associated View of Invoiced CRM Contract

This makes sense, but in the case that I have my custom Payment entity involved, I do want to collect and record Payment information as the Contract goes, even after it has been activated or invoiced. Is it possible to have the Create New button back in this case? The answer is YES, you can do it through HTML hack. The following is the script that you can copy to your form’s onLoad event.

/**
 * Recover "New" button in a CRM associated view where the master CRM record is read-only.
 * @author Daniel Cai, http://danielcai.blogspot.com/
 *
 * Parameters:
 * @param navItemId: LHS navigator's HTML element ID of the associated view.
                     It usually starts with "nav".
 * @param relName:   The relationship name that the associated view represents.
 * @param label:     The label of the New button.
 * @param title:     The title of the New button.
 */
 function recoverNewButtonForAssociatedView(navItemId, relName, label, title) {
    var clickActionPattern = /loadArea\(['"]{1}([A-Za-z0-9_]+)['"]{1}\).*/;
    var iframe;

    var recoverNewButton = function() {

        var frameDoc = iframe.contentWindow.document;
        if (!frameDoc) return;

        var grid = frameDoc.all['crmGrid'];
        if (!grid) return;

        var addNewBtnId = formatString('_MBlocAddRelatedToNonForm{0}{1}GUID', grid.GetParameter('otc'), crmForm.ObjectTypeCode);
        var addNewBtn = frameDoc.getElementById(addNewBtnId);
        if (addNewBtn === null)
        {
            var menuBar = frameDoc.getElementById('mnuBar1');
            if (!menuBar) return;
            
            var toolbar = menuBar.childNodes[0].childNodes[0].childNodes[0].childNodes[0];
            
            var html = "<li id=_MBlocAddRelatedToNonForm{0}{1}GUID class=ms-crm-Menu title=\"{3}\" tabIndex=-1 onclick=window.execScript(action) " +
    "action=\"locAddRelatedToNonForm({0},{1},'{2}', '')\">" +
    "<span class=ms-crm-Menu-Label><a class=ms-crm-Menu-Label tabIndex=-1 onclick=\"return false;\" href=\"javascript:onclick();\" target=_self>" +
    "<img class=ms-crm-Menu-ButtonFirst tabIndex=-1 alt=\"{3}\" src=\"/_Common/icon.aspx?objectTypeCode={0}&iconType=DBGridIcon&inProduction=1&cache=1\">" +
    "<span class=ms-crm-MenuItem-TextRTL tabIndex=0>{4}</span></a></span>";
            html = formatString(html, grid.GetParameter('otc'), crmForm.ObjectTypeCode, crmForm.ObjectId, title, label);
            toolbar.innerHTML = html + toolbar.innerHTML;
        }
    };

    var onReadyStateChange = function() {
        if (iframe.readyState === 'complete') {
            recoverNewButton();
        }
    };

    (function init() {
        if (!crmForm.ObjectId) return;

        var navItem = document.getElementById(navItemId);
        if (!navItem) return;

        var clickAction = navItem.getAttributeNode('onclick').nodeValue;
        if (!clickAction || !clickActionPattern.test(clickAction))
            return;

        var areaId = clickAction.replace(clickActionPattern, '$1');

        navItem.onclick = function loadAreaOverride() {
            loadArea(areaId);

            iframe = document.getElementById(areaId + 'Frame');
            if (!iframe) return;

            iframe.attachEvent('onreadystatechange', onReadyStateChange);
        }
    })();
}

To call the above function, you can do something like this.

recoverNewButtonForAssociatedView('nav_new_contract_payment', 'new_contract_payment', 'New Payment', 'Add a new Payment to this record');

If you get everything right, your associated view should look like this:

Associated View of Invoiced CRM Contract with New Button

A few final notes about the script before we go:

  • The script opens up the possibility to create or update information for any CRM record that is read-only, by using a child entity assuming that the user has proper privileges against the child entity.
  • The script should work for any 1-to-many relationship if you have provided navItemId and relName parameters correctly.
  • The script doesn't actually check if the login user has the proper privilege to create new record for the associated entity. The New button will show regardless the current user has the privilege or not, but the CRM platform should prohibit Save happening if the user doesn't actually have the Create privilege for the associated child entity.
  • Undocumented CRM JavaScript function formatString was used to make the code easier to read.
  • The script doesn't look very pretty due to the lengthy HTML code, but it should be quite readable, in my humble opinion.  :-)

Hope this helps.

P.S. This blog post is actually a response to a question on CRM Development Forum.