Integrating Dynamic 365 CRM with Third party API via custom connector using Power Automate – Part2

This article is in continuation of Part1 where I tried to give you a comprehensive description of Creating Custom Connectors in Power Automate for third party system API which enables a clearer understanding of custom connectors and its functionality. In Part2, I will give you a brief idea about how to integrate Dynamic 365 CRM with a third-party API via custom connector using Power Automate with a real time business use case.

I will take few moments to explain about multi-currency handling in Dynamic 365 CE.

The base currency is set while setting up the instance or the environment. There can only be one Base currency for instance or environment. Most importantly, the base currency cannot be updated.
But you are selling your products in the global market. It is important to understand that you will have prospects using multi-currencies. Dynamic 365 CE OOB supports you to create multi-currencies seamlessly. The below mentioned screenshot illustrates the default currency record in Dynamic 365 CE which consists of currency code, currency symbol, currency name and exchange rate.

To define additional currency, navigate to setting -> Business Management

We can create additional currencies either system or custom. To create system currency, we can select any of the existing currency codes.

For instance, while we create an opportunity record, Dynamic 365 CE automatically sets the default currency for the user.

Based on the customer, the Rupee currency is set in opportunity record.

While calculating the base amount, Dynamic 365 CE considers the Exchange Rate defined in the currency record.

Unfortunately, Dynamic 365 CE does not update Exchange Rates automatically. We should have some mechanism to update the exchange rate on a daily basis.

Let’s Start!!

Our primary goal is to capture live Exchange Rate from third party API and update the currencies records in the Dynamic CE on a daily basis. So, it is better to create a scheduled cloud flow which runs every day.

Step#1: Create a Recurring flow

In the next step, we must provide the trigger timing details. Define the starting date and time and repeat frequency. As per our requirement, we want to run it daily so choose to repeat every 1 day at 1:00 PM.

Step#2: Initialize Variable
Now we will add an action to initialize the variable to set the base currency code.

Step#3: Retrieve Currencies detail from Dynamic 365 CE
Add a new action to retrieve Currencies records from Dynamic 365 CE by using Dataverse connector and action as List rows.

Select columns:

Enter a comma-separated list of columns to return. In our case, selected few columns like “transactioncurrencyid,currencyname,isocurrencycode,exchangerate” from currencies table.

Filer Rows:

Filter expression to narrow down the set of rows that Dataverse returns such as “statecode eq 0” for rows with state code is equal Active.

To exclude base currency, the added condition to check “isocurrencycode ne USD” for rows with currency code is not equal to USD.

Step#4: Create Currency Code Array

Add Data Operation - Select action to create a new array from a list that contains currency code. 

Note: Although you can add or remove elements by using the select action, you can’t change the number of objects in the array.

Step#5:

Time has come to use our custom connector to get a live exchange rate for a given currency code list. Select Currency Exchange Rate action from choose an operation template.

As we know, the currency exchange rate takes two parameters.

1. Source – Base currency is passed.

2. Currencies - Specify a comma-separated list of currency codes. So, I used join expression to join array values with separators of comma delimiter (,).

Expression:

join(body('Select_Currency_Code'), ',' )

In the background connector calls the API, and you get the response, which includes the live Exchange rate for a given currencies list.

My initial thought is to create a cloud flow which runs on a daily basis to capture live Exchange Rate against currencies and update in the Dynamic 365 CE. But my thought processes evolve to add more capabilities to match with real time business process flow. Updating Currencies Exchange Rate is not quite that simple. It will go through several levels of approval process before the exchange rate is reflected in the Dynamic 365 CE. So, I decided to incorporate a PDF file generation and approval processes functionalities.

Step#6:

Using Select - Data Operation action to create a new array which contains currency code, currency name, latest exchange rate and GUID. This array will help us to easily format data, generate a PDF file and update exchange rate values in Dynamic 365 CE.

To get currency exchange rate from API response JSON object. I have written an expression to parse the response object using the output function by passing currency code to pick the exact exchange rate value from the API response JSON object.

Expression:

outputs('Currency_Exchange_Rate')?['body/quotes']?[concat('USD',item()?['isocurrencycode'])]

Step#7:

I want to present the data in HTML table format. The first and foremost option is to select Create HTML table - Data Operation action. This action will change a JSON array input into an HTML table seamlessly.

Columns are mapped either automatically or header and values are mapped manually. In our case, we have mapped only essential columns to create an HTML table.

Step#8:

Create a new step using compose operation action and enter the required CSS style for the table to generate a PDF file.

Step#9:

I picked a OneDrive connector to create a file (html file) and convert file (PDF file) seamlessly. As a first step we will use Create file action to create an HTML file in OneDrive.

Step#10:

Add convert file action to convert a HTML file into PDF file format using the path.

supported file conversion formats are html, htm, docx, msg, ppt, pptx, xls, xlsm, xlsx etc.

while using convert file action, we should be aware of known limitations and file size constraints in place for the protection of the connector's service. note that attempting to perform operations beyond the limits will result in rejection, errors or timeouts. Few key important limitations are listed below.

