Updating Records in Dynamics 365 for Operations Using Excel

Posted on: March 8, 2017 | By: Jarrod Kraemer | Microsoft Dynamics AX/365

Authored by: Craig Leska – Senior Technical Consultant

At some point you will want to do a mass update on records in Dynamics 365 for Operations.  In previous versions, there were several ways to accomplish this; using 3rd party tools, writing some custom code or using the Excel add-in. All of them are valid methods and still a viable option in D365.

Using the Excel method to mass update records in D365 has really evolved into a user-friendly experience which is faster and less cumbersome than the Excel add-in on previous versions.

I will walk through the steps needed to do mass updating of the customer master in our Contoso USMF data set.

We start out in the All Customers list page in the Account Payable module, you may want to filter this list to include a group of customers. I am excluding the Customer Group of 90 which is Intercompany accounts.

Once we like what see, we need to export to Excel using the Open in Microsoft Office button in the top right hand side of your browser then finally Open in Excel and download the file when presented.

 

After the file opens you may be asked to sign-in again within Excel. After you are authenticated you have the ability to add more criteria to your filter and even pull data from multiple companies.  To reiterate, I am excluding Intercompany accounts in my example.

 

From here we can make changes to the file.  I have made many changes to all customers in customer group 10 on five different fields. Once we are satisfied with these changes we can publish back into D365 by clicking the Publish button.

After changes are ready to be published, simply click the Publish button in the task pane on the Excel file. Remember to remove any filters you may have in place or Excel will remind you .

 

 

 

Don’t forget to refresh the customer master before looking at the customer record.

 

 

We can verify the changes were successful by opening the records and here we see the changes have taken place.

 

In general:

We filter on our fields, make the relevant changes to our Excel file and then we can publish back to D365.

Before you click the publish button, you will have to remove any filtering on the file.

Updating these records took a matter of seconds with only little to no technical skills needed.

 

All D365 logic is observed so you cannot enter an unknown value. For example, 54 in the customer group when a customer group 54 does not exists or enter a string in a currency or date field.

 

 

I used the Customer Master as an example.  You can take it further and update Terms of payments or Cash discounts as well.  Just be sure you include all relevant fields in the Excel file.

 

 

Remember the one cardinal rule of data entry

Always verify your changes and the data is what you expect.  For additional information please feel free to reach out to us at info@loganconsulting.com or (312) 345-8817. 

All the best! 
Logan Consulting 
www.loganconsulting.com



2020 Nucleus Research Report on ERP Technology

Free Download:

2020 Nucleus Research Report on ERP Technology

Download the guide ›