Synchronize (Bi-Directional) Dynamics 365 CRM Cloud data with SQL Azure using Skyvia’s Synchronization Package


Continuing our previous post where we used the replication package

https://nishantrana.me/2019/11/13/easily-set-up-dynamics-365-ce-crm-replication-incremental-to-azure-sql-sql-on-premise-using-skyvias-data-integration-services/

here we will see how we can use the synchronization service for bi-directional sync.

Below of the prerequisites of synchronization package to perform bi-directional sync –

  • We need to use the created on and modified on fields of the Entity
  • The synchronized tables to have an auto-generated primary key.

So here we need to alter the SQL Azure’s Contact table that was auto-created earlier by the Replication package to have the auto-generated primary key.

Connect to the SQL Azure database using Skyvia’s Query service and perform the alter operation

Or use the Query Editor (preview) of Azure

With necessary changes made, let us create a new Synchronization package

Specify the connection and click on Add Task to create the synchronization task.

We have specified contact entity from Dynamics CRM Source and contact table in SQL Azure which was earlier created for us by the replication package.

It automatically maps all the columns here based on the column name.

We can also manually specify mapping here for both Source To Target and Target to Source fields to be used for sync.

Click on Finish once done with the mapping.

Similarly to replication packages, the synchronization packages can be scheduled.

Click on Save to create the Task.

Click on Run

The run history shows the 220 records synced from Dynamics CRM to SQL Azure.

We can see the 220 records created in our SQL Azure DB

Now let us update a record in SQL Azure and run the package again.

We can see the record updated in Dynamics CRM

Now let us delete a few records from Azure SQL and run the package

We can see the records delete from Dynamics CRM

Similarly, let us delete a couple of records from Dynamics CRM and run the package

We can see the 8 records deleted in the destination SQL Azure

Thus, we saw how easy it is to do bi-directional sync using Skyvia’s Synchronization package.

Hope it helps..

Easily set up Dynamics 365 CE \ CRM replication (incremental) to Azure SQL / SQL On-Premise using Skyvia’s Data Integration services


In the earlier post, we saw how we can use Skyvia’s query feature to execute SQL command against our Dynamics 365 CE

https://nishantrana.me/2019/10/25/execute-sql-select-insert-update-and-delete-command-on-dynamics-365-customer-engagement-data-using-skyvia-query/

In this post, we will cover how we can use Skyvia’s replication feature to create a copy of our cloud data i.e. Dynamics 365 Online data in a relational database like SQL Server and most interestingly keep it up to date automatically with basic configuration steps.

Creating replication of cloud data can be useful in different data integration scenarios and most importantly for reporting and data analysis need as with online we are limited with the use of Fetch XML.

The Skyvia’s replication service takes care of syncing Create, Update and Delete operation performed on Dynamics 365 Entity records with the destination SQL Database.

For this we need to Skyvia expects an object’s key as well as modifiedon, createdon fields to be included for replication.

For delete to work it expects, audit history to be enabled on that Entity.

In this post, we will take a simple example of replicating Contact entity records from Dynamics 365 Online to SQL Azure.

Login to Skyvia

https://app.skyvia.com/#/packages

Create the connections

Source Dynamics CRM Connection:

Destination SQL Azure Connection:

We have a database named SampleDB already created in SQL Azure.

With connections created, now let us create a new package of type replication

For the replication, we have specified source connection as the CRM connection and destination as the SQL Azure.

We have selected the option of Incremental Updates along with Create Tables and Drop Tables option which will delete if there is an existing table of the same name as the entity and will create a new one.

In the Select Objects section, we have a specified Contact entity. We can also select multiple entities for replication.

Edit task option in the selected entity let us specify the fields and filter criteria on the source.

We also have the option of scheduling that we will look at in the end of the post.

Save the package and click on Run to see it in action.

While it is running, the Run History section shows the details

It auto refreshes with all the details.

We can see the 228 records of contact records replicated in SQL Azure.

Now let us create a new contact and run the package again.

Here we have created a contact record as shown below in CRM.

On running the package, we can see that it has added a new record in the Run History

Back in SQL Azure also our count has increased to 229.

Now let us update one of the records in the source and run the package. The run history shows one of the records being updated.

Finally, let us delete a couple of records (9) from the source and run the package. (We need to make sure we have enabled Audit on Contact entity for delete to work)

Back in SQL Azure, we can see total record count decreased

Here we were running the package manually, however, in real-world scenarios we would be scheduling it.

For this, we can edit our package and navigate to the Schedule section and click on Set Schedule.

For e.g. we have scheduled here it to run every hour starting from 6 p.m. till 10 a.m.

On save, we can check the run history

Thus, we saw how easy it is to replicate the data from Dynamics 365 CE to our SQL Database using Skyvia’s Integration service.

To achieve the same using Scribe Online Replication Service

https://nishantrana.me/2019/01/09/using-scribe-online-replication-services-rs-to-replicate-dynamics-ce-data/

and KingswaySoft 

http://www.kingswaysoft.com/blog/2017/04/04/How-to-Retrieve-Incremental-Changes-from-CRM-in-your-ETL-Process

Hope it helps..

Single Instance / Organization Considerations in Dynamics 365 Customer Engagement / Dynamics CRM


Let us start from the scenario that the enterprise has decided to use Microsoft Dynamics CRM /365 CE as a preferred customer relationship solution for their organization.

For a large enterprise and/or having multiple different departments, the first question that comes up is, do they need to have a single instance or organization or multiple instance or organization. Well, that decision is not that easy but one thing is for sure the decision should be made as early as possible in the project.

Well you could refer to these posts that could help us to make the decision

