Recently, while working on a project that needed to update the Exchange rates in CDS, I was tasked at finding a solution that would be able to retrieve data from a SQL Server hosted on an Azure Virtual Machine. There were so many different approaches, and security was the main one, but I decided to do a deep dive and testing out how Power Automate will be able to accommodate these requests.
The first thing of course was the creation of the SQL Server database and add the data to it. I created a table in SQL Server that would be updated on a regular basis containing the Exchange Rates that I needed. For the purpose of this post, I used Azure SQL Server, and not a managed instance or a SQL Server hosted on Azure. The image below show the table and values that were added to the SQL Server database.
You can see in the image that we store the Exchange Rate Code, The Exchange Rate Date (in Date format and string format) and the Exchange Rate value on that particular date.
Next, I created an entity in CDS to store that information. I called the new entity Transaction, and added fields of the Transaction Amount, Exchange Rate and the Transaction Amount in US Dollars. When adding a new record to the Transaction table we will only add the Currency and the Transaction Amount.
The first test that was to be performed was to create a Power Automate flow, that would get triggered on the creation of a new Transaction record, retrieve the Code Name of the currency, and then call Azure SQL Server (using Username and Password connection), to get the rows that correspond to the query that we provided.
I then initialized a CurrentDate variable that would contain today’s today in the format of the Date String that I created in the database. The formula for that date was:
formatDateTime(utcNow(), ‘yyyyMMdd’)
I used the Get rows (V2) action, adding a Filter Query which would specify a condition where the Exchange Rate Code is the code I previously retrieved and the Date is the current date, which was set in the previous step. The Select Query would return the value of the Exchange Rate value that I needed.
The image below shows the steps to get here.
Next, although I know I will have a single result, I still did an Apply to Each as I used the Get Rows for my SQL Server connection. I add the value property to the Apply to each action, and then add a Compose Step to calculate the amount in US dollars. The calculation here is slightly more complex, but still considerably easy if you know the names of your actions and items. This uses the mul to multiple two values, and conversion of each of the values to float to allow this multiplication to happen.
mul(float(items(‘Apply_to_each_3’)?[‘ExRateValue’]), float(triggerOutputs()?[‘body/nyc_transactionamount’]))
The final step here is to update the Transaction record and passing the ExchangeRateValue from the SQL query results and the Transaction Amount in US dollars. The image below shows the logic that is built in the Apply to each section.
Next we save the flow, and we need to test this logic. In order to test this, we navigate to our Model-driven app, and provide the Currency and the Transaction amount, as shown in the below image. As the flow was created to trigger on create of a new transaction record or update of the total amount, this will execute. The image below shows the information that was entered in the record before the flow executed. You will notice that the transaction number, transaction exchange rate and transaction amount (USD) are left empty.
You can wait approximately 5-10 seconds and then be able to see the results in the flow as shown below:
Now that we saw that we could do this by connecting to SQL Server directly, I was going to test a few other alternatives. I created an Azure function that would connect to the SQL Server and retrieve the same value for me. The main purpose is that this Azure function would be available across the board, even if you did not have access to SQL Server. You will need to add the System.Data.SqlClient nuget package to your Azure function project and to the header of your file. Your function code will expect two values as part of a query string, the currency code and the formatted date. The code is not fully optimized, but you can see the code below:
Now that we created the Azure function, let’s take a look at how the modified Power Automate flow will look like. The trigger and the first three action will be the same. Instead of the Get Rows, we would have an HTTP request, and we would pass the formatted URL, containing the code and the date parameters that we need to pass to the Azure function. We would use a compose action, similarly to what we did before to multiply the result from the Azure function (Exchange Rate) and the total amount of the base currency: mul(float(body(‘HTTP’)), float(triggerOutputs()?[‘body/nyc_transactionamount’]))
Finally we would update the record in CDS with the values of the Exchange Rate and the result of the multiplication. The image below shows the final flow:
I ran the same tests in CDS and the results were the same, only using an API, so that it could be called from anywhere.
Now that I saw that this works, I wanted to add another option to this. I build a custom connector that would call the Azure API, so that it could be accessed directly from flow. This worked nice as well. The image below shows the test that I ran within the connector builder, and can be added to the Power Automate flow easily.
Although this entire process is doable, and somewhat cumbersome as you have to update your Exchange Rates on a daily basis and make sure that the data in your database is synched, there are APIs that are available for use that will allow you to just call a custom API and retrieve the data that you are looking for.
One of these solutions is called XE Currency Data, and for $800/year you can get 10,000 API requests per month. Additional requests are available of course at a higher price. I have not reviewed other vendors of a similar api, but this seems to be something that should be readily available and maybe even available for free for smaller request numbers.