• The maximum archive size for Extract archive to folder action is 50 MB and 100 files inside.
• Then when a file is created or when a file is modified triggers will skip every file bigger than 50 MB.
• Some errors, like "Access Denied" usually indicate that the file is locked (e.g., by Excel services). Also, the user, file folder, or tenant may have a policy preventing access.

Step#11:

A PDF file created successfully. Now time to attach the PDF file and send it for approval. 

Create a step using "Start and wait for an approval" action and select "Approve/Reject-First to respond" for approval type.

Approval type: when a flow with Create an Approval action is configured with Approve/Reject - First to respond, it waits until it's assigned to approve or assigned to reject the approval request.

Step#12:

To evaluate the user response is equal to Approve! then we need to add a "condition" action to handle the response from the previous approval action.

on the condition card, select an empty area in box on the left. The dynamic content list opens. Select the Outcome parameter to add it to the box. In the box in the middle of the condition card, select is equal to. In the box on the right, enter the "Approve" text which is mentioned in the approval type.

Step#13:

If yes block:

If Outcome is equal to "Approve" then add Apply to each action to loop through each item from live currency exchange rate list and update Dynamic 365 CRM currencies records with latest Exchange rate.

Step#14:

Finally, I decided to store the exchange rate PDF files in SharePoint for future reference.

Using SharePoint connector, add create file action to enter file name and output of converted PDF file content body is passed to file content for the create file action. Select correct SharePoint site address and folder path where we want to store the file.

All set! Let's see the cloud flow in action.
The Old Exchange Rate cited below

Live Exchange Rate is created as a HTML file in OneDrive

HTML file is successfully converted to PDF and attached in approval email.

When approver approves the Exchange rate then Cloud flow will update the latest Exchange Rate in Dynamic 365 CRM. The Below screenshot is the updated Exchange rate.

Finally, Cloud flow will store the approved versions of the Exchange Rate PDF file into SharePoint for future reference.