https://medium.com/capgemini-dynamics-365-team/does-enterprise-scale-dynamics-require-multiple-instances-f1931a443930

https://nishantrana.me/2019/07/23/single-organization-vs-multiple-organization-single-instance-vs-multiple-instance-key-points-to-consider-in-dynamics-365-ce-on-premise/

Now suppose we have made the decision to go with the single instance \ organization, so what are some of the key points we need to consider.

There could be multiple ways of implementing and defining the segregation layer as well as what needs to be shared from data and customization perspective among the different business units, the most important being the security model and the business unit structure being one of the key components of it.

Check out the below Webinar –

http://www.xrmcoaches.com/2017/07/free-webinar-friday-creating-your-dynamics-365-security-model/

also listen to the below podcast to get some guidance around the setting up the security model

http://crmmvppodcast.com/episode-19-setting-up-your-security-architecture-like-a-crm-mvp

In one of our projects, the way we segregated different departments, after defining the appropriate business unit structure, was by having the Business Unit lookup (custom field) in all the common entities that were being shared by the departments and populating that field accordingly, every time record is created either through User Interface or through code and to refer the same field in the customizations (plugin, workflow etc.) as and when applicable.

In the other project, we were referring to the Business Unit lookup field in the System User / Team Entity.

Well there could be multiple ways of doing the same with its own pros and cons.

But what if you have already implemented for one of the units and didn’t consider that the same instance would need to be extended to accommodate the different business unit(s).

Well, then it is time to modify a couple of things to make the Business Unit structure scalable (if it is not).

Let us go through the each of the main components one by one and how they can be segregated among different business units.

Wrong root business unit name?

Set Parent Business Unit lookup field as optional, open the Business Unit record and rename it.

Need to rename the organization?

For Online à

Navigate to Admin Portal, select the instance and click on edit and update the organization name and if required the URL as well as shown below.


For On-Premise à

  • Follow the below steps to update the Display Name

https://blogs.msdn.microsoft.com/darrenliu/2013/09/21/change-crm-organization-display-name/

  • Updating Organization Database Name

https://blogs.technet.microsoft.com/crmbipl/2008/01/14/how-to-change-crm-4-0-organizations-database-name/

Forms

Create separate new Main forms for different business units.

Enable appropriate security roles for the Form.

Views

Fields

Reuse the fields that are common instead of ending up creating a large number of fields.

Field security profiles can also be used if required when certain entity’s records are shared across the business unit and we want to hide the sensitive information.

Business Rules

Business rule can be configured to run on the scope of Form instead of the Entity.

Dashboards

For Dashboards, we can enable the security roles.

Workflows

  • Use scope fields to make sure the workflows fire appropriately.
  • Or put the first step to check for which business unit it is firing (depends on the segregation logic implemented)

Plugins

Have a field in the records to identify to which Business Unit it belongs to and check for this field as the first thing in the plugin or check for the Business Unit of the user under whose context the plugin is getting fired or use the secure \ unsecure configuration etc. it purely depends upon the way the segregation logic has been implemented.

SLAs

Use SLA lookup field to apply the SLA dynamically through plugin or workflow.

https://nishantrana.me/2019/07/24/apply-sla-choosing-the-sla-in-the-sla-lookup-field-for-the-sla-enabled-entities-in-dynamics-365-ce/

Business Process Flow

If there are multiple business process flow, make sure appropriate one is applied

Apply business process flow while creating an entity record.

https://www.powerobjects.com/blog/2019/04/24/business-process-flows-switch-bpf-dynamics-365-javascript/

There would be multiple other key points to be considered apart from the one listed above specific to each implementation; the above points are some of the things that can be taken as guidelines.

Lastly we must consider  creating different apps for different department \ teams\ users and add required solution components to it.

Design custom business apps by using the app designer

Please feel free to add comments and share your thoughts.

Hope it helps..

Generate Dynamics 365 CE (CRM) Business Unit Org Chart through SmartDraw


Recently we were looking at the options of generating a Business Unit Org Chart for quick review.

Using SmartDraw we were able to do achieve it very quickly.

Steps

  • Keep only the Name and Parent Business column and delete all the other columns including the hidden ones.

  • Excel with required columns.

  • Create the trial account of SmartDraw cloud

  • Click on Org Charts from the New Document menu.

  • Select Build Org Chart from Data option

  • Browse and select the xls file.

  • Click on Process.

  • Select Show in Shape for the columns and click on the Build diagram.

  • The result

  • The other option is to select any of the Organizational Charts

  • Select Import from file

  • Select the excel file

  • The result

  • The chart can be exported, shared and print.

Thus SmartDraw makes it very easy to generate the Org Chart for the Business Unit.

To achieve the same through Visio

http://www.planetxrm.com/12-steps-to-creating-crm-business-unit-org-charts/

Hope it helps..

Fixed – Workflows not firing “Waiting For Resources” in Dynamics 365 On-Premise


Recently in one of our environments, we started getting the issue that all our workflows were getting stuck at “Waiting for resources”.

As usual first step was to check if Microsoft Dynamics 365 Asynchronous Processing Service is running it or not.

The service was disabled, so we enabled it. But still, it didn’t fix the issue.

Next thing we tried was as suggested in the below article

https://www.powerobjects.com/blog/2013/03/28/how-to-fix-a-crm-workflow-stuck-in-progress/

The value for AsyncSdkRootDomain was blank which we updated followed by a restart of the service. Still no luck.

Eventually, we came to know that Servers setting for the roles Full Server were in the disabled state inside the Deployment Manager for the organization.

The reason CRM was still working because these services (including IIS Web Site) were already manually started earlier, however, the status was still disabled for this setting within Deployment Manager. (These settings were done by the network team who were planning to create the replica of these machines, and they wanted to disable the applications and our CRM technical team was unaware of it.)

