Microsoft Dynamics 365 is a suite of AI-powered business applications designed to help organisations manage their operations, improve customer engagement, and make data-driven decisions. It integrates both Customer Relationship Management (CRM) and Enterprise Resource Planning (ERP) capabilities, allowing businesses to streamline processes and enhance productivity. The suite consists of many products like:
Dynamics 365 Sales: Helps manage customer relationships and sales pipelines.
Dynamics 365 Customer Service: Provides tools for customer support and service management.
Dynamics 365 Finance: Manages financial operations and reporting.
Dynamics 365 Supply Chain Management: Optimises supply chain operations.
Dynamics 365 Marketing: Automates marketing campaigns and customer engagement.
Dynamics 365 Business Central: An all-in-one business management solution for small and medium-sized businesses.
Dynamics 365 primarily uses Microsoft Dataverse (formerly known as Common Data Service) as its underlying database. Dataverse allows for secure and scalable data storage, and it integrates with other Microsoft products like Power BI, Office 365, and Azure.
In this article I would like to highlight one of the key challenges with Dynamics 365 – handling fluctuating exchange rates, i.e. ensuring each financial record uses an accurate exchange rate based on the date the transaction was made. In Dynamics 365 currency information is managed using a multicurrency system, which allows each record to be associated with its own currency. This is achieved with the following:
Transaction Currency Table: Each record can have a transaction currency, which is stored in the "TransactionCurrency" table in Dataverse. This table includes details like the currency code, symbol, and exchange rate relative to the base currency.
Base Currency: Each company defines its own base currency during initial setup. All transaction currencies are related to this base currency through defined exchange rates. The base currency is stored in the "Organization.BaseCurrencyID" attribute.
Money Attributes: For each money column in a table, Dataverse automatically creates a corresponding read-only, system-calculated column that stores the value in the base currency. For example, if you create a column "Invoice Amount", the system will also create another column called "Invoice Amount Base" that holds the value in the base currency.
Exchange Rates: Exchange rates are defined to associate transaction currencies with the base currency. These rates are used to convert transaction amounts into the base currency for aggregation and analysis.
The Challenge: Static Exchange Rates in Dataverse
When generating an entity that processes exchange rates within any Dataverse table, the system pulls exchange rate data from the built-in Transaction Currency table. This table contains user-defined, static values for each currency, which may not always align with the actual rates on the specific date of a financial operation. This poses a challenge, as there are many situations in which a record created in the past must be updated which also triggers the system to overwrite the original exchange rate for the record. Another example – a record for which the transaction date is already in the past by the time the record was added to Dataverse. In other words, we need the flexibility to inject the correct exchange rate for each financial record based on its issue/transaction date.
Our Solution: Custom Dataverse Plugin
To address this challenge, we developed a solution that uses a custom Dataverse plugin which can be triggered by any REST API capable system (for example, we use Power Automate with a simple HTTP action). Our solution has three main components:
Historical Exchange Rate Data Storage. An automated mechanism to continuously pull and store historical data for various exchange rates. The source of truth could be a bank such as the European Central Bank and their exchange rate API, or an online currency converter API. Each day, the exchange rate information is recorded in a custom table in Dataverse. This ensures we have a complete archive of exchange rates that can be referenced at any time. We recommend a simple/low-code solution for this step like Power Automate.
Financial Record Creation with Dynamic Exchange Rates. When a new record must be created (for example an invoice), we pull the desired exchange rate from the custom table. With this approach we can decide what date to use for the currency exchange, thus ensuring accuracy.
Overriding the Default Exchange Rate. The retrieved exchange rate is used to override the default rate that would normally be pulled from the built-in Transaction Currency table. This is where our custom plugin intervenes to intercept the exchange rate transaction and to replace it with the correct historical value.
Below is a visual overview of the process flow:
How the Plugin Works
The custom Dataverse plugin was developed by Nuwey to handle the dynamic retrieval and replacement of exchange rates:
Triggering the Plugin: The plugin is triggered by the RetrieveExchangeRate event message, which is raised by the system whenever Dataverse attempts to update the currency field for a record in any table.
Processing Conditions: The plugin “intervenes” only when specific parameters are passed via the Dataverse Web API. This conditional activation gives us precise control over when the override is executed.
Efficient Execution: Once triggered, the plugin intercepts the default exchange rate retrieval from the built-in Transaction Currency table and replaces it with the value we provided it with. The plugin executes in just a few milliseconds, even when debug profiling is enabled, ensuring it does not delay the operation.
Key Benefits
This custom-built solution by Nuwey has the following benefits:
Versatility
The plugin can be applied to any table within Dataverse that includes an exchange rate field. This makes it a flexible solution adaptable to various financial operations across the system.
Control
The versatility is further enhanced by the ability to pass customised parameters when calling the Dataverse Web API, allowing precise control over when and how the exchange rate override is applied. This ensures that financial data remains accurate and relevant to the transaction context.
Performance
Despite its powerful functionality, the plugin is highly efficient. It operates seamlessly within Dynamics 365 with negligible performance impact, executing swiftly and reliably.
Conclusion
Extending the capabilities of Dynamics 365 and Dataverse by adding a dynamic exchange rates functionality does not only address a key limitation but also greatly improves efficiency. By automating this process with a Power Automate flow and the Nuwey Dataverse plugin, we can streamline operations by saving considerable time previously spent on manual validations and thus reducing the risk of errors. This ensures that financial operations remain accurate and reliable.
A significant advantage of this plugin is its wide applicability. It can be used with any Dataverse table that handles financial or currency data, enabling consistent management of exchange rates across various financial transactions within the CRM system. The ability to control this functionality through specific parameters in the Web API ensures that the override occurs only when needed, providing precise control over data management processes. Do not hesitate to contact us if you want to implement this solution to your Dynamics 365 system.
Comentarios