How To Create Power Automate Flow To Update Currencies In Dynamics 365

Consider your sales team doing business with accounts in different countries and you have more than 1 currency in your system. They ask you to daily update all the currencies in your system so they can finalize the deal at the latest exchange rate.

For on premise versions this would need lot of codes. Like creating API’s to get data then connect CRM to update each record one by one. This would work for online versions too but there is much more simplier no code way to do that.

Creating API Key

We will use currencylayer api for retrieving exchange rates. For this we will create a free account and get our api key.

Open the link https://currencylayer.com/signup/free and create yourself an account. It will create an api access key for you.

Click 3-Step Quickstart Guide and copy the API endpoint in Step 2.

It will be something like this: http://api.currencylayer.com/live?access_key={your_api_key}

Now to be able to use this api endpoint we will create a custom connector.

Creating a Custom Connector

To create a connector for our API click Data -> Custom Connector and click New custom connector select Create from blank.

If you can’t see the image clearly, right click and select open image in new tab.

Fill the fields as below:

  1. General
    1. Scheme: HTTP
    2. Host: api.exchangeratesapi.io
    3. Base URL: /
  2. Security: Leave as it is
  3. Definition:
    1. Click new action
    2. Summary: Get Latest Currency Exchange
    3. Operation ID: Read
    4. Visibility: none
    5. Request: Click Import from sample
    6. Verb: GET
    7. URL: Paste your API endpoint.
  4. Code: Leave as it is
  5. Click Create Connector.
If you can’t see the image clearly, right click and select open image in new tab.

When you click Create Connector, it will create the connector with the given definiton. It may leave the Connector editing page, if it leaves open it again in edit mode and click the Test step.

In Connections grid select your connector, in Read grid enter your api_key and click test operation. You should see the response now.

If you can’t see the image clearly, right click and select open image in new tab.

Creating a Cloud Flow

Now we created our api key and we created a connection for our api endpoint. Only remained part is retrieving and updating our exchange rates. For this we will create a cloud flow. If you are not familiar with flows, you can think them as modern workflows.

Go to make.powerapps.com and select Flows tab, then click New flow button on the ribbon bar. Because of our flow will be run daily, we will choose Scheduled cloud flow.

After you click Create, the flow editor will be open.

Now let’s stop and think for a second what will our process be.

  • We will connect to Dynamics 365 to retrieve currencies
  • Then we will retrieve exchange rates from our api
  • We will update currency exchange rates.

So let’s start by adding a Dataverse connector to retrieve our currencies.

  • Click New Step
  • Click Microsoft Dataverse connector.
  • Click List Rows function.
  • Choose Currencies table(table name is the display name of your currency entity)

Next, we will add a variable to store our exchange rates.

  • Click New Step
  • Write variable into the search bar
  • Select Initialize variable function
  • Name: Rate
  • Type: Float

Next, we will call our custom connector to retrieve exchange rates api.

  • Click New Step
  • Click Custom tab
  • Select Currencies Exchange Rate
  • Paste your api key to the access_key text box.

We will parse our API response to store the retrieved exchange rate in our Rate variable.

  • Click New Step
  • Write Apply to each into the search bar.
  • Select Apploy to each.
  • Select an output from previous steps: Select value under the List rows section.
  • Click add an action.
  • Write Parse Json into the search bar.
  • Select Parse Json function
  • Click content and select body under the Get Latest Currency Exchange section.
  • Click Generate from sample to generate the api schema
  • Paste your API response(you can retrieve a response from your api endpoint http://api.currencylayer.com/live?access_key={your_api_key})
  • It will generate the schema for you.

We parsed our response, now we will store the retrieved exchange rate.

  • Click Add action
  • Write Set variable into the search bar.
  • Select Set variable function.
  • Name: Rate
  • For the Value input, click Value field then click the Expression tab and paste this expression : body(‘Parse_JSON’)?[‘rates’][items(‘Apply_to_each)?[‘isocurrencycode’]]

While we are iterating through our currency records in our for each expression(because we selected List rows value as our for each value), we are storing our parsed json response as our variable. While we are storing our variable we are reaching to the [‘isocurrencycode’] to select the currency code of the current for each step.

If in our retrieved currency list, the isocurrencycode of our first index is USD, then we will store USD value of the parsed json response. And we will update this currency with this value.

  • Click Add Action
  • Select Dataverse connector
  • Select Update row function
  • Table name: Currencies
  • Row ID: items(‘Apply_to_each’)?[‘transactioncurrencyid’]
  • Exchange rate: select our Rate variable

Click save. Your flow is now created and ready to be run.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s