Overview

When using multiple currencies in your Dynamics 365 instance you will want to keep the exchange rates updated for more accurate currency conversions. 

We can do this easily using the Open Exchange Rates API and North52 BPA's WebFusion and Scheduler components.

North52 BPA Solution

The North52 Decision Suite solution works like this:

• A Schedule is set up to run daily and executes a Custom Action
• A Formula is set up on the Custom Action which:
  • Calls the Open Exchange Rates API using the CallRestAPI function
  • A list of exchange rates for all currencies are returned
  • We then find all currencies in the Dynamics 365 system and then loop over each one (except the default currency) updating the exchange rate field with the corresponding value returned from the API request

Set up API, xCache, Global Action, Formula and Schedule

Setup Account on openexchangerates.org

• Go to https://openexchangerates.org/ and sign up for an account, using USD as your base currency allows you use a free account
• Once you have signed up you will get an App ID, copy/note this App ID
  • Note: If using the free version of OpenExchangeRates, you can only have the base currency be USD. If your organization's base currency is not USD, you will need to use the paid version. However for testing, the free version will work fine.

xCache

To securely store the App ID we use an xCache record. Only System Administrators or specifically authorized users will be able to access this value.

• Navigate to the North52 App and select Business Process Activities > xCache
• Create a new xCache record
  • Enter OpenExchangeRates for the Category
  • Add AppID to the Base Key so that the full BaseKey name is OpenExchangeRates_AppID
  • In the Value Information tab enter your Open Exchange Rates App ID into the Value (Secured) field
• Click Save

Global Action

• Navigate to your environment
• In the right hand top corner, click on the cog wheel and select Advanced settings
• Navigate to Settings > Processes and click the New button
• Enter Update Exchange Rates for the Process name
• Select Action from the Category drop-down list
• Select None (global) from the Entity drop-down list
• Click OK
• Click Save
• Add the Process Unique Name including the prefex to the end of the Process Name, is should now be Update Exchange Rates new_UpdateExchangeRates (the new_ may be different depending on your system settings)
  
  • This step is to get around a bug when you execute fetch-xml to lookup at run-time the unique name of the Action. We logged this bug with Microsoft but it has yet to be resolved.
• There are no Process Arguments or Steps to be defined so we can click the Activate button to activate the process

Formula

• Create a new Formula, setting the following values in the Formula Guide
  • Source Entity set to N52 Command
  • Set Formula Type to Action
  • Select the Decision Table editor
• Change the Name of the Formula to Currency - Action - Get Exchange Rates
• Set Action Event to new_UpdateExchangeRates (the Action you created in the step above)
• Click Save
• Select any cell in column A and right-click to show the Decision Table Context Menu
  • Select Insert > Insert Table Calculation (===) to insert a new column at position A (Note: A Table Calculation is a calculation that is executed once before anything else the on the Decision Table sheet. It is used to store a value that can be then referenced in other parts of the Decision Table logic)
• Replace the default Set Name text with Get Rates from Open Exchange Rates
• Select cell A4
• Click the + button in the top right of Decision Table sheet to expand the Formula editor for the cell
• Select the Functions tab and type callrest into the Search functions input box and press enter
• Click on the CallRestAPI function to add it to the Formula editor
  • Replace 'baseurl' with 'https://openexchangerates.org/api/'
  • Replace 'resource' with 'latest.json'
  • Replace 'method' with 'GET'
  • Within the SetRequestParams() function, type 'app_id' followed by a comma and a space 
    • Select the Source tab
      • Expand the xCache node
      • Expand the OpenExchangeRates node and click on OpenExchangeRates_AppID,
        •  xCacheGetGlobal('OpenExchangeRates_AppID') will be added to the Formula
  • Replace 'responsestatuscode' with 'OK'
  • For SetRequestActionPass replace 'action' with GetVar('responsecontent') - responsecontent is an internal variable which is populated by the CallRestAPI function
  • For SetRequestActionFail replace 'action' with ThrowError('Exchange Rates Request Failed: ' + GetVar('responsecontent')) - this will present a standard Business Process error message with any details supplied by the API if there is an error
  • The CallRestAPI function should look like this:
    
  • Click the X button in the top right of Decision Table sheet to close the Formula editor
  • Click Save
