Showing posts with label Duplicate Detection. Show all posts
Showing posts with label Duplicate Detection. Show all posts

Tuesday, September 20, 2016

CRM Alternate keys with OptionSet fields

The alternative keys feature introduced in CRM 2015 U1 turns out to be extremely useful, especially for integration scenrios in which you might want to keep a record of an “external key” from another system or you want to enforce duplicate prevention (for real).

In some cases you would like to make the alternative key contain an OptionSet type of field. For example, I wanted to define an alternative key based on “Contact Type” and “Email” so that CRM could check that no contacts of the same type with the same email address.
However, when you try to define the key on the contact entity I could no select my “Contact Type” field as part of the key:




So I can see a number of fields and realize that only fields of numeric or text value are available to pick. After some research I found that it is documented in the SDK that only these fields can be added to a key:



















So no luck! The only option you have is to create a hidden text field which will hold the value of your OptionSet but as text and then add that field to your key. I try to define this field as a calculated fiueld but then again, calculated fields cannot be added to alternate keys (this is by experiment, not by documentation). Therefore, I had to define a business rule to populate that field based on the options of your OptionSet. In my example, my OptionSet has options “Nurse” and “Doctor” so I create the business rule as follows (note you should set “Scope” to “Entity”)



Now I am able to add the calculated field to my key:





After this is done, I can test to make sure I can create both a nurse and a doctor with the same email but if I try to create 2 nurses with the same email I get the error:



So this works. It is not the most elegant solution since you are essentially duplicating data. However, if you must include an OptionSet field as part of a key this could be an easy way to enforce it :-)

Enjoy!!




Saturday, January 26, 2013

Problems with leading and trailing blank spaces in CRM data

I recently ran into a number of problems that ended all up being due to blankspaces in some CRM data. I thought of sharing where these problems come from, how blank spaces can affect CRM functionality and how you can resolve them.

You might wonder: how can you end up with leading or trailing blank spaces in text fields in CRM? While it is true that CRM forms are smart enough to remove these blank spaces upon form save, it might not be the case when the data comes in from data import or during integrations with external systems. It sounds like a small problem, but if you are not careful, blank spaces can become a data integrity nightmare for the following reasons:

1. You might end up with duplicate data that is hard to resolve later on. For example you might have account “123” and account “ 123 “ which reference to your same customer. Now all your activities, invoices and all related records are potentially spread across 2 different records. Fortunately, duplicate detection ignores leading/trailing blank spaces so these 2 records would be considered duplicate if you have a published rule on account number field. However, if these accounts are created from the SDK (or through an integration layer), chances are that the duplicate detection is not enforced because when making web service calls duplicate detection is off by default unless specified in the create/update message. Fixing duplicate parent records can be a really long and boring task.

2. Assume you have multiple contacts related to account “123” that you want to import using the data import wizard. However, your account appears in CRM as “ 123 “ (with blank spaces). It will be impossible for import wizard to match the contact’s parent account and import will fail because the parent account was not found. You might say: I can just change my import spreadsheet to reference account “ 123 “ instead of “123”. The answer is: it does not matter if in your import XML file you enter “123” or “ 123 “ as the parent account, CRM will not be able to resolve the parent account!! In short: You cannot use the out-of-the-box data import feature to import related records to that account until you fix the blank spaces, argh.

3. Once you fix all your integration points to trim all text fields before committing a create / update to CRM, how do you clean the thousands of records that already exist? My first guess was: Export to Excel for re-import, use some Excel functions to remove blank spaces and import back to CRM. Again to my surprise, removing leading/trailing blankspaces in text fields is not considered a change for CRM so during import, the records were not even processed! When you export records for re-import, CRM is smart enough to know that if you did not update a row, it will not re-import that row. However, it is not smart enough to figure out that you removed blank spaces so it also ignores your update.

4. Sometimes the CRM forms trim blank spaces for text fields. This can become a problem because if your data has trailing blank spaces, the CRM form will remove then on load, and automatically the form is marked as dirty! Therefore, just by opening the record your form is dirty and you get a warning about pending changes when you close the form even if you did not update the record. It also prevents some functionality since some of our ribbon buttons require the form to not be dirty. It is also not evident what was happening and took us a while to figure out why some forms where always getting marked as dirty.

 

