Wednesday, April 22, 2009

Code for Non-Programmers: Migrating Record Owners

I use this code in every Microsoft Dynamics CRM migration I do. We use a tool called Scribe for CRM migrations (ESSENTIAL). Scribe is a fantastic tool, but, despite their marketing otherwise, you DO need to know some programming code to have a successful migration.

DBLookup is a function that is used to populate fields in one entity in CRM that are linked to another entity in CRM. For instance, if your source data has record owners, you probably want the records in CRM to be owned by the same people. However, record owners are users, and to have the system know what user to link to a record as its owner, we must tell the system what the user's ID is. This is a background code that you never see, called a GUID. Without DBLookup, all records will all be owned by the person running the migration. DBLookup is also useful in populating Parent Companies or Primary Contacts or any of a thousand other linked fields.

In Scribe, you have Source fields, identified as S##, where ## is the counter of the field assigned by Scribe, and you have Target fields, in this case, our CRM fields. Any CRM fields that are linked to another entity are identified by two fields, one a text field that usually ends in "name" and the other the linking ID field, which is a 34 character GUID, the name of which always ends in "id". In this sample, let's say our Source field is S14 Record Owner. In the Target, CRM, we have Owner and Ownerid. Scribe does not allow you to link directly from your source text field to the CRM Owner field. To make the link, you would use the following formula on CRM's Ownerid field:

DBLookup(S14,"T","SystemUser", "Fullname","SystemUserID")

To a programmer, this is obvious. To the rest of us, here's the logic:

S14 is the Scribe reference number for the Source field. This is the only thing you would change in your application.

"T" means target. You are comparing the source information to data in the target. There are other choices like "S" for Source, but for Owner, you will always use "T".

"SystemUser" is the name of the table where the target data is found, in this case, the user table, because owners of records in CRM must be users.

"FULLNAME" is the name of the field within the SystemUser table that will have data matching your Source information (S14 in the sample). These must be exact matches.

"SystemUserID" is the computer's unique code field (GUID) associated with the data in the FULLNAME field. Until we have this GUID, the system does not know who the owner is.

There you go! Nothin' to it, right?

Look for more Code for Non-Programmer blogs. I'll include snippits for CRM customization and for Scribe migrations. If you want to contribute your code with explanations for non-programmers, I'd love to share it!

No comments:

Post a Comment