Dynamics 365 Business Central: Automate Currency Exchange Rate Updates (Currency Exchange Rate Services and Transformation Rules)

Hi, Readers.
Today, we will discuss how to automate currency exchange rate updates in Business Central.
As you know, we can manually update the currencies exchange rate on Currencies page.

There is a quick and easy way to automate this process, Currency Exchange Rate Services.

Contents

To set up a currency exchange rate service
Transformation Rules

To set up a currency exchange rate service

Business Central requires a exchange rate service that provides an XMLor Json feeds. So you can use the external service to keep your currency exchange rates up to date, such as FloatRates.

You can find the feeds file corresponding to your local currency.

Because the local currency of the Demo database is GBP, I use the GBP URL for this test.
GBP XML Feeds: http://www.floatrates.com/daily/gbp.xml

Well, let’s start.

1. Choose Exchange Rate Service -> Exchange Rate Services on Currencies page, or enter Currency Exchange Rate Services in tell me, and then choose the related link.

2. Choose the New action.

3. Fill in the following fields on the Currency Exch. Rate Service page:
Code and Description: Specify the setup of a service to update currency exchange rates.
Service URL and Service Provider: Specify the URL and name of the service provider.
For example:
Code: FLOAT
Description: Float Rates
Service URL: http://www.floatrates.com/daily/gbp.xml
Service Provider: Floatrates

4. Open the Field Mapping area and populate the Source, Default Value, and Transformation Rule columns.
The content in Source you can select is the details in the XML file set in Service URL.
For example: Same as demo data, keep the value of Exchange Rate Amount at 100.

Caption	Source	Default Value	Transformation Rule
Parent Node for Currency Code	/channel/item
Currency Code	/channel/item/targetCurrency
Starting Date	/channel/item/pubDate		US_DATE_FORMAT
Exchange Rate Amount		100
Relational Exch. Rate Amount	/channel/item/inverseRate		MULTIPLYBY100

The purpose is to map the values to these four fields when manually modifying the exchange rate.

Video:

5. Now you can choose Preview to confirm whether the value you get is correct.

Working on it…

OK.

Note: If the date format is inconsistent with that in BC, the value cannot be obtained.

6. Select Enable, after you have selected this option, job queue entries are created for the daily update of exchange rates. You will be asked to open the Job Queue Entries page.

You can set the recurrence and Starting time in JOB Queue Entry Card.

7. If you did not enable a job queue entry for automatic update of the currency exchange rates as provided by the external service, you must retrieve the information manually and on a periodical basis from the currency exchange rate service.

Choose the Update Exchange Rates action.

The Exchange Rate will be updated.

PS:
1. I can get Relational Exch. Rate Amount from xml file, but if I want this value to be displayed after multiplying by 100. How to do it?

You can use Transformation Rule.

Transformation Rules

If the values in the fields you are mapping differ, you must use transformation rules for data exchange definitions to make them the same. You define transformation rules for data exchange definitions by opening an existing definition, or creating a new definition, and then on the Line Definitions FastTab, choosing Manage, and then Field Mapping. Predefined rules are provided, but you can also create your own. The following table describes the types of transformations that you can perform.

Option	Description
Uppercase	Capitalize all letters.
Lowercase	Make all letters lowercase.
Title Case	Capitalize the first letter of each word.
Trim	Remove empty spaces before and after the value.
Substring	Transform a specific portion of a value. To specify where to start the transformation, choose either a Start Position or Starting Text. The starting position is a number that represents the first character to transform. The starting text is the letter immediately before the letter to replace. If you want to start with the first letter in the value, use a starting position instead. To specify where to stop the transformation you choose either Length, which is the number of characters to replace, or the Ending Text, which is the character that is immediately after the last character to transform.
Replace	Find a value and replace it with another. This is useful for replacing simple values, such as a particular word.
Regular Expression – Replace	Use a regular expression as part of a find and replace operation. This is useful for replacing multiple, or perhaps more complex, values.
Remove Non-Alphanumeric Characters	Delete characters that are not letters or numbers, such as symbols or special characters.
Date Formatting	Specify how to display dates. For example, you can transform DD-MM-YYYY to YYYY-MM-DD.
Decimal Formatting	Define rules for decimal placement and rounding precision.
Regular Expression – Match	Use a regular expression to find one or more values. This is similar to the Substring and Regular Expression – Replace options.
Custom	This is an advanced option that requires assistance from a developer. It enables an integration event that that you can subscribe to if you want to use your own transformation code. If you are a developer and want to use this option, see the “Tip for Developers: Example of the Custom Option” section below.
Date and Time Formatting	Define how to display the current date as well as the time of day.

Example of the Custom Option: InputText * 100.

After you define your rules you can test them. In the Test section, enter an example of a value that you want to transform, and then check the results.

2. If you don’t need to keep the value of Exchange Rate Amount at 100.
Field Mapping setting:

Caption	Source	Default Value	Transformation Rule
Parent Node for Currency Code	/channel/item
Currency Code	/channel/item/targetCurrency
Starting Date	/channel/item/pubDate		US_DATE_FORMAT
Exchange Rate Amount	/channel/item/inverseRate
Relational Exch. Rate Amount		1

3. You can also use other free service URL. But it will be a bit troublesome when dealing with field mapping.
For example: https://cny.fxexchangerate.com/rss.xml

It can also be achieved by using Transformation Rules and a little customization

END

Hope this will help.

Thanks for your reading.

ZHU