Thursday, December 30, 2010

MSCRM 4.0: LINQ to FetchXML Queries

When it comes to querying data from Microsoft Dynamics CRM server, I am a much big fan of FetchXML if compared to CRM Query Expression. There are a few reasons for that:

  1. FetchXML is more powerful, it can do something that Query Expression may not be able to do the same, such as aggregation, conditional criteria in linked entity, etc.
  2. FetchXML is more readable and more maintainable, it's much easier to add one or more extra attributes to be returned, or in a different scenario that you may want to add one or more new query criteria.
  3. FetchXML is more intuitive, and it could make your code refactoring effort a lot easier when you need to do so.

But FetchXML does come with a caveat, you need to deal with the XML string that MSCRM server responds back to you. There are a number of CRM SDK documents telling you how to perform a FetchXML query, but usually they all stopped at the point that you get the result XML back from CRM server. You don't actually have any clue about the next step, which is how you should process and manipulate the result in XML format in an efficient and elegant fashion.

In this blog post, I am going to show you a short snippet code that tells how you can properly do this, using the two XElement extension methods that I shared in my previous blog post.

Suppose that you have a requirement to get all CRM accounts in Washington state (WA), so you came up with the following FetchXML query:
<fetch mapping='logical' count='10'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='numberofemployees' />
      <attribute name='owninguser' />
      <attribute name='customertypecode' />
      <attribute name='donotemail' />
      <attribute name='createdon' />
      <filter>
         <condition attribute='address1_stateorprovince' operator='eq' value='WA' />
      </filter>
   </entity>
</fetch>

