Skip to main content

SharePoint 2007 Integration With Dynamics CRM - Solution - Part 2

OK, here's the Part 2 of the solution I proposed here.

We will develop a custom Web part which will display number of application forms in CRM posted from SharePoint Web site in the form of Key Performance Indicator – assume there is a target for number of application forms.

We will also assume for this sample that the application form in our case will be the task which we create in CRM when new application is submitted from the SharePoint Web site.

Solution
1. Create a Web part and add the Web Reference to point to the CRM Server Web Service.
For CRM 3.0 it will be http://your_server:5555/mscrmservices/2006/crmservice.asmx, for CRM 4.0 it will be http://your_server:5555/mscrmservices/2007/crmservice.asmx

2. Add the following properties to the Web part class:

private string _sCrmWebUrl = ...;
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM Web Server URL"),
Category("Custom Properties")]
public string CrmWebUrl
{
get { return _sCrmWebUrl; }
set { _sCrmWebUrl = value; }
}

private string _sCrmUserName = ...;
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM User Name"),
Category("Custom Properties")]
public string CrmUserName
{
get { return _sCrmUserName; }
set { _sCrmUserName = value; }
}

private string _sCrmUserPassword = ...;
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM User Password"),
Category("Custom Properties")]
public string CrmUserPassword
{
get { return _sCrmUserPassword; }
set { _sCrmUserPassword = value; }
}

private string _sCrmUserDomain = ...;
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM User Domain"),
Category("Custom Properties")]
public string CrmUserDomain
{
get { return _sCrmUserDomain; }
set { _sCrmUserDomain = value; }
}

private string _sCrmSearchAttribute = "contactcode";
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM Search Attribute"),
Category("Custom Properties")]
public string CrmSearchAttribute
{
get { return _sCrmSearchAttribute; }
set { _sCrmSearchAttribute = value; }
}

private string _sCrmSearchValue = "CRM Search Value";
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("CRM Search Value"),
Category("Custom Properties")]
public string CrmSearchValue
{
get { return _sCrmSearchValue; }
set { _sCrmSearchValue = value; }
}

private int _nKpiTarget = 0;
[Personalizable(PersonalizationScope.Shared),
WebBrowsable(true),
WebDisplayName("KPI Target"),
Category("Custom Properties")]
public int KpiTarget
{
get { return _nKpiTarget; }
set { _nKpiTarget = value; }
}

private CrmService _service = new CrmService();

3. Add method to initialize CRM Web Service:

private void GetCRMService()
{
_service.Url = GetServiceUrl();
_service.Credentials = new System.Net.NetworkCredential(CrmUserName, CrmUserPassword, CrmUserDomain);
}

4. Add method to search for the contact. We will use just one attribute to search - say Contact Code - but it could be extended if necessary to search using more attributes:

public Guid SearchForContact()
{
Guid gResult = Guid.Empty;
try
{
QueryExpression queryContact = new QueryExpression();
queryContact.ColumnSet = new MyCRMService.AllColumns();
queryContact.EntityName = EntityName.contact.ToString();

queryContact.Criteria = new FilterExpression();

queryContact.Criteria.FilterOperator = LogicalOperator.And;

ConditionExpression Condition = new ConditionExpression();

Condition.AttributeName = CrmSearchAttribute;

Condition.Operator = ConditionOperator.Equal;

Condition.Values = new object[] { CrmSearchValue};

queryContact.Criteria.Conditions = new ConditionExpression[] { Condition };

BusinessEntityCollection retrievedContacts = service.RetrieveMultiple(queryContact);

if (retrievedContacts.BusinessEntities.Length > 0)
{
gResult = ((contact)retrievedContacts.BusinessEntities[0]).contactid.Value;

}

}
catch (System.Web.Services.Protocols.SoapException soapex)
{
... // error handling
}
catch (Exception ex)
{
... // error handling
}
return gResult;
}

5. Add method which will query the CRM system for the tasks we created and display the status of the tasks and total number for the KPI:

