How to migrate User Views and Shared Views from CRM OnPremise to CRM Online

on CRM Dynamics, MIGRATION, On Premise to Online, User Views, Shared Views

Hello everyone,

Today the article will be about :
"How to properly migrate the User Views + the Sharing of these views from an On Premise CRM to an Online one ?"

There are a lot of useful tools to migrate data from one point to the other (ex : Scribe which i used to migrate the main data).
But those don't handle all type of data, and more precisely the Views in this topic.

For this case, you will have to use your favorite Visual Studio and do a bit of code. Lucky you, since I was in this situation few months ago, I will give you the guidelines and pieces of code to make your life easier.

From the Webservice provided by the CRM (either Online or OnPremise) it's not possible to query the data you need to actually do the migration of these data.

First Step : Querying the data

Here you have two possibilities, I've used both and it really depends on how you need to proceed.
- First option would be to dynamically query the database when you run your program with a simple "sqlCommand", and here is the magic query to retrieve all views from all users : 

SELECT UserQueryId, InternalEMailAddress, StatusCode, FetchXml, Description, ColumnSetXml, StateCode, UserQuery.Name, EntityLogicalView.Name as ReturnedTypeCode,  
LayoutXml, AdvancedGroupBy, ConditionalFormatting, ParentQueryId, 0 as Shared  
FROM [CRM_MSCRM].[dbo].UserQuery inner join[CRM_MSCRM].[dbo].EntityLogicalView on ReturnedTypeCode = ObjectTypeCode  
Inner join [CRM_MSCRM].[dbo].SystemUser on SystemUserId = OwnerId WHERE StatusCode IS NOT NULL  
UNION  
SELECT poa.PrincipalObjectAccessId as UserQueryId, su.InternalEMailAddress, uq.StatusCode, uq.FetchXml, uq.Description, uq.ColumnSetXml, uq.StateCode,  
uq.Name, elv.Name as ReturnedTypeCode, uq.LayoutXml, uq.AdvancedGroupBy, uq.ConditionalFormatting, uq.ParentQueryId, 1 as Shared  
FROM [CRM_MSCRM].[dbo].PrincipalObjectAccess poa  
inner join [CRM_MSCRM].[dbo].userquery uq on uq.userqueryid = poa.objectid  
inner join [CRM_MSCRM].[dbo].EntityLogicalView as elv on uq.ReturnedTypeCode = elv.ObjectTypeCode  
Inner join [CRM_MSCRM].[dbo].SystemUser as su on su.SystemUserId = poa.principalid  
WHERE poa.objecttypecode = 4230 and poa.principaltypecode = 8  
and  StatusCode IS NOT NULL  
ORDER BY InternalEMailAddress"

From this query, you have all necessary informations to recreate the view properly. This option is great if you have new data when you prepare your script and you need to actually run it.

- Second option , if you manage to block the write rights to your users during the migration, you can do the above query on your SQL Server and then keep the results in a text file. To re-use this text file, please follow the article of my colleague : Quick lookup in C# which is a huge time saver especially when you have thousands and thousands of records to migrate.

Second Step : check if the ObjectTypeCodes changed between your OnPremise and your Online for custom entities

For sure you will have to take care of this step since you can't simply import your database backup. Since the default entities such as Contact, Opportunity, Account, etc are installed by default the codes are the same, but it's not the case with you custom entities.

Why do you need to take care of this code ?
Because each view in based on this code to query and render the data.

Lucky you, you can dynamically retrieve these code to avoid having any static data to modify each time you want to test.
Here is the code to retrieve the new ObjectTypeCode : 

public string retrieveEntityCode(OrganizationServiceProxy _serviceOnline, string entitylogicalname)  
{
     Entity entity = new Entity(entitylogicalname);
     RetrieveEntityRequest EntityRequest = new RetrieveEntityRequest();
     EntityRequest.LogicalName = entity.LogicalName;
     EntityRequest.EntityFilters = EntityFilters.All;
     RetrieveEntityResponse responseent = (RetrieveEntityResponse)_serviceOnline.Execute(EntityRequest);
     EntityMetadata ent = (EntityMetadata)responseent.EntityMetadata;
     string ObjectTypeCode = ent.ObjectTypeCode.ToString();
     return ObjectTypeCode;
}

Third step : Modifying the LayoutXml content

As seen above, the ObjectEntityCode might have change and we saw how to retrieve the new ones.
We also know that a view contains a lot of fields (cf the sql query in the first step) and we will have a closer look to the "LayoutXml" one now.