Enabling it fixed the issue for it.

Hope it helps..

You do not have the necessary privileges to import security roles. Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed while importing solution in Dynamics 365 CE


Recently after upgrade of our Dynamics 365 CE from 8.2 to 9.1, when the System Administrator tried importing the solution (managed), he got the below error

You do not have the necessary privileges to import security roles. : Microsoft.Crm.CrmSecurityException: SecLib::CheckPrivilege failed. User: e7282250-7d82-e711-80df-1458d0431690, PrivilegeName: prvWriteRole, PrivilegeId: bd123e14-17ba-40f6-8d8b-18f4bffa7e50, Required

The error message is quite weird, first as we have never got this error earlier and here it was System Administrator user who was importing.

We tried importing the solution with the using another System Adminstrator account and it worked for that other user.

Hope it helps..

The workflow cannot be published or unpublished by someone who is not its owner error while importing solution in Dynamics 365 CE


Recently after upgrade of our Dynamics 365 CE from 8.2 to 9.1, when the System Administrator tried importing the solution (managed), he got the below error

The workflow cannot be published or unpublished by someone who is not its owner.

Error while importing workflow {ec05f40d-fd14-46ab-96b3-be1f5b108ef3} type Workflow name Marketing List – Update CT Relationship: The workflow cannot be published or unpublished by someone who is not its owner.

What the error says is that the user who is importing the solution needs to be the owner of the workflow that is there in that solution.

http://gonzaloruizcrm.blogspot.com/2011/05/processesworkflow-ownership-faqs.html

https://stackoverflow.com/questions/17159920/crm-2011-ru13-the-workflow-cannot-be-published-or-unpublished-by-someone-who-is

The thing was that we have never got this issue earlier for that System Admin user and we would have imported the solution at least 10 times earlier as well. This time the only thing different was that it was in the newer version 9.1

Changing the owner of the workflow and assigning it to the one who is importing will fix this issue. However interestingly and as expected when we tried importing the same solution using a different system administrator (again not the owner of the workflow in the target system), the import was successful with warning.

“The original workflow definition has been deactivated and replaced”

Read the below post to understand how workflows are managed internally

https://www.inogic.com/blog/2010/11/workflows-behind-the-scene/

Please share your thoughts, if someone has faced this issue recently and how they managed to fix this?

And what caused the issue to the first system admin and worked for the other.

Sample Code to retrieve instances using Online Management API in Dynamics 365 Customer Engagement


With version 9.0 of Dynamics 365 CE we now have Online Admin API that supports the following operations like Create, Retrieve, Delete, Backup and Restore instances. The user needs to have the Global Administrator or Service Administrator role in the Office 365 tenant to perform these operations.

As a first step we need to register the application with Azure Active Directory.

https://nishantrana.me/2016/11/13/register-a-dynamics-365-app-with-azure-active-directory/

and get the values for

Client Id and Authority.

Get the Service URL here

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/online-management-api/get-started-online-management-api#service-url

Sharing a sample code which can be used to retrieve all the instances in a specific tenant.

The sample code: –


public class MySampleApp
{

private const string Resource = "https://admin.services.crm.dynamics.com/";

// username and password
private const string UserName = "username.onmicrosoft.com";
private const string Password = "password";

// client id and authority of the application registered
private const string ClientId = "b2c5028d-57e6-4df7-9940-3243214948b";
private const string Authority = "https://login.microsoftonline.com/3432432e2-5fbe-4b78-a55a-bcb342d4f859/";

private static AuthenticationResult _authResult;

private static void Main(string[] args)
{
var authContext = new AuthenticationContext(Authority, false);
var credentials = new UserCredential(UserName, Password);

// Get the token
_authResult = authContext.AcquireToken(Resource, ClientId, credentials);

Task.WaitAll(Task.Run(async () => await GetInstances()));
}

private static async Task GetInstances()
{
var httpClient = new HttpClient
{
BaseAddress = new Uri(Resource),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add("OData-MaxVersion", "4.0");
httpClient.DefaultRequestHeaders.Add("OData-Version", "4.0");
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer",
_authResult.AccessToken);

var retrieveResponse =
await httpClient.GetAsync("/api/v1/instances");

if (retrieveResponse.IsSuccessStatusCode)
{
var jRetrieveResponse =
retrieveResponse.Content.ReadAsStringAsync().Result;

var result = JArray.Parse(jRetrieveResponse);
foreach (var data in result)
Console.Write("Id " + data["Id"] + "\nUnique Name " + data["UniqueName"]);
}
}
}

Result: –

The other properties: –

SiteMapName in the AppModuleSiteMap is null or empty error while importing V9 Solution in Dynamics 365 Customer Engagement


We recently upgraded our Dev and Test environment to V 9.0.

Dev

Test

While importing a solution from Dev to Test which had SiteMap we got the below issue.

“The SiteMapName in the AppModuleSiteMap is null or empty”

It turns out that the following tags were required and were missing in the Customization.xml

We added that tag and were able to import the solution successfully.

Interestingly if we export the same solution from Test and search for this tag, it is again missing, however, we can see the following XML section added there

This section was not there in dev’s sitemap.

Hope it helps..

Different Updating Instance status during Dynamics 365 Upgrade


The day has come when we had scheduled the upgrade of our Dynamics 365 instance Test Instance(from 8.2 to 9.0).

We can see the below message during the scheduled time à “The update will being within 24 hours. No rescheduling is available”.

The following Reschedule Update option will go missing during the scheduled time.

After waiting for few minutes, we see the Update Status as Queue (Not Started)

Followed by the status as “Database Upgrade”

