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

Post a Comment

Popular posts from this blog

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

Image
First of all add the following .js file, Link and the JavaScript to your <Head> tag :: <head runat="server">     <title></title>    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script> <script type="text/javascript" src="js/quicksearch.js"></script> <script type="text/javascript">     $(function () {         $('.search_textbox').each(function (i) {             $(this).quicksearch("[id*=gvDetails] tr:not(:has(th))", {                 'testQuery': function (query, txt, row) {                     return $(row).children(":eq(" + i + ")").text().toLowerCase().indexOf(query[0].toLowerCase()) != -1;                 }             });         });     }); </script> </head> // This Script dynamically searches the GridView cells and filters out the unw
Read more

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

In this blog I have given a list of all StateCode and StatusCode of useful entities. Please refer MSDN for more details for any doubt if you have regarding any thing. I also have collected this through MSDN The following table shows default Microsoft CRM values for StateCode and StatusCode. Each StateCode has its own default StatusCode value. For example, if a Product StateCode is 0 (Active), the default StatusCode is 1 (Active(default)). If the Product StateCode is 1 (InActive), the default StatusCode is 2 (Inactive(default)). Microsoft CRM only accepts StatusCodes that are valid for a particular StateCode. Entity Status (statecode) Status Reason (statuscode) Account (account) 0 Active 1 Active 1 Inactive 2 Inactive Activity (activitypointer) 0 Open 1 Open 1 Completed 2 Completed 2 Canceled 3 Canceled 3 Scheduled
Read more

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

Dumps for Microsoft Dynamics CRM MB2-703 Practice Exam Questions Free...!!! After a lot of net surfing i got few questions for passing out the exam for certification in Microsoft Dynamics CRM MB2-703 Practice Exam Questions. I think you will find it helpful and please comment if it helped you. Although this is just a shortcut for passing exam but it really helps a lot to clear exam in short duration of time. Note:: Don't blame if question doesn't matches as i have only collected questions and provided all at a single place and there is no guaranty that this will definitely come in exam. Apart from this you should go through CRM 2011 MB2- 866 dumps also. Topics and their percentage share in MB2-703 Exam: Create and Customize Solutions (10-15 percent) Customize Entities and Entity Relationships (10-15 percent) Customize Fields (10-15 percent) Manage Forms (10-15 percent) Manage Views (10-15 percent) Create and Customize Charts and Dashboards (10-15 pe
Read more

How to import CSV files into DataTable in C#

Below is code the which you can use for converting a csv file into Data Table in C#. This method also handles comma values inside a double quoted value. 1. Provide the file path of your csv file: string sCsvFilePath = @"C:\Downloads\Sample.csv"; 2. Call Custom Method: DataTable  dtTable = ConvertCSVtoDataTable(sFilePath); 3. Copy and Paste below: public   static   DataTable  ConvertCSVtoDataTable( string  sCsvFilePath) {      DataTable  dtTable =  new   DataTable ();      Regex  CSVParser =  new   Regex ( ",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))" );      using  ( StreamReader  sr =  new   StreamReader (sCsvFilePath))     {          string [] headers = sr.ReadLine().Split( ',' );          foreach  ( string  header  in  headers)         {             dtTable.Columns.Add(header);         }          while  (!sr.EndOfStream)         {              string [] rows = CSVParser.Split(sr.ReadLine());              DataRow  
Read more

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

Image
In this blog i am going to show we could show enlarded image when hover on Link or an image like we see on any e-commerce website. First of all right click on your application >> Select Add New Folder and Give name as images >> Once folder created place some images in folder to show preview of images when hover on link using JQuery in asp.net. After completion of folder creation and insertion of images in folder write the following code in your aspx page :: Put the below code inside the <heaD> tag::  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"> </script> <script type="text/javascript" language="javascript">     $(document).ready(function () {         ShowPreview();     });     // Configuration of the x and y offsets     function ShowPreview() {         xOffset = -20;         yOffset = 0;         $("a.preview").hover(funct
Read more
go to top image