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

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

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.

Log in to PowerApps Studio and create the form as below.

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

More on context

So, thought of using the wonderful PCF Custom Control Builder tool to do that by Danish (which got a new update today – 22- Oct-2019)

Connect to the organization, select Edit existing PCF Control option

Specify the Control Location and VS Command Prompt Location (VsDevCmd.bat)

And click on Reload Details which will list down the details of the component and the solution as shown above.

Click on Open in VS Code to open the project in Visual Studio Code and start making the required update.

It opens the project in Visual Studio Code.

i.e.

Here let us update the index.ts file to use context to fetch the GUID.

Save the changes. And click on Build

i.e.

Now click on Test to see it in action or to debug.

We can see it populating the GUID

To debug it à we can use the developer tools (F12) and put the breakpoint in the index.ts file.

Now let us deploy it to our organization

Deployment in action

After successful deployment, we can see the solution added to our organization

Now let us use it for the single line of the text control.

Publish the changes.

Create the new lead record and we can see the GUID of the record created in the control

Thus, the wonderful PCF Custom Control Builder plugin of XrmToolBox makes it so easy to update the existing PCF Control and also to create new PCF Control.

Hope it helps..

Live Chat using ProProfs Chat in Dynamics 365 CE (CRM)

ProProfs chat is another live chat solution that integrates with both Dynamis 365 CE as well as CRM On Premise. Recently I wrote about PowerChat which is another live chat solution that works on both online and on premise version of Dynamics CRM.

In this post we’d see how quickly we can setup the integration and be up and running and also compare it with PowerChat.

Navigate to https://www.proprofs.com/chat/integrations/microsoft-dynamics/ to get the details and click on Try Chat Free to get started.

After creating the account, go to Integration option and select Microsoft Dynamics and set the toggle switch on.

Specify the Dynamics CRM URL, Dynamics CRM Account, on-premise or online and should lead, contact and case should be auto created or not.

Once enabled, go to Installation option and copy the code and paste it on the website or page.

As soon as the visitor starts the chat, the agent will receive the Invite.

The agent can see the details of the visitor like location, device etc.

The customer can end the chat and give rating as shown below.

The agent gets the notification that user has ended the chat.

As configured, we can see the case, lead and contact record created in Dynamics 365 CE, with description having the chat transcript.

To add agents, we can choose the Add Operator option

Routing order gives the option of specifying the sequence of operator accepting chats if chat routing is enabled. Similarly, visitor can select the operation they want to interact with as specified by Allow Operator Selection option.

For different business units having different websites/ pages, we would want them to be served by different operators, herein we can use Departments.

Department will have a code which we need to place under the ProfProfs Chat code to send chat to that specific department.

Also, for Admin, there are Reports available Summary, Rating, Operator, Timesheet, Visitor.

Theme settings à

Chat Window Settings à

Real Time Monitoringà

Comparing the ProProfs Chat with PowerChat with regards to Dynamics CRM integration à

  • PowerChat provides a managed solution that we import inside our Dynamics CRM, then we register through the configuration page of that solution to get started. In case of ProProfs there is no solution file. We register at their site.
  • PowerChat Users are Dynamics CRM Users and can chat with visitors from within Dynamics # CRM. In ProProfs Chat, the operators are not the crm users and their account needs to be created / added separately.
  • PowerChat allows different Chat Widgets to be created, to be used in different web pages or web sites. Agents can be associated to the Chat Widgets. In ProProfs Chat, there is concept of Departments to achieve the same.
  • PowerChat allows agents the option to create lead/ contact/case records manually during the chat. In ProProfs Chat, the lead /contact/ case records are auto created based on the configuration, we can select which records to be created.
  • PowerChat auto creates PowerChat Activity record for each chat which has all the details of the chat. ProProfs Chat doesn’t create any other records expect lead/contact/case inside Dynamics CRM.
  • They both have the option of configuring the chat windows, fields to be added, snippets to be reused during chat, multi agent support.
  • PowerChats is natively integrated with Dynamics CRM, so the CRM Users do not have to be working on 2 different applications. However, ProProfs brings in with it lot more features like routing chat to another operator, co-browsing, APIs support, Reports, Real Time Visitor monitoring etc.

Check the guide here to see all the features

https://chathelp.proprofs.com/home

Hope it helps..

Enable Live Chat in Microsoft Dynamics 365 CE (CRM) using PowerChat

PowerChat is the live chat solution / addon developed by PowerObjects that supports both Dynamics CRM On-Premise and Online.

Below we will look at how to set it up and what are the features it provides.

To get started

Download the appropriate solution

https://www.powerobjects.com/powerpacks/powerchat/

After successful import of the solution, next step is to register the solution. For this, open the solution and click on the configuration page, provide the required details in the Profile and Setup tab.


In Setup tab, provide the Cloud Location nearest to your deployment.

The PowerChat solution adds the following entities in the sitemap.

and the following security roles

In Agents i.e. agent record we can specify the System User who would be working as agent and topics they are working on.

Topic, Agent and Widgets work hand in hand. We can have multiple topics defined in the system and for each topic we can define the agents who’d be handling that topic along with the widget that will be used for that topic.

Widgets specifies the Chat Interface that would be deployed in the website. Clicking on Design opens the design interface for the widget.

Below are some of the different options that can be configured for the chat widget.

Once we are done with the configuration of the chat widget, we need to click on GENERATE SCRIPT ribbon button to get the script for the widget which we will be embedding to our websites.

Below is the script generated à

Below is the sample website where we have embedded the above script

