cancel
Showing results for 
Search instead for 
Did you mean: 

Join the discussion

Most Recent
ShanmugaRaja
Advocate II
Advocate II

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

 

This article is the first part of the integrating Dynamic 365 CRM with Third party API via custom connector using Power Automate discussion series which focuses on giving you a comprehensive description about Creating Custom Connector for Third Party API using Power Automate.


As I need a Third-Party API to access, a bit of google Fu led me to find out this free APILayer, which is really handy to request the exchange rate against a specific currency by setting the base parameter in the request.

 

Working principle of the solution is:

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. I have divided this real time use case in to two parts:

1. Creating Custom Connector to retrieve live Exchange Rates using Power Automate
2. Creating recurring flow to retrieve live Exchange Rates for provided currencies and send it for an approval. The approved exchange Rates are updated in Dynamic 365 CRM on confirmation from Business users.

Power platform is enabled developer to create custom connector in five easy steps.

 

Let’s Start!!

 

Step #1: Update General Details

General tab, do the following:

  • Upload a connector icon of your choosing (optional).
  • Updated icon background color of your choice.
  • In the Description field, enter a meaningful value. This description will help others decide whether the connector might be useful to them.
  • Update Host to the address for the Currency Exchange Rate API. The connector uses the API host and the base URL to determine how to call the API.

 

ShanmugaRaja_0-1675090413016.png

 

Step #2: Specify Authentication Type:

There are several options available for authentication in custom connectors. The Currency Exchange Rate APIs use API Key authentication.

  1. On the Security tab, under Authentication type, select API Key.

 

ShanmugaRaja_1-1675090603871.png

 

2. Under API Key, update a parameter label, name and location details.

 

ShanmugaRaja_2-1675090660086.png

 

ShanmugaRaja_0-1675315274405.png

 

Step#3: Create the connector definition

Custom connectors can have one or more actions. In this article on currency exchange rate connector will only have one action.

1. On the definition tab, select New action.

 

ShanmugaRaja_0-1675091463349.png

 

2. In General section, Add a summary, description and Operation ID for this action. The summary and description will be show in Power Automate to identify the action whereas Operation ID will be used in Power Apps. Operation ID must be a text string with no space.

 

ShanmugaRaja_1-1675091533662.png

 

ShanmugaRaja_1-1675315341133.png

 

3. In Request section display inform based on the HTTP request for the action. Select Import fromsample.

 

ShanmugaRaja_0-1675091925364.png

 

4. Specify the information necessary to connect to the API. The Verb list shows a standard set of API request types (GET, POST, DELETE, PUT, PATCH, HEAD, OPTIONS). In our case, selected the GET method and specify the request URL (https://api.apilayer.com/currency_data/live?source=source&currencies=currencies) And then select Import.

 

ShanmugaRaja_1-1675092037560.png

 

When we import the sample URL its query parameters appear in the Request section. We must setup each of the parameters.

 

ShanmugaRaja_2-1675092113493.png

 

ShanmugaRaja_0-1675315428648.png

Edit the request parameters

 

ShanmugaRaja_0-1675092482224.png

 

In our case, query parameters “source” and “currencies” are required fields. So, we need to change the setting by providing a description and set Is required? field to Yes.

 

Edit Source Parameter:

 

ShanmugaRaja_1-1675092553553.png

 

Edit Currencies parameter:

 

ShanmugaRaja_2-1675092589641.png

 

5. The Response area displays information based on the HTTP response for the actions. Select Add default response.

 

ShanmugaRaja_3-1675092647108.png

 

6. Just input a sample of the response JSON into the body section and click Import.

 

ShanmugaRaja_4-1675092682058.png

 

 

Note: The JSON response can be obtained by copying and pasting our sample URL into the web browser.

7. The Validation section display any issues that are detected in API definition. The validation status will be displayed in the upper-right corner of wizard.

 

ShanmugaRaja_5-1675092783321.png

 

Step#4: Code (Preview)

This step is optional. So, I have ignored this step and going to Step 5: Test.

 

ShanmugaRaja_6-1675092843698.png

 

Step#5: Test

Before testing our custom connector, we need to Create connector. Click on Create connector button in the top. 

 

ShanmugaRaja_7-1675092960253.png

 

Now custom connector is ready to test. Click on the New connection button.

 

ShanmugaRaja_8-1675093025730.png

 

To Authenticate the custom connector, a new Tab is opened to enter the API key. This API key is obtained from the APILayer web site. After entering the API key, click the create connection button.

 

ShanmugaRaja_9-1675093086534.png

 

Return to the Test tab and click the refresh icon button in the upper-right corner of wizard.

 

ShanmugaRaja_0-1675093170617.png

 

Choose the newly create connector in the selected connection field. Then enter a value for the source and currencies fields. Finally, select Test operation button to our custom connector.

 

ShanmugaRaja_1-1675093239576.png

 

ShanmugaRaja_1-1675315520377.png

 

The connector calls the API, and we can review the response, which includes the exchange rate for provided currencies (INR, AUD, EUR).

 

ShanmugaRaja_2-1675093376545.png

 

Our custom connector is ready to fly now. In Part2, I will illustrate how to integrate this custom connector with Dynamic 365 CRM using Power Automate. Stay tuned!

ShanmugaRaja
Advocate II
Advocate II

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.

 

ShanmugaRaja_0-1675316161403.png

 

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

 

ShanmugaRaja_1-1675316208233.png

 

ShanmugaRaja_2-1675316217891.png

 

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

 

ShanmugaRaja_3-1675316259674.png

 

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

 

ShanmugaRaja_4-1675316292064.png

 

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

 

ShanmugaRaja_5-1675316344908.png

 

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

 

ShanmugaRaja_6-1675316396245.png

 

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

 

ShanmugaRaja_7-1675316512559.png

 

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.

 

ShanmugaRaja_8-1675316567511.png

 

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

 

ShanmugaRaja_9-1675316616329.png

 

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.

 

ShanmugaRaja_10-1675316689685.png

 

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. 

 

ShanmugaRaja_0-1675420943821.png

 

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.

 

ShanmugaRaja_1-1675421056665.png

 

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'), ',' )

 

ShanmugaRaja_2-1675421388880.png

 

ShanmugaRaja_3-1675421412692.png

 

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

 

ShanmugaRaja_0-1675429819477.png

 

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.

 

ShanmugaRaja_0-1675422478799.png

 

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.

 

ShanmugaRaja_1-1675422673659.png

 

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.

 

ShanmugaRaja_2-1675422745228.png

 

Step#8:

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

 

ShanmugaRaja_4-1675422858719.png

 

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.

 

ShanmugaRaja_5-1675422937680.png

 

Step#10:

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

 

ShanmugaRaja_6-1675423009915.png

 

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.

 

ShanmugaRaja_0-1675446088620.png

 

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.

 

ShanmugaRaja_8-1675423979601.png

 

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.

 

ShanmugaRaja_9-1675424256901.png

 

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.

 

ShanmugaRaja_1-1675446154981.png

 

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

 

ShanmugaRaja_11-1675424615292.png

 

Live Exchange Rate is created as a HTML file in OneDrive

 

ShanmugaRaja_0-1675449415769.png

 

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

 

ShanmugaRaja_5-1675446924974.png

 

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.

 

ShanmugaRaja_14-1675424898529.png

 

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

 

ShanmugaRaja_4-1675446487375.png

 

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

 

Happy Learning and Happy Sharing. Cheers!!

 

Helpful resources

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/
Join Blog
Interested in blogging for the community? Let us know.