And CRM may return a result like the following one:
<resultset morerecords="0" paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{4BC7A5F8-AD02-DE11-83DE-0003FFE51F61}&quot; first=&quot;{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}&quot; /&gt;&lt;/cookie&gt;">
   <result>
      <name>AB Company</name>
      <numberofemployees formattedvalue="95">95</numberofemployees>
      <customertypecode name="Customer" formattedvalue="3">3</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Baldwin Museum of Science</name>
      <numberofemployees formattedvalue="250">250</numberofemployees>
      <customertypecode name="Customer" formattedvalue="3">3</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{3AC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Blue Yonder Airlines</name>
      <numberofemployees formattedvalue="150">150</numberofemployees>
      <customertypecode name="Other" formattedvalue="12">12</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{45C7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Brown Company</name>
      <numberofemployees formattedvalue="39">39</numberofemployees>
      <customertypecode name="Prospect" formattedvalue="8">8</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{4AC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Budget Company</name>
      <numberofemployees formattedvalue="32">32</numberofemployees>
      <customertypecode name="Reseller" formattedvalue="9">9</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{4BC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
</resultset>
Your job, as a CRM developer, is to process the result in an Object-Oriented fashion after you have got the result from CRM server. Suppose you are a fan of LINQ query as well, you may want to do it in an elegant way by utilizing the power of LINQ query.

To help you achieve this goal, you may include the two extension methods that I just mentioned in one of your static utility classes, and then start to write your FetchXML query code. The following is a short snippet code that could be used as your start point. You may be able to tell that what I am showing you here is a snippet code from an NUnit test.
string fetchXml = @"
<fetch mapping='logical' count='10'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='numberofemployees' />
      <attribute name='owninguser' />
      <attribute name='customertypecode' />
      <attribute name='donotemail' />
      <attribute name='createdon' />
      <filter>
         <condition attribute='address1_stateorprovince' operator='eq' value='WA' />
      </filter>
   </entity>
</fetch>
";

string fetchResult = crmService.Fetch(fetchXml);
XDocument xml = XDocument.Parse(fetchResult);

var accounts = from item in xml.Root.Elements()
               select new
               {
                   AccountId = item.ParseValueAs<Guid>("accountid"),
                   Name = item.ParseValueAs<string>("name"),
                   NumberOfEmployees = item.ParseValueAs<int?>("numberofemployees"),
                   DonotEmail = item.ParseValueAs<bool?>("donotemail"),
                   CustomerTypeCode = item.ParseValueAs<int?>("customertypecode"),
                   CustomerType = item.ParseValueAs<string>("customertypecode", "name"),
                   CreatedOn = item.ParseValueAs<DateTime?>("createdon"),
               };

var createdOn = new DateTime(2008, 2, 22, 16, 0, 0);

// Get the first account returned
var firstAccount = accounts.ElementAt(0);

// Assertions for the first account returned
Assert.AreEqual(new Guid("{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}"), firstAccount.AccountId);
Assert.AreEqual("AB Company", firstAccount.Name);
Assert.AreEqual(95, firstAccount.NumberOfEmployees);
Assert.AreEqual(false, firstAccount.DonotEmail);
Assert.AreEqual(3, firstAccount.CustomerTypeCode);
Assert.AreEqual("Customer", firstAccount.CustomerType);
Assert.AreEqual(createdOn, firstAccount.CreatedOn);

As you may see, I have tried to parse customertypecode (a picklist field in account entity) in two different ways to serve different purpose. First I tried to get the integer value of the picklist field, then I tried to get the field's value attribute which is what the integer value represents for. The second value could be more meaningful to the end user of your application.

As I have mentioned in my previous blog post, you should use basic .NET CLR types as the target types when you try to parse FetchXML result.

All I have shown here is not actually something of rocket science, but hopefully it has given you enough idea how to properly parse a CRM FetchXml query result string in an easy way.

Cheers, and Happy New Year to everyone!

Sunday, December 26, 2010

From LINQ to XML to LINQ to Objects: Two Generic XElement Extension Methods

LINQ is one of the most amazing programming language innovations in the few recent years. Coming with .NET framework 3.5, there are a whole new stack of XML classes that help you manipulate XML document in your .NET program using LINQ approach.

In real-world programming practice, we often have the need to read from an XML file and convert to an enumerable list of strong-typed objects which you start to work with in your .NET application. But I have never come across a complete sample code that shows me how to do this properly in a simple way, so I came up with the following two extension methods that might help you if you need to do the same thing in your life.

/// <summary>
/// Convert the value of an XElement's child element to the specified generic type.
/// </summary>
/// <typeparam name="T">The target type to be used to convert the XML element value</typeparam>
/// <param name="xe">The XML element that contains child elements</param>
/// <param name="childNode">The child element that you want to parse its element value</param>
/// <returns>Converted value of type T</returns>
public static T ParseValueAs<T>(this XElement xe, XName childNode)
{
    return ParseValueAs<T>(xe, childNode, null);
}

/// <summary>
/// Convert the value of an XElement's child element (or one of its attributes) to the specified generic type.
/// </summary>
/// <typeparam name="T">The target type to be used to convert the XML element or attribute value</typeparam>
/// <param name="xe">The XML element that contains child elements</param>
/// <param name="childNode">The child element that you want to parse its element or attribute value</param>
/// <param name="attribute">If provided, the attribute value will be parsed. Otherwise, the element value will be parsed</param>
/// <returns>Converted value of type T</returns>
public static T ParseValueAs<T>(this XElement xe, XName childNode, XName attribute)
{
    if (xe == null)
        return default(T);

    XElement childElement = xe.Element(childNode);
    if (childElement == null)
        return default(T);

    bool valueIsEmpty = attribute == null
                            ? string.IsNullOrEmpty(childElement.Value)
                            : childElement.Attribute(attribute) == null || string.IsNullOrEmpty(childElement.Attribute(attribute).Value);

    if (valueIsEmpty)
        return default(T);

    string value = (attribute == null) ? childElement.Value : childElement.Attribute(attribute).Value;

    Type type = typeof(T);

    if (type == typeof(bool) || type == typeof(bool?))
    {
        switch (value.ToLower())
        {
            // You may tweak the following options a bit based on your needs
            case "1":
            case "true":
            case "yes":
                value = "true";
                break;

            case "0":
            case "false":
            case "no":
                value = "false";
                break;

            default:
                return default(T);
        };
    }

    TypeConverter converter = TypeDescriptor.GetConverter(type);
    return (T)converter.ConvertFromString(value);
}
Given that you have an XML file, suppose it's called contacts.xml, like this:
<Contacts>
  <Contact>
    <Name>Patrick Hines</Name>
    <Phone>408-555-1234</Phone>
    <BirthDate>1990-01-01</BirthDate>
    <Address>123 Main St, San Jose, CA, 94500</Address>
  </Contact>
  <Contact>
    <Name>Patrick Hines</Name>
    <Phone>510-444-0123</Phone>
    <Address>345 Center Ave, Milpitas, CA, 94439</Address>
  </Contact>
</Contacts>
You can use the following C# snippet to convert the XML to your LINQ objects.
XDocument xml = XDocument.Load("contacts.xml");

var contacts = from item in xml.Root.Elements()
               select new
               {
                   Name = item.ParseValueAs<string>("Name"),
                   Phone = item.ParseValueAs<string>("Phone"),
                   BirthDate = item.ParseValueAs<DateTime?>("BirthDate"),
                   Address = item.ParseValueAs<string>("Address"),
               };
I used an anonymous type for simplicity, but you can always use any defined class that you may already have in your application, so you are working with the objects that you are familiar with.

As usual, here are a few notes before we conclude this blog post:
  • You should always try to use the basic CLR types as your target type, such as int, bool, string, DateTime, etc.
  • As I have mentioned, I have offered a pair of overloaded methods, they are serving for different purpose. One of them is parsing the XML element's value, another one is parsing an XML element's attribute value. With the overloaded signatures, you have the flexibility to either read the XML element or an XML element's attribute.
  • You may want to tweak a little bit about how you want to parse into boolean value depending on your XML data and business requirement.
  • I didn't include any exception handling in the code, which you may want to add, if your XML data is unpredictable.
  • It's advisable to return nullable type, so when the XML element or attribute doesn't exist, it return null as the value, which makes more sense in most cases.

[Update - Jan 6, 2010] I updated the code so that the extension methods now support XML namespace.

If you are coming Microsoft Dynamics CRM world, I will show you in my next blog post about how to use the above extension methods to properly parse your FetchXML query result. Stay tuned.

Hope this helps, cheers!

Friday, November 26, 2010

SharpSSH: A Recompiled Version Compatible with Latest 64-bit Windows Server System

One of my recent project works is to integrate our CRM solution with a few data sources from different vendors, which requires me to develop an ETL component to download incremental update files from SFTP sites before processing them in our CRM application.

Since there is no support of SFTP in .NET framework itself, I have to look for alternative solution, so I ended up with the open source library called SharpSSH, which is pretty promising.

I was very happy that it worked with my first try on my local development VPC image which is on Windows Server 2003, 32-bit edition. But as soon as I promoted my ETL component to our UAT environment which is on Windows Server 2008 R2 (of course, 64-bit system), I ran into a "Bad Data" error when my component was trying to make connection to the SFTP site.

I did a little Google search, and found the solution here. I followed snowcoder's instructions, made a bunch of changes, and recompiled the library. Then everything worked for me on the 64-bit system.

Here are the download links, I hope that they could save you some time if you ever need this component (There were some errors in snowcode's original code he pasted there probably due to HTML formatting issue):

SharpSSH-1.1.1.13 Library (DLLs only)

SharpSSH-1.1.1.13 Source Code

[DISCLAIMER] I am by no means an expert on hardcore networking or cryptology programming (I have no intention to become one either at this moment), I am posting the recompiled library just for your convenience. All credit goes to Tamir Gal, snowcoder, and Mono Project. The license should stay as whatever it is originally.

[Further Note - Updated December 2011] There is another open source library in the community called SSH.NET, which you might want to check out. The library has got support for .NET 3.5, .NET 4.0, Windows Phone, Silverlight respectively. It looks very promising, and it has been actively maintained by the author.

Hope this helps!

Thursday, November 25, 2010

Non-sense CRM4 Customization Import Error

Here is another CRM4 customization import error that I recently ran into, but it's a non-sense and probably a nasty one.

What the problem is, I have been trying to update my development VPC image from rollup 9 to a more recent one. As soon as my VPC image is updated to any rollup after 10, I start to receive an error message when I try to import CRM customizations. The weird thing is, this error even happens in the same environment. By the same environment, I mean that it happens even I was exporting the CRM customizations and then re-importing the same customization file on the same server box.

Here is what the error message looks like:
Failure: incident_activity_parties: Cascade link type 'NoCascade' is invalid for Delete.
Nonsense Customization Import Error
Since I had a little bit cycle today and I was aware that Microsoft has just released Rollup 14 last week, I thought it would be a good chance to give it another try. I usually had pretty good confidence at Microsoft team's fast turnaround in terms of bug fixing. Unfortunately it's not the case this time, the same error persisted after I updated to Rollup 14.

I was naive enough, I thought that I could become a hero if I could find a solution for this issue. So I turned on CRM server trace log, and re-ran the import process. After about two tries (first try failed for a different reason), I got 115 log files in my CRM trace folder (they consumed 1.1GB hard drive space on my poor VPC image). However those log files didn't give me much helpful information, except another error message that was thrown before the previously mentioned message, which stated that "Failed to import entity relationship incident_activity_parties", which I don't have any clue why it has happened either.

After about 30 minutes of experimenting and Google search, I gave up on this. A few folks in the community have been suggesting to muck around the customization xml file, which I am not so encouraged to go this path, as I believe that could possibly cause more problems in the long run that it actually solves.

[Update - Feb 28, 2011] I finally figured out what the problem is, I had Data Migration Manager installed on my server. As soon as I uninstalled DMM and the problem went away. Still a mystery for me, but I can now finally go with more recent rollups.

Wednesday, November 24, 2010

JavaScript Snippet: Test Whether a JavaScript Variable is Regular Expression

Here is a short snippet if you ever need to check whether a JavaScript variable is regular expression.

function isRegExp(obj) {
   return Object.prototype.toString.call(obj) === '[object RegExp]';
};

You might be wondering why on the earth you ever need this function. It could be when you want to develop a validation framework that takes a variable for validation purpose, and you want to make the validation framework flexible as flexible as possible, so that the variable could be a function, string or even a regular expression. Your validation framework would do different thing based on the type of the validation variable that is passed to the framework. Hope this makes sense to you now, maybe you have a totally different reason for using it, which is absolutely up to you... :-)

[UPDATE - Dec 10, 2010]: A couple of hours after I published the original blog post, I figured that I should be able to use JavaScript instanceof operator to achieve the same purpose, which could be an easier solution. But further reading indicated that will not work correctly if you are evaluating a cross-frame RegExp object. If you are interested, you may continue reading a very detailed explanation with regard to a similar testing of JavaScript Array object. I have also updated my code a little bit so it looks pretty much the same as kangax's isArray function now, shamelessly. ^_^

A lesson learned from this is, you should only trust JavaScript instanceof operator if your code never has the need to go cross frames. If you want a robust solution to evaluate the type of a JavaScript object, you better take the similar approach as I have just illustrated you.

You can always expect some surprise from JavaScript. Stay tuned until next time.

Sunday, August 15, 2010

Yet Another Error when Exporting/Publishing CRM Customizations

Not long after I had a CRM customization export exception that I documented previously, I ran into another CRM customization related error. It happened to me when I try to export/publish one of my CRM entities, what I got is a screen shot like this.
Customization Export Error

The key message here is , "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. "

This is a very generic message that doesn't give me much information about the exception. So I turn on CRM trace log, and what I got from trace log was not much helpful either. It simply says, "System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.".

Microsoft knowledge base article 947096 has given a solution for this error. But I was not able to find any offending attributes by following the instruction. So I had to look for a solution for the error, here is what I have come up after digging information from Google and the CRM forum (The error occurred to me about a month ago, but I just got time to document the solution, I can not find the particular thread any more at the time I am writing this blog post, which is the primary reason that I want to write the blog post since the helpful information is not easy to find, and it was buried in a very long thread).

  1. The first thing you need to do is to identify the offending entity if you don't know yet. You may try to utilize the Binary Search algorithm that you have learned in school (or somewhere else) to help you find the entity. The way to work is, you start by publishing the first half set of all CRM entities, if you run into error, then the offending entity must belong to the first half set. If you don't run into any error, then the offending must belong to the second half set. Keep doing this until you find the one. Suppose that we have identified that new_offendingentity is the entity that caused us grief.
  2. Launch SQL Management Studio and connect to CRM database. (Make sure that you make a full backup of your CRM database before proceeding to next step)
  3. Identify the culprit CRM fields. In order to identify the columns that ceased the problem, you can execute the following SQL script.
    SELECT * FROM OrganizationUIBase
    WHERE ObjectTypeCode IN (
        SELECT ObjectTypeCode FROM MetadataSchema.Entity
        WHERE Name='new_offendingentityname'
    )
    SQL Query to Find Offending FormXML
    After running the SQL script, you should get two records as shown below. One record has InProduction = 0, another one has InProduction = 1. They basically represent the customization XML that you are drafting, and the customization XML that is already published.
    The column that should interest you is FormXml (the one has InProduction = 1). You may copy the content of FormXml column to an XML editing tool, such as Visual Studio (you can use Visual Studio formatting tool to make it more readable by pressing Ctrl+K following Ctrl+D), and inspect the XML string. At the same time, you can open your entity's customization page, and navigate to its Attributes page so that you can see all fields that belong to the offending entity. If you look carefully enough, you should be able to find out, one (or more, but most likely one) of CRM fields in the XML is missing from the actual entity customization, which is the cause of the problem. If you cannot determine the offending CRM fields, you should not proceed to next step, in which case you might want to inspect the record that has InProduction = 0, this is where the solution documented in the knowledge base article can come to rescue you.

    After you have determined the missing field, you are now ready to take action to correct the issue.
  4. Execute the following SQL script to delete the offending FormXml record that has been published.
    DELETE FROM OrganizationUIBase
    WHERE ObjectTypeCode IN (
        SELECT ObjectTypeCode FROM MetadataSchema.Entity
        WHERE Name='new_offendingentityname'
    )
    AND InProduction = 1
    SQL Query to Fix Offending FormXML
  5. After you have successfully deleted the offending FormXml record, you should now be able to export and publish the customizations of the involved entity.
PLEASE 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 is that there is orphan or missing field in your published FormXML which was not documented in the mentioned Microsoft knowledge base article.

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

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.

Saturday, June 05, 2010

MSCRM 4.0: A Few Tips on Using CRM Configuration Data Utility

In case you are not aware, CRM Configuration Data Utility is a handy tool released by Microsoft sometime last year. What it does is to help you transfer CRM configuration data from one environment to another.

I recently happen to have the need to transfer some configuration data between two environments, so I spent a little time to poke around with the utility, and I am very pleased with the tool, but I quickly realized a few limitations with the toolkit that have been intentionally engineered by Microsoft team. Fortunately the utility comes with complete source code, so I was able to tweak it to meet my needs. Here are a couple of tips that might help you make best use of the tool.

  1. This utility is a really convenient tool if you want to transfer any configuration data stored in your custom entities. You can export data from one environment, and import to another one within a few mouse click. But here is the catch, it's engineered to only support the export and import of custom entities. But with the source code, you can easily change it to support any entities including system ones. What you need to do is to change the following code in ExportDataForm.cs file (RetrieveEntitiesComplete method)
    IEnumerable<EntityMetadata> entities = response.CrmMetadata
        .Cast<EntityMetadata>()
        .Where(entity => entity.IsIntersect.Value == false && entity.IsImportable.Value && entity.IsCustomEntity.Value)
        .OrderBy(entity => entity.DisplayName.UserLocLabel.Label)
        .ToList();
    
    
    to
    IEnumerable<EntityMetadata> entities = response.CrmMetadata
        .Cast<EntityMetadata>()
        .Where(entity => entity.IsIntersect.Value == false && entity.IsImportable.Value)
        .OrderBy(entity => entity.DisplayName.UserLocLabel.Label)
        .ToList();
    
    

  2. The utility is a smart tool, but it is a little bit too smart when it comes to the import. It will actually try to do duplicate check before importing the data. The duplicate check is based on the imported entity's primary attribute, which isn't correct all the time. Sometimes, we do have two different CRM records with the same value of the primary attribute. For instance, we have a self-referenced entity, which has a parent field to refer to the same entity itself. It's often the case that the primary attribute of two CRM records are the same, but they are linked to two different parents, they are essentially two different records in the business world. In order to have the utility to do straight import, you should comment out the following code in dynamicentityutility.cs file (GetByIdOrPrimaryAttribute method)
    // Locate the following code and comment out the entire block in dynamicentityutility.cs file
    // in order to stop the configuration data utility from performing duplicate check. 
    string primaryAttribute = MetadataUtility.RetrievePrimaryAttribute(metadataService, entityName);
    QueryExpression query = new QueryExpression(entityName)
    {
        ColumnSet = new ColumnSet(fields)
    };
    
    ConditionExpression primaryAttributeExpression = new ConditionExpression(primaryAttribute.ToLower(), ConditionOperator.Equal, primaryAttributeValue);
    query.Criteria.Conditions.Add(primaryAttributeExpression);
    
    RetrieveMultipleRequest request = new RetrieveMultipleRequest()
    {
        Query = query,
        ReturnDynamicEntities = true
    };
    
    RetrieveMultipleResponse response = (RetrieveMultipleResponse)_crmService.Execute(request);
    
    if (response.BusinessEntityCollection.BusinessEntities.Count > 0)
        resultList.AddRange(response.BusinessEntityCollection.BusinessEntities.ConvertAll<DynamicEntity>(x=> (DynamicEntity)x));
    

The beauty about this tool is, you often configure your CRM workflows in one environment (for instance, your development environment), and those CRM workflows sometimes refer to particular CRM records as configuration data, so there is dependency between your workflow and those CRM records. When you export those workflows and import to a different environment (for instance, your production environment), you will have to re-create the same set of CRM records in the new environment in order to make the workflows work. But if you create the records manually, the records' internal ID (Guid) would be different, so you will have to reconfigure each workflow to point to the new created CRM records, otherwise, you would not be able to publish your workflows. But with the configuration data utility handy, you can simply transfer the configuration data, the IDs will remains the same as what it was in the original environment, so you don't have to reconfigure your workflows, it will save you tremendous effort when you try to promote workflows from one environment to another which have dependency on configuration data.

Hope this gives you some basic ideas about how to tweak the utility. In my opinion, it's a pretty well-engineered piece of software.

Tuesday, May 25, 2010

Use CRM Web Service Toolkit to Implement Associate/Disassociate/SetState Functions

There was a comment in my new CRM Web Service Toolkit release page, complaining the following functions were missing from the toolkit when comparing to Ascentium library.

- Associate
- Disassociate
- SetState

I want to make it very clear upfront. It was never my intention to beat anyone or anything by writing the toolkit. I wrote it simply because I had too much pain to write ad-hoc JavaScript functions to make CRM Web Service calls. It was very inefficient, and also error-prone.

If any of you ever care about how and why it happened, here is a bit story behind the toolkit. I started with a few reusable JavaScript functions at the very beginning, without knowing Ascentium library exists (If I knew in the first place, I would probably never started), and gradually made it into a helper utility. To be honest, it took me quite some effort to get there, as I was an amateur JavaScript developer. I liked most of the implementation, but it was too simple, can only do a couple of things. So I decided to make it better, that's how CRM Service Toolkit 1.0 was born, which killed me almost another whole weekend time plus some evening time during that week, it was the time that I came cross Ascentium library, from which I incorporated the self-contained BusinessEntity concept. The toolkit looked a lot better, but since it's my spare time hobby project, I didn't actually bring the toolkit to my work project until almost 3 months later due to time constraint and distraction of other engagements. As soon as I started to use it in my work project, I immediately realized some problems, most significantly, adding toBoolean() prototype function to JavaScript Number object was simply a wrong decision. That's the biggest motivation for me to write a 2.0 version, as I feel obliged that I have to address this bad design. In the meantime, I wanted to incorporate some security features to the toolkit as they are very often used in CRM projects. That's where v2.0 came from. But since I can only do it on my personal time, it took me roughly a month to find some spare time to really focus on the v2.0 enhancements.

Way off-topic, I just want to make it clear about my intention of writing the toolkit.

Back to the topic of those missing functions, I actually thought about introducing them to the toolkit library, but I decided not to do so, in order to keep the toolkit as nimble as possible, I didn't seem to see they are so often used.

However if you ever need those functions, here are the implementations:

Associate and Disassociate Functions

/**
 * Associate two CRM records that have a N:N relationship. 
 * @param {String} relationshipName Name of the many-to-many relationship.
 * @param {String} entity1Name Entitiy name of the first record to be associated.
 * @param {String} entity1Id CRM Record ID (GUID) of the first record to be associated.
 * @param {String} entity2Name Entitiy name of the second record to be associated.
 * @param {String} entity2Id CRM Record ID (GUID) of the second record to be associated.
 * @return {object} The XML representation of the result.
 */
associate = function(relationshipName, entity1Name, entity1Id, entity2Name, entity2Id)
{
    var request = [
"<Request xsi:type='AssociateEntitiesRequest'>",
    "<Moniker1>",
        "<Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity1Id, "</Id>",
        "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity1Name, "</Name>",
    "</Moniker1>",
    "<Moniker2>",
        "<Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity2Id, "</Id>",
        "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity2Name, "</Name>",
    "</Moniker2>",
    "<RelationshipName>", relationshipName, "</RelationshipName>",
"</Request>"
].join("");

    return CrmServiceToolkit.Execute(request);
};

/**
 * Disassociate two CRM records that have a N:N relationship. 
 * @param {String} relationshipName Name of the many-to-many relationship.
 * @param {String} entity1Name Entitiy name of the first record to be disassociated.
 * @param {String} entity1Id CRM Record ID (GUID) of the first record to be disassociated.
 * @param {String} entity2Name Entitiy name of the second record to be disassociated.
 * @param {String} entity2Id CRM Record ID (GUID) of the second record to be disassociated.
 * @return {object} The XML representation of the result.
 */
disassociate = function(relationshipName, entity1Name, entity1Id, entity2Name, entity2Id) {
    var request = [
"<Request xsi:type='DisassociateEntitiesRequest'>",
    "<Moniker1>",
        "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity1Name, "</Name>",
        "<Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity1Id, "</Id>",
    "</Moniker1>",
    "<Moniker2>",
        "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity2Name, "</Name>",
        "<Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entity2Id, "</Id>",
    "</Moniker2>",
    "<RelationshipName>", relationshipName, "</RelationshipName>",
"</Request>"
].join("");

    return CrmServiceToolkit.Execute(request);
};

SetState Function

[Update - May 26, 2010] vlad007 left a comment pointing out the request XML was not in right sequence, so I have just updated the script. Thanks vlad007!
/**
 * Set a CRM record's state by its statecode and statuscode. 
 * @param {String} entityName Entitiy name of the CRM record to be updated.
 * @param {String} id CRM Record ID (GUID) to be updated.
 * @param {String} statecode New statecode in string, eg, "Active", "Inactive".
 * @param {Integer} statuscode New statuscode in integer, use -1 for default status.
 * @return {object} The XML representation of the result.
 */
setState = function(entityName, id, stateCode, statusCode) {
    var request = [
"<Request xsi:type='SetStateDynamicEntityRequest'>",
    "<Entity>",
        "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", entityName, "</Name>",
        "<Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>", id, "</Id>",
    "</Entity>",
    "<State>", stateCode, "</State>",
    "<Status>", statusCode, "</Status>",
"</Request>"
].join("");

    return CrmServiceToolkit.Execute(request);
};

Please be advised, those functions were not actually unit tested. Please let me know if you have problems using them.

The above code should also be ablet to give you some basic ideas about how to add support of any other CRM messages that you may need to the toolkit.

Cheers!

Sunday, May 23, 2010

Release: MSCRM4 Web Service Toolkit for JavaScript v2.0

[UPDATE - July 4, 2010] A new version has been released at http://danielcai.blogspot.com/2010/07/crm-web-service-toolkit-for-javascript.html, please ensure to check out.

Here is another update of CRM Web Service Toolkit for JavaScript that I released to codeplex site today, most likely this is going to be the last release before CRM5. This new release is based on previous version (v1.0), and it comes with a few more enhancements:
  1. A new method called queryByAttribute() has been added, which allows to retrieve a specific entity's record by using one or more than one pair of attribute and value
  2. Three new methods have been added to help facilitate user and user security role related queries, including getCurrentUserId(), getCurrentUserRoles(), isCurrentUserInRole()
  3. The toBoolean() prototype function that I added to JavaScript Number type in the previous version is now obsolete, instead I have added a new prototype function to the toolkit's BusinessEntity object. So if you want to retrieve a CRM Boolean type field's value, you should use something like this: businessEntity.getValueAsBoolean('new_mybooleanfield')
  4. A new prototype function called getValueAsLookup has been added to the toolkit's BusinessEntity object, which allows you to parse the values of a CRM lookup field that you retrieved through the toolkit and convert it to a CRM lookup control's DataValue. For instance, you could do something like this: crmForm.all.new_mylookup.DataValue = businessEntity.getValueAsLookup("new_mylookup", "new_mylookupentity")
Again, here are a few samples that might help you get started with the toolkit.
  1. Use CrmServiceToolkit.Create() to create a CRM record.
    // Use CrmServiceToolkit. Create() to create a CRM contact record.
    var contact = new CrmServiceToolkit.BusinessEntity("contact");
    contact.attributes["firstname"] = "Diane";
    contact.attributes["lastname"] = "Morgan";
    contact.attributes["gendercode"] = 2;
    contact.attributes["familystatuscode"] = 1; // Picklist : Single - 1
    contact.attributes["creditlimit"] = 3000;
    
    var createResponse = CrmServiceToolkit.Create(contact);
  2. Use CrmServiceToolkit.Update() to update a CRM record.
    //Use CrmServiceToolkit.Update() to update a CRM contact record. 
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C';
    var contact = new CrmServiceToolkit.BusinessEntity("contact");
    contact.attributes["contactid"] = contactId;
    contact.attributes["firstname"] = "Diane";
    contact.attributes["lastname"] = "Lopez";
    contact.attributes["familystatuscode"] = 2; // Married
    
    var updateResponse = CrmServiceToolkit.Update(contact);   
  3. Use CrmServiceToolkit.Retrieve() to retrieve a CRM record.
    // Use CrmServiceToolkit.Retrieve() to retrieve a CRM contact record.
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C'; 
    var cols = ["firstname", "lastname", "familystatuscode", "creditlimit", "birthdate", "donotemail"];
    var retrievedContact = CrmServiceToolkit.Retrieve("contact", contactId, cols);
    
    alert(retrievedContact.getValue('lastname'));
    alert(retrievedContact.getValue('firstname'));
    alert(retrievedContact.getValue('familystatuscode')); // Picklist's value (integer)
    alert(retrievedContact.getValue('familystatuscode', 'name')); // Picklist's selected text
    alert(retrievedContact.getValue('creditlimit')); // Currency field's value
    alert(retrievedContact.getValue('creditlimit', 'formattedvalue')); // Currency field's formatted value (string)
    alert(retrievedContact.getValue('birthdate')); // Datetime field's date/time value
    alert(retrievedContact.getValue('birthdate', 'date')); // Datetime field's date string
    alert(retrievedContact.getValue('birthdate', 'time')); // Datetime field's time string
    alert(retrievedContact.getValueAsBoolean('donotemail')); // Bit field's value
  4. Use CrmServiceToolkit.RetrieveMultiple() to retrieve a collection of CRM records.
    // Retrieve all contacts whose first name is John. 
    var firstname = 'John'; 
    var query = [
    "<q1:EntityName>contact</q1:EntityName>",
    "<q1:ColumnSet xsi:type='q1:ColumnSet'>",
       "<q1:Attributes>",
          "<q1:Attribute>firstname</q1:Attribute>",
          "<q1:Attribute>lastname</q1:Attribute>",
          "<q1:Attribute>familystatuscode</q1:Attribute>",
          "<q1:Attribute>ownerid</q1:Attribute>",
          "<q1:Attribute>creditlimit</q1:Attribute>",
          "<q1:Attribute>birthdate</q1:Attribute>",
          "<q1:Attribute>donotemail</q1:Attribute>",
       "</q1:Attributes>",
    "</q1:ColumnSet>",
    "<q1:Distinct>false</q1:Distinct>",
    "<q1:Criteria>",
       "<q1:FilterOperator>And</q1:FilterOperator>",
       "<q1:Conditions>",
          "<q1:Condition>",
             "<q1:AttributeName>firstname</q1:AttributeName>",
             "<q1:Operator>Equal</q1:Operator>",
             "<q1:Values>",
                "<q1:Value xsi:type='xsd:string'>", firstname, "</q1:Value>",
             "</q1:Values>",
          "</q1:Condition>",
       "</q1:Conditions>",
    "</q1:Criteria>"
    ].join("");
    
    var retrievedContacts = CrmServiceToolkit.RetrieveMultiple(query);
    
    alert(retrievedContacts.length);
    alert(retrievedContacts[0].getValue('lastname'));
    alert(retrievedContacts[0].getValue('firstname'));
    alert(retrievedContacts[0].getValue('familystatuscode');
    alert(retrievedContacts[0].getValue('familystatuscode', 'name'));
    alert(retrievedContacts[0].getValue('creditlimit'));
    alert(retrievedContacts[0].getValue('creditlimit', 'formattedvalue'));
    alert(retrievedContacts[0].getValue('birthdate'));
    alert(retrievedContacts[0].getValue('birthdate', 'date'));
    alert(retrievedContacts[0].getValue('birthdate', 'time'));
    alert(retrievedContacts[0].getValueAsBoolean('donotemail'));
  5. Use CrmServiceToolkit.Fetch() to retrieve a collection of CRM records using FetchXML query.
    // 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='familystatuscode' />",
          "<attribute name='ownerid' />",
          "<attribute name='creditlimit' />",
          "<attribute name='birthdate' />",
          "<attribute name='accountrolecode' />",
          "<attribute name='donotemail' />",
          "<filter>",
             "<condition attribute='firstname' operator='eq' value='", firstname, "' />",
          "</filter>",
       "</entity>",
    "</fetch>"
    ].join("");
    
    var fetchedContacts = CrmServiceToolkit.Fetch(fetchXml);
    
    alert(fetchedContacts.length);
    alert(fetchedContacts[0].getValue('lastname'));
    alert(fetchedContacts[0].getValue('firstname'));
    alert(fetchedContacts[0].getValue('familystatuscode');
    alert(fetchedContacts[0].getValue('familystatuscode', 'name'));
    alert(fetchedContacts[0].getValue('creditlimit'));
    alert(fetchedContacts[0].getValue('creditlimit', 'formattedvalue'));
    alert(fetchedContacts[0].getValue('birthdate'));
    alert(fetchedContacts[0].getValue('birthdate', 'date'));
    alert(fetchedContacts[0].getValue('birthdate', 'time'));
    alert(fetchedContacts[0].getValueAsBoolean('donotemail'));
  6. Use CrmServiceToolkit.Delete() to delete a CRM record.
    // Use CrmServiceToolkit.Delete() to delete a CRM contact record. 
    var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C';
    var deleteResponse = CrmServiceToolkit.Delete("contact", contactId);
    alert(deleteResponse);
  7. Use CrmServiceToolkit.Execute() to execute a message.
    // Use CrmServiceToolkit.Execute() to execute a message. 
    var whoAmI = CrmServiceToolkit.Execute("<Request xsi:type='WhoAmIRequest' />");
    currentUserId = whoAmI.getElementsByTagName("UserId")[0].childNodes[0].nodeValue;
    alert("Current user's ID is " + currentUserId);
  8. Use CrmServiceToolkit.queryByAttribute() to retrieve a CRM record using one criterion.
    // Use CrmServiceToolkit.queryByAttribute() to retrieve a set of CRM records.
    var retrievedContacts = CrmServiceToolkit.queryByAttribute("contact", "firstname", "John"); // Retrieve all contacts whose first name is John.
    
    alert(retrievedContacts[0].getValue('lastname'));
    alert(retrievedContacts[0].getValue('firstname'));
    NOTE: In this example, I didn't specify columnSet parameter, so it will return all available fields of the contact entity, which is a really BAD practice. You should always specify what you want to get, if that's possible.

    NOTE: The signature of this method has been changed in v2.1, please refer to the latest release page if you are using v2.1.

  9. Use CrmServiceToolkit.queryByAttribute() to retrieve a CRM record using more than one criterion, with specified column set and sorting order.
    // Use CrmServiceToolkit.queryByAttribute() to retrieve a set of CRM records using more than one criterion with specified column set or sorting order
    var attributes = ["firstname", "lastname"];
    var values = ["John", "Wayne"];
    var cols = ["familystatuscode", "ownerid", "creditlimit", "birthdate", "donotemail", "donotphone"];
    var orderby = ["jobtitle"]; // Sort by Job Title
    var retrievedContacts = CrmServiceToolkit.queryByAttribute("contact", attributes, values, cols, orderby);
    
    alert(retrievedContacts[0].getValue('middlename'));
    NOTE: Again, the signature of this method has been changed in v2.1, please refer to the latest release page if you are using v2.1.

  10. Use CrmServiceToolkit.getCurrentUserId() to get the current user's ID.
    // Use CrmServiceToolkit.getCurrentUserId() to get the current user's ID.
    var currentUserId = CrmServiceToolkit.getCurrentUserId();
    
    alert(currentUserId);
  11. Use CrmServiceToolkit.getCurrentUserRoles() to get all the system roles that the current user has been assigned to.
    // Use CrmServiceToolkit.getCurrentUserRoles() to get all the system roles that the current user has been assigned to.
    var roles = CrmServiceToolkit.getCurrentUserRoles();
    
    alert(roles[0]); // Prompt the user's first role. 
  12. Use CrmServiceToolkit.isCurrentUserInRole() to check if the current user has a particular role.
    // Use CrmServiceToolkit.isCurrentUserInRole() to check if the current user has a particular role.
    var isSystemAdministrator = CrmServiceToolkit.isCurrentUserInRole("System Administrator");
    
    alert("I " + (isSystemAdministrator ? "AM" : "AM NOT") + " a System Administrator. "); 
As usual, here are a few notes about using the toolkit.
  1. The following CRM JavaScript functions have been used in order to keep the file size minimal (Aside from this reason, I am not a big fan of reinventing the wheel).
    • GenerateAuthenticationHeader() function
    • _HtmlEncode() function
    • CrmEncodeDecode.CrmXmlDecode() function
    • CrmEncodeDecode.CrmXmlEecode() function

    If you ever need to run the toolkit out of the context of a CRM form, you'll need to make the above functions available to the toolkit script.

  2. When you retrieve records from CRM using the toolkit's Fetch, Retrieve, RetrieveMultiple, or the new queryByAttribute methods, what you get will be the instance(s) of CrmServiceToolkit.BusinessEntity, which contains all CRM attributes (fields) that have been returned from CRM. However, you should not try to access those attributes directly, instead you use the instance function - getValue() or getValueAsBoolean() to get the value of the CRM field. The reason behind this is, CRM doesn't return anything if a field's value is null, in which case your JS code will blow up if you try to access the field (attribute) directly. With that being said, you should also be informed that a CRM filed's value could be null, be sure to handle it properly in your JS code.
  3. As mentioned previously, when dealing with the value of CRM bit data type that you have retrieved from CRM (Only Fetch, Retrieve, RetrieveMultiple, queryByAttribute methods are really concerned), you should use getValueAsBoolean() method to get the value. This seems to be the only field type that the toolkit cannot detect correctly. For all other type of CRM fields, you can pretty much use getValue() instance method to do the job.
  4. The toolkit will throw error if the CRM service calls failed with any exceptions, it's always a good idea to use try/catch block to manage the potential errors. An example would be:
    // It's always a good idea to contain any errors that could be thrown be the toolkit.
    try
    {
        var contactId = '3210F2BC-1630-EB11-8AB1-0003AAA0123C'; 
        var cols = ["firstname", "lastname", "familystatuscode", "creditlimit", "birthdate", "donotemail"];
        var retrievedContact = CrmServiceToolkit.Retrieve("contact", contactId, cols);
    
        // Do the rest of work
    }
    catch(err) {
        var errMsg = "There was an error when retrieving the contact information...\n\n";
        errMsg += "Error: " + err.description + "\n";
        alert(errMsg);
    }
  5. CRM's Execute message is a versatile message. Anything that you cannot easily achieve through the other 6 messages, you should resort to the toolkit’s Execute() method. Again, please refer to MSCRM 4.0 SDK for more CRM messages.
  6. The toolkit release has a test page included (CrmServiceToolkitTest.aspx), which utilizes QUnit as the test engine. In order to run the test script, you should deploy it along with all other files to ISV/CrmServiceToolkit folder (Please create this folder first), then you can launch http://crmserver:port/MyOrgName/ISV/CrmServiceToolkit/CrmServiceToolkitTest.aspx to run it. If you are in good luck, you should see a screen like this:
    CrmWebServiceToolkit2Test
    NOTE: The unit tests will actually write a contact record to your CRM database, and it will be deleted as part of the unit tests. 
I hope that I have covered everything.

[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.

P.S. You should probably have noticed that I have repeated most of the content in my previous toolkit release page, the reason is that I want to provide a single updated page for you to have all the information, so you don't have to go back and forth between the old release page and this release page.

Have fun with the toolkit, hope the toolkit can help you become a more productive CRM developper.

[UPDATE - July 4, 2010] A new version has been released at http://danielcai.blogspot.com/2010/07/crm-web-service-toolkit-for-javascript.html, please ensure to check out.

Thursday, May 13, 2010

MSCRM 4.0: Offset CRM Dates in Workflow without Being Limited

When using Microsoft Dynamics CRM workflow designer tool, you will be limited to a maximum of 31 days (as shown below) if you ever need an offset date based on another CRM date. The same limitation also apply to months, the maximum offset of months is 36 months.
CRM Date Offset Limitation
In order to overcome the limit, I have come up two custom workflow activity classes which let you create any offset date, using the workflow base class that I just created.

1. InvariantOffsetDays
using System;
using System.Globalization;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Workflow;
using Microsoft.Crm.Sdk;

namespace CrmSpikes.Workflow
{
    /// 
    /// Calculate a series of dates using a base date with a set of predefined offset numbers. 
    /// @author Daniel Cai, http://danielcai.blogspot.com/
    /// 
    [CrmWorkflowActivity("Invariant Offset Days", "Custom Workflow")]
    public class InvariantOffsetDays : SingleActivityBase
    {
        private DateTime _baseDate;

        #region Workflow Parameters

        public static DependencyProperty BaseDateProperty =
            DependencyProperty.Register("BaseDate", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmInput("Base Date")]
        public CrmDateTime BaseDate
        {
            get
            {
                return (CrmDateTime)GetValue(BaseDateProperty);
            }
            set
            {
                SetValue(BaseDateProperty, value);
            }
        }

        public static DependencyProperty Offset7DaysProperty =
            DependencyProperty.Register("Offset7Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("7 Days after the base date")]
        public CrmDateTime Offset7Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset7DaysProperty);
            }
            set
            {
                SetValue(Offset7DaysProperty, value);
            }
        }

        public static DependencyProperty Offset14DaysProperty =
            DependencyProperty.Register("Offset14Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("14 Days after the base date")]
        public CrmDateTime Offset14Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset14DaysProperty);
            }
            set
            {
                SetValue(Offset14DaysProperty, value);
            }
        }

        public static DependencyProperty Offset21DaysProperty =
            DependencyProperty.Register("Offset21Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("21 Days after the base date")]
        public CrmDateTime Offset21Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset21DaysProperty);
            }
            set
            {
                SetValue(Offset21DaysProperty, value);
            }
        }

        public static DependencyProperty Offset28DaysProperty =
            DependencyProperty.Register("Offset28Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("28 Days after the base date")]
        public CrmDateTime Offset28Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset28DaysProperty);
            }
            set
            {
                SetValue(Offset28DaysProperty, value);
            }
        }

        public static DependencyProperty Offset35DaysProperty =
            DependencyProperty.Register("Offset35Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("35 Days after the base date")]
        public CrmDateTime Offset35Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset35DaysProperty);
            }
            set
            {
                SetValue(Offset35DaysProperty, value);
            }
        }

        public static DependencyProperty Offset42DaysProperty =
            DependencyProperty.Register("Offset42Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("42 Days after the base date")]
        public CrmDateTime Offset42Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset42DaysProperty);
            }
            set
            {
                SetValue(Offset42DaysProperty, value);
            }
        }

        public static DependencyProperty Offset49DaysProperty =
            DependencyProperty.Register("Offset49Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("49 Days after the base date")]
        public CrmDateTime Offset49Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset49DaysProperty);
            }
            set
            {
                SetValue(Offset49DaysProperty, value);
            }
        }

        public static DependencyProperty Offset56DaysProperty =
            DependencyProperty.Register("Offset56Days", typeof(CrmDateTime), typeof(InvariantOffsetDays));
        [CrmOutput("56 Days after the base date")]
        public CrmDateTime Offset56Days
        {
            get
            {
                return (CrmDateTime)GetValue(Offset56DaysProperty);
            }
            set
            {
                SetValue(Offset56DaysProperty, value);
            }
        }

        #endregion

        #region SequenceActivity

        protected override void ExecuteBody()
        {
            _baseDate = BaseDate.UniversalTime;

            Offset7Days = CalculateOffsetDate(7);
            Offset14Days = CalculateOffsetDate(14);
            Offset21Days = CalculateOffsetDate(21);
            Offset28Days = CalculateOffsetDate(28);
            Offset35Days = CalculateOffsetDate(35);
            Offset42Days = CalculateOffsetDate(42);
            Offset49Days = CalculateOffsetDate(49);
            Offset56Days = CalculateOffsetDate(56);
        }

        private CrmDateTime CalculateOffsetDate(int offset)
        {
            DateTime resultDate = _baseDate.AddDays(offset);
            return CrmDateTime.FromUniversal(resultDate);
        }

        #endregion
    }
}
After you have registered the workflow assembly, and added the custom workflow activity to your workflow by providing a base date, you can then access the generated offset dates as shown below:
InvariantOffset
InvariantOffsetDays class uses a set of predefined offset numbers to generate a series of offset dates based on the provided base CRM date. The offset is hard-coded in the class due to the way how workflow activity works. You may change it to any intervals or any combination of offset numbers.

