RPA with Dynamics 365 Finance & Supply Chain

-
Image
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
Post a Comment

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 1.0.0.0 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.


Comments

Post a Comment

Popular posts from this blog

Using Full Page Apps in Dynamics 365 Finance

-
Image
Have you ever wanted to put a website or a model-driven Power App in one of your existing Dynamics 365 Finance workspaces or forms?  You might already know you can create a new workspace with a website, but did you know you can add the website to an existing workspace? Here is an example of the Vendor invoice entry workspace with the Invoice Capture Power App embedded. Prior to using this feature, you need to turn on "Saved views support for workspaces" in feature management. Once this feature is enabled you can follow the following steps to add the Invoice Capture app or any model-driven Power App or website to an existing workspace. Open the vendor invoice entry workspace From Options - choose Personalize this page From personalize menu - select the 3 dots - and then Add an app Once that is selected (the screen goes gray) - click on the section header where you want to add the app (in my example I put it in Links) On the Add an app menu - choose Website Give it a name (my e
Read more

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

-
Image
There are many different articles available on Time Intelligence in Power BI.   Many new users to Power BI do not really understand the importance of having a date table in their data model.   I have spoken on this topic on many different occasions and continue to receive a lot of questions about the topic.   In this article I am going to try and cover a few tips on how to work with dates in your Power BI model. Many new users simply use the Auto Date & Time function of Power BI.   This function automatically creates a hidden date table for each field in the model with a date or date/time data type.   This function is enabled by default and it allows for slicing by Year, Quarter, Month and Day.   The problem with Auto Date and Time is that it only works with a standard calendar (Jan to Dec).   It requires a datetime column in the fact table.   It does not work with multiple fact tables and it does not support time intelligence calculations (YTD, MTD, etc.).   So there must be
Read more

RPA with Dynamics 365 Finance & Supply Chain

-
Image
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
Read more