Integrating Radi with SharePoint and Dynamics CRM

As CRM is the main focus at my work, I undertook a project to create a Dynamics CRM look and feel website. I had a read through the article on stunnware and came up with a few ideas.  I also used the CRM UI style guide for reference. It is available from the CRM SDK.

The end result ended up looking like this: www.jaythom.com.au

The cool part is that it renders in both IE and Firefox. Well almost (: 

I used telerik controls for nearly everything, the CRM panel bar, the AJAX functionality.

I wish I had more time to clean up the code and CSS, remove all bugs and add more CRM-looking features.

We will soon run it in a SharePoint environment.

If anyone is interested, I could put together my version of a guide on creating a CRM look and feel website/application.

This post is really about performance. I had a situation where I started off using subqueries:

  SELECT DISTINCT Account.Name AS BusinessName, Test.New_name AS TestName,
                          (SELECT     COUNT(*)
                            FROM          New_Test T2
                            WHERE       T2.New_AccountId = Account.AccountId AND
                            T2.new_testtypeid = Test.new_testtypeid AND
                                        new_result LIKE '%PASS%') AS PassedItems,
                          (SELECT     COUNT(*)
                            FROM          New_Test T2
                            WHERE   T2.New_AccountId = Account.AccountId AND
                            T2.new_testtypeid = Test.new_testtypeid AND
                                    new_result LIKE '%FAIL%') AS FailedItems
FROM Account INNER JOIN
     New_Test Test ON Account.AccountId = Test.new_accountid

Looking at the above SQL select query, for each row of the returned records two additional select statements must be executed. Well the problem is that CRM views involve a few tables, and the particular company I was doing this for had LOTS of records. While the above example is significantly simplified, reduced in size, shortened etc. etc. the query I had to use was way too much of a performance hit on a quality server. I roughly estimated it will take around 6 days to execute. And I had to use the LIKE keyword. I even tried without using the views.

So after a lot of good old google searching, reading articles and blogs I came up with the following: I grouped the results and used CASE statements within the COUNT function to provide me with the sums of data that I require.

SELECT DISTINCT Account.Name AS BusinessName, Test.New_name AS TestName,
                      COUNT(CASE WHEN New_Test.new_result = 'FAIL' THEN 0 ELSE NULL END) AS FailedItems,
                      COUNT(CASE WHEN New_Test.new_result = 'PASS' THEN 0 ELSE NULL END) AS PassedItems
FROM Account INNER JOIN
     New_Test Test ON Account.AccountId = Test.new_accountid
GROUP BY Account.Name, Test.New_name

The result was 0.06 seconds compared to 6 days. I also had to use some scalar subqueries to make sure any one-to-many relationships aren't diluting my aggregated results. Scalar queries can be used to prevent group by results to duplicate in one-to-many situations.

Great fun and quite a power saving result.

Here is how I created custom columns within Document Libraries and Lists which display data from Dynamics CRM. It allows you to assign an entity record from CRM to an item in a List or DL in Sharepoint.

In part 1 I'll be showing a static approach using the CRM web service. I'll be using the Account entity's Name as an example. Once the account name is assigned to a document, the text string (Account Name) is stored by SharePoint. It won't link dynamically to the entity, i.e. if you delete the account in CRM or rename it, changes won't propagate to your document library.

Part 2 will go through a different approach which will reference the entity and update records dynamically. I will also go through how you can relate many entities to 1 document or list item.

To develop the solution I will create a SharePoint custom field type and a custom field control to render the field.

• Open Visual Studio and create a new project. Select an “Empty” type from the templates that come from the Windows SharePoint Services extentions for Visual Studio
• Rename your class to something meaningful
• Add a new item to the project – a “Field Control” form the SharePoint category
• You will end up with references to the Microsoft.SharePoint DLL, a Field class inheriting from SPFieldText and a Field Control class
• Rename your classes and filenames to your preference
  

Now we have to create the field control. We will be using the CRM Web Service do interact with CRM entities.

• Add a Web Reference to the CRM web service. The web reference URL should be http://[yourservername]/mscrmservices/2006/crmservice.asmx
• Here's the code I used for my FieldControl:

   public class CrmFieldControl : TextField
    {
        private CrmField field;
        private ListBox listBox;
        private HtmlTable table;
        public CrmField.TestServerCrmService.CrmService crmservice;        public CrmFieldControl(CrmField parentField)
        {
            this.field = parentField;
            this.listBox = new ListBox();
            crmservice = new CrmService();
            crmservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
        }        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
        }
         protected override void CreateChildControls()
        {   QueryExpression query = new QueryExpression();
            ColumnSet cols = new ColumnSet();
            cols.Attributes= new string[]{"name"};
            query.EntityName = CrmField.TestServerCrmService.EntityName.account.ToString();
            query.ColumnSet = cols;
            // Retrieve the accounts.
            CrmField.TestServerCrmService.BusinessEntityCollection ecAccounts = crmservice.RetrieveMultiple(query);            base.CreateChildControls();
            this.table = new HtmlTable();
            HtmlTableRow row = new HtmlTableRow();
            table.Rows.Add(row);
            HtmlTableCell cell = null;
            if (this.ControlMode == SPControlMode.Edit || this.ControlMode == SPControlMode.New)
            {
                cell = new HtmlTableCell();
                cell.ColSpan = 2;
                cell.Attributes["class"] = "ms-formdescription";
                cell.InnerText = "Select an Account:";
                row = new HtmlTableRow();
                table.Rows.Add(row);
                cell = new HtmlTableCell();
                row.Cells.Add(cell);
                this.listBox = new ListBox();
                this.listBox.Rows = 12;
                foreach (account theaccount in ecAccounts.BusinessEntities)
                {
                    ListItem li = new ListItem(theaccount.name, theaccount.name);
                    this.listBox.Items.Add(li);
                }                String currentValue = (String)this.ItemFieldValue;
                if (currentValue != null && currentValue != String.Empty
                {
                    this.listBox.SelectedValue = currentValue;
                }
                else if (this.listBox.Items.Count > 0)
                {
                    this.listBox.SelectedIndex = 0;
                }
                this.listBox.Attributes["onchange"] = "document.all.j_crmdata.InnerHTML = this.options[this.selectedIndex].text;";
                cell.Controls.Add(this.listBox);
                row.Cells.Add(cell);
            }            cell = new HtmlTableCell();
            LiteralControl literalControl = new LiteralControl();
            String accname = null;
            object AccountObject = this.ItemFieldValue;
            if (AccountObject != null)
            {
                accname = (String)AccountObject;
            }            if (accname == null || accname == String.Empty)
            {
                accname = "";
            }            literalControl.Text = "<div id='j_crmdata'>" + accname + "</div>";
            cell.Controls.Add(literalControl);
            row.Cells.Add(cell);
            base.Controls.Add(table);
        }         public override void UpdateFieldValueInItem()
        {
            this.EnsureChildControls();
            try
            {
                this.Value = this.listBox.SelectedValue;
                this.ItemFieldValue = this.Value;
            }
            catch (Exception)
            {
                ;
            }        } 

        protected override void Render(HtmlTextWriter output)
        {
            this.table.RenderControl(output);
        }
   }

• I instantiate the CRMService in my constructor.
• I used a QueryExpression to retrieve my accounts
• SPControlMode allows me to render the control accordingly.
• I populate my listbox with all the accounts in the edit and new modes.
• I then use a LiteralControl to render the field in display mode.

Next, we have to deploy the field. We have to create a CAML field type definition file and copy it to the TEMPLATESXML folder.

• Build the solution and add the created DLL to the GAC.
• Create the field type definition. This article goes through this in detail.
  
• This is the file that I created:

<?xml version="1.0" encoding="utf-8" ?>
<FieldTypes>
  <FieldType>
    <Field Name="TypeName">CrmField</Field>
    <Field Name="ParentType">Text</Field>
    <Field Name="TypeDisplayName">Account</Field>
    <Field Name="TypeShortDescription">Account</Field>
    <Field Name="UserCreatable">TRUE</Field>
    <Field Name="Sortable">TRUE</Field>
    <Field Name="AllowBaseTypeRendering">TRUE</Field>
    <Field Name="Filterable">TRUE</Field>
    <Field Name="FieldTypeClass">Jaythom.Sharepoint.CrmField, CrmField, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9f1da00116c78ec5</Field>
    <PropertySchema>
      <Fields></Fields>
    </PropertySchema>
    <RenderPattern Name="DisplayPattern">
      <HTML><![CDATA[<div>]]></HTML>
      <Column HTMLEncode="TRUE" />
      <HTML><![CDATA[</div>]]></HTML>
    </RenderPattern>
  </FieldType>
</FieldTypes>

• Save it here:  C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12TEMPLATEXML
• Run an iisreset
• Test your solution!

 You should be all set. The edit properties page of a Document Library record will show a listbox with all CRM accounts. Good luck and please feel free to ask any questions.

EDIT: Check out this blog post from Nick Sevens. He goes through custom field types quite well.

OK it's about time and here it is – my first blog post.  I work as a developer focusing on CRM, SharePoint and general .NET web applications for a Melbourne based company called Jaythom www.jaythom.com.au (Australia).  I am a big fan of community-based knowledge sharing and I've gained a lot from blogs and forums, so now it's time I gave something back. Here's my contribution.

I plan to share experiences on the following technologies and issues:

ú         CRM development

ú         SharePoint development and customization

ú         Integration between CRM and SharePoint

ú         SQL, Reporting Services and Business Intelligence

ú         General .NET experiences

ú         ASP.NET  and everything related

Catch.