Importing Duplicate Detection Rules

Since early versions of Dynamics 365, we have been using Duplicate Detection rules and it is a handy feature. Ironically, still we can't import those rules with Solution. In most cases, users configure them in different environment and its not a big deal. Still, it can be error-prone and inconsistent.

So alternative is to consider Duplicate Detection Rule entities as reference data and import them using Configuration Migration Tool coming with SDK. Usually you will find this tool in below location of the SDK;

\SDK\Tools\ConfigurationMigration

Anyway, below are the two points to keep in mind when importing Duplicate Detection rules;

1) Rules should be Unpublished before importing.

2) Need to include both Duplicate Detection Rule and Duplicate Rule Condition in correct Order.

Hope this helps.

Handle comma separated string stored in a cell in SQL table

This is a challenging thing to do in SQL queries, though one single task that needs to be done in most of the instances. In other words, our challenge is to loop through the values stored in single cell.
My Sample string: Mango, Orange, Pine Apple, Avocado (Separator can be anything)

1) PARSENAME

This is my first approach. PARSENAME is a special function we can use to retrieve item by item in string of items, but separator should be just “.” (i.e. dot). In fact, in my code I am first replacing my separator (which is “;”) to a dot.

Anyway, biggest drawback with this is, PARSNAME seems to be working only up to 4 items.

DECLARE @FruitStr VARCHAR(500)
DECLARE @NumberOfItems int
DECLARE @Count int

SET @FruitStr = 'Mango;Orange;Pine Apple; Avocado'

SET @FruitStr = Replace(@FruitStr,';','.')
SET @NumberOfItems = (len(@FruitStr) - len(Replace(@FruitStr,'.','')))+1

SET @Count = 1

WHILE @Count <= @NumberOfItems
    BEGIN
       PRINT PARSENAME(@FruitStr,@Count) --this is the single item
       SET @Count = @Count + 1;
    END;

2) For any number of items

This method works for any number of items. What I do here is identifying first item within a loop (by searching first separator) and looping through removing the item we already read.

DECLARE @FruitStr VARCHAR(500)
DECLARE @FruitItem VARCHAR(50)
SET @FruitStr = 'Mango;Orange;Pine Apple; Avocado'
 
SET @FruitStr = @FruitStr + ';'

WHILE CHARINDEX(';',@FruitStr,1) != 0
BEGIN
    SET @FruitItem = SUBSTRING(@FruitStr,1,CHARINDEX(';',@FruitStr,1))
    PRINT LEFT(@FruitItem,LEN(@FruitItem)-1) --this is the single item
    SET @FruitStr = SUBSTRING(@FruitStr,CHARINDEX(';',@FruitStr,1)+1,LEN(@FruitStr))
END

Hope this is helpful.

One practical usage I came through;

Once I needed this solution in Data migration for a CRM where source file for activities came with comma separated lists for Email addresses for Email activities or attendees of the Appointments. This way I managed to create JSON objects which were then injected through KingswaySoft Adaptor.