PowerApps – Nishant Rana's Weblog

Load data from SQL On-Premise to CDS (Common Data Service) using Power Platform dataflows

Let us continue our previous post where we created a connection to the below On-Premise SQL Server Database using an On-premises data gateway.

https://nishantrana.me/2020/07/06/configuring-on-premises-data-gateway-to-connect-tosql-server-on-premise-data-source-power-platform/

Here we will use the Power Platform
dataflows to load contact entity in CDS using that same on-prem table.

https://make.powerapps.com/home

Navigate to Data – Dataflows and create a new dataflow

Dataflow can store the entities either in Common Data Service or Azure Data Lake for analytical purposes.

Leave the Analytics entities only checkbox unchecked as we will be saving the entities in the Common Data Service.

Next, let us specify the data source. Here we have selected SQL Server Database as the source.

Specify connection details

On specifying the Server and Database details, it will auto-populate the rest of the connection details if already defined.

Select the table and click on Transform data

Transform Data give us the option of further refining the data to match the destination.

Click on Next and specify the mapping.

We can either load data in a new entity

Or load to an existing entity.

Here we have selected contact entity and clicked on Automap option which has mapped FirstName and LastName fields for us. Manually map the email field. Here we have not mapped the ContactID field for now.

Here for Key fields in Field Mapping, it will list down alternate key, if defined for the entity.

Specify the refresh settings. We have kept is as refresh manually.

With refresh completed, let us check the contact entity.

Go to Data – Entities and Data tab, we can see the records added.

Now let us add one more record in the source database and refresh the dataflow manually.

As we had not specified key while defining the data flow, we have all the records created again including the new one.

Now let us define an alternate key for the contact entity.

And edit the data flow to map the ContactID field of the source database to the newly created alternate key.

Define the mapping for the alternate key field.

Also, let us delete the old records that were synced. After refresh is done, we can see the records created

What about the update?

Let us update the record in source and run the refresh

As expected, the record is updated and no duplicate records are created in CDS.

What about delete?

Now let us delete the record in the source and run the refresh

The records are not deleted in the Common Data Service.

What about updates in CDS?

As this is a one-directional flow to load the data from source to destination, any change in CDS will be overridden and will not be synced back to the source SQL Server database on the next refresh run.

Let us update records in CRM, and run the refresh.

As expected, the record in CDS is updated back with source DB.

What about the history and log of the refresh?

Show refresh history option gives us all the details of the refresh run and the failure and success of the records.

Configuring On-Premises data gateway to connect to SQL Server on-premise data source – Power Platform

Let us take a simple example to understand the steps to be performed for configuring an On-premises data gateway.

We have below database in our on-premise SQL Server which we connect to using On-Premise data gateway.

Below are the steps we need to perform to configure it –

https://make.powerapps.com/

Download the On-Premises Data Gateway

Follow the installation wizard to install the on-premises data gateway

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install#minimum-requirements

Select Register a new gateway on this computer

Specify the name and the recovery key and click on configure

This completes the setup of the gateway.

Within Power Apps, we can see the gateway record created.

Now let us create a connection to our On-Premise SQL Server Database that uses the gateway configured

Navigate to Data – Connections and click on the new connection.

Select the SQL Server connection type, and specify the connection details of the on-prem database.

Select the gateway configured and click on Save, which will test the connection.

On a successful connection, we can see the connection created with status Connected.

Now as we have the connection established with our On-Premise SQL Server database through the gateway, we can use it for in various cloud services like Power BI, Power Apps, Power Automate, Logic Apps, Dataflow, etc.

Get more details here

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Another option to connect to SQL On-Prem database is through Azure Hybrid Connections (this would for Azure App Service)

https://nishantrana.me/2018/02/19/using-azure-hybrid-connections-to-connect-to-sql-on-prem-database-from-azure-webjob/

https://www.slideshare.net/biztalk360/the-hitchhikers-guide-to-hybrid-connectivity-81149063

Hope it helps..

Solved – AADSTS50058: A silent sign-in request was sent but none of the currently signed in user(s) match the requested login hint PowerApps

We might get below error while trying to login to Power Apps

AADSTS50058: A silent sign-in request was sent but none of the currently signed-in user(s) match the requested login hint. Trace ID: b4c0c138-c2d9-46c8-999f-3d10414c2d00 Correlation ID: 60ab6eb7-bef1-44e1-8756-bdc33907e6ef Timestamp: 2020-07-03 19:11:01Z

