Nishant Rana's Weblog

Export Dynamics 365 Data to Excel – different options

There are different options available for exporting Dynamics 365 Entity records to excel. The simplest being Export to Excel feature available in the product.

However, we might run into limitations like below

https://nishantrana.me/2018/08/15/maximum-file-size-exceeded-while-using-export-to-excel-in-dynamics-365-ce/

The easiest way of overcoming these limitations is to use the XrmToolBox plugin – Export to Excel

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

Other options include writing a windows executable that uses Dynamics 365 Web API for exporting data to excel format.

Use Microsoft Flow and export it to CSV file.

Create an SSRS report with required records \ columns and use Export to excel functionality.

Using KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 and other 3^rd party data integration tools.

One such 3^rd party tool that makes it easy to export the data to excel and also to schedule is Skyvia’s Export package.

Let us see a simple example to understand how it works.

Here we are planning to export a custom entity record that has 1835629 records.

Here we have already created the required Dynamics CRM Connection.

Navigate to the Data Integration section and create a new Export Package.

For target, we have .csv as the file format. For Target, we have various options of either saving it locally, FTP or in cloud services.

Here we can filter the records, specifying an ordering for them, select the columns we want to export.

Click on Save to create the package. Alternatively, we can also specify the schedule for the package to run at.

Click on Run

We can see the package running and its status in the Run History section.

Each of the options for exporting the excel has its own pros and cons. For e.g. OOB Export to Excel feature has a limitation with the number of records that can be exported, with KingswaySoft we need someone skilled in SSIS to write the package and also, we need to buy the license if we need to schedule it. With other 3^rd party apps also we would need to buy the license.

Hope it helps..

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.

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..

Perform actions on the host model-driven form from embedded canvas app using ModelDrivenFormIntegration control in Dynamics 365

Let us continue our previous example of embedding the canvas app within the model-driven app and extend it to perform actions on the host form using ModelDrivenFormIntegration control.

This ModelDrivenFormIntegration control acts as a bridge and brings the contextual data from the host model-driven form to the embedded canvas app.

Here we will look at some of the pre-defined actions that can be performed using the ModelDrivenFormIntegration Control.

For this update our existing Canvas App and add the below buttons to the Form control in the canvas app

Save Form

For the Save Form button, first, create the flow (Power Automate) which will update the record.

https://nishantrana.me/2019/11/04/step-by-step-calling-flow-from-powerapps-dynamics-365-ce-crm/

Below we are calling the Flow from Save Form button and passing GUID of the record along with values of name, city and phone field.

Here we have used ModelDrivenFormIntegration’s Item property to retrieve the id of the account record in our formula that is being used to call the Flow (Power Automate)

Result –

updating the address 1 city field by appending text “Redmond” in the embedded canvas app.

save

Refresh Form

The true and false parameter value of the RefershForm method specifies whether to show prompt or not before saving any unsaved data on the host form.

Result –

refreshing the form, shows the address 1 city field of the form in the right section with the appended text “Redmond” as saved earlier.

refresh

Navigate to Main Form

NavigateToMainForm method’s parameters are entity name, form name, record id.

Result –

Here we are opening the same record.

navigatemain

Navigate to View

NavigateToView method’s parameters are entity name and view name.

Result –

Opens the view specified in the parameter

NavigateToView

Open Quick Create Form

NavigateToView method’s parameter is entity name.

Result –

Opens the quick create form of the entity specified in the parameter.

Get all the details here

Hope it helps..

KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 vs using Data Import Wizard

I was recently asked that what are the benefits of using KingswaySoft tools for Data Migration need vs our out of the box Data Import wizard.

We are talking about 2 different solutions altogether so the direct comparison might not be appropriate. However, highlighting a few of the capabilities and limitations of each of them.

As we all know, the simplest and straight forward way of importing data into Dynamics 365 is by using the Data Import Wizard.

A Dynamics CRM user can be trained to use this feature.

The feature is easy to use, intuitive and does the job well when it comes to quickly import a small set of data.

Also, we can see the status of the import job, did it succeed or how many records failed, etc.

However, there are certain limitations

We cannot term it as a data integration solution as we are not scheduling it.

It is not real-time as the asynchronous job running in the background takes care of it.

We can only perform an update using the file exported one time only. Every time we are performing an update, we need to export the records again. We will get an error if the records have been modified after the file was exported.

The status of the records cannot be changed.

We cannot have our custom logic or transformation is applied.

The maximum file size for .zip file is 32 MB.

For other file formats like .csv,.xml or text files the limit is 8 MB.

Microsoft recommends limiting import to 20K rows or fewer. For a large volume of data Microsoft recommends programmatic way as most efficient.

Here another option for data import that we can use is the Configuration Migration Tool

https://docs.microsoft.com/en-us/power-platform/admin/manage-configuration-data