`Perhaps this article will also give you some inspiration on other ways to make use of Power Automate.

Happy Learning and Happy Sharing. Cheers!!

Securing sensitive information in documents is becoming a hard requirement in many organizations.  With the enactment of privacy laws, the ability to redact information at scale is a challenge.  I’ll show you how this can be done using the Power Platform and a simple Azure function.

Now, you can make the Chat Bot more effective in handling queries from Customer by using Power Automate Flow.

In Power Automate one of the common scenarios was to extract Look up Field Text which will be a nightmare for the people who are new to Power Automate about how to extract details of lookup field. As an example, on update of Contact Table, Account Details will be extracted.

In Power Automate, during certain scenarios we must traverse all the records in excel file table and based on few conditions content in excel to be updated. List Rows action present under Excel Online(Business) Connector in power automate can be used. As an example scenario of updating eligibility of Employees based on Age explained here.

In Power Automate, at times we must join few items which received in the form of strings. As a scenario, response of the user was taken to show this function behavior.

One of the common scenario was to update data in Excel Spread sheets . To achieve this functionality automatically, we can use an action- Update a row into a Table in Power Automate under connector Excel Online (Business). As an example, after Sending emails to contacts, Employee details will be updated with sent Date time stamp in a separate column.

In Power Automate, at times we must validate values and proceed further if result is true, in these scenarios If function is used. As a scenario, response of the user was taken to show this function behavior.

One common manual work was to send emails to list of users, where this can be achieved by using Power Automate. As an example, sending emails through excel sheet from OneDrive considered to explain this.

Most of the common usage of storing data in Excel Spread sheets and a common scenario was to add data into Spread Sheet. We can use an action Add A row into a Table in Power Automate under connector Excel Online (Business). As an example, Employee details will be passed to Spread sheet through Power Automate.

In Power Automate, at times we must validate two values and proceed further if result is true, in these scenarios Equals function is used. As a scenario, name of the person and current years values were taken to show this function behaviour.

In Power Automate, at times we must validate two expressions and proceed further if both the expressions are true, in these scenarios and function is used. As an scenario, name of the person and lucky number values were taken to show and function behavior.

In Power Automate, at times we must repeat certain steps until a condition met. In these scenarios we make use of Do until function as an example counter value will be incremented till it become 3.

In Power Automate, for doing validations or Function will be used. As an example, an String variable and a number is used to do this validation.

In Power Automate, for doing validations Not Function will be used. As an example, an String variable used to work with Not function.

In Power Automate, generic arithmetic operations like Less Than will be used to validate provided integer values. As an example, an integer variable used to work with Less than function.

In Power Automate, generic arithmetic operations like Greater will be used to validate provided integer values. As an example, an integer variable used to work with Greater function.

In Power Automate, generic arithmetic operations like GreaterOrEqual will be used to compare between values. As an example, Speed Limit with integer value considered to compare with other integer values.

Recently Microsoft announced Graph API HTTP Request action step. Good news is its not a premium connector. There are lots of potential for this.

In this blog I will explain how to create a table and read an Excel sheet dynamically?

My scenario is drag and drop an Excel file (No table) in One Drive. Power Automate will trigger and read the Excel contents.

Lets start building the flow.

Here I am using my flow trigger as When a file (Excel) is created under OneDrive.

Next we need to capture the Excel file identifier which is needed for subsequent Graph API steps below. Graph API URL to get the identifier is:

https://graph.microsoft.com/v1.0/me/drive/root/children?$select=name,id&$filter=startswith(name, ‘{triggerOutputs()?[‘headers/x-ms-file-name-encoded’]}’)

Next we need to capture the body value (See below Content Value mapping : body(‘Send_an_HTTP_request’)?[‘value’]) of the response above and collect the identifier and name. For this I am going to add a Parse JSON step and pass the body value (body(‘Send_an_HTTP_request’)?[‘value’]) of response body of the previous step. See below.

Above Parse JSON Schema is as follows:

{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“@@odata.etag”: {
“type”: “string”
},
“id”: {
“type”: “string”
},
“name”: {
“type”: “string”
}
},
“required”: [
“@@odata.etag”,
“id”,
“name”
]
}
}

To avoid the loop I have used the following expressions to find the Excel file name and Id.

body(‘Parse_JSON’)?[0][‘name’]

body(‘Parse_JSON’)?[0][‘id’]

Next we need to find the used range values of Excel worksheet. This is used for creating the table with specified range.

To find the used range using Graph API is as follows:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/usedRange(valuesOnly=true)

Next capture the body response and look for the address property to get the value range (eg:Sheet1!A1:B7). We are interested in the cell values only. So we need to use the last() and split() expressions to extract the cell range. See below.

Above expression is as follows:

last(split(body(‘Send_an_HTTP_request_4’)?[‘address’],’!’))

Next we are ready to create table.

Graph API to create table is:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/tables/add

Under the body we need to pass the range value earlier we found and set whether the excel sheet has header or not.

Above step will create a table under the worksheet. Above under the URI I have hardcoded Sheet1 which is the default sheet name for my worksheet.

Next I am using the Parse JSON step to get the table id by passing the body from previous step. Using the filename and table id we can read the Excel sheet contents.

Above Parse JSON Schema is as follows:

{
“type”: “object”,
“properties”: {
“@@odata.context”: {
“type”: “string”
},
“@@odata.type”: {
“type”: “string”
},
“@@odata.id”: {
“type”: “string”
},
“style”: {
“type”: “string”
},
“name”: {
“type”: “string”
},
“showFilterButton”: {
“type”: “boolean”
},
“id”: {
“type”: “string”
},
“highlightLastColumn”: {
“type”: “boolean”
},
“highlightFirstColumn”: {
“type”: “boolean”
},
“legacyId”: {
“type”: “string”
},
“showBandedColumns”: {
“type”: “boolean”
},
“showBandedRows”: {
“type”: “boolean”
},
“showHeaders”: {
“type”: “boolean”
},
“showTotals”: {
“type”: “boolean”
}
}
}

Now we have built our flow and below is the overall flow looks like.

References:

https://docs.microsoft.com/en-us/graph/use-the-api

https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

Hope this is helpful and thanks for reading my post.

In Power Automate, at times we must work on JSON with String Values as input. We can use JSON function in power automate to convert string values to JSON and perform related operations. As an example, string values separated with comma and also xml string values are used.

In Power Automate, at times we must work on strings which have XML values. We can use XML function in power automate to convert string value to XML and perform related operations. As an example, notification xml string is used here to send notification to required person.

In Power Automate, at times we must work on fraction values, float function is used to achieve this. As an example, in order to send discounts notifications to customers during festival season we make use of this fraction function to provide price of a product after discount.

In Power Automate, at times we need to take decision based on Boolean value, it could be string or Boolean value. As an example, Power outage variable with true value is considered for demonstration and then Bool function is used for condition validation.

In Power Automate, at times we need to merge 2 different strings into an Array we can use Create Array Function so that we can get easily desired result. As an example, let us see how to get an array of emails with 2 strings as well as process to merge two different arrays of questions and answers into a single array.

In Power Automate, at times we need to compare values Min Function is used so that we can get easily desired result. As an example, let us see how to get Minimum age of 2 persons as well as minimum value from a given array of integers with the help of Min

In Power Automate, at times we need to compare values Max Function is used so that we can get easily desired result. As an example, let us see how to get Maximum age of 2 persons as well as maximum value from a given array of integers with the help of Max function.

In Power Automate, at times we need to perform Addition arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate total age of  2 persons with the help of Addition function.

In Power Automate, at times we need to perform Subtraction arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate warranty period that is available in years for Generator with the help of Subtraction function.

In Power Automate, at times we need to perform multiplication arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate number of minutes in a Day with the help of multiplication function.

In Power Automate, at times we need to perform division arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate price of a candy for quick reference from a bundle cost with the use of div function.