Power Automate

Business Central Company Master Data Synchronisation with Power Automate

There are apps that handle this and I’ve seen a colleagues version of it recently which is great. However, why code when you don’t have to? Limiting factor with no code will be that there is no restriction on other companies i.e. other companies can create data. Some solutions look to reduce this possibility. This is a basic version that doesn’t cover that 🤪

So here’s the goal – have one BC company create master data and pass it onto other companies. Have one companies master data modifications passed to other companies. Have one companies master data deletions result in deletions in other companies.

Note that each type of data action will be in an individual Power Automate Flow – i.e. one for Insert, one for modify and one for delete.

Code when you don’t have to isn’t quite what I’ve gone for but it is certainly low code. My reasons are totally justified as you’ll see. This solution could be used on G/L Accounts, Customers, Vendors, Items, Dimensions, Fixed Assets, Posting Groups etc. I have chosen to demo it with G/L Accounts as you need a little extra there. If you check the standard API for accounts (g/l accounts) it only has a GET command. Which is fine for one part of the process but no good after that. I’ve knocked up a page with the idea of using it as a web service, could be done with an API page too. Either way that gives us an endpoint to work with in Power Automate. Whether you are dealing with insert, modify or delete the start of the flow will use the regular BC connector. Only after that will you look at the HTTP connector which enables you to work with endpoint URL, like that of our web service page. Once you have that page you are able to POST, PATCH or DELETE. The start of the flow can use an event based trigger from the BC connector for insert, modified or deleted. Insert and modify follow the same concept so I’ll cover just one. For DELETE something else is needed. Code can be found here: https://github.com/JAng13sea/Blogs/tree/master/Master%20Data%20Sync

Each flow will start with the BC connector and use one of the 3 triggers we are interested in. Note that Power Automate refers to the actions as New (Insert), Changed (Modified) and Deleted (that’s the same 😁)

In my example with G/L Accounts not much information can be taken from the standard BC connector with a GET command. That is why I have the supplementary page in addition to it allowing me to perform more CRUD actions.

The second part of the flow is to GET the details of the record which has been created or modified – the delete step does something slightly different, read on to find out. The HTTP connector is being used here to pull the required data. An ODATA filter has been added so that the correct record is shown. The standard BC connector will give you in “Row id” value that is dynamically displayed.

Once the HTTP connector is dealt with for pulling the right information the in built Parse JSON action can be used. This will make each part of the response data from the GET command be accessible dynamically so we can produce a new JSON for patching or posting data into one of the other companies 👍 The parse JSON allows you to paste a schema. If you have tested the URI from the GET command you will have something to copy and paste

Once a schema has been generate from the sample – note the button you can press – it will place a valid JSON schema in the main area of this action, like above.
Depending on how many companies this will be for you need to create the above for each. Thankfully a copy and paste feature does exist and you can just change the company value. I have used an ODATA page here so company name is accepted. For an API page use the company ID value

An “Apply to each” starts each block that handles the companies being updated – this is due to the JSON we parse – it is just a loop but it will only run once per record. Place the “value” into the “Apply to each” so it can iterate through the record. Add a new HTTP action and choose the relevant command (PATCH for modify, POST for insert, DELETE for delete). The URI here selects the “Number” of the record to be worked with and that has come directly from the JSON that was parsed. Once your headers are entered you just need to build up a body. If you access the URI from a browser or with a HTTP test tool grab a sample body. Remove the static elements and place in the dynamic JSON. As mentioned the copy and paste feature speeds this process up for the other companies where a change on the company name or ID is the only added step.

Create parallel branches for the other companies you want to update. Passing the data at the same time is fine as in the back end they are all separate tables

What has to happen for the delete option though? If we delete the data from the “master” company the reference information is lost and of course per company the ID value will be different as that is a unique GUID. To aid this I have opted for an event subscriber in a codeunit for the OnDelete trigger for each table I intend to use the feature for. There is a supporting table which then holds the deleted information so I have enough to then use a primary key value in one of the other companies to do a delete.

A supporting page is then used for the initial GET command so the details can be retrieved about the deleted record. After that the structure of the flow remains similar.