• We can perform a quick test to ensure the CallRestAPI is functioning as expected:
  • Ensure that the Trace Level (Register tab > Deployment Settings) is set to Information and the Formula has been saved
  • From the top right of the Decision Table/Formula canvas click on the Lightning Bolt icon
  • Click the Execute button
  • If there are no errors, 'NoOp' should be displayed in green text
  • Expand the N52 Formula Trace tab from the main form
  • Open the Trace record and locate the responsecontent value from approximately line 66. This should be a JSON response from the Open Exchange Rates API
• The next part of our Decision Table Formula will be to loop over all Currency records and update the Exchange Rate using the value returned from the API:
  • Select any cell in column B, right-click and select Insert > Insert ForEachRecord
  • While cell B2 is selected click on the Functions tab and enter findrecords into the Search Functions input box and press enter
  • Shift-click on the FindRecords function to open the function wizard:
    • Type All Currencies in the Friendly Name field and leave the Type as Calculation
    • Set Entity Name to transactioncurrency (select Currency from the search options)
    • Set Where Attribute to *
    • Set Equals Value to *
    • Leave Select Attribute (*), Record Count (50) and No-Lock (true) to their default values
    • The completed wizard will look like this:
      
    • Click Generate
    • The function is added to the hidden Global Calculations sheet
  • We need to exclude the base currency from the update (as it is not possible to update the base currency exchange rate) so do this using the Condition column:
    • Select cell C2 and type Exclude Base
    • Right-click anywhere on the Decision Table and select Operations > Toggle Advanced Mode to reveal row 3
    • Select cell C3 and click the + button in the top right of Decision Table sheet to expand the Formula editor
      • From the Functions tab search for CurrentRecord
      • Click on CurrentRecord under the Loop node to add it to the Formula canvas
      • Delete the placeholder text 'fieldname'
      • From the Explore tab, search for the Entity Currency, expand the Currency node and CTRL-click the Currency Code node to add 'isocurrencycode' to the CurrentRecord function
      • Click the X button in the top right of Decision Table sheet to close the Formula editor
    • In cell C4 type !='USD' (or whatever your base currency code is)
  • The next step is to update the Exchange Rate field for each Currency found:
    • Select cell D2, click on the Explore tab, search for the Entity Currency, expand the Currency node
      • Click on Exchange Rate
    • Select cell D4 and click the + button in the top right of Decision Table sheet to expand the Formula editor
      • From the Functions tab search for GetVar
      • Select GetVarJsonValue under the WebFusion > Process - JSON node to add it to the Formula canvas
      • Replace the placeholder text 'selecttoken' with 'rates.' and immediately after a space followed by + and a space
        • From the Functions tab search for CurrentRecord
        • Click on CurrentRecord under the Loop node to add it to the Formula canvas
        • Delete the placeholder text 'fieldname'
        • From the Explore tab and the expanded Currency node, CTRL-click the Currency Code node to add 'isocurrencycode' to the CurrentRecord function
      • Delete the placeholder text 'defaultvalue'
        • From the Functions tab search for CurrentRecord
        • Click on CurrentRecord under the Loop node to add it to the Formula canvas
        • Delete the placeholder text 'fieldname'
        • From the Explore tab and the expanded Currency node, CTRL-click the Exchange Rate node to add 'exchangerate' to the CurrentRecord function (this just sets the value to itself if for any reason the API does not return a rate)
      • Click the X button in the top right of Decision Table sheet to close the Formula editor
  • Click Save
• We can perform another quick test to ensure the Decision Table is functioning as expected:
  • From the top right of the Decision Table/Formula canvas click on the Lightning Bolt icon
  • Click the Execute button
  • If there are no errors, 'NoOp' should be displayed in green text
  • Check the Currencies from Settings > Business > Business Management > Currencies to ensure that they have been updated with the values from the API

Schedule

We need to set up a simple Schedule that will execute the custom Action on a daily basis.

• Navigate to the North52 App and select Business Process Activities > Schedules
• Click the New button
• Enter Daily Exchange Rates Update for the Name
• Set Frequency to Daily
• Set Schedule Type to Global Action
• Set Workflow to the Action created earlier - Update Exchange Rates
• Set the Start DateTime to the date and time you would like the Schedule to Start
• Click Save
• From the N52 Commands menu select Start Schedule
• Review the System Jobs to see that the Schedule is running