However, we normally use third party tool like the one developed by KingswaySoft for most of our complex data migration requirements, instead of writing one such tool ourselves because it would need to develop from scratch with performance, logging, ease of configuration etc. in mind, which could be time-consuming.

Below we can find all the details about the SSIS Integration toolkit developed by KingswaySoft

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365

With powerful components like CRM Connection Manager, CRM Source and Destination Component and CRM OptionSet mapping component we can develop codeless data integration solutions (packages).

With SSIS coming into the picture we are not limited to XML, Excel, CSV or text files as our source. Recently in one of our implementations, we had complex XML as our source files, for which we use the XML Source Component of KingswaySoft

https://www.nigelfrank.com/blog/integrate-dynamics-365-for-customer-engagement-with-xml/

The components are intuitive, flexible, feature-rich and very easy to use and configure. We can easily map our source data with Dynamics 365 entities.

We can have full logging (failure and success) and retry mechanism designed in the SSIS Package easily.

With KingswaySoft CRM components we can achieve high performance. The batch size and the total number of threads can be configured.

KingswaySoft offers the free developer license, under this license the software can be used within Visual Studio or Business Intelligence Development Studio. The limitation is that it cannot be run on a scheduled basis or from the command line outside the development environment, for that one needs a license.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/purchase

The tool is constantly evolving along with new updates and changes in the Dynamics 365 CE /CDS.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/change-log

The solution built using KingswaySoft can be deployed to Azure-SSIS Integration runtime.

The KingswaySoft’ s support team is very knowledgeable and helpful and we can expect a quick resolution of any of our queries. I remember our team facing difficulty while configuring one of their components and the support team was able to guide us on how to configure it correctly.

However, there are few things to consider while opting for KingswaySoft

Need for SQL Server Environment (for scheduling the packages)
Resource(s) skilled in SSIS.

The other tools worth considering when it comes to data integration and migration need with Dynamics 365 are

Scribe

https://help.scribesoft.com/scribe/en/sol/conn/crm.htm

Cozy Roc

https://www.cozyroc.com/ssis/dynamics-crm

Below are couple of blog posts I wrote while working with the KingswaySoft’s components and Scribe Online

https://nishantrana.me/tag/kingswaysoft/

https://nishantrana.me/category/scribe-online/

Hope it helps..

Embedding a canvas app on a model-driven app in Dynamics 365

Let us start with some of the key points about the embedded canvas app

The embedded canvas app is now generally available. During preview.
The embedded canvas app supported sub grid control in preview, which is now no longer available.
The canvas app custom control is only supported for the Unified Interface Web client type. It doesn’t support Phone and Tablet client types.
We can only have 1 canvas app on the form. We can have multiple embedded canvas app but only 1 of them could be enabled.
It is recommended to have an embedded canvas app on a required field else it will not refresh on any change in the data on the host model-driven form.
The embedded app can only be created from the host model-driven form.
The existing canvas app cannot be used as an embedded canvas app.

To create the canvas app custom control, select the controls tab in the Field Properties dialog, and click on Add Control.

Select Canvas app in App Control dialog.

The Canvas app will have Web client selected as default and Phone and Tablet option will be greyed out.
Entity Name shows the name of the field that is associated with it.
App ID will be populated when the app is created.

Clicking on Customize opens the PowerApps Studio.

We can see the canvas app form control added to the canvas app.

We can see the fields for city, name, and phone already added to it.

ModelDrivenFormIntegration is a special control that is responsible for bringing the contextual data from the form to the embedded canvas app.

Using Edit Fields option, we can add, remove, edit, reorder the fields of the data source.

Just for the simplicity let us keep the app as it is.

Click on File à Save à The cloud (Save to PowerApps)

On a successful save, we can see the App ID generated.

Uncheck Display label on the form, save and publish the customization.

Changing the value in the Phone field in the form and saving the form changes the value inside the canvas app form control.

To Edit the canvas form control, we need to follow the same steps, open the field properties, controls tab and click on Customize

After making the changes inside PowerApps studio, save and publish the changes.

The other Dynamics CRM User say poweruser2 will get the below error message when they open the same form if the app is not shared with them.

The app can be shared with specific users, security groups or everyone within the organization. The users should have appropriate security roles to access the entities and also appropriate permissions on the cloud services that are being used as part of the canvas app.

There are no privileges specific to the canvas app within Dynamics 365 CE

To share the app, Navigate to File and select Share option

Here we searched for the user, made him Co-owner and assigned security roles which will give him access to Accounts entity.

We can see the embedded canvas app rendered properly in this case for the poweruser2

In this post, we covered some of the key points about the embedded canvas app along with steps to create, update and share it (although we just used the OOB generated app here), I would recommend to check out the videos by Shane Young to learn about PowerApps and different scenarios that we could implement.

Hope it helps..

Step by Step – Calling Flow from PowerApps (Dynamics 365 CE/CRM)

Let us take a simple example of creating lead to understand how we can call Flow from PowerApps.

TextInput for entering the Last Name, First Name and Email ID.
Button to call Flow.
The label named GUID which will display the GUID of the created lead record.

Now select the button, go to Action menu and click on Flows to create a new flow.

Click on create a new flow.

Select Instant – from blank option

Select PowerApps as the trigger.

Add a new Step – Create a new Record (Dynamics 365)

To create a new record step, specify the Dynamics 365 Organization Name, select Leads as the entity name.

For the Last Name field, click Ask in PowerApps that will generate the parameter to which we will pass the value from our PowerApps.

The generated parameter.

Repeat the above step for the First Name and Email field.

*for Topic field we have passed a hardcoded value.

Save the create step and add a new step “Respond to a PowerApp or flow”

Here we have selected output of type Text

We have added the output parameter as varLeadId and selected leadid as the unique identifier.

Save the Flow and navigate back to PowerApp and select the flow created for our button.

After adding the Flow, the next step is to update the formula to send the field’s value to the flow and also to receive the output from it.

Below will be our formula to pass the text to the flow.

But as we are expecting the GUID in return, we need to update it.

We are using the Set function to set the variable varResponseFromFlow with varleadid output.

Also, we have updated the label field for GUID with the variable

Now let us save the changes and run the PowerApp.

On clicking the button, we can see the GUID updated on the label on the form.

Back in our Dynamics 365 CE, we can see the lead created.

Thus we saw how easy it is to write a PowerApp which interacts with Dynamic 365 CE using Flow. We took a very basic example to understand how things glue, but we can clearly see the potential here to implement complex real-world scenarios with ease.

Hope it helps..

Execute SQL Select, Insert, Update and Delete command on Dynamics 365 Customer Engagement Data using Skyvia Query

Recently came across the wonderful product called Skyvia Query

It is one of the most advanced Visual Query Builders out there, that let us write complex SQL queries with simple drag and drip and configuration. The most interesting part is that it works with both Cloud as well as On-Premise databases.

To try it out we can create a free trial account.

https://app.skyvia.com/register

In this post, we will see how we can quickly use it to connect to our Dynamics 365 CE Database and perform select, insert, update and delete queries.

Navigate to connections and create a new Dynamics CRM Connection

Provide the connection details and test and save the connection.

Navigate to the Query section and select New Builder option to use SQL Builder tool to create the query

Specify the connection and search for the Entity (table)

Here we have searched for and selected the Opportunity Entity. Now simply drag and drop the fields to the designer surface.

Results Fields:

Filters:

Sort Fields:

Click on Execute (F9) to get the result.

Let us drag and drop opportunity rating field in the result fields section and Execute it

The results can be exported to PDF, CSV and can also be used for creating a chart that can be exported as an image.

PDF:

Chart:

Going back to our designer screen, now let us work with the actual SQL query that was being generated by the query builder for us.

For this, click on the SQL tab.

Below is the generated query.

We can work with the query in the SQL query window itself and can also drag and drop fields from the Table on the left.

For e.g. we have dragged Records count on the SQL query window, which generated the count query for us.

Now let us see the Insert query in action

We can see our contact record created

Now let us perform Update.

Here it asks for the confirmation before performing the update. Selecting Apply All performs the update.

Now let us look at the DELETE query in action.

Similar to Update it asks for the confirmation before deleting the records.

Thus Skyvia Query tool makes it so easy and intuitive to work with Dynamics 365 CE data, as we are using SQL statement instead of Fetch XML.

I would highly recommend giving this product a try.

https://skyvia.com/pricing/

Hope it helps..

Using PCF Custom Control Builder (XrmToolBox Plugin) to update existing control in Dynamics 365 / PowerApps

A couple of months back I had written my first PCF Control to display GUID of the record, it was more of a hello world kind of thing.

https://pcf.gallery/guid-control/

Well, the code was using Xrm.Page client API which as we know is deprecated.

So, it was time to update it to use context instead.

Working with Git Branches in Visual Studio 2019

Continuing our previous posts, let us now see how we can work with branches within Visual Studio 2019.

Branches give the option to isolate the work from the original code base, this way we could have a developer(s) working on bug fixes, new features etc. which then can be merged back to the master branch later.

Open the Branches page in Team Explorer and click on the New Branch option.

Here we have created a new branch name MyFirstBranch from the master branch, and have also selected the option Checkout branch to work with that branch.

Create Branch creates the new branch as shown below.

To navigate to the branches page of Team Explorer, we can also use the Status bar of Visual Studio

Now let us make some changes in the program.cs file in the new branch created.

Do Commit and Push
All

From the team explorer, Branches page, we can see that the new branch has been added in the remote repository after commit.

To merge changes back from this new branch to the master branch, click on the master branch and select Merge option in the Branches page

Merge from Branch: MyFirstBranch
Into current branch: master.

As we have selected the option Commit changes after merging click on Merge pushes and sync the changes in the master branch in the remote repository as shown below.