Back to the feature: Bulk editing in grids

Microsoft released a preview of the upcoming release of Dynamics 365 Finance and Operations version 10.0.38. When reading through the What’s New section and checking out the new records in Feature Management, I found one particularly interesting feature called Bulk editing in grids. In this post, I will share my findings when trying out this new feature.

Background

The title of this blog contains: Back to the feature. Why did I use this wordplay? I will explain. It was a day back in February 2013 when Microsoft removed the Fill utility from Microsoft Dynamics AX 2012 R2. In Dynamics AX 2009 and the first release of AX 2012, this tool was used for mass updates in the user interface. Mass updating records is always in high demand. I got several times questions about mass updates and noticed a lot of questions on the Dynamics Community forum. When there is a need to update a lot of records, often you want to do this as quickly and as easily as possible. As mentioned, an easy feature was removed. I was disappointed and wrote a blog about 10 years ago: Dynamics AX 2012 R2 – Missing features – Dynamicspedia

The reason provided by Microsoft was that the Fill utility did not validate the data. As an alternative, Microsoft suggested using SQL updates executed by an SQL administrator. For sure this method is not validating the data either. Using SQL, you would need to have technical knowledge of the data model. Also using the table browser and writing X++ coding were alternatives, and more often used than before. For sure, all alternatives were slowing down options for mass updates and correcting some data.

Dynamics 365 Finance and Operations

When Microsoft turned Dynamics AX into a SaaS solution, we came to know that direct access to the SQL Server database for the production environment would be restricted. This was done for security reasons and to prevent applying direct changes that would cripple the database. Oddly, where it was suggested to do mass updates via SQL updates, this was not possible anymore on production instances. Even the table browser form was limited by making it read-only for production environments.

For fixing data, we could use X++ scripts and in particular cases also work together with Microsoft Support to apply SQL updates once this was tested carefully in a sandbox environment. In case of mass updates, we could use data management or the Excel connector. In some cases, a Power Automate flow was also useful.

For about two years now, there has been an option for running custom X++ scripts with zero downtime. This was an improvement where it was not required to perform a full new deployment in case there was a need for executing a runnable class. Still, this requires technical skills, and not all updates are allowed in such scripts. For example, in my experience updates to the number sequences table were blocked by some validations.

Also, with a new grid control. Microsoft made it possible to paste information from Excel into a grid. Grid capabilities – Finance & Operations | Dynamics 365 | Microsoft Learn.

As mentioned in the introduction, Microsoft now brings back a mass-update utility in the user interface where functional users can quickly and easily update contents for multiple columns on a grid for multiple selected records. I will share my initial experiences below.

Bulk editing in grids

When you deploy Microsoft Dynamics 365 Finance and Operations version 10.0.38 (or later), you will find a new feature on the Feature Management workspace.

You must enable the feature to activate it in your environment. After enabling it, initially, I couldn’t find out how the bulk edit was working as there is at the moment of writing this blog no public documentation. Like 10 years ago when I couldn’t find information about the deprecation of the Fill utility, this time, I could not find information when clicking on Learn more. This leads to a generic page for the grid control features. Also, there is no word mentioned in the release plans nor the What’s New section on Microsoft Learn for this extremely welcome and useful feature. I would like to thank and give a shoutout to Ievgen Miroshnikov who provided me with the tip on where to find the bulk edit on grids.

The feature is available on any grid if the data source allows for editing the records. Let’s start with the first example of editing User information.

The option for bulk editing records can be found after selecting multiple records by clicking on the three dots at the right of the grid. A menu will appear where you can click the option Edit selected rows…

An Edit rows dialog appears where you can select the fields available on the grid. Non-editable fields, like the primary key and in this example, the Telemetry ID, cannot be used for this feature. The same is valid for edit and display methods. On the Users page, the Person field is an edit method where the actual selected value will be saved in a table with the help of X++ coding.

Both the Field and the Value (if applicable) columns are supported with a lookup. In the screenshot above, I selected the Company field to be updated to DEMF for the selected four records. Click Apply to continue.

The changes will be visible in the Grid where the changed records will be presented in italicized font, except for the last selected line. The changes are not effective yet. You can review the changes and then decide to Save the changes. The changes to the grid are like pasting from Excel using the Typing ahead of the system feature. When you accept the changes, it will per row validate the data and then save it to the database. If the validation fails, you will need to correct the data. Where Microsoft deprecated the Fill utility in the past because it was not validating for accurate data, the validation part is now implemented. Kudos to Microsoft, however, there is a caveat that I will explain below in this blog.

Instead of changing a single column, you can add multiple columns using the new Bulk editing in grids feature.

You can add more fields by clicking the + Add button. In case you want to remove a field, you can click the X button behind the field value. When you click the … button, you can manage to move up or down a field. This will execute the changes in a particular field order.

What should you do in case you need to change fields that are not present on the grid? With the help of personalization and Saved Views, you can add columns to the grid. The fields that were added, can then also be used for the mass updates.

As mentioned above, the grid should have an editable data source. When opening for example the All sales orders page, the bulk edit option is not available as you can see in the screenshot below.

What happens in case a grid contains data from multiple data sources? Can you bulk-edit details from multiple tables in one go? The answer is yes, you can. I will take the sales order lines as an example where data is presented from the sales order lines and inventory dimensions, like site and warehouse, are on the same grid but stored in a related table.

Assume we want to update the Discount percent, Site, and Warehouse for all order lines where the category is a Speaker. As shown in the screenshot, the inventory dimensions are also an option to choose from. Before accepting the changes, I would like to repeat a remark I stated above. You can manage the order of the fields in which they will be updated. Depending on the setup, you can get a dialog asking if price information needs to be reset when changing e.g. a Site or Warehouse and prices were changed manually. In this case, the discount is first applied, triggering a dialog to confirm what to do with resetting the prices or not.

Unfortunately, each field change can trigger the dialog. This will also be done per record. For bulk updating these five records, I got ten times the confirmation dialog. To prevent having this dialog, in this case, you can set the Discount percent field as the last field in the order. Then in case there was no other manual price override, it will not show the dialog for all the records and all fields.

After the changes, the records are updated according to the input of the user.

There is more…

Next to the experiences shared above, you can make inventory dimensions visible via the Display menu item on sales order lines. In case you add e.g. the Color dimension, the field is not selectable in case the products in the selected records don’t have this product dimension available.

It is needless to say the feature is also working on custom forms and tables containing grids.

If you want to try out this new feature, you can install version 10.0.38 (or later). Version 10.0.38 (CY24Q1) is the first release for 2024 when Microsoft will reduce the number of updates to four per year. To know when this version will be generally available and will be up for automated updates, visit the Service update availability page.

I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.


That’s all for now. Till next time!