private DataTable GetCustomerDataTable(Guid gContactId)
{
DataTable dtResult = new DataTable();
dtResult.Columns.Add("Action");
dtResult.Columns.Add("Value");
try
{
GetCRMService();

QueryExpression queryTasks = new QueryExpression();
queryTasks.EntityName = EntityName.task.ToString();
queryTasks.ColumnSet = new AllColumns();
queryTasks.Criteria = new FilterExpression();
queryTasks.Criteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "regardingobjectid";
condition1.Operator = ConditionOperator.Equal;

condition1.Values = new object[] { gGontactId.ToString() };

queryTasks.Criteria.Conditions = new ConditionExpression[] { condition1 };

BusinessEntityCollection retrievedTasks = _service.RetrieveMultiple(queryTasks);

if (retrievedContacts.BusinessEntities.Length > 0)
{
DataRow dr = dtResult.NewRow();
dr["Action"] = "Total:";
dr["Value"] = retrievedContacts.BusinessEntities.Length;

foreach (BusinessEntity entity in retrievedContacts.BusinessEntities)
{
task task = (task)entity;
dr["Action"] = "Status:";
dr["Value"] = task.statuscode.name;
}
dtResult.Rows.Add(dr);
}
}
catch (System.Web.Services.Protocols.SoapException soapex)
{
... // error handling
}
catch (Exception ex)
{
... // error handling
}
return dtResult;
}

6. Override Render method to output the DataTable that we have got from the CRM:

protected override void Render(HtmlTextWriter writer)
{
base.Render(writer);
Guid gContactId = SearchForContact();
DataTable dt = GetCustomerDataTable(gContactId);
writer.Write("Our Mighty CRM Integration Data:");
writer.Write("<table>");
int n = 0; // row counter
foreach (DataRow dr in dt.Rows)
{
writer.Write("<tr>");
writer.Write("<td>");
writer.Write(dr["Action"].ToString());
writer.Write("</td>");
writer.Write("<td>");
if (n == 0) // first row with totals
{
int nTotal = (int)dr["Value"];
if (nTotal < KpiTarget)
writer.Write("<font color='Red'>" + dr["Value"].ToString() + "</font>")
else
writer.Write("<font color='Green'>" + dr["Value"].ToString() + "</font>")
}
else
{
writer.Write(dr["Value"].ToString());
}
n++;
writer.Write("</td>");
writer.Write("</tr>");
}
writer.Write("</table>");
}

7. Build the Web part. Install it on the SharePoint Web site and enjoy.

Here I just illustrate the concept - how you can integrate SharePoint with Dynamics CRM using custom Web parts. Of course there is a way to display different icons when the target is met or not, search for different entities in CRM etc.

Also this Web part is made reusable so you can easily install it and configure to use on any SharePoint Web site without the need to recompile or rebuild your solution.

Stay tuned :)

Here's Part 1

Comments

Popular posts from this blog

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server.

According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition.
The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you wou…

SharePoint 2010 Search Issue - FQDN Crawl

I have recently set up a standalone SharePoint 2010 environment.

The Web application was created with host header and the site collection is accessible from the client machines, but not internally. That was because of using FQDN to access the Web site.

The error when I tried to access site internally was similar to the one described here:

You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version

And the search returned the following error in the Event Log:

"The start address cannot be crawled.

Context: Application 'Search_Service_Application', Catalog 'Portal_Content'

Details: This item could not be crawled because the crawler could not connect to the repository."

One of the suggestions was to disable the loopback check, but that would compromise the Web server.

So what I have done was:

1. Added a binding to my IIS Web site for a different port. Let's say my Web server name is win-v7m…

SharePoint 2013 - Setting Up External Content Type

There were earlier posts where we discussed External Content Types setup for SharePoint 2010:

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

External Content Types - Reload - Setting up for SQL Server database using SQL Server authentication - SharePoint 2010 Server

This one is about creating connection to the custom SQL Server database (External System) in SharePoint 2013.

1. Create Secure Store Service Target Application

1.1. Go to Central Administration -> Manage Service Applications -> Secure Store Service Application. Click "Generate New Key" if required:


1.2. Provide Pass Phrase:

1.3. Create "New" to create new Target Application:


1.4. Provide the name and other parameters and click "Next":

Note: It's good idea to specify "Group" for Target Application Type. In that case you would be able to manage access to the external data using Active Directory groups rather …