Update/Delete/Create data across multiple Legal entity using Excel Addins in Microsoft Dynamics 365 Finance and Operations

Greetings,

In today's blog post, we will explore some useful tricks for working with Microsoft Excel add-ins. Specifically, we will cover how to create, modify, update, and delete data across multiple legal entities within Microsoft Dynamics 365 Finance and Operations.

Scenario:

Suppose you need to perform multiple create, delete, and update operations across different legal entities on a specific form. This can be a time-consuming and laborious task if done manually. Fortunately, there is an alternative solution using MS Excel add-ins, which we will demonstrate in this post.

Prerequisites:

• This technique applies to forms that have a corresponding data entity.
• Microsoft Excel 2016 or a higher version must be installed on your system.
• Microsoft Dynamics Excel add-ins must be properly configured within Microsoft Excel.

Example:

To illustrate this process, we will use the Fixed Asset Group form. However, this functionality is not limited to this form alone and can be used in any entity-backed form in Dynamics 365 Finance and Operations.

Steps

Navigation: Under Modules go to Fixed assets -> Setup -> Fixed asset groups

Click on Office Icon situated at the Right top and select the Data entity available under the “Open in Excel” section and click on the Download button.

  

Kindly note Many times, you might see multiple Data entities in the “Open in Excel” section Those might be one created by your development team or provided by the ISVs. Select the appropriate entity as per your requirement. Avoid using data entity that is marked as Obsolete.

Open the downloaded Excel file and wait until data is loaded.

 

Click on “Enable Editing” just in case you get the above ribbon.

Now we have to add “DataAreaId”. This is the optional step however it will helpful for us to understand the legal entity from where data is pulled from.

Click on Design Icon

 

Click on the pencil icon to edit under the Design section.

 

Locate the “DataAreaId” column and double-click on it to move it under Selected fields. 

Additional Hint: You can also click on Add menu under the selected fields section to move the column “DataAreaId” from Available fields to Selected fields.

 

Once you add the “dataAreaId” column to the selected field you can reshuffle its position by clicking on the “Up” and “Down” menu.

Click on the Update button to save the changes. You will be prompted with the below pop-up. Select Yes to accept the changes.

 

Click on the “Done” button to complete the changes.

 

Now you can see the newly added filed in excel i.e. “Company (dataAreaId)”

 

Now we need to remove the filter so that we can see data from all the legal entities. So, to do this click on the Filter icon

 

Once you clicked on the filter Icon, click on the “Delete” icon as shown in the image below and save the changes by clicking on the “Done” button.

 

Click on the Refresh button to get the latest data. You will get the pop-up…Just click on “Yes”

 

Now here is the Magic 😉. You have data across Legal entities in excel. Go ahead and perform your business operation i.e Create, Update and Delete. 

 

Once you have performed your operation, do not forget to click on the “Publish” button to save your changes to dynamics.

 

Kindly note: There should not be any filter applied on Excel columns while publishing the data.

Thank you for reading it patiently😉. That’s all for today. Please share your view through the comment section.