In between the Status message will be Backup and Restore.

https://docs.microsoft.com/en-in/dynamics365/customer-engagement/admin/manage-updates#update-status

Then it kept interchanging the status from Updating Instance (with no status) to Updating Instance – Database Upgrade for close to 2.5 hours.

Then although the status was Updating Instance the Open link got enabled after 2.5 hours.

Which opened the following notification

And finally, after 10 minutes or so à

We were all set with version 9.1

In total it took around 3 hours approximately.

Time to enjoy the successful upgrade and explore the new features ..

 

Plugin on Pre-Validation Stage in Dynamics 365 CE


Recently we had a requirement to delete the Account record without deleting the associated Contact records.

If we try deleting the account record we’d get the following message box

The relationship definition can’t be updated as well to achieve this

So, we wrote a plugin on the pre-validation stage of pre-delete event of Account, which will retrieve and loop through all the child contact records and set its parent customer field as null.

In case of pre-operation the child records were not available.


public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext pluginContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService organizationService = serviceFactory.CreateOrganizationService(pluginContext.UserId);
EntityReference targetEntity = (EntityReference)pluginContext.InputParameters["Target"];
QueryExpression getContacts = new QueryExpression("contact");
getContacts.Criteria.AddCondition(new ConditionExpression("parentcustomerid", ConditionOperator.Equal, targetEntity.Id));EntityCollection allContacts = organizationService.RetrieveMultiple(getContacts);foreach (Entity contact in allContacts.Entities)
{
Entity contactToBeUpdated = new Entity("contact");
contactToBeUpdated.Id = contact.Id;
contactToBeUpdated.Attributes["parentcustomerid"] = null;
organizationService.Update(contactToBeUpdated);
}}

 

Another practical scenario

https://www.inogic.com/blog/2017/03/plugin-pre-validation-operation-to-show-an-error-message-as-well-as-log-the-error/

Hope it helps..

Using RetrieveRecordChangeHistoryRequest to get Audit Detail in Dynamics 365 CE


Just sharing a sample code to retrieve the Audit Detail using RetrieveRecordChangeHistoryRequest.


RetrieveRecordChangeHistoryRequest changeRequest = new RetrieveRecordChangeHistoryRequest();
changeRequest.Target = new EntityReference("entitySchemaName", "entityId"));
RetrieveRecordChangeHistoryResponse changeResponse =
(RetrieveRecordChangeHistoryResponse)_service.Execute(changeRequest);

AuditDetailCollection auditDetailCollection = changeResponse.AuditDetailCollection;

foreach (AttributeAuditDetail attrAuditDetail in auditDetailCollection.AuditDetails)
{
var auditRecord = attrAuditDetail.AuditRecord;

Console.WriteLine("Entity: " + ((EntityReference)auditRecord.Attributes["objectid"]).LogicalName);
Console.WriteLine("Operation: " + auditRecord.FormattedValues["operation"]);
Console.WriteLine("Action: " + auditRecord.FormattedValues["action"]);

var newValueEntity = attrAuditDetail.NewValue;

foreach(var attrNewValue in newValueEntity.Attributes)
{
Console.WriteLine("New Key: " + attrNewValue.Key + "New Value: " + attrNewValue.Value);
}

var oldValueEntity = attrAuditDetail.OldValue;

foreach (var attrOldValue in oldValueEntity.Attributes)
{
Console.WriteLine("Old Key: " + attrOldValue.Key + "Old Value: " + attrOldValue.Value);
}
}

The record whose Audit History we are retrieving :

Output :

AuditDetail.AuditRecord :

AuditDetail.OldValue n NewValue:

Hope it helps..

Alternate Key not getting created on solution import in Dynamics 365


Recently, we moved our Solution from Development to Test. Then on running one of the SSIS Packages we got the below error

The specified key attributes are not a defined key for the entity [4] CRM service call returned an error:

On opening the Entity for customization, and checking for the key, we saw that it was in the Status Pending.

On opening the system job, there was no detail for the error.

We normally get this error, if there are records already available that have duplicate values for that alternate key field. However, in this case, there we no record as this entity was being moved first time in the Test.

Well to just fix this we thought of manually reactivating the key

However, that threw a new error that “Reactivate entity key is only supported for failed job.”

To fix this go to Settings à System Jobs à search for the failed system job and delete them

Now we will see the status as Failed

This time it will allow running the reactivation.

Finally,

Hope it helps..

Upgrade to new Xrm client API object model (v9) smoothly using XrmToolBox plugins


We are currently in process of upgrading Dynamics from version 8.2 to 9.0. One of the major change is updating our current JavaScript to the new Xrm Client API Object Model.

https://docs.microsoft.com/en-us/dynamics365/get-started/whats-new/customer-engagement/important-changes-coming#some-client-apis-are-deprecated

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/understand-clientapi-object-model

Below are the most useful tools à

Install the plugin, connect to your organization and click on Retrieve Jscript Webresources, it will list all the JScript Web resources found.

Click on Scan

It will list down all the issues found.

One common change that we need to do across all our JScript is to pass the ExecutionContext from Form as well as Fields (and Ribbon) events. Script Finder makes it extremely easy to find them out.

Click on Find Scripts usage and it will generate a report with all the details as shown below

Now we can follow the generated report and directly make the change to the form, field, and ribbon instead of searching for it manually.

Happy Upgrading !!

Dealing with OptionSet inside PowerBI in Dynamics 365 CE


Updated 8-Sep-2018 –> Please check the Power Query Builder tool of XrmToolBox. Thanks Scott Sewell for informing about this wonderful tool.

Suppose, we have created a Power BI Report which makes use of OptionSet field.

