Migrate Connections using Kingswaysoft Adapter

We all use Kingswaysoft Adapter to migrate data to Dynamics CRM. Just thought of sharing related tricks.

Here I reveal how to migrate connection, which can be little tricky than other entities. Important part is we need Connection roles are configured correctly. In my example, Employee and Employer Roles needs to be configured as illustrated below.

Account (i.e. Organisation in given example) is Employer and Contact (i.e. Individual in given example) is Employee. See how they associated with each other and enabled for particular entities.
Now see the fields we need to map with relevant information. We pass correct texts for Roles and Guids for related records. We need to pass object type code as well.

Actual fields we are mapping here as below;

Record1id = Connected To
Record1roleid = Role (To)
Record2id = Connected From
Record2roleid = Role (From)

For Roles need to resolve with Primary field as below since we are passing the names. For the safe side I enable the "Ignore Case". Still we need to make sure spellings are correct.

Object Type Code for Custom entities
This is tricky since this value can be different based on environment. Just check the URL of an open record of same type to obtain it.

Passing and Receiving values (parameters) in Custom workflow

Just thought of posting this code snippet on input/output parameters in CWF. In most of the places it explains well on how to declare the parameters, but not using of get, set methods in the code.

Here how we declare them;

[RequiredArgument]
[Input("Salary")]
public InArgument<Double> SalaryVal { get; set; }

[OutputAttribute("Tax")]
public OutArgument<Double> TaxVal { get; set; }

...and this is how you associate to the code within the Execute method;

protected override void Execute(CodeActivityContext executionContext)
{
//...........
//...........

Double _salaryVal = SalaryVal.Get(executionContext);

// Calculation goes here
// and return to _taxVal

TaxVal.Set(executionContext, _taxVal);

}

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.

Read PartyList types and items

It is not to say that PartyList data type in Activities are the most complex ones in Dyanmics CRM. They can be called as multi-lookups of multi types. I had to play a bit to understand how I am going to identify item with specific entity type and do something. Though of sharing the code; in this example, I am checking the attendee list of appointment and see if I have Contact type to do something with those items.

EntityCollection _ReqAttendees = _appointmentContext.GetAttributeValue<EntityCollection>("requiredattendees");
if ((_ReqAttendees != null) && (_ReqAttendees.Entities.Count > 0))
{
  foreach (var _party in _ReqAttendees.Entities)
  {
    if (_party.GetAttributeValue<EntityReference>("partyid").LogicalName == "contact")
    {
       // Do the work 
       // Contact ID : _party.GetAttributeValue<EntityReference>("partyid").Id
       
    }
  }
}

Hope this is helpful.

If value exists in the array

Just a small code snippet to check if given value exists in an array. This is for integer, but can be applied to any type.

        public static bool isInIntArray(int[] _array,int _val)
        {
            if ((Array.IndexOf(_array, _val)) > -1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

In CRM, I use this especially when need to compare option set values for complex logics.

Cheers!

Delete duplicates and keep only the latest record

This SQL code could be helpful since it’s a general requirement to remove duplicates while keeping latest record.

Considered example, we have a primary key called Id and Account Number is the field we are concentrating on.

Though this code is a loop, this works quickly since loop goes only for records with duplicates.

DECLARE @AccountNumber uniqueidentifier
DECLARE @ID VARCHAR(50)

DECLARE _cursor1 CURSOR for
 SELECT A1.AccountNumber
 FROM Tbl_Account as A1
 WHERE A1.AccountNumber is not null
 GROUP BY A1.AccountNumber
 HAVING (SELECT COUNT(A2.AccountNumber) FROM  Tbl_Account A2 WHERE A2.AccountNumber = A1.AccountNumber)>1

OPEN _cursor1

FETCH NEXT FROM _cursor1 INTO @AccountNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN

    SET @ID = NULL

    SELECT TOP 1 @ID = Id FROM Tbl_Account WHERE AccountNumber = @AccountNumber ORDER BY [ModifiedOn] DESC
    
    DELETE FROM Tbl_Account WHERE AccountNumber = @AccountNumber AND Id <> @ID
 

FETCH NEXT FROM _cursor1 INTO @AccountNumber
END      
CLOSE _cursor1

DEALLOCATE _cursor1

Enjoy!