Error AADSTS50058 is

UserInformationNotProvided – This means that a user is not signed in. This is a common error that’s expected when a user is unauthenticated and has not yet signed in.
If this error is encouraged in an SSO context where the user has previously signed in, this means that the SSO session was either not found or invalid.
This error may be returned to the application if prompt=none is specified.

https://docs.microsoft.com/en-us/azure/active-directory/develop/reference-aadsts-error-codes

Few solutions to this error are

Try using InPrivate mode.
Clearing the browser cache – which would force the user to enter the authentication information again, which then would get a new token.
https://powerusers.microsoft.com/t5/Building-Power-Apps/Authentication-error/m-p/388458/highlight/true#M113179

After the cache is cleared or if we open it in incognito mode, the user is asked to enter the login in again.

And the user can log in to Power Apps successfully.

Check below links for more detail –

https://github.com/AzureAD/azure-activedirectory-library-for-js/issues/323

https://docs.microsoft.com/en-us/azure/active-directory/develop/msal-js-known-issues-ie-edge-browsers#other-workarounds

Hope it helps..

Setup Entity records routing – Omnichannel for Customer Service

In the previous posts, we learn about provisioning, setting up a live chat, and WhatsApp channel.

In this post, we’d see how to set up an Entity records channel.

Through the Entity records channel, we can route cases as well as other entities to the omnichannel agents.

To enable an entity for Routing, enable Activities and Queues for that entity.

Leave the “Automatically move records to the owner’s default queue …” checkbox unchecked for automatic distribution of records to work.

Inside the Omnichannel Administration app, navigate to Channels à Entity Records to create a new entity record channel.

A default workstream will be created automatically, we can also select an existing workstream.

Click on the Routing Rules tab to define the routing rule.

Add a new rule item i.e. for case type as a problem, route the record to Complaints Queue.

Activate the routing rule created.

Let us create a new case of type problem and apply routing to it.

We can see the records added to open work items for agents to pick as defined in the CDS entity workstream.

We can also set the Work distribution mode to Push that would send the notifications to the agent

To automatically route the record we can use Power Automate to define a flow that calls the Apply Routing Rule action.

Refer below blogs to learn in-depth about the Omnichannel

https://neilparkhurst.com/2020/05/29/omnichannel-for-customer-service-collection/

https://thecrm.ninja/omnichannel-for-dynamics-365/

along with Microsoft Docs

https://docs.microsoft.com/en-us/dynamics365/omnichannel/omnichannel-customer-service-guide

Hope it helps..

Setup WhatsApp Channel (Preview) in Omnichannel for Customer Service

In the previous post, we provisioned the Omnichannel for Customer Service and had configured the Chat channel.

In this post, we’d see how to set up the WhatsApp channel (preview)

Within the Omnichannel Administration app, navigate to Channels à WhatsApp and create a new WhatsApp account record.

Provide the required consent

As a first step, let us set up the Twilio sandbox account to be used for WhatsApp channel configuration.

Create Twilio WhatsApp account

https://www.twilio.com/whatsapp

Navigate to console and copy the value of Account SID and AUTH TOKEN

https://www.twilio.com/console

Specify Account SID and Auth token of the Twilio account created in the new WhatsApp channel record.

Saving the record will generate the Twilio inbound URL, copy that URL.

Navigate to Twilio Console à Programmable SMS à WhatsApp and activate the sandbox.

Follow the instructions to configure the sandbox.

On successful confirmation, select Sandbox in the navigation menu and paste the Twilio Inbound URL generated eariler in the “When a message comes in” text box

Back in our WhatsApp channel record, add the sandbox WhatsApp number configured

Specify the Twilio Sandbox WhatsApp number and select the out of the box WhatsApp workstream.

The default WhatsApp workstream

Next click on Validate to check the configuration

With validation successful now we are good to test it.

Send the message to the Twilio sandbox number

The agent will receive the notification from the WhatsApp channel configured.

On accepting the notification, Agent can now communicate with the visitor.

Thus, we saw how seamless it is to configure and get started with WhatsApp channel in Omnichannel for Customer Service.

Refer below blogs to learn in-depth about the Omnichannel

https://neilparkhurst.com/2020/05/29/omnichannel-for-customer-service-collection/

https://thecrm.ninja/omnichannel-for-dynamics-365/

along with Microsoft Docs