It is on incident entity and we have selected priority code and state code option set fields. The problem is that we only get the value for them, so to get the label either we can manually specify it or use the plugin Power BI Option Set Assistant. We’d see both the methods.

Let us take state code field first and specify label for them manually.

In the Query Editor, select New Query and specify following value and save it.

= #table({“value”,”label”},{{0,”In Progress”},{1,”Resolved”}})

Select the main query and click on Merge Queries

Select statecode and value to map them, specify left outer join for Join Kind as shown below

Select the new column added to the query and check the label.

We now have the label specified added as a new column to our query.

Here, for small set of values we can specify the label manually, however if there are too many values this might not be feasible and also if there are changes in OptionSet inside Dynamics 365 CE, we’d have to do it manually here, which makes it difficult to maintain.

So, let us use the wonderful PowerBI OptionSet Assistant plugin from our favorite XRMToolBox

Install the plugin

Click on Load Entities and select Case Entity.

Select Priority field and click on “Create records for selected option sets

Basically, it will create a new entity named gap_powerbioptionsetrefs entity, which will hold the records corresponding to each of the values of the optionset field selected.

Back in Power BI create a new query and select the gap_powerbioptionsetfrefs entity and save the query.

Now follow the same steps, select the main incidents query and perform merge queries operation as shown below

Expand the column to select the label field

Our final query à

Hope it helps..

Upgrading from Dynamics CRM 2016 On-Premise to Dynamics 365


Make sure the Solution is compatible to Dynamics 365 (8.2), for this check the Version Compatibility

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg328109%28v%3dcrm.8%29#version-compatibility

Upgrade the existing CRM 2016 On-Premise Server to CRM 2016 (8.1) for its solution to be compatible with Dynamics 365 Online using cumulative updates.

https://support.microsoft.com/en-gb/help/3142345/microsoft-dynamics-365-onpremise-cumulative-updates

If upgrading from previous version i.e. prior to CRM 2016, use “Migrate by using a new instance of Microsoft SQL Server” approach

https://technet.microsoft.com/en-us/library/hh699669.aspx

https://technet.microsoft.com/en-us/library/hh699747.aspx

Once updated to compatible CRM 2016 On Premise (8.1), go to settings à customizations and export the existing solutions or create a new solution, add all the required solution components to it as unmanaged which will be imported to the new Dynamics 365 online environment.

Create a new Dynamics 365 Online Environment and synchronize it with your existing Active Directory

https://blogs.msdn.microsoft.com/crm/2013/07/18/how-to-synchronize-crm-online-with-your-active-directory/

With solution ready and imported to new Dynamics 365 Online, next step is migration of data.

Consider using a data migration tool like KingswaySoft.

Check out their Migration Starter Pack.

https://www.kingswaysoft.com/blog/2016/09/16/Announcing-Migration-Starter-Pack-for-CRM-Online-and-CRM-On-Premise

or Scribe

https://www.scribesoft.com/solutions/dynamics-365/

Use following tools to update the JavaScript to Version 9.0 of XrmToolBox.

https://www.xrmtoolbox.com/plugins/XrmToolBox.Dynamics365V9JavascriptValidator/

  • Script Finder (to update the form and fields for passing the executionContext)

https://www.xrmtoolbox.com/plugins/MsCrmTools.ScriptsFinder/

https://community.dynamics.com/crm/b/develop1/archive/2017/11/11/executioncontext-hits-the-big-time

Update JavaScript to use Web API.

https://github.com/jlattimer/CRMRESTBuilder

SQL Based report, if possible, needs to be converted to use Fetch XML. Use the following tool to speed up the process.

http://www.sql2fetchxml.com/

If the reports are too complex, plan to use Power BI reports.

https://technet.microsoft.com/en-us/library/dn708055.aspx

Plugin and Custom workflow needs to be updated to run in an isolated environment i.e. sandbox along with references to the latest SDK assemblies.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/plugin-isolation-trusts-statistics#web-access

https://www.nuget.org/packages/Microsoft.CrmSdk.CoreAssemblies/

If the existing On-Premise is integrated to other applications, make sure they can still talk with new Dynamics 365 Online.

Recurring jobs if needed, can be deployed to Azure as Azure WebJobs.

https://docs.microsoft.com/en-us/azure/app-service/websites-dotnet-deploy-webjobs

Use Hybrid Connection to talk with On-Prem resources.

https://docs.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections

These were some of the points I could quickly think of. Please share your thoughts and experiences in comments. I’d update this list.

Do check out :

Microsoft Dynamics CRM (on-premises) to Microsoft Dynamics
Online Migration Guide

https://download.microsoft.com/download/6/D/6/6D67BDEA-1D67-42B4-A52A-CF13CD547CB5/OPtoCRMOnlineMigration.pdf

Hope it helps..

LookupRecordNotAvailable error while using Configuration Migration Tool in Dynamics 365 Customer Engagement


While trying to migrate the record using the Configuration Migration Tool, we were getting error like below in the log

ImportEntity – Result: True – Entity: transactioncurrency – Adding transactioncurrency to the reprocess List as field createdonbehalfby with value 05d52058-eb35-41e8-8af7-dfb7120c4599 – LookupEntity systemuser and Record Id 2655712b-e13b-e711-80fb-5065f38b5691 (SYSTEM) – (action:Update) failed validation for reason code: LookupRecordNotAvailable

As we do not want to migrate the createdonbehalfby field, the easiest fix for this is to remove the field from the schema.

Ideally instead of adding the entire Entity, we should select only those fields whose value we want to migrate in the Schema file.

Hope it helps..

Power BI with Dynamics 365 CE – Dynamics 365 Content Pack


In previous posts we covered

Creating a Power BI Report using Dynamics 365 Online Service.

