How to automatically update currency exchange rates in CRM using Power Automate

In this blog I am going to show how we can update the currency exchange rates in Dynamics CRM periodically using the power automate.

The most common issue we face in CRM is the currency conversion since there is no out of the box option available to update the currency available in Dynamics CE. Most of us do it either manually when working with multiple currencies and that takes a lot of effort and even, we don’t have any in-built option in Power Automate to auto-update currencies in CRM. So, here are the steps which you should apply for automatically updating the currency on periodic basis.

Step 1: Start with creating the custom connector. This is just to get the latest exchange rates via an API.

  •   Goto : Data-> Custom Connectors-> New Custom Connector


  •   Add Name and URL (api.exchangeratesapi.io/)


  •         Click on Security and on the next page click on Definition without entering any value for authentication because the API is open, and we don’t need any subscription key or authentication.

 

  •         On the Definition page, click on +New Action button and enter required values for Summary and Operation ID and click on ‘Import from Sample’.


 

  • Now we have the Get request URL and the Query in our custom connector.  Just click on Create Connector and Test the operation.



Step 2: Since we have the API ready to request the latest exchange rate via our custom connector is ready we just need to create the flow for updating the currency on daily basis in our system.

  •         Set the recurrence: I chose once a day, you can set as per your requirement.



  •         Get the base currency from CRM which can be extracted from Organization entity. Use the Expand Query to get the currency code which need to be feed to the API. Also pass the row count value as 1 to get just the first row as the flow creates ‘Apply to Each’ action if we don’t specify single value for the base currency.

Use the query: basecurrencyid($select=isocurrencycode)

(Why Expand Query.?: To get the data from the related entity)

  • Next initialize a variable to store the first value returned from the above query. Use this query to get the value : first(body('Get_Base_Currency')?['value'])?['basecurrencyid/isocurrencycode']

  •         Now, call the custom connector which we created to get the list of exchange rates from the API, pass the base currency from the above query to the connector



  •         From the above query we will get the result (exchange rates) in Json format and we just need to apply them to the currencies in our system but before that we’ll have to filter out the non-base currencies from CRM because we cannot update the value of base currency and it will throw an error.

So, for this we will query the currency table and outer join it with the organization table to select only the non-base currencies. Here is the fetchXml for your reference

 

<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>

 

  •         Once we get all non-base currencies from the CRM, we need to update them with the latest exchange rates value by using the Apply to Each block. Pass the value from the previous step and then update each one by pulling the corresponding exchange rates from the Json data which we got in the 4th step (Get exchange rate) query.

For your reference, here is the code for matching the currency code from the currency in CRM and the Json result from the Get Exchange Rate query: float(body('Get_exchange_rates')?['rates']?[items('For_Each_Requested_Rate')['isocurrencycode']])

 

 

Done. Above steps will update the exchange rates value of all currencies in the system.

 

>> Before: 


 

>>> After: 


 

 

 

Comments

Popular posts from this blog

Search data in Gridview on Textbox Key press event using JQuery in Asp.Net- C#

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

Dumps for Microsoft Dynamics CRM MB2-703 Practice Exam Questions Free

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

Show GridView row details in tooltip on mouseover with jQuery using asp.net inside GridView in c#

go to top image