ReadyXRM

A little know feature of Dataverse is the ability to manage multiple currencies. This is one of the many standard features of Microsoft Dynamics 365 that is also available when building your own Power Apps based on Dataverse.

A while back I posted a blog called Guide to Currencies in the Common Data Service (Note that the Common Data Service is now known as Dataverse).

When working with different currencies in Power Apps or Dynamics 365, there is no out of the box mechanism to automatically update the exchange rates in Dataverse. You need to do this manually. In my original post I promised to write some instructions with a method to keep the exchange rates updated automatically. Here it is!

This is actually a pretty simple process thanks to custom connectors and Power Automate.

In my research, I got a lot of the technical details from this post titled Exchange Rate Conversion with Power Automate by fellow MVP Ryan MacLean. Check out this post and others by Ryan, there is a lot of great stuff there!

Getting Current Exchange Rates via an API

The first thing we need is a source for exchange rates. While there are come published connectors, I followed Ryan’s blog and decided to use a free service called Exchange Rates API, which are based on the European Central Bank.

The API is very simple, I can pass it a base currency rate (in my case, CAD) and it will return a list of currencies along with the current exchange rate values.

An easy way to try out the API is with Postman.

1. Create a new GET request using the URL found on the Exchange Rates API site.
2. Add in the parameter of “base” and a value of a currency code
3. Hit Send
4. View the results, the are returned as a JSON string

For the exchange rate API we don’t need any authentication or subscription key (for now) but the hosters of the site ask that we cache the results as to allow it to remain free and open. SO DON’T ABUSE IT! For my example, we will update the exchange rates once a day.

The next step is to create a custom connector using this API. Again, I followed the instructions on Ryan’s blog post to create a custom connector for this API.

From the Power Apps maker portal, ideally in a solution, create a new Custom Connector from blank. Give the connector a name and put in the Exchange Rate API URL (api.exchangeratesapi.io) in the host field.

The next step is security, but since the Exchange Rate API is open, we can skip this step.

In the Definition step we need to add a New Action

From that step we put in any summary or operation id that make sense. Once we have those values, then click on Import from sample.

In this case we can simply cut and paste an example from the Exchange Rate API website.

Here is the URL that I used:

https://api.exchangeratesapi.io/latest?base=CAD 

We will have the GET request URL and the Query (base currency) defined in our custom connector.

At this point we can create a connection and test our custom connector (optional step).

Currencies in Dataverse

Now that we have our custom connector defined and have a way to request the latest exchange rates, the next step is to create a Power Automate flow that will update all of currencies setup in our Dataverse environment. Before we dive in, lets take a quick refresher on how Dataverse stores currencies.

If you recall when you created your Dataverse environment and added a database, you had to decide on a base currency.

To add and edit additional currencies, you can access the currencies from the Business section on the Power Platform admin center for the specific environment.

Here you can add new currencies and manually update the exchange rates. Note that you cannot change the default base currency.

To understand how all these currencies work on both standard and custom tables and how they affect currency column values, please refer to my blog post on currencies.

Creating a Power Automate flow to Update Exchange Rates

Now that we have our base currency and a series of foreign currencies setup in Dataverse, we can create a Power Automate flow to run once a day to update the currencies.

Here is a high level overview of the flow, and we will drill down into each step.

For our trigger, we will want to run this flow once a day. You can choose any schedule you want but out of respect of the Exchange Rate API site admins, try to keep requests to a minimum.

We want to be able to determine our base currency to send to the API. We could easily hardcode this value, but since I might want to export this flow as part of a solution and provide it to different customers that might be using different base currencies, I would rather query Dataverse to get this value.

We can determine the base currency from the Organizations table. This table should only ever have one row and it contains much of the default business data from an organization. We need to use an Expand Query to the get the actual currency code that we will need to feed to the API. An Expand Query is basically a way to pull in columns from a related table from what you are querying.

The Expand Query:

basecurrencyid($select=isocurrencycode)

Even though the Organizations table has only one row and we specified in our row count that we only want one row, flow will still create a “Apply to Each” action if we don’t specify a single value for our base currency. For this action we will create a variable and use an expression to select the first value.

The following is the expression, get the first value of “currency code” from the step called “Get Base Currency”

first(body('Get_Base_Currency')?['value'])?['basecurrencyid/isocurrencycode']

Now that we have our base currency in a variable, we can call our custom connector and query the API for a list of exchange rates. In this action, we pass along our base currency.

The action will return the list of exchanges rates in a JSON format. Power Automate has various actions available to parse or convert these values. However, we are able to use the data in the format that it is provided to us.

Now that we have a list of exchange rates, we need to apply them to our currencies in Dataverse.

We start by retrieving the currencies, HOWEVER, we only want the “Non” base currencies or else we will get an error, as we cannot update the exchange rate of our base currency (1:1). (I learned the hard way)

For this I created a FetchXML Query (using FetchXML Builder, of course!) and used an outer join on the organizations table to choose only currency records that did not have a match on the base currency value.

Here is the complete FetchXML query:

<fetch>
  <entity name="transactioncurrency" >
    <link-entity name="organization" from="basecurrencyid" to="transactioncurrencyid" link-type="outer" alias="org" />
    <filter type="and" >
      <condition entityname="org" attribute="basecurrencyid" operator="null" />
    </filter>
  </entity>
</fetch>

And here is the step to retrieve currencies from Dataverse using that FetchXML.

Finally, once I had my query of non-base currency currencies, I was able to update each one (in an “Apply to Each” block), by pulling the corresponding exchange rate from the JSON data that was returned from the Get Exchange Rate query.

Here is the expression to match the currency code from the currency in Dataverse to the value in the JSON string that was returned from the Exchange Rate API and update the exchange rate value.

float(body('Get_exchange_rates')?['rates']?[items('For_Each_Requested_Rate')['isocurrencycode']])

The flow will cycle through the enabled currencies.

You can test the flow and make sure that it updates the list of exchange rates in Dataverse, and view the results in the currency table.

As a bonus, you can enable auditing on the Currency table and to see the changes over time of the exchange rates;

Summary

There are a lot of “hidden” features in Dataverse that come from the original Dynamics CRM or are base features of Dynamics 365 that you can utilize in your Power Apps solutions. Using Power Automate and custom connectors, anyone can create powerful extensions with little or no code. Scheduling Power Automate flows also adds an entire new dimension to automatically updating data. This post used currency as an example, but you could potentially use the same method for things like stock prices or the even the weather. The possibilities are endless.

Cover Photo by John McArthur on Unsplash

Nick Doelman is a Microsoft MVP, a Microsoft Certified Trainer and one of the lead trainers and content creators on https://365.Training. Following Nick on twitter at @readyxrm