2. VariantOffsetDays class
using System;
using System.Globalization;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Workflow;
using Microsoft.Crm.Sdk;

namespace CrmSpikes.Workflow
{
    /// <summary>
    /// Calculate a new date using a base date and an offset number (positive or negative). 
    /// @author Daniel Cai, http://danielcai.blogspot.com/
    /// </summary>
    [CrmWorkflowActivity("Variant Offset Days", "Custom Workflow")]
    public class VariantOffsetDays : SingleActivityBase
    {
        #region Workflow Parameters

        public static DependencyProperty BaseDateProperty =
            DependencyProperty.Register("BaseDate", typeof(CrmDateTime), typeof(VariantOffsetDays));
        [CrmInput("Base Date")]
        public CrmDateTime BaseDate
        {
            get
            {
                return (CrmDateTime)GetValue(BaseDateProperty);
            }
            set
            {
                SetValue(BaseDateProperty, value);
            }
        }

        public static DependencyProperty OffsetProperty =
            DependencyProperty.Register("Offset", typeof(CrmNumber), typeof(VariantOffsetDays));
        [CrmInput("Offset (Positive or Negative)")]
        public CrmNumber Offset
        {
            get
            {
                return (CrmNumber)GetValue(OffsetProperty);
            }
            set
            {
                SetValue(OffsetProperty, value);
            }
        }

