RPA with Dynamics 365 Finance & Supply Chain

Are you looking for ways to automate some of your routine accounting functions, like reconciling sub ledgers to the general ledger?   Microsoft has recently created some examples using Power Automate Desktop (think of it like Task Recording on steroids). It can not only automate steps through Dynamics 365, but it can also automate Excel and other applications. In one example from Microsoft they automate the AR to GL reconciliation: Automatic running of the AR aging and the customer/ledger reconciliation reports. Power Automate then extracts data from both reports in Excel and compares the balances. Power Automate then posts a Team message letting you know the subledger is in balance. Power Automate saves the reports with the date in the file name, so there is backup. Microsoft Directions & Example   I working with the example, I needed to make a couple of adjustments to the sample flow that was provided but I was able to get it to work. Here is a video of the flow running in

Adding a new entity to Dual-write in Dynamics 365 Finance

As Microsoft continues to grow, expand and improve Dynamics 365 Finance and Supply, one of the most powerful features released has been Dual-write.  Dual-write is an out-of-box infrastructure the provides near-real-time interaction between customer engagement apps and Finance and Supply Chain apps.  In summary, it is a tightly coupled, bidirectional integration between Finance and Supply Chain applications and Dataverse (formally known as Common Data Service).  Dual-write makes integration with Dynamics 365 Sales and other CE application very straight forward.  It also opens up the opportunities to use PowerApps, Power Portal and Power Virtual Agent with Dynamics 365 Finance and Supply Chain apps by connecting through the Dataverse.

There is already documentation from Microsoft as well as many blogs already available on setting up Dataverse.  Here are a few links: 

Instead of covering the same information in this blog I am going to look at expanding the use of Dual-write, so I can build a Bot with Power Virtual Agent to provide customer balance and aging information to my sales team through Microsoft Teams.  In future articles, I will discuss building the Bot, but prior to starting on the Bot, I need to data available in Dataverse.  Let’s take a look at how I can expand Dual-write to bring more data from Dynamics 365 Finance and Supply Chain to Dataverse.

Step 1 – Identify the Finance and Supply Chain Data Entity that contains the data you are looking to synchronize with Dataverse. 

From the Data Management workspace, you can view or add new target entities.  In my case, the data I am looking for is available in the CustAgingEntity.  I will add this entity into my list.  The name provided in the Entity column will be the name used inside of Dual-write.

Step 2 – Now that I have identified the source data, it is necessary to create a target location in Dataverse.

Sign in to PowerApps and select the environment that is connected with Dual-write.

Choose tables – New Table

Step 3 – Once the table is created in Dataverse, it is necessary to add to appropriate columns.

Make sure to understand your source data.  Are there related tables, like Customer or Company?  What are the data types of the fields?

For related tables, it is necessary to create a lookup column in the new table in Dataverse.  In my example, it was necessary to create lookup columns for Company and Customer Account.  This related the table to the Company and Account tables in Dataverse.

Note: It is not necessary to have every column from the source entity included in the Dataverse table.  It is only necessary to include any required columns as well as the data you need for your business solution.

This may take a couple tries to get the columns you need.  You might you Excel or Power BI to connect to the source Data Entity, so you can view and understand the data you are bringing into Dataverse.

Step 4 - Create a key for Dataverse table

It is necessary to create a key on the Dataverse table.  The key should be the same as the entity from Dynamics 365 Finance.  For this example, the key is Company and Customer Account.

Step 5 – Create a new table map in Dual-write.

From Data Management workspace, select Dual-write.

In Dual-write, select Add table map

The Add table window will open, and you will select the table (data entity) from Dynamics 365 Finance & Supply Chain, then select the new table that was just created in Dataverse.  Select the Publisher, Version number (probably since this is the first build) and enter a description.

Step 6 – Map columns between Dynamics 365 Finance and Dataverse

Once the new table is added to the Dual-write list, it is necessary to map the columns between the two sources.  Simply click on the table map line to open the mapping window.

Now use the Add mapping button at the top of the screen to add a column for mapping.  It will add a row below that says None = None.

Click on the None on the left side, to select the appropriate column from Dynamics 365 Finance.

Click on the None on the right side, to select the appropriate column from Dataverse

Once all the columns have been mapped, Save the table mapping.

Step 7 – Run initial sync and set the table map to run state

After the table mapping is saved – select run to run the initial sync

This will transfer the existing data from Dynamics 365 Finance to Dataverse and turn the table mapping to a run state, so as new information is added the tables will stay in sync.

Step 8 – Confirm data in Dataverse

It is possible to view the data in Dataverse by opening the table and selecting Data

Now the data is synchronizing with Dataverse it can be used with PowerApps, Power Virtual Agent, Power Automate and other tools that work with Dataverse.


In a future article, I will use this data along with a Power Virtual Agent Bot.


Popular posts from this blog

Creating a date table with a fiscal year in your Power BI data model

RPA with Dynamics 365 Finance & Supply Chain

Using Full Page Apps in Dynamics 365 Finance