https://docs.microsoft.com/en-us/dynamics365/omnichannel/omnichannel-customer-service-guide

Hope it helps..

Admin Center URL(s) in Microsoft Dynamics 365 ( Power Platform)

Listing down Administration Center URL(s) for quick reference à

Admin Center	URL
Microsoft 365 Admin Center	https://admin.microsoft.com/
Power Platform Admin Center	https://admin.powerplatform.microsoft.com/
PowerApps Admin Center	https://admin.powerapps.com/
Dynamics 365 Admin Center	https://port.<region>.dynamics.com/G/instances/instancePicker.aspx for e.g. EMEA https://port.crm4.dynamics.com/G/instances/instancePicker.aspx UAE https://port.crm15.dynamics.com/G/instances/instancePicker.aspx North America https://port.crm.dynamics.com/G/instances/instancePicker.aspx India https://port.crm8.dynamics.com/G/instances/instancePicker.aspx Replace <region> with
Dynamics 365 Advanced Settings	https://[orgname].[region].dynamics.com/main.aspx?settingsonly=true
Dynamics 365 Home	https://home.dynamics.com/
Power Apps Maker Portal	https://make.powerapps.com
Power Automate Admin Center	https://admin.flow.microsoft.com/
Power BI Admin Portal	https://app.powerbi.com/admin-portal
Azure Active Directory	https://aad.portal.azure.com/

Hope it helps..

Provision – Omnichannel for Dynamics 365 Customer Service

For one of our projects we are exploring capabilities of Omnichannel.

Below are the steps for provisioning the Omnichannel for Dynamics 365 Customer Service for quick reference.

First, we need to make sure we have the Customer Service Hub already installed.

https://trials.dynamics.com/

Next, we need to have an active subscription of Chat for Dynamics 365 Customer Service or Dynamics 365 Digital Messaging

The other options

https://docs.microsoft.com/en-us/dynamics365/omnichannel/try-channels

Navigate to Microsoft 365 Admin Center

https://admin.microsoft.com/

Select Purchase services
à
Add-ons

Select Dynamics 365 Customer Service Digital Messaging Add-on trial.

And also assign the add-on license to the users who need to work with Omnichannel Service.

We’d be presented with the below message.

Login to Dynamics 365 Administration Center and navigate to Applications Tab and click on manage for Omnichannel for Customer Service.

https://admin.powerplatform.microsoft.com/environments

This opens the Manage environment page for Omnichannel.

Click on Add Environment to add the environment.

Here we can select the environment to which we want omnichannel solutions to be configured.

Click on next and we can then add chat as one of the channels.

Click on Next to similarly setup SMS, Social, and Microsoft Teams as the channels.

Click on Finish to start the installation.

It will show the status as Installed after some time. (took around 1 and half hour in our case)

To manage the channels or delete the omnichannel we can use the same Manage option for Omnichannel for Customer Service in the Applications tab within Dynamics 365 Admin Center.

We can use the Delete button to remove the Omnichannel configuration à

We’d also see the Omnichannel Administration and Omnichannel for Customer Service apps in the list of Dynamics 365 Apps.

Get all the details here

https://docs.microsoft.com/en-us/dynamics365/omnichannel/administrator/omnichannel-provision-license

Hope it helps..

Few points to consider -Status Reason transitions in Dynamics 365

We can specify Status Reason Transitions for Case as well as custom entities. Using the status reason dialog box, we can define (filter) which values are available to be set as next status reason.

Let us take a scenario where we have the following values defined for Status Reason for Active State in a custom entity named Test Entity.

Just to keep it simple we have a just on transition defined i.e. from A1 we can only select A2.

So now when a user creates the Test entity record with A1, the only other option user can select is A2.

Now let us try updating it through backend and set value as A3 for the same record with value A1 as status reason. (Here we have used SQL 4 CDS plugin)

Well, it updates the record with value A3 and doesn’t throw any error.

This would make us think that it only works from the client side and will not work from the server side.

Let us now create a workflow to update the value to A3.

Let us update the subject of the record to trigger the workflow for the below record.

Interestingly we get the error and are not able to set it to A3 as expected.

That was with the real-time workflow, for the asynchronous workflow also we get the error.

Let us give it one more try and update it through service.

Through the service call, we can update it.

So basically, the State transition rules will work as expected from user interface and workflows, however, through service we are still able to override it, which we need to be aware of before using this feature.

Hope it helps..