        public static DependencyProperty ResultDateProperty =
            DependencyProperty.Register("ResultDate", typeof(CrmDateTime), typeof(VariantOffsetDays));
        [CrmOutput("Result Date")]
        public CrmDateTime ResultDate
        {
            get
            {
                return (CrmDateTime)GetValue(ResultDateProperty);
            }
            set
            {
                SetValue(ResultDateProperty, value);
            }
        }

        #endregion

        #region SequenceActivity

        protected override void ExecuteBody()
        {
            DateTime baseDate = BaseDate.UniversalTime;
            DateTime resultDate = baseDate.AddDays(Offset.Value);
            ResultDate = CrmDateTime.FromUniversal(resultDate);
        }

        #endregion
    }
}
VariantOffsetDays class accepts two parameters, which are the base date, and the offset days (int, either positive or negative), as shown below. It’s a more flexible solution than InvariantOffsetDays, the trade-off is it can only generate one offset date at one time.
VariantOffset

Both classes can be used to create any type of offset, including month-based offset or year-based offset, which is your call. 

The reason that I wrote this blog post was that two persons asked similar questions within a month on CRM Development Forum and CRM Forum about how to specify an offset date for a CRM workflow that cannot be done using the native CRM workflow design tool. There doesn't seem to be any available solution on Internet to address this issue, so I decided to write the custom workflow along with the workflow base class.