The ObjectTypeCode is used in this field to tell the System which entity the data come from.

I used a simple Regex to match the property in my field value and replace it with the new EntityTypeCode, here is a sample of code : 

if (view.EntityLogicalView.Name.Contains("new_"))  
{
    string pattern = "object=\\\"([0-9]+)\\\"";
    Regex rx = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
    string newObjectTypeCode = "object=\"" + retrieveEntityCode(_service, view.returntypecode.ToLower()) + "\"";
    uQ.LayoutXml = rx.Replace(view.layoutxml, newObjectTypeCode);
}

This will simply replace the old code with the new one. You can see that I use the method seen abode : "retrieveEntityCode".
I check if the "EntityLogicalView.Name" contains my prefix as we need to consider only the custom entities.

We are almost good !

Fourth Step : Create the View record on the CRM Online

It's now time to create these views in the system, finally !
One important point to take care of here is that to associate each view to the correct user like it was on the OnPrem system, we need to use the impersonate functionality of the SDK.

So we create our UserQuery newview = new UserQuery(); and associate all properties to the object.
To manage the impersonification, you have to set the CallerId of you OrganizationServiceProxy object with the SystemUserId of your user.
Then the final line of code will be the service.Create(newview); and you are done !

Additional Step : the Shared View

I after the creation of all new views you now want to take care of the shared views between users, you simple have to go through the 4 steps above with two exceptions.

- The query over your Database :

SELECT [PrincipalId],su.[InternalEmailAddress], su2.InternalEMailAddress as OwnerOfView  
,[ObjectId]
,[ObjectTypeCode]
,[PrincipalObjectAccessId]
FROM [CRM_MSCRM].[dbo].[PrincipalObjectAccess] as poa INNER JOIN  
[CRM_MSCRM].[dbo].[SystemUser] as su on poa.PrincipalId = su.SystemUserId INNER JOIN
[CRM_MSCRM].[dbo].[UserQuery] as uq on uq.UserQueryId = poa.Objectid INNER JOIN
[CRM_MSCRM].[dbo].[SystemUser] as su2 on uq.OwnerId = su2.SystemUserIdWHERE objecttypecode = 4230 and principaltypecode = 8 AND su.InternalEMailAddress != su2.InternalEMailAddress AND su2.isdisabled = 1

- The creation of the shared records will be done in two steps :

  • We need to retrieve the user who had access to a user's view and which rights this user had on it. (We can't give full permission here, it makes no sense) To retrieve this access, we will need to query our OnPremise system like this : 
// Retrieve Users + AccessMask
var accessRequest = new RetrieveSharedPrincipalsAndAccessRequest  
{
    Target = new EntityReference(UserQuery.EntityLogicalName, IdOfMySharedView)
};

_serviceOnPremise.CallerId = UserOwnerOfTheViewOnPremiseId;  
var accessResponse = (RetrieveSharedPrincipalsAndAccessResponse)_serviceOnPremise.Execute(accessRequest);

if (accessResponse.PrincipalAccesses.Count() == 0)  
    return;

Here your accessResponse object will contains all the sharing, that's why i'm checking if there is any, because if it's not the case, no need to go further.

  • Now we will take care of the "Sharing" record creation. We will loop through each PrincipalAccess and do something : 
foreach (PrincipalAccess pa in accessResponse.PrincipalAccesses)  
{
    var grantAccessRequest = new GrantAccessRequest
    {
        PrincipalAccess = new PrincipalAccess
        {
            AccessMask = pa.AccessMask,
            Principal = new EntityReference(SystemUser.EntityLogicalName, UserWhoGetsTheViewOnlineId)
        },
        Target = new EntityReference(UserQuery.EntityLogicalName, IdOfMySharedView)
    };

    _serviceOnline.CallerId = UserOwnerOfTheViewOnlineId;
    var resp = _serviceOnline.Execute(grantAccessRequest);
}

Conclusion

If you were brave enough to read this block of text, i hope you got the overall picture of what I did.
From my experience with this migration, it's not hard to manage, the biggest problem here was to find what data I needed and how I was suppose to use it in order to create my records on the new system.
For example : I performed a full migration of my views (~ 15000) before I realized the ObjectTypeCode issue :)

I hope those guidelines will help.

Thanks for reading,
Happy CRM

Clement

Dynamics 365 CRM & Power Platform addict.