Story
The 'nice-to-have' requirement for a SMB selling services across select countries is to automatically update currencies on a regular basis so that sales reflect the general changes of exchange rates minimizing the financial risk of a downward trend.
Architect
Flow has all the widgets necessary to schedule automation triggers, connectors to retrieve D365 currencies and third part exchange rates, connectors to update D365 currencies, and logic to add sufficient degree of robustness so that the automation will work without attention and, should there be an issue, the ability to notify an administrator. The design should be flexible to take care of any new currencies that are added to D365 without needing to change the code.
Considerations
- Flow executions cost money (however small) so no need to over schedule its execution
- In this story, there is no need to keep a history of currency changes. This flow updates the Rate value of a target Currency. I've worked with other clients where finance need to keep a list of currency changes and that these changes are 'locked' from a financial audit perspective - where this is a requirement, change the Flow to meet these needs
Flow
Here is a look at the main flow...
RECURRENCE:
this component provides a means to trigger a currency update based on a date and time - local or universal
CODE:
initializes a string variable that is to contain the D365 currency code that is to be updated (name='CODE')
XPATHSEARCH:
the selected exchange rate feed I selected is formatted in XML; initializes a string variable that is to contain a dynamically generated XPATH statement to be used to search the feed for the rate of the target currency code (name='XPATHSEARCH') NOTE: for emphasis, this flow does not create a table out of the exchange rates but simply searches the feed for the desired rate using XPATH so there is no loop-in-loop structures - more efficient!)
RATE:
initializes a string variable that is to contain the currency feed exchange rate for a D365 currency code (name='RATE') (NOTE: I chose a string instead of a whole number due to incompatibilities with decimal types in D365 - 'Exchange Rate' field in D365 is a decimal type)
List Records:
retrieves a list of D365 Currency records. Note: I don't filter here but just get a raw list of all Currencies. If you have deactivated currencies or want to exclude the base currency then add this filter to the query.
HTTP:
the exchange rate source site (name='HTTP'). I used a feed from '
http://www.floatrates.com/daily/cad.xml ' but being a Canadian company, we have since changed this to use that from the Bank of Canada (
https://www.bankofcanada.ca/rates/exchange/daily-exchange-rates/#download where one can get exchange rates in CSV,JSON, and XML format. Note the structure of this feed is completely different which will change how this Flow will be structured).
GET CURRENCIES FROM D365:
main loop (see below)
Loop components...
GET CURRENCIES FROM D365:
references the list of currencies from D365 and provides the looping structure (read, 'for each' currency in D365...)
GET A CURRENCY CODE FROM D365:
for the current currency record in D365, store the currency code text string in this variable 'CODE'
BUILD AN XPATH TO GET RATE OF CURRENCY CODE:
insert the currency code text string held in 'CODE' variable into an XPATH statement held in this variable 'XPATHSEARCH'; this XPATH statement is used to get a new rate (I'm not executing an XPATH here just building the text string). I've used a concatenation function to stitch the first part of the xpath together with the the code text string, and the last part of the xpath defined as follows:
concat('/channel/item[targetCurrency="',variables('CODE'), '"]/exchangeRate/text()')
For reference, 'channel' is the root element of the feed; 'item' element is the child of the channel and represents the details of each currency; one retrieves a 'currency record' by referencing the 'item' by array reference (aka item[1]); this can be done dynamically by a search - in this case, [1] is replaced by the result of a search for an element 'targetCurrency' which has a value equal to the current 365 currency code (so if there are 3 currencies in D365 and the first one is USD then targetCurrency will equal 'USD'); the xpath statement does not return the item (aka currency) 'record' but instead returns the value of the exchange rate directly by referencing the 'exchangeRate' element and returning the text contained within.
GET NEW RATE:
this executes the xpath statement against the exchange rate source feed and 'cleans up' the result by removing any brackets and quotations (how I wish there was a Match regex to use here!)
replace(replace(replace(string(xpath(xml(body('HTTP')),variables('XPATHSEARCH'))), '[',''),']','' ),'"','')
NOTE: if a currency code in D365 is not found in the exchange rate feed then the output RATE is an empty string. This will always occur based on the fact that in D365 the base currency is returned in the D365 query and the base currency will not be found in the source feed.
Condition:
The retrieved exchange rate from the source feed is what determines whether to update D365. For the condition component, the value stored in the variable 'RATE' is checked to see if it isn't an empty string.
If there is a value then move ahead and update D365; if not, do nothing. One could extend the solution with a notification to the D365 administrator that a currency was not updated. However, in this solution the base currency is included in the list of currencies, it is important to exclude the base currency from the list of currencies retrieved from D365 if you want to add some notification otherwise you will get a notification each time the Flow runs.
Update a Record:
If the result of the condition is 'true' then update the current D365 Currency record by assigning the field 'Exchange Rate' the value of the variable 'RATE'.
One could extend the solution here to note a successful execution of the update or to send a notification to an administrator if desired.