Note: Please make sure to include the workflow base class that I created in order to compile the code.

Download the source code and compiled assembly below.




Hope this helps if you ever need to do the same thing.

MSCRM 4.0: A Convenience Workflow Base Class

When writing CRM workflow code, you often need to look up some information from its execution context, or instantiate CRM Web Service from the context. If your workflow is complex, you might have more than one method in your workflow activity class, in which case, you are most likely going to pass around the execution context object or the instance of CRM Web Service from one method to another, which is really tedious.

I have come up a convenience workflow base class which might help you become more proficient when developing your custom workflow activity code.

using System;
using System.Workflow.Activities;
using Microsoft.Crm.Workflow;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;
using System.Web.Services.Protocols;

namespace CrmSpikes.Workflow
{
    /// <summary>
    /// Base class for all Sequence Workflow Activities which consist of only a single workflow activity.
    /// @author Daniel Cai, http://danielcai.blogspot.com/
    /// </summary>
    public abstract class SingleActivityBase : SequenceActivity
    {
        private ICrmService _crmServiceHandle;
        private IMetadataService _metadataServiceHandle;

        #region Base class members

        public IWorkflowContext Context { get; set; }

        /// <summary>
        /// CRM Web Services, instantiated on-demand.
        /// </summary>
        public ICrmService CrmServiceHandle
        {
            get
            {
                _crmServiceHandle = _crmServiceHandle ?? Context.CreateCrmService();
                return _crmServiceHandle;
            }
        }

