How To Detect a Duplicate Entry in Microsoft CRM 3.0

• Download source - 132.68 KB

Introduction

CRM systems are great, but they are very prone to human error. The ease of data entry is sometimes counterproductive especially if you want to keep your data clean.

In one of my projects, the client required that CRM be up to date with all the latest data from their legacy accounting system. The data import job runs every 24 hours and because they are still using the legacy system for some tasks, duplicate entries find their way into the system.

There are many fine ISV products to detect duplicates and handle them accordingly, but given the simple test I had to perform, the cost outweighed the time it would take to develop the simple duplicate detection system that I needed.

Using the Code

In this code, I used JavaScript for posting data to my detection script and C# for the *.aspx page.

Essentially, all I needed to do was test against a single field. On the change event of an input field in the Account entity, I sent some data to a web service using JavaScript and returned a response.

If the response indicated that an account already exists, than I gave the user the option to open the existing account's form.

C# Code

Essentially, I have the Fetch XML that will be sent to CRM 3.0 via the API. In this particular case, I needed to test the accountnumber field for duplicates.

I pass the account number to my script via a form post (will be shown later), that is why i am testing for the Request["AccountNumber"].
After building the XML, the script sends the request to CRM and retrieves the response.
Load the XML into a System.Xml.Xm lDocument object and test to see if the number of rows returned is more than zero.
If it is more than zero, for my purposes the script returns the accountid.

crmService.CrmService crm;
 // Put user code to initialize the page here
 crm = new CrmService();
 crm.Credentials = System.Net.CredentialCache.DefaultCredentials;

 string fetchxml = "<fetch mapping='logical'>";
 fetchxml+= "<entity name='account'>";
 fetchxml+="<all-attributes/>";
 fetchxml+="<filter type='and'>";
 fetchxml+=    "<condition attribute='accountnumber' operator='eq' value='{0}'/>";
 fetchxml+=    "</filter>";
 fetchxml+=    "</entity>";
 fetchxml+="</fetch>";

if(Request["AccountNumber"]!="")
 {
 fetchxml = string.Format(fetchxml,Request["AccountNumber"]);
 string xmlResponse = crm.Fetch(fetchxml);
 System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
 xdoc.LoadXml(xmlResponse);

if(xdoc.SelectNodes("resultset/result").Count>0)
 {
    Response.Write(xdoc.SelectNodes("resultset/result")[0]["accountid"].InnerText);
 }else
 {
    Response.Write("0");
 }
}else
 {
   Response.Write("0");
 }

JavaScript

This portion can be used in either the OnSave event for any entity or the OnChange event for any field.
I don't need to worry about browser incompatibility when developing for Microsoft CRM 3.0, because the platform was built to be used in Internet Explorer 6+. I create the URL along with the data of the field I want to test against, and use the Microsoft.XMLHTTP ActiveX object to post it to your script.
If you take a look at the C# code for the script, it returns "0" if nothing is found and the account id otherwise.

At this point, I ask the user if they would like to be redirected to the existing account.

var url = "http://localhost/CheckDuplicateAccounts/CheckDuplicateAccount.aspx?
		AccountNumber=" + crmForm.all.accountnumber.DataValue;
var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
xmlhttp.open("POST", url, false);
xmlhttp.setRequestHeader("Content-Type", 
	"application/x-www-form-urlencoded; charset=utf-8");
xmlhttp.send("");

var result = xmlhttp.responseText;
if(result != "0")
{
  if(confirm("This account number already exists in the system, 
		would you like to open the EXISTING account?"))
  {
    document.location = "http://"+
	document.domain+":5555/sfa/accts/edit.aspx?id=" + result;
  }
}

Points of Interest

I quickly realized that the script could be very generic, but time constraints prohibited me from doing so. This script can be used for any entity and any number of fields.

I would like to make it async in the future as there is a delay while it checks for duplicates.

Oshri Cohen www.nuvoit.com