How to find duplicate records in an entity using fetch xml

(0) Share

Report

Posted on by Sunil Sharma

120

Hi All,

We are using CRM 3.0. We have a requirement for finding duplicate records in an Entity. I am able to find it through Sql Query, However I am finding it difficult to convert Sql Query to Fetch Xml.

Could you please help me for creating this query in Fetch Xml. Or Else Is there any other way to find duplicate records in an entity using Fetch XML. If Yes, Could you please guide me to get the Fetch Xml in that way.

Below is the Sql Query:

Select adc.new_Name,adc.new_AccountNumber,adc.New_CustomerId

from new_adcenteraccount adc

Where adc.new_AccountNumber in

(

Select new_AccountNumber from new_adcenteraccount

Group by new_AccountNumber

Having count(new_AccountNumber ) > 1

)

Thanks.

Regards,

Sunil Sahrma

*This post is locked for comments

I have the same question (0)

All responses (1)

Answers (0)

hassan chandoklow 175 on at

Like (0)

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

The 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"); }

The 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 IE6+.
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 acount 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; } }

Was this reply helpful? Yes No