        /// <summary>
        /// CRM Metadata Service, instantiated on-demand.
        /// </summary>
        public IMetadataService MetadataServiceHandle
        {
            get
            {
                _metadataServiceHandle = _metadataServiceHandle ?? Context.CreateMetadataService();
                return _metadataServiceHandle;
            }
        }
        #endregion

        #region SequenceActivity

        /// <summary>
        /// Workflow step.
        /// </summary>
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            try
            {
                IContextService _contextService = (IContextService) executionContext.GetService(typeof (IContextService));
                Context = _contextService.Context;

                ExecuteBody();
            }
            catch (SoapException ex)
            {
                string errMsg = string.Format("{0}: {1}", ex, ex.Detail.InnerText);
                throw new InvalidPluginExecutionException(errMsg);
            }
            catch (Exception ex)
            {
                string errMsg = string.Format("{0}: {1}", ex, ex.Message);
                throw new InvalidPluginExecutionException(errMsg);
            }
            finally
            {
                if (_crmServiceHandle != null)
                    _crmServiceHandle.Dispose();

                if (_metadataServiceHandle != null)
                    _metadataServiceHandle.Dispose();

                Context = null;
            }

            return ActivityExecutionStatus.Closed;
        }

        /// <summary>
        /// Overridden in derived classes: contains the actual workflow step.
        /// </summary>
        protected abstract void ExecuteBody();