Publishing it and Showing it inside Dynamics 365 and scheduling its Refresh.

Apart from creating our own Power BI Report another way of quickly be up and running is to use the Content Packs provided by Third Party Service, for e.g. here we will be using Sales Analytics for Dynamics 365 content pack provided by Microsoft.

Login to the Power BI service, click on Get Data, select Services.

Select Apps and choose the Sales Analytics for Dynamics 365 from the AppSource.

If we search for Dynamics 365, we can see around 15 Apps provided by Microsoft.

Back into our Sales Analytics for Dynamics 365 App, when we click on Get in now we are presented with the option where we need to Specify the URL of our Dynamics CRM Online Service and Fiscal Year End Month Number for that organization.

In the next screen, we need to select OAuth2 as the authentication method and followed by signing in.

This installs the app which would be available in the Apps section.

Clicking on the installed Sales Analytics App opens the Dashboard

Clicking on Ask a question about your data inside Dashboard allows us to analyze the data by allowing us to choose the criteria

Here we have filtered the accounts by created on by month along with the owner

Also, we can see around 10 different reports created inside that app.

  • Sales Performance
  • Sales Leaderboard
  • Win/Loss Analysis
  • Top Won/Lost Details
  • Sales Pipeline
  • Sales Pipeline Dashboard
  • Sales Activity
  • Open Activities
  • Lead Analysis
  • Account Analysis

Similarly, we can create our own Content Packs to be shared with either specific group or with the entire organization.

Click on settings gear inside Power BI Service and select Create Content Pack

Here we are creating a content pack choosing the Dashboard, Reports and the Dataset that we created earlier in the previous posts.

Once published, the user 2 can click on Get Data and search for and can see the content pack.

Clicking on Connect adds the Dashboard, Report, and the corresponding Dataset to the My Workspace of the user. Only the user who had created the content pack can edit the artifacts that are part of Content Pack, however, the other user of the organization can save a copy of it and can work on it.

More on it

https://docs.microsoft.com/en-us/power-bi/service-organizational-content-pack-manage-update-delete

Hope it helps..

Power BI with Dynamics 365 CE – Refreshing the Dataset


In the previous post, we used Dynamics 365 Dashboard and Power BI Tile to show Power BI Dashboard and report inside Dynamics 365.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-showing-power-bi-dashboard-tile-inside-dynamics-365-ce/

Now as a next step, we want to make sure that the users are looking at the latest data possible inside the Dashboard\Reports.

In case of Online Services like Dynamics 365 (Online), we have the option to manually Refresh the data.

To see it in action, let us create a lead record in Dynamics 365.

This is how our Dashboard looks like for now à

Now back in Power BI Service right, click on ellipsis for the Dataset and select Refresh Now.

We can see the newly created lead in our Power BI Report inside Power BI Service and Dynamics 365 CE.

The other option we have is to Schedule the refresh.

Navigate to the Dataset and select Schedule Refresh

We can select the Refresh Frequency as Daily or Weekly.

And we can set it to refresh up to 8 times per day as shown below.

With Power BI Pro License, we can complete the scheduled refresh 8 times a day whereas the Power BI Premium allows the refresh up to 48 times per day.

https://powerbi.microsoft.com/en-us/power-bi-premium/

Some helpful links

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

https://www.powerobjects.com/2018/01/11/power-bi-data-refresh-performance/

https://crmtipoftheday.com/category/power-bi/

Hope it helps..

Power BI with Dynamics 365 CE – Showing Power BI Dashboard / Tile inside Dynamics 365 CE


In the previous post, we created a simple report and published it to the web and used IFrame to display them in Dynamics 365 CE.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-publishing-power-bi-report-to-the-web/

Now let us create a Power BI
Dashboard using that report, which we will then display inside Dynamics 365 CE as a proper native Dynamics 365 CE’s Power BI Dashboard.

Let us take the fastest route to create them, back in Power BI Service, hover over the chart in the report and select the option “Pin Visual

We will be presented with the option of creating a new dashboard or add to an existing one.

Here we have created a dashboard named “My Dynamics 365 Dashboard

Similarly, we can pin or add the other chart in our Report to this Dashboard.

Apart from pinning the chart from the report into the Dashboard we also have the option to configure the Phone View for the Dashboard by clicking on Create phone view.

Phone view lets us specify how the report should look within the mobile device by dragging the reports in the designer surface.

This is how our Dashboard looks now inside Power BI service.

Now suppose we want to share this Dashboard with other users and also want to display this Dashboard inside Dynamics 365 CE, for this, we need to Share them.

However, as soon as we click on Share button we would get the below message.

To share and collaborate we need to have Power BI Pro license.

With Power BI Pro license, we can share it the with other users in the organization having the Power BI Pro License and also use the Reports \ Dashboard within Dynamics 365 CE.

Click on Try Pro for free and Start Trial which assigns the user 60-day free trial

After successfully assigning the license click on Share and select the user to grant access to.

Here we have selected user2 here.

If you remember from the previous posts, the Dashboards and Reports reside inside My Workspace.

Alternatively, we create a workspace and add the members (user 2 in our case) to it with edit access.

Note: We can publish the report that we created earlier from Power BI Desktop to this new app workspace.

While creating the new workspace we can also define the Access to it for the other users.

Here we have already assigned Dynamics 365 CE Enterprise Edition Plan and Power BI free license to User 2.

User 2 can see the report in Shared with me section inside Power BI service.

As we had mentioned earlier, when the user2 tries to open the report he is prompted with the message that Power BI Pro license is required to see the reports shared,

Click on Try Pro for free to start the trial for the User 2.