Back in CRM, users need to click on Power Chat and start the PowerChat for communication to begin.


Power Chat interface à

Once the user clicks on Chat Now button on the Chat window, the agent gets the notification to Accept the chat.

Below is how the communication looks like for the agent as well as the customer. The green bubble below is the timer that captures the duration of the chat.

The agent also has the option of creating the lead, contact and case record associated with the chat. Create Lead and Create Contact option creates the lead and contact record in the background using the information auto populated at the Visitor Input section on the right panel.

Create Case option allows agent to specify the Case Title and Description along with the Contact Name.

Agents and Customers both have the option to end the chat, the customer also have the option to rate the agent when they close the chat.

Every interaction creates PowerChat activity record in Dynamics CRM as shown below, which records the chat history, rating, duration etc. information about the chat.

Agent can also set their status, if the agent is in Away or Busy status it will not receive the notification to accept the new chat.

As we discussed earlier if we have multiple agents configures for same topic, they both will get the notification to accept the chat as shown below.

Also, Agent has the option of adding another agent to the chat.

The invited chat agent can either accept or decline the request.

Below is how the communication looks like when we have multiple agents working with the same customer. Both the users are communicating with the Customer at the same time as shown below.

One more feature that agent has while interacting with the customer is to use the snippet, which basically defined predefined content which agents can use.

Thus, we saw how easy it is to configure the PowerChat and get started with Live Chat capabilities in no time.

Few months back we had also explored PowerApproval the approval solution for Dynamics CRM as part of PowerPacks add-on. Do check it out.

https://nishantrana.me/2019/01/02/implementing-multi-level-approval-using-powerapproval-in-dynamics-365-ce/

Hope it helps..

Out of preview – New Model-Driven form designer (WYSIWYG) in PowerApps

Finally the new model-driven designer is out of preview and available for general use

https://powerapps.microsoft.com/en-us/blog/announcing-the-general-availability-of-the-new-model-driven-form-designer/

Since I wrote about it last the few changes that we can see are

  •  New Field button that allows adding Lookup Fields. However I still don’t see the option of Option Set fields there.

Form

  • Grouping of Components Section

Form

  • Preview for the components

Form

Looking forward to see all the remaining features that are part of our classic Form Designer like addition of Business Rules, Show Dependencies, Enable Security Roles etc. to be part of the new designer.

Below are the previous blog posts that I wrote about the new model driven apps while it was still in preview –

https://nishantrana.me/2019/08/19/subgrid-quickview-linear-gauge-arc-knob-linear-slider-controls-added-in-new-model-driven-form-designer-wysiwyg-in-powerapps/

https://nishantrana.me/2019/08/05/sub-grid-added-to-the-new-model-driven-form-designer-wysisyg-in-powerapps/

https://nishantrana.me/2019/05/27/few-improvements-in-new-model-driven-form-designer-wysisyg-preview-in-powerapps/

https://nishantrana.me/2018/12/24/the-new-model-driven-form-designer-wysisyg-in-powerapps-for-model-driven-apps-dynamics-365-ce-preview/

Hope it helps..

Performance testing Dynamics 365 CE using StresStimulus

Recently we were evaluating StresStimulus, the load testing tool, for our performance testing requirements. The tool is quite user friendly and intuitive and in a matter of few minutes (and few clicks), we were up and running.

Get the tool (trial) here

https://www.stresstimulus.com/download

Install it with the Fiddler Add-On (recommended) option checked. It also has stand alone tool along with the fiddler add-on,

Once installed, open the fiddler, go to StresStimulus tab and click on Record Test Case …

This opens us the Test wizard from us, here we have selected Web Browser – Chrome as the recording source for Dynamics CRM.

Specify the Dynamics CRM URL and other options as required and click on Record

During recording we can specify various transaction, or basically events that we are capturing inside the recording toolbar. Here clicking on Start Recording will open Chrome for us with Dynamics CRM URL specified. We have specified “Login” in the transaction box to capture set of actions specific to login.

After login, we want to capture creation of Contact Create, we can specify the same in Recorder tool bar and continue creating the contact record in CRM.

Similarly, we can record other events, once done, click on Stop to stop the recording.

Do check out this post à

https://community.dynamics.com/crm/b/crmperformancetesting/posts/performance-testing-dynamics-crm-365-scenarios

On successful autocorrelation parameters are created we can configure our test case.

The first button gives us the option of removing the unwanted host by auto checking the hosts.

Click on delete to remove the unwanted hosts auto selected for us.

Next we are presented with the option of selecting the content types to include in the test case.

Followed by Server authentication credentials. (in our case we ignored it)

As the last step we can verify the test case, which replays the test case to check for any errors, before we can save it or run it.

Next after successful verification, we can define Load Pattern and Number of VU (Virtual user)

Here we have specified Steady Load with 50 Users. The other options available are Step Load, Peak Load and Custom Load. We can request trial to receive 7 day license, which allows us to specify 50 VU, else the download version is limited to 3 VU.


We are leaving the Test Duration values as default

Same for the think time i.e. 2 minutes.

Finally, we can click on Run Test to run our test configured. We can also run the test from Run and Monitor Test Options

We can see our Test Run in action

Once the test run completes, we are presented with the Test Summary

Graphs tab

Details Tab

VU Activity Tab

Back in CRM we can see the records created as a part of test run.

We can also compare results of different test run.

Below is our multi-test report.

Same report in Graph Form.

Get all the details here

https://www.stresstimulus.com/load-testing-tool/dynamics-crm-performance-testing

Hope it helps..