        #endregion
    }
}

To take advantage of the base class, your workflow class should inherit from the above SingleActivityBase class, such as this one.

using Microsoft.Crm.Workflow;

namespace CrmSpikes.Workflow
{
    [CrmWorkflowActivity("My Custom Workflow", "Custom Workflow")]
    public class MyCustomWorkflow : SingleActivityBase
    {
        #region Workflow Parameters
        #endregion

        #region SequenceActivity

        protected override void ExecuteBody()
        {
            // Do my stuff
        }

        #endregion
    }
}

The benefits of using the convenient base class are:

  1. You can use the base class’s Context property to access the your workflow execution context anywhere in your activity class that you inherit from the base class.
  2. You can use the base class’s CrmServiceHandle property to make CRM service calls right away. The base class takes care of instantiating (and also disposing) CRM Web Service in an on-demand fashion.
  3. Similar to the above, you can use the base class’s MetadataServiceHandle property to make CRM metadata service calls right away. The base class takes care of instantiating (and also disposing) CRM Metadata Service in an on-demand fashion as well.
  4. The base class tries to handle exceptions in a consistent way.

The idea was originally inspired by one of open source projects on CodePlex.com site, but I don't remember which project it is. However, the code here is pretty much a complete rewrite from the scratch. If you happen to know the project, please let me know, I will include a link here to the original project. :-)

