Exchange Rate Automation in Dynamics 365 (Power Automate)
Context and Client Need
The client is a diversified business conglomerate managing several companies through a unified instance of Microsoft Dynamics 365 Finance and Operations (FO). Utilizing Dynamics 365’s multicompany functionality, they operate across multiple subsidiaries, managing business processes such as sales, purchase orders, budgeting, invoicing, and more. Due to their global operational footprint, they handle transactions in multiple currencies, specifically USD, EUR, ARS, CAD, and GBP.
Within their Dynamics 365 environment, they established multiple exchange rate types, each tailored to specific companies and distinct transactional purposes (e.g., sales, purchasing). Previously, significant manual effort was required daily to update these currency exchange rates, consuming valuable administrative time and resources. The client thus sought an automated solution to streamline and automate the daily update of exchange rate quotations.
Proposed Solution
A focused, efficient project scoped at approximately 30 labor hours was proposed to implement an automated solution using Microsoft Power Automate. The automation workflow retrieves daily exchange rates directly from the “Banco de la Nación Argentina” (BNA) API endpoint and updates the corresponding exchange rates in Dynamics 365 FO automatically using its “Data Entities” feature.
Additionally, the solution included robust logging capabilities, persisting essential details such as transaction date, currency conversion (source and target currencies), exchange rate values, and the exchange rate type name. Furthermore, the proposed automation includes an email notification mechanism that alerts administrative personnel of any execution failures, enhancing the reliability and traceability of the process. The automation was configured to run daily, excluding weekends, utilizing the CRON scheduling capabilities within Power Automate.
Development
The development process involved creating a structured set of Power Automate flows organized hierarchically:
- CRON Flow: Scheduled nightly, this primary flow initiates the automation process, executes on designated weekdays, and manages error notifications.
- Principal Flow: Responsible for fetching real-time exchange rate data from the BNA API, validating the data against the Dynamics 365 instance, and subsequently triggering specific flows for each exchange rate type.
- Specific Exchange Rate Type Flows: Four specialized flows were implemented, each designed to update exchange rate quotations for distinct exchange rate types within Dynamics 365. These flows also logged each update in a dedicated Dataverse table, ensuring data accuracy and historical tracking.
All flows and associated configurations were encapsulated into a cohesive Power Platform solution—a bundled package containing related flows, Dataverse tables, and environment variables—developed in a dedicated development environment aligned to a Dynamics 365 testing instance.
Deployment and Results
The deployment process was straightforward, involving exporting the solution from the development environment as an unmanaged solution, subsequently converting it to a managed solution for importation into the client’s production environment. Notably, there was no need for manual adjustments to the Dynamics 365 instance URL post-deployment, as it was securely encapsulated within an environment variable, simplifying the import process.
The project achieved successful implementation and garnered explicit approval from end-users, effectively eliminating manual daily updates, significantly reducing administrative overhead, and enhancing operational efficiency and accuracy in managing exchange rate quotations.
