Microsoft Flow data correction

When using the cloud version of Microsoft Dynamics 365 for Finance and Operations, there are certain tasks which are a bit more cumbersome if you compare it with the freedom you have when running the application on-premise. When there was a need for data correction, we were used to quickly develop a job or class with x++ logic to repair data. In certain cases, we could deliver a list with identification numbers which was then used as looping to correct or delete only certain records. In this post I will explain how you can be more agile creating correction scripts using Microsoft Flow.
If you are not (yet) familiar with Microsoft Flow, you can still read this post to be able to apply similar actions on your future requirements.

The challenge

Assume, there is a need to delete a series of sales orders. E.g. a certain integration was run twice at the same time. Don’t ask me how it would be possible, but believe me, I have seen many insane issues. Due to the fact that they did run the same time, the duplicates are in between the correct sales orders. E.g.:

SO00012 correct
SO00013 correct
SO00014 duplicate
SO00015 correct
SO00016 duplicate

Recently this happened in a Dynamics 365 environment and the support consultant made a list of all these sales orders and analyzed which order numbers should be considered as the duplicate. So, how to delete in this case over 750 sales orders as quick as possible?

The past

Previously, we were used to write a script that opens a source file with these sales orders and then delete them within a loop. It could be developed quite quickly and moving the job or class using an xpo or model made it also very agile to deploy it in a test environment. After completing the tests it could be moved to production and run it. Even within organizations with very strict procedures, this process could be completed within a day.

Now if we are using the cloud based version of Dynamics 365 for Finance and Operations, we can still develop such job as runnable class. However the deployment will be the bottleneck for fast tackling the issue. Deployments can only be done in production when the deployable package is installed in a staging environment, tested and marked as release candidate. Then you have to log a request in advance and ensure it is installed in a maintenance window. So, what would be an alternative to be more agile when you are using the Microsoft managed cloud version?

Microsoft Flow

As of a few months, we are using Microsoft Flow at a customer to automate some user processes. E.g. I did create a Flow to import order lines where the external item number was specified. Flow did a loop and using e.g. the customer/vendor external item table, you can find the correct item number for the order. Another Flow is used to create products with a lot of details for related tables.

For this requirement, you can also use Microsoft Flow. The only thing you need as preparation is checking if there is a data entity which is public and supports correct delete actions. Then you can start building the flow. As this post is more intended to share a concept, I will not explain too much about the details how to create Flows. If you want to learn more, you can explore many examples and search on internet for some examples. I’m also considering creating more blogs about using Microsoft Flow with Microsoft Dynamics 365 for Finance and Operations.

The Flow for this example looks like this:

Each Flow will start with a trigger. The list with order numbers was converted as table in Excel. The file can be picked up using the Microsoft Excel connector. Then for each row, there is a Delete record action which is using one of the instances of your Microsoft Dynamics 365 for Finance and Operations environments. In this example, I have used the Sales order headers V2 entity to delete the records. The Object id field should be filled with the company identification and the primary key of the data entity.

Creating this Flow was done very quickly. First you have to test it in a test environment to see what happens and if all is OK. When you run it, it can result in a perfect executed Flow. Deleting the order, will also delete the order lines and related inventory transactions like deleting an order manually. But be aware that also some orders might give errors. This can occur if there are order lines with e.g. a picking status. In that case, you will notice some errors and you can review them:

You can browse the results per row or directly jump to the next failed record and see what error message was returned why the order cannot be deleted.

This scenario, can save you a lot of time compared to deleting the orders manually.

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!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.