After successful activation user will be able to see the report inside Power BI Service. Here we are done with Sharing and assigning license part, now let us display it within our Dynamics 365.

Before we can do so we need to enable Power BI Visualization Embedding inside Dynamics 365 CE.

Navigate to Settings à Administration à System Settings and enable Power BI integration.

Open CRM, and select New to create a new Personal Dashboard and select Power BI Dashboard

It will load the all the Workspaces and the corresponding Dashboard inside it to which the user has access to.

Enable for mobile will make the dashboard available on Dynamics 365 for Tablets and Phones.

Select the Dashboard and click Save.

The Dashboard inside Dynamics 365 à

Similarly, User 2 can create a personal Power BI Dashboard inside Dynamics 365 CE and select the dashboard that is shared.

Apart from adding the Dashboard, we can also add the Power BI Tile to the Dashboard.

Create a new Dynamics 365 Dashboard, click on add a Power BI tile option to add the tile as shown below.

The CRM Dashboard showing Power BI tile à

This completes the sharing and displaying the power bi dashboard and reports part inside Dynamics 365 CE, next we will cover how the data can be refreshed so that users are looking at the most up to date data possible.

Hope it helps..

Power BI with Dynamics 365 CE – Creating Power BI Report


To begin with, let us first create a Dynamics 365 Trial.

https://trials.dynamics.com/

After our CRM trial is setup up successfully, we will install the OOB Sample Data which we would be using for creating the Power BI report.

Next, we need to create a free trial for the above user to use Power BI Service.

Go to Power BI site, and click on Start Free to get started.

Use the same user account (trial) that was used to set up the Dynamics 365 trial.

https://powerbi.microsoft.com/en-us

Note: If we already have an organization account with Power BI License we can use it to log in to Power BI else can associate a free trial to the same organization account instead of creating a new one. The gmail, hotmail, yahoo etc personal account are not allowed. https://app.powerbi.com/.

Download the Power BI Desktop as well as click on Try Free.

Power BI Desktop is basically a feature rich windows application that allows us to get data from various services online or on-premise and develop Dashboard and Reports efficiently.

The alternate way to download is through Microsoft Downloads siteà

https://www.microsoft.com/en-us/download/details.aspx?id=45331

After installing it and signing in and click on Get Data ribbon button to connect to the Dynamics CRM Online.

Select Dynamics 365 Online as the Online Services to connect to.

Specify the Web API URL of the instance.

Copy it from Customization à Developer Resources

Select the Organization Account and Sign in to the CRM Instance.

In the Navigator window, we have searched Lead entity and clicked on Edit to select the columns that we need for our sample report.

In the Query Editor window, click on “Choose Columns“, we have selected the Revenue, Created On, Subject and Owning
User field to be used for the report.

Now to get the Full Name of the system user for Owning User, expand that column, and select Full Name as the value to be displayed as shown below.

Click on Close and Apply to apply the changes to the query and to go back to the main editor.

Back in the main editor, we can see the fields added.

Now from the Visualizations section pick any of chart type.

Here we have picked a Stacked Bar Chart to start with. We can drag the fields and specify Area, Legend, and Value and also use the Filters section to generate the report as shown below.

The report after adding the fields à

Similarly, we can add our filter in the FILTER section to filter the records further as shown below

We can also add multiple charts in the report by selecting it from the Visualizations section.

For e.g. here we have used a Clustered Column chart to see all the leads based on Owner and Created On Date.

This completes the creation of simple reports which uses Dynamics 365 Online as Data Source.

In the next post, we’d publish this report to Power BI Service and display it within Dynamics 365 CE.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-publishing-power-bi-report-to-the-web/

Hope it helps..

Maximum File Size Exceeded while using Export to Excel in Dynamics 365 CE


We got this error while trying to export around 70K data through export to excel in Dynamics 365 CE.

Log file gave us detail of the error i.e. Excel file exceeded size lime of 4 MB.

One workaround is to export small set of data (page wise) by applying some order criteria (either in the Grid or through Advanced Find)

Or better use the wonderful Export to Excel tool in XrmToolBox

https://dreamingincrm.com/2015/05/30/new-xrmtoolbox-plugin-export-to-excel/

It took few mins to export the records in excel and the file size was close to 19 MB.

Hope it helps..

Thirty two “Microsoft’s Very Passionate” professionals thanked with giant “WALL-OF-LOVE” at Microsoft Summit!!


Happy to see my pic there. Thanks to Peter Laker and Microsoft’s Face API .

https://www.linkedin.com/pulse/thirty-two-microsofts-very-passionate-professionals-thanked-laker/

(one more reason to smile often 🙂 )

 

User Security Manager – My first XrmToolBox Plugin


Being one of the biggest fan and the most frequent daily user of XrmToolBox, always had this wish to write a plugin for it.

Thanks to Prashant Maurya  (a dear friend and ex-Microsoft Employee) for making this wish come true. (Who played the major role in developing it).

The tool is called – User Security Manager.

The Plugin make it easy for the administrators to manage all the security related aspects of the system users. The tool also gives 360-degree information of the user in correspondence to business unit, security roles and teams.

In our current project we had around 180 users using the system divided into more than 50 business units, which involved frequent update of their business unit, changes in the security roles assigned, update in team assignment and also during testing we had to assign the same BU and roles to the test users in the org, which wasn’t that easy to do from the user interface.

So, the idea was to come up with a tool that will let an administrator to

  • View all the security roles that assigned to the user either directly or through the team that the user is member of.
  • View all the Teams the selected user is part of.
  • With just a single click, remove all the security roles assigned to the user.
  • With just a single click, remove user from all the teams that he is part of.
  • Easily assign and unassign single or multiple security role to the user.
  • Easily add or remove user from a single or multiple team.
  • Change the Business Unit of the user.

User Guide: –

Load all the users

After connecting to the organization, we can click on Load Users to load all the system users of the organization.

List details of the user selected

  • For the selected user, in User Detail Tab’s General section, we can get the general information of the user like First Name, Last Name, Title, Primary Email and Access Mode.
  • Next to the General section, we can see all the security roles assigned to the user either directly or through the teams the user is member of.
  • The Roles tab shows all the Roles assigned to the user and also the roles that are available in the business unit to which user belongs in Assigned and Available section along with their counts.
  • The Teams tab shows all the teams user is member of and also the teams that are available in the organization in Assigned and Available section along with their counts.

Remove all security roles of the user.

To remove all the security roles of the user, click on Remove All Roles button.

Remove user from all teams.

To remove the user from all the teams, click on Remove All Teams button.

Assign Security role to the user.

To assign security role to the user, check the security roles from the available security roles section and click on “<<” button.

To remove security role from the user.

To remove security roles from the user, select the roles from the Assigned section and click on “>>” button.

Assign User to Team(s).

To assign user to team(s), select the teams from the available section and click on “<<” button.

Remove user from Team(s)

To remove user from Team(s), select the team from the Assigned Section and click on “>>” button.

Change the business unit of the user.

To change the business unit of the user, select the Business Unit from the Business Unit drop down in General Section of User Details.

Please install it, use it and share your feedback which would help us to improve it

https://www.xrmtoolbox.com/releases/

Hope it helps..


	

When does the Plugin registered on SetState message fires in Dynamics 365?


Hi,

Recently we were writing a plugin that should fire when a record is activated.

In this kind of scenario, it is suggested\recommended to register plugin in both SetState and SetStateDynamicEntity message.

https://community.dynamics.com/crm/f/117/t/184070

https://blogs.msdn.microsoft.com/crm/2008/03/07/developing-plug-ins-for-crm-4-0/

Interestingly we had the following observations

  • The Plugin fires only for SetStateDynamicEntity message.
  • The Plugin fires even if the SetState message step is disabled or not registered.
  • Only SetStateDynamicEntity message is fired when the record is Activated or Deactivated. For incident entity also, it was the only message fired on Reactivating the record. The plugin didn’t trigger in case of Resolve or Cancel Case.
  • Only SetStateDynamicEntity message is fired, even if we are updating the state of the record using SetStateRequest of SDK.
  • As SetStateRequest message is deprecated, if we use UpdateRequest to update the state of the record, the plugin that is registered on SetState and SetStateDynamicEntity will not fire. It will trigger only if registered for Update message.

Plugin Trace log records :-

We are yet to figure out when exactly the SetState will be fired. Please share your thoughts and experience around the same.

Hope it helps..

My Interview at MSDynamicsWorld.com


Check out my interview at MSDynamicsWorld.com

https://msdynamicsworld.com/story/dynamics-crm/microsoft-dynamics-profile-nishant-rana-goes-man-series-cricket-mvp

2018-06-16_0941

Thanks MSDynamicsWorld.com and Linda Rosencrance.

PLAN PROPERLY before using Back and restore and Copy instances in Dynamics 365


Update – 25 June 2018 – Took nearly 20 days to get both the instances up.

Update – 12 June 2018Support Engineer has been not been able to resolve either of the support ticket which were raised last Thursday 7th June. One instance is not opening and the other instance is not restored with the backup we had requested. They are unable to find the backup till now.

Recently we tried copying our DEV to one of the TEST instances. The idea was we were upgrading the click dimensions solution in our DEV so we wanted to make sure everything works fine after the upgrade and in case it didn’t we could then again copy it back from the TEST.

We had faced back and restore issues (instance failed) in the past so decided to go the Copy instance way this time.

However, we got the Instance Failed error on the TEST instance to which we were copying DEV to.

Interestingly the Edit, Reset, Delete etc option were still available and we could open the TEST instance (the original, as the copy of DEV had failed) without any issues.

Then we thought of giving a try again and started copy from DEV to TEST again. This time again it failed. And with no option of Edit, Reset, delete etc. and the Instance was also not opening.

The error à

Along with that we also tried Restoring the Backup in one of the other TEST instances, that also expectedly failed.

So basically, we ended up with 2 unusable instances.

A few weeks back also we faced the same with our Dev environment and the only way out was to raise the support ticket with Microsoft. That time it took 5 days for DEV to be restored. Imagine the development activity that came to halt.

Last year also we had faced this issue in Production and had to work with the support team for 16 – 20 hours continuously to get it restored.

So, if we have some critical activity planned like Production Deployment and we are counting on Back and Restore or Copy an instance to rescue us in case anything goes wrong after production deployment, we need to plan for this Instance Failure as well while restoring or copying.

Basically, be ready to raise a support ticket with the highest severity to restore things back, which could take hours or in worst case days and do all the production deployment over the weekend or have enough downtime allocated.

Hope it helps..

CRM service call returned an error: The server is busy and the request was not completed. Try again later while inserting records using CRM Destination Component of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365


While running one of our package we got the below error in few of the records

“CRM service call returned an error: CRM service call returned an error: The server is busy and the request was not completed. Try again later. (Error Code: -2147180543, Detail Message: The server is busy and the request was not completed. Try again later.)”

“CRM service call returned an error: CRM service call returned an error: The request channel timed out while waiting for a reply after 00:01:59.7991956. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout. (Error Type / Reason: Timeout)”

One way to get around this error is to increase the Timeout (secs) in the Service Timeout Setting of CRM Connection Manager.

We updated it to 600 seconds and the number of records that failed got drastically reduced.

Also make sure that following option is checked

Hope it helps..