Tag Archives: 4.0

Overcoming the Limitations of CRM’s Duplicate Checking Functionality – Part 2

In my earlier post I described how CRM’s duplicate checker will happily find matches on partial matches due to its approach of matching blanks.  A rule like:

… will match on just the First Name and Last Name fields when the E-mail field is blank, effectively opening your matching rule up to be just a name match, which can be far too broad.

The solution requires a bit of coding.  Here’s the approach:

1. Create a new attribute on the Contact entity to hold a shadow copy of the Email address field, we don’t need it to be visible to our users so make it non-searchable and don’t bother placing it on the form.

2. Create a Plug-In or Custom Workflow Assembly that will either copy the value of the Email field into this new attribute, or, in the absence of a value, will paste the GUID of the Contact record into our new Email Match Field.

3. Change your duplicate check rule to refer to the new Email Match Field instead of the standard Email field.

And you’re done.  A new Contact’s first name, last name and email address will be compared to the existing contacts, each of which will either have a proper email address or a GUID, so a match will require an existing contact to have the same email address.

Note: You’ll need to run your customisation across your existing data so keep that in mind when deciding on your approach.  

If you want to match on name + phone number you can follow the same approach but in your custom code consider stripping the formatting out of your phone number to ensure formatting variations don’t hide any duplicates.

Advertisements

Overcoming the Limitations of CRM’s Duplicate Checking Functionality

Microsoft CRM comes with a real time duplicate checking engine that warns users of potential duplicates as they enter new records.  Sounds good doesn’t it?  However, it’s not a comprehensive solution and has a few weaknesses especially when you start dealing with large numbers of customer records where the likes of good old John Smith starts to pop up a lot.   But with a bit of targeted customisation you can get it working for you.

Let’s start with some requirements.  Say we want a duplicate check to run as new Contacts are entered which looks for any existing contacts with the same name and email address.   Sounds useful.   And on the surface it looks like you can configure this.   Here’s what you would logically configure in CRM to try and address this requirement:

 

Now if you publish this rule and then push through a test you will find the duplicate checker doesn’t report a suspected duplicate when you expect it to.

The first thing to check is whether CRM has completed the creation of match code for this rule.  This shows up under System Jobs in the Settings area, look to see if a MatchCode job has run since you made the change, and check it has a status of Completed:

What CRM is doing here is its creating a match code for each Contact in the database based on the rule you defined.  In this case the match code string will be “<contact.fullname><contact.emailaddress>”.   The duplicate checker is then able to check against this one field to find duplicates.  CRM actually creates a new SQL table to house this matching data.

So, you need to let CRM complete that step, then you can retry your test.   But, again you will find the duplicate checker doesn’t appear to be working.

The problem appears to be an issue of timing.  The duplicate checker appears to be reading the ‘”Full Name” of the Contact being entered before CRM has actually calculated what that is (based on the First Name and Last Name fields that were entered). 

The workaround to this is to have the duplicate rule refer to the First Name and Last Name fields instead, e.g..:

Again, wait for CRM to rebuild it’s match code table, and then retry your test. 

Now, you should see the duplicate warning starting to appear:

I mentioned customisation earlier and we haven’t had to do any yet, and yet it looks like we have this working now.   One problem.   Try entering a new Contact with the same name as an existing contact where neither of the Contacts has an email address.   In this instance the duplicate checking engine will consider this to be a match.   So, in the absence of email addresses we are now matching on name alone.   If dealing with a lot of customers then this will result in a lot of false positives being reported, which frustrates users, who start to pay less attention to the duplicate detection dialog box.

The reason CRM is getting this wrong is because of it’s match code approach.   Imagine these contacts exist in your CRM:

Full Name Email Address Match Code
John Smith johnsmith@abc.com JohnSmithjohnsmith@abc.com
John Smith johns@xyz.co.nz JohnSmithjohns@xyz.co.nz
John Smith null JohnSmith

And you enter a new John Smith without an email address.   CRM determines its match code as:

John Smith null JohnSmith

 

And it then looks up that match code in its match code table, and find record # 3.

Stink.  😦

Next post, I’ll show you to address this.

CRM Reporting Tips and Tricks for Faster Query Performance #2 – Using Table Variables

If you have a report query that joins multiple data sets together then consider using table variables to speed up query performance.

Here’s an example:  I have a report with a Sales Team parameter that allows the user to select one more Sales Team to report on.  The report then displays the open appointments, quotes, and opportunities owned by the Account Managers in those Sales Teams.

Typically I would write my query like this:

select metric_name = ‘Appointments’,  metric_count  = count(*)
from filteredappointment
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)

union all

select metric_name = ‘Opportunities’,  metric_count  = count(*)
from filteredopportunity
where statecodename = ‘Open’
and createdby in (select systemuserid from FilteredSystemUser where new_saleteamid in @Team)

union all

select metric_name = ‘Quotes’,  metric_count  = count(*)
from filteredquote
where statecodename = ‘Open’
and createdby in (select systemuserid from filteredsystemuser where new_saleteamid in @Team)

But recently I’ve found with more complex queries I’ve needed to take a slow performing component of my query and specifically direct SQL to process that component and to then place the results in a table variable for me to refer to later in the query.   In the above query each of the 3 query components refer to the list of users that should be reported on.   By using a Table variable I can query for and store that user list as the first step in my query and then join that result set into each of the 3 queries.   This approach looks like this:

DECLARE @USERS_SELECTED TABLE(USERS varchar(50))

INSERT INTO @USERS_SELECTED (USERS)
(select systemuserid from filteredsystemuser where new_saleteamid in @Team)

select metric_name = ‘Appointments’,  metric_count  = count(*)
from filteredappointment a
inner join @USERS_SELECTED x on a.createdby = x.USERS
where statecodename = ‘Open’

union all

select metric_name = ‘Opportunities’,  metric_count  = count(*)
from filteredopportunity o
inner join @USERS_SELECTED x on o.createdby = x.USERS
where statecodename = ‘Open’

union all

select metric_name = ‘Quotes’,  metric_count  = count(*)
from filteredquote q
inner join @USERS_SELECTED x on q.createdby = x.USERS
where statecodename = ‘Open’

Now the above example is very simplistic, but this approach has proved very successful for me.   If your SQL statement contain a number of selects which then get aggregated up and joined together, then you will benefit from this too.   If your query is performing poorly run each of the selects in isolation, find the slow running one and try running it at the start of your SQL and place the result set into a table variable.  You might start to find that these sorts of complex SQL statements become a lot more readable as well if you follow this approach.  I have one query where I have followed this approach for each of the ten selects embedded in the query giving me ten of these work tables which the query then joins together in a very simple SQL statement at the end of the query.  Very easy to read and very easy to troubleshoot.

I hope this helps.

CRM Reporting Tips and Tricks for Faster Query Performance #1 – UTC Date Fields

Anyone writing CRM reports will be familiar with CRM’s handy filtered views.   The requirements these views were designed to meet were quite complex.  They needed to filter the data returned based on CRM’s record level security and they needed to localise the data returned based on the user’s time zone and language preferences.   They achieve all this, but at a price, performance.   You need to lift your game with CRM and put some serious analysis into your queries and into what SQL is being asked to do to deliver the responses to your queries.

If you are suffering from slow running reports and your reports are selecting their data based on dates then try switching your queries to use CRM’s native UTC fields.  You probably followed your instinct and used CRM’s handy localised date fields rather than the native UTC fields – e.g. you wanted to return Opportunities created between two dates, so you wrote something like the below (with start and end date variables temporarily added, to be replaced later by report parameters):  

DECLARE @STARTDATE as Date
DECLARE @ENDDATE as Date
SET @STARTDATE = ‘2010-04-07’
SET @ENDDATE = ‘2010-04-11’

SELECT *
FROM FILTEREDOpportunity
WHERE CONVERT(DATE, CREATEDON) BETWEEN @STARTDATE AND @ENDDATE

In order to return your result set SQL Server has to check each Opportunity record in the database in turn, taking the value in it’s OpportunityBase.CreatedOn field (which is the creation date in UTC time) and run that through a function that converts that UTC value into your time zone so that it can determine whether that value falls in the last 30 days.   There’s a fair bit of processing required there and you are stifling SQL’s ability to use its indexes.  

Here’s a much more efficient query: 

DECLARE @STARTDATE as DateTime
DECLARE @ENDDATE as DateTime
SET @STARTDATE = ‘2010-04-07’
SET @ENDDATE = ‘2010-04-11’

DECLARE @STARTDATEUTC as DateTime
DECLARE @ENDDATEUTC as DateTime
SET @STARTDATEUTC = dbo.fn_LocalTimeToUTC(@STARTDATE)
SET @ENDDATEUTC = dbo.fn_LocalTimeToUTC(CONVERT(DATE, (@ENDDATE + 1)))

SELECT *
FROM FILTEREDOpportunity
WHERE CREATEDONUTC BETWEEN @STARTDATEUTC AND @ENDDATEUTC

This query takes the initial start and end dates that are to be used and determines the equivalent UTC datetime values.  It then compares CRM’s native UTC createdon field to this UTC date range to find the records.  Where as before SQL Server had to translate the createdon date of each Opportunity record it can now use the native value and the only conversion is required is the initial one-off translation of the parameter fields. 

I have found this type of approach to generate significant improvements.   But, you need to be very careful with your logic to make sure you don’t miss any records created either early or late in the day.  I suggest you have your query initially return your datetime fields in both UTC and localised formats and make you test thoroughly.

Hope this helps!

Regards,

Gareth

Analysing Workflows that Failed to Complete

There are a number of reasons why CRM workflow instances fail to complete.  It is important you regularly check for failed workflows to ensure the integrity of  your system. 

A failed workflow instance might be indicative of:

  • A workflow rule operating under a user context which has insufficient permissions assigned
  • A sloppily written workflow rule that in some scenarios attempts record updates to records that no longer exist
  • Environmental issues / timeouts / system errors
  • Missing email addresses

Each time a workflow rule is triggered a System Job is created.  These jobs carry a status and when a failure is experienced they are stamped with an error code, an error message and are set to a ‘Waiting’ status.

Here’s a useful bit of SQL I have been using to analyse a system’s failed workflow instances.  It provides a meaningful description for the error codes I have encountered.  I put this behind an Excel report so that I can pivot the results and monitor volumes.

select asyncoperation0.asyncoperationid as ‘asyncoperationid’, asyncoperation0.name as ‘name’,

asyncoperation0.regardingobjectidname as ‘regardingobjectidname’, asyncoperation0.operationtypename as ‘operationtypename’,

asyncoperation0.statuscodename as ‘statuscodename’, asyncoperation0.owneridname as ‘owneridname’,

convert(date,asyncoperation0.startedon,123) as ‘startedon’,

asyncoperation0.statecodename as ‘statecodename’, convert(date,

asyncoperation0.createdon,123) as ‘createdon’,

asyncoperation0.errorcode,

[message],

waitingreason = case                                         when asyncoperation0.errorcode = -2147204784 then ‘SQL error’

                                                                        when asyncoperation0.errorcode = -2147220946 then ‘Cannot updated closed record’

                                                                        when asyncoperation0.errorcode = -2147187962 then ‘CRM permissions issue’

                                                                        when asyncoperation0.errorcode = -2147218688 then ‘Object address not found or party maked as non-emailable’            

                                                                        when asyncoperation0.errorcode = -2147220969 then ‘Record to be changed no longer exists’           

                                                                        when asyncoperation0.errorcode = -2147204303 then ‘Maximum field length exceeded’

                                                                        when asyncoperation0.errorcode = -2147220970 then ‘Cannot access file – file in use’

                                                                        when asyncoperation0.errorcode = -2147201001 then ‘Request timed out’           

                                                                        when asyncoperation0.errorcode = -2147204718 then ‘Invalid email address for send email’                         

                                                                        else ‘<Unknown>’

                                                end

from FilteredAsyncOperation as asyncoperation0

where (asyncoperation0.recurrencestarttimeutc is null and asyncoperation0.statuscode = 10)

and asyncoperation0.errorcode is not null

/*and case                                            when asyncoperation0.errorcode = -2147204784 then ‘SQL error’

                                                                        when asyncoperation0.errorcode = -2147220946 then ‘Cannot updated closed record’

                                                                        when asyncoperation0.errorcode = -2147187962 then ‘CRM permissions issue’

                                                                        when asyncoperation0.errorcode = -2147218688 then ‘Object address not found or party maked as non-emailable’            

                                                                        when asyncoperation0.errorcode = -2147220969 then ‘Record to be changed no longer exists’           

                                                                        when asyncoperation0.errorcode = -2147204303 then ‘Maximum field length exceeded’

                                                                        when asyncoperation0.errorcode = -2147220970 then ‘Cannot access file – file in use’

                                                                        when asyncoperation0.errorcode = -2147201001 then ‘Request timed out’           

                                                                        when asyncoperation0.errorcode = -2147204718 then ‘Invalid email address for send email’                         

                                                                        else ‘<Unknown>’

                                                end = ‘<Unknown>’*/

order by asyncoperation0.startedon desc, asyncoperation0.asyncoperationid asc

Adding a button to the CRM toolbar to launch a CRM view

I have found that frequently there will be one key entity in CRM that users spend most of their time on.  Sometimes its Cases, other times its Opportunities.  No matter which entity it is you can improve the user experience by providing faster access to that entity by adding a toolbar button via CRM’s ISV config settings.  Here’s how…

Firstly, this is how it looks.   I’ve added a “My Opportunities” button to the CRM toolbar.  When clicked, a new Internet Explorer window is launched displaying the Opportunity entity, and specifically, displaying the My Open Opportunities view.  Not only have I given my users a 1-click navigation solution but I have also given them a way of launching this key view in a separate window where it can remain open and easily accessible whilst they jump around between other entities back in the main CRM window.    This works in the Outlook client and the web client, but the gains are probably felt more when using the Outlook client as you no longer ‘lose your place’ as you flick back to your Inbox or over to your Calendar.

Here’s what the ISV config entry looks like:

This sits under the <ToolBar> node.

The Button Icon attribute defines the relative path to the button’s image file.   You can create you own image files and point to those but I find you can normally find something useful in the _imgs folder on the CRM server.

The URL attribute is where we specify the URL of the view.   Easiest way to determine the URL of a view is:

– Navigate to the view in CRM

– From the More Actions menu, select Copy Shortcut Of Current View…

– Paste this into Notepad

Quick and easy, but makes CRM just that little bit more enjoyable to work with.

Suppressing CRM Menu Items Based on Privileges

Your first step to simplifying your CRM menu for your users should be to configure security roles that grant READ access to only those entities your users will be working with.  

e.g.. take away their permissions to the CASE entity and like magic no more CASE menu item…

Now, this approach only gets you so far.  For example you will find you have to give users READ permission to Users and Business Units and as a result the Settings and Administration menus remains visible to them.   And you might find you don’t want something like Competitors showing on the menu but you do want users to be able to specify against their Opportunities when they are closing them.

Microsoft have given us a a tool that helps us here – in the Sitemap definition we can add privilege checks to individual menu items.   The privilege check looks like this:

I have highlighted in yellow the section of the Sitemap that defines the Competitor menu item.   Inside the red box is the privilege check.  The addition of this line instructs CRM:  “hide the above menu item unless the user is assigned a security role that gives them CREATE permission on the COMPETITOR entity”.

So, once you’ve trimmed back security roles have a look at what your users are left with and where you want something visible only to your super users ask yourself “what permission do my super users have that my normal users don’t?” and add the corresponding privilege check.

Some examples:

– If Queues are used for Case assignment by team leaders, and your standard user does not have permission to assign Cases then add a privilege check to the Queues menu item based on the ASSIGN permission to the CASE entity.

– If you don’t want the SETTINGS menu to appear then you can hide it using this approach but you will need to add the privilege check to each menu item that is appearing under the SETTINGS menu.  Consider adding a privilege check to each menu item that references the CREATE permission on the USER entity.  This will hide the entire Settings area from everyone except those users who are allowed to create new users.

– In the below example Marketing menu items are each given a privilege check:

Now, if you are struggling to pick a permissions test that will work for you, consider creating custom entities just to support this approach.  For example, create a new entity called HiddenMenu and ensure the security role your senior management users are assigned includes READ permission to this entity.  Then, in your Sitemap file go to each menu item that you want available only to those senior management users and add a privilege check that requires the user has the READ permission to the HiddenMenu entity.   Easy 🙂