POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

Congratulations! You’ve made the decision to switch from Salesforce.com to Microsoft Dynamics CRM! We may be biased, but we’re pretty positive that you are going to love the features and benefits of Dynamics CRM! Now that you’ve made the switch, you may be wondering how you can migrate your previous Salesforce.com tasks into your new CRM activities. Well, in today’s blog we have those answers for you! So without further ado, let’s begin!

tips-and-tricks

For the created look up tables, we used Scribe Insight to populate the values. You will need to get all of your CRM records and insert SF ID and CRM Guid in the new staging mapping table as well. IDs in Salesforce are made up of an 18-character-long, case-sensitive string and the data source table has several key fields that will drive our logic. The first three characters identify which Salesforce entity the records belongs to. As you can see from the image below, all records in the ID column start with “00T”, which translates to task.


This table illustrates some other prefixes commonly used by Salesforce.


For this example, we load source data into the SQL Server staging data base. This makes the processes much easier to implement by creating lookup/translation tables for the entities that were loaded into the CRM system previously, thereby eliminating the need to make expensive calls to the CRM services in order to populate lookup fields in the new records.

For instance, when the user import is performed, we create a two fields table:

CREATE
TABLE [dbo].[SF2CRMUserMap](

    [SfId] [nchar](18)
NOT
NULL,

    [CrmId] [uniqueidentifier] NULL

CONSTRAINT [PK_SF2CRMUserMap] PRIMARY
KEY
CLUSTERED

(

    [SfId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY
=
OFF, ALLOW_ROW_LOCKS
=
OFF, ALLOW_PAGE_LOCKS
=
OFF) ON [PRIMARY]

)
ON [PRIMARY]

You then do the same for all other entities previously loaded that you need for a successful Activity assignment. Going forward, you simply need to construct a SQL statement to get the source rows and then prepare them for the CRM import.

SELECT


ISNULL(cb.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) CreatedByGuid,


ISNULL(mb.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) ModifiedByGuid,


ISNULL(o.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) OwnerGuid,

a.CrmGuid as AccountGuid,

WhoEntity =

    CASE

    WHEN
LEFT(t.WhoId, 3)
=
‘003’
THEN
‘contact’

    WHEN
LEFT(t.WhoId, 3)
=
’00Q’
THEN
‘lead’

    WHEN a.CrmGuid IS
NOT
NULL
THEN
‘account’

    WHEN t.WhoId =
‘000000000000000AAA’
THEN
‘systemuser’

    ELSE
NULL

    END,

WhoGuid =

    CASE

    WHEN
LEFT(t.WhoId, 3)
=
’00Q’
THEN (SELECT CRMId FROM SF2CRMLeadMap WHERE SFId = t.WhoId)

    WHEN
LEFT(t.WhoId, 3)
=
‘003’
THEN (SELECT CrmGuid FROM SF2CRMContactMap WHERE SFId = t.WhoId)

    WHEN a.CrmGuid IS
NOT
NULL
THEN a.CrmGuid

    WHEN t.WhoId =
‘000000000000000AAA’
THEN (SELECT crmid FROM SF2CRMUserMap WHERE SFId = t.OwnerId)

    ELSE
NULL

    END,

WhatEntity =

    CASE


WHEN a.CrmGuid IS
NOT
NULL
THEN 1

    WHEN
LEFT(t.WhatId, 3)
=
‘006’
THEN 3

    WHEN
LEFT(t.WhatId, 3)
=
‘a0F’
THEN 123

    WHEN
LEFT(t.WhatId, 3)
=
‘500’
THEN 112

    WHEN
LEFT(t.WhatId, 3)
=
’01t’
THEN 1024

    WHEN
LEFT(t.WhatId, 3)
=
‘701’
THEN 4400

    WHEN
LEFT(t.WhatId, 3)
=
‘001’
THEN 1

    ELSE 8

    END,

    WhatGuid =

    CASE

    WHEN a.CrmGuid IS
NOT
NULL
THEN    a.CrmGuid

    WHEN
LEFT(t.WhatId, 3)
=
‘a0B’
THEN (SELECT CRMId FROM SF2CRMDepartmentMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘006’
THEN (SELECT CRMId FROM SF2CRMOpportunityMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘a0F’
THEN (SELECT CRMId FROM SF2CRMCompetitorMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘500’
THEN (SELECT CRMId FROM SF2CRMCaseMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
’01t’
THEN (SELECT CRMId FROM SF2CRMProductMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘701’
THEN (SELECT CRMId FROM SF2CRMCampaignMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘001’
THEN (SELECT CrmGuid FROM SF2CRMAccountMap WHERE SFID = t.WhatId)

    ELSE
NULL

    END,

t.*

FROM sf_Task n

LEFT
OUTER
JOIN SF2CRMAccountMap a ON t.AccountId = a.SFID

LEFT
OUTER
JOIN SF2CRMUserMap cb ON n.CreatedById = cb.SFId

LEFT
OUTER
JOIN SF2CRMUserMap mb ON t.LastModifiedById = mb.SFId

LEFT
OUTER
JOIN SF2CRMUserMap o ON t.OwnerId = o.SFId

As you can see, there are some default GUIDs set in the query. This is done in order to assign records from the users that didn’t make it into CRM to a default/migration user.

Now you have the query ready, tuned, and the indexes on the lookup fields set. The next step will be to set the mapping in Scribe Insight and map the fields accordingly.



State code and status code formulas used for this mapping are below:

S21 – “IsClosed” bit field and S14 – “Status” text field.

IF(TRIM(S21),1,0)

IF(S14=”Completed”,5,IF(S21,5,

(IF(

ISPICKLISTVALUE(TRIM(S14),”CRM”,”task”,”statuscode”,””),

TRIM(S14),2))))

All that’s left now is to map the other fields accordingly, test, and run. When the job is finished, you will have all the tasks set and assigned to the appropriate records in CRM!

That’s all for our blog today! Still not sure if making the switch from Salesforce.com to Microsoft Dynamics CRM is right for you? Let us convince you!

Happy CRM’ing!

Avatar for JoeCRM

JoeCRM

Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more businesses and organizations around the world.