We let our production CRM get really messy because we did not notice this problem until too late (we never noticed the external systems were sending data padded with blank spaces) so cleansing the data became quite challenging since it was spread to multiple fields in multiple entities, many duplicate parent and child records. As I mentioned earlier, fortunately duplicate detection does consider these 2 cases as duplicates (“123” and “ 123 “) so you can execute multiple duplicate detection jobs to find and resolve duplicates. However, it can be a really long task to do. In our case, because of the magnitude of the data cleanse that was required we had to invest in a data cleansing tool that would iterate through multiple records resolving the blank spaces and the duplicates which reduced considerably the amount of manual work required to solve the problem. The “tool” in a nutshell consisted simply in a custom workflow activity that takes as input a CRM query (Rollup Query) and then retrieves and cleans all the records specified as input. Then we can use on-demand workflows and provide different queries to resolve duplicates in multiple entities. In any case our conclusion is that we should have thought about this from the beginning and I hope this post helps other avoid the same problem before it is too late.

Monday, February 13, 2012

Duplicate Detection in CRM 2011

The Duplicate Detection feature has improved considerably since CRM 4.0 but it is often not given much attention. I would like to highlight in this post what has improved in Duplicate Detection, how to use it and what is still missing (areas of improvement for future releases).

Synchronous MatchCode Generation
MatchCodes allow the CRM platform to detect duplicates in the create/update operations. In CRM 4.0 the MatchCode generation was implemented asynchronously with a recurring system job which would generate the MatchCodes. The implication of that process is that if you create a record and then create a new duplicate record within a few seconds, the platform would not detect the duplicate because the MatchCode was not yet generated. This blog post talks about how to work around this limitation by making use of the PersistInSyncOptionalParameter; however, when creating/updating a record through the UI, the parameter was not enabled by default so you would need to write code to force the MatchCode generation in a synchronous manner. In CRM 2011 the PersistInSyncOptionalParameter (or CalculateMatchCodeSynchronously) parameters no longer apply because MatchCode generation is always done synchronously. What this means is that regardless of how fast you are creating one record after the other (and theoretically how many threads you have), the CRM platform will always be able to detect immediately if you are creating a duplicate. The fact that you can rely on the Duplicate Detection Engine to capture all your duplicates without having to consider any time variable is a huge relief in my opinion.

Different Way to Specify Whether to Run Duplicate Detection on Record Create/Update
Not much has changed here except perhaps the syntax. By default, when you execute a create or an update request against the CRM Web Service, the record will be created/updated without checking for duplicates. If you want to override this behaviour so that you force the platform to check for duplicates you would need to set the SuppressDuplicateDetection optional parameter to “false”. Not specifying this optional parameter has the same effect as if you would set it to “true” (bypasses duplicate detection):
CreateRequest req = new CreateRequest();
Account acc = new Account()
{
    Name = name,
};
req.Target = acc;
req.Parameters["SuppressDuplicateDetection"] = false;

try
{
    service.Execute(req);
}
catch (FaultException<OrganizationServiceFault> ex)
{
    if (ex.Detail.ErrorCode == -2147220685)
    {
        // duplicate detected: Handle it here
    }
    else
    {
        throw;
    }
}


Enhanced Duplicate Detection Processing (UR5+)
With the release of UR5, there have been some other improvements to Duplicate Detection:
  1. You can specify in a rule whether or not duplicate detection should consider two null (blank) values as identical
  2. You can select to detect duplicates only from active records, this can be useful to improve performance.
    What is Still Missing?
    In my opinion, the most fundamental feature that Duplicate Detection still lacks is the ability to package duplicate detection rules as part of a solution. It is quite painful to have to replicate the rules in all environments an having to do it directly in production can be scary, some rules are quite complex and error prone. I hope Microsoft has plans for improving their solution framework in the future to include “solutionizing” duplicate detection rules!