D365 Finance and Operations. Data modifications in Production and Sandbox environments.

Introduction

If you migrate from one of the previous versions (Ax 2009/Ax 2012) you might have a kind of job pack for data correction for typical cases. From my perspective, it makes sense to adjust, test and move those jobs to D365 Finance and Operations so that you can use them by demand as before. 

In addition, you can create and use a set of classes for data corrections that can be applied by demand while the IT team is solving the root cause of the data inconsistencies.

Anyway, the question that is still here: How can we fix urgent data issues in D365 Finance and Operations in Production or Sandbox environments?

Important: Please keep in mind: Any data adjustment in the Production environment must be tested in a Prod copy (Sandbox UAT) environment first. Do not apply data modification operations in the Production environment if they were not tested previously. In the production environment you have no second attempt if the data modification works wrong.

Overview

In Ax 2012 we had access to AOT and could develop a job or open the table via the table browser and adjust data in close to real-time. In D365 Finance and Operations production or Sandbox environments, there is no AOT anymore and the table browser can be opened in the view mode.

Luckily, there are some ways to correct data in D365 Finance and Operations as well.

•  First of all, it is still possible to develop a kind of jobs. In D365 Finance and Operations it is

    called "X++ scripts with zero downtime". I described this standard feature in one of my previous

    posts. With this feature, you can call the standard system business logic and use all available 

    X++ commands. In my opinion, it is the best option if you can’t deploy (for some reason) 

    a new deployable package with the new jobs into the production environment.

•  Another option can be using the Microsoft Dynamics Excel add-in and Data Entities. If you 

    have data entities for the required tables/forms and there are not so many records in the 

    table/form that can be a convenient option. From my perspective, this option can be applied to 

    master data mostly. In addition, the standard entities have edit restrictions similar to their 

    source tables. If you would like to create new entities for data modifications it may be 

    needed to copy standard entities and change their field properties. If you choose this way you 

    need to keep this approach in mind and develop the required entities or add the required fields 

    to the standard entities in advance. However, this is a cheating way as well, as you may 

    bypass some system restrictions. You need to be sure that you understand all the 

    consequences of the changes.

•  Another available option is using the Open Data Protocol (OData). In this case, you need to 

    use the Postman application and connect the application to D365 Finance and Operations 

    instance. Please keep in mind, the data entities that you are going to use should have 

    the "Public" property - "Yes", and the "Public Collection Name" property should be populated. 

    As I mentioned above, you can use the standard entities or create new ones for the data 

    modification purpose.

    In the Internet you can find a lot of posts how to use Postman with D365 Finance and 

    Operations OData entities. Just to give you an idea there are a couple of examples: 

    link1, link2.

The next options are not the best ones, if you ask me. They can be applied only if you know exactly what you are doing and it is your "last hope". With those options, you can make direct changes in the database and bypass validation or other code that would run normally.

•  The next option can be using the Just-In-Time (JIT) database access. 

    From a data manipulation perspective, it is the same opportunity you had in Ax 2012 when you

    connected to Ax 2012 SQL Database via SQL Server Management Studio. In this way, 

    you can create, edit, and delete records in Production and Sandbox database tables directly. 

    However, this is not really recommended, as you may bypass validation or other code 

    that would normally run if you were using the system form and table methods for changing 

    data in the table. Before introducing any changes in the tables via SQL you have to 

    understand all the consequences of the changes.

•  The final option is to use a custom tool for data correction. As far as I know, there are some 

    available tools and extensions. For example, this one. You can find other ones. You should be 

    aware that Microsoft may decline support cases if they find this kind of tool in your 

    Prod environment. I have seen this notification in one of the posts on the Microsoft Community 

    forum. Unfortunately, I could not find it to share the link.

Conclusion

As you may see there are different options for data correction. Most likely you will be able to solve any data issue with one of the available options. My personal advice would be: to collect job/classes and Postman queries. As a result, you will have a list of tested classes and Postman requests for data corrections that you can use by demand.

I would recommend using the Just-In-Time (JIT) database access really carefully. Before introducing any changes with this option, you have to understand all the consequences of the changes.