It’s not rocket science, but it should make your CRM workflow development a little more convenient. Of course, if you have even better idea about the base class, I would appreciate to have your comments here.

Cheers!

Monday, May 03, 2010

MSCRM 4.0: Adding a Button to a Form Toolbar using Client Script

Usually we add custom button to CRM form through ISV.config customization, but there could be scenarios that you may want to add button on-fly in the form’s onLoad event. Here is the script that just does this job.
/**
 * Add a Button to a CRM4.0 form toolbar using client script.
 * @author Daniel Cai, http://danielcai.blogspot.com/
 */
function createToolbarButton(btnTitle, btnId, clickAction, imagePath, btnLabel, includeSpacer) {
    var toolbar = document.all.mnuBar1.rows(0).cells(0).childNodes[0];
    var html = (!includeSpacer) ? '' : '<li class="ms-crm-Menu-Spacer" tabIndex="-1">&nbsp;<img style="clip: rect(0px 4px 17px 0px); background-image: url(/_imgs/imagestrips/control_imgs_1.gif); width: 4px; background-position-y: -55px; height: 17px" alt="" src="/_imgs/imagestrips/transparent_spacer.gif"></li>';
    html += '<li id="' + btnId + '" + class="ms-crm-Menu" title="' + btnTitle + '" tabIndex="-1" onclick="window.execScript(action)" action="' + clickAction + '">';
    html += '<span class="ms-crm-Menu-Label"><a class="ms-crm-Menu-Label" tabIndex=-1 onclick="return false;" href="javascript:onclick();" target=_self>';
    html += (!imagePath) ? '' : '<img class="ms-crm-Menu-ButtonFirst" tabIndex="-1" alt="' + btnTitle + '" src="' + imagePath + '" />';
    html += '<span class="ms-crm-MenuItem-TextRTL" tabIndex=0>' + btnLabel + '</span></a></span>';
    
    toolbar.insertAdjacentHTML("beforeEnd", html);
}
To call the script, you may simply do this:
createToolbarButton("Test button", "mybuttonid", "myfunc()", "/_imgs/ico_16_4200.gif", "Test button label", true);
The code looks a little messy due to its lengthy html code, but once you have pasted to your file, it shouldn't look too bad. :-)

The parameters that the function accepts are all self-explanatory, hope you can figure out without requiring much explanation.

By the way, this is a re-post of my response to a question on CRM Development Forum.

Cheers!