locked
extending "Detect Duplicates" with SQLServer SOUNDEX? RRS feed

  • Question

  •  

    I've only recently started using Dynamics4.0 and I'm just after a bit of an idea how to extend the "Detect Duplicates" functionality on saving new records (not running reports) by using the SOUNDEX capabilities of SQLServer.

     

    the bits I'm particularly stuck on are how to tap into the existing Detect Duplicates when submitting the form, and how this specific duplicates rule can be added to the entities I'm interested in.

     

    I've really only got two entities I'm concerned about and I'm using matching on last name and various combinations of street address.

     

    I've found one idea that looked promising

    http://advantageworks.blogspot.com/2008/01/extending-duplicate-detection-with.html

    - until I realised it was using the SoundEx algorithm in JavaScript, not SQLServer.

     

    so... has anyone done SQLServer Soundex on DynamicsCRM? anyone willing to offer a mudmap in how to get this going and then save it as a duplicates rule?

     

    thanx

    barry.b

     

     

     

    Monday, May 12, 2008 6:15 AM

Answers

  • I don't think Imarn's answer is correct. As far as I know you cannot change the way the duplicate detection mechanism works on the server (which is what you'd need to use the SQL SOUNDEX function).

     

    The only supported way I can see how you could do this is write a Plug-in to evaluate and the SOUNDEX result from SQL and store it in a custom attribute (one per text attribute you're applying SOUNDEX to), then use the custom attributes in the duplicate detection rule

    Monday, May 12, 2008 8:49 PM
    Moderator
  • Barry,

     

    Detecting duplicates based on SQL's SOUNDEX operator is a very useful functionality but it is only supported for English language. Since CRM is a software that is used internationally, we did not have it OOB.

     

    In order to achive the same result an ISV can very easily create a custom attribute and write a small plug-in that can store the output of SQL's SOUNDEX operator on not just one field but as many fields as she may like. By registering this plug-in pre-create and pre-update of an entity, she can have an easy and standard way of extending the duplicate detection feature. Now she can just define a duplicate detection rule based on Exact match of this new attribute and enjoy the rest of the duplicate detection functionality without doing any extra effort.

     

    I don't think that the effort required to build such functionality is anywhere near to the kind of power and flexibilty you are getting. Anyway, the real time, client-side duplicate detection that you are looking for is not supported OOB but can be easily achieved using CRM's duplicate detection public SDK and asynchronous java script calls.

     

    Thanks

    Abhishek

     

    Tuesday, May 13, 2008 9:32 AM

All replies

  • Yes you can extend it.

     

    See MS CRM SDK will help you

     

    Regards,

    Imran

     

     

    Monday, May 12, 2008 11:14 AM
    Moderator
  • I don't think Imarn's answer is correct. As far as I know you cannot change the way the duplicate detection mechanism works on the server (which is what you'd need to use the SQL SOUNDEX function).

     

    The only supported way I can see how you could do this is write a Plug-in to evaluate and the SOUNDEX result from SQL and store it in a custom attribute (one per text attribute you're applying SOUNDEX to), then use the custom attributes in the duplicate detection rule

    Monday, May 12, 2008 8:49 PM
    Moderator
  • many thanks David

     

    so you're saying there's no integration points at all I can get to in the detect duplicates section?

     

    "The only supported way" yes, that seems to be the limitation.

     

    OK, I'm just a touch closer now - that's the sort of noob mudmap I was looking for

    I need to research into plug-ins and how to register events against them

     

    (I'm trying to see if I can use the same checking for bulk data imports as well as form save). It's just the culture shock of needing to customise turnkey software instead of writing it from scratch...

     

    doesn't seem like a high demand for this sort of functionality. I must live in a region with notoriously bad spellers....

     

    thanks again David

     

     

    <thinking type="wishful">

    to be perfectly honest, what I'm really looking for is to capture the (in this case) the last name (or address) with that textbox's onBlur()  as an Ajax call back to the server where I can run a SOUNDEX query, returning a warning that there's a possible match and the operator should clarify it with the client (returns an array of entity ID's so the details can be called up if required so they throw away the current form before saving if the operator finds a match)

    </thinking>

     

     

    Monday, May 12, 2008 10:32 PM
  • Barry,

     

    Detecting duplicates based on SQL's SOUNDEX operator is a very useful functionality but it is only supported for English language. Since CRM is a software that is used internationally, we did not have it OOB.

     

    In order to achive the same result an ISV can very easily create a custom attribute and write a small plug-in that can store the output of SQL's SOUNDEX operator on not just one field but as many fields as she may like. By registering this plug-in pre-create and pre-update of an entity, she can have an easy and standard way of extending the duplicate detection feature. Now she can just define a duplicate detection rule based on Exact match of this new attribute and enjoy the rest of the duplicate detection functionality without doing any extra effort.

     

    I don't think that the effort required to build such functionality is anywhere near to the kind of power and flexibilty you are getting. Anyway, the real time, client-side duplicate detection that you are looking for is not supported OOB but can be easily achieved using CRM's duplicate detection public SDK and asynchronous java script calls.

     

    Thanks

    Abhishek

     

    Tuesday, May 13, 2008 9:32 AM
  • no ISV's around here I'm afraid. just us code monkeys and a boss breathing down our necks ...

    "the real time, client-side duplicate detection that you are looking for is not supported OOB but can be easily achieved using CRM's duplicate detection public SDK and asynchronous java script calls."

    OK, so you're suggesting adding to the form's Javascript to make the Ajax calls, writing a bunch of independant code to then make SQL calls to the underlying CRM database? and that this is a totally seperate code execution path origionating from the "Add new entity" form all of the way down to the database?

    "
    By registering this plug-in pre-create and pre-update of an entity, she can have an easy and standard way of extending the duplicate detection feature."

    by that stage the horse has already bolted ... the operator has already typed in all the data required for a new entry BEFORE they realise it's a duplicate. then they have to throw it all away and retrieve the existing record...








    Tuesday, May 27, 2008 2:16 AM