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.

Business Central Month End with Power Automate

A month end procedure is a regular topic to be covered when implementing finance with D365 BC. Some finance systems throw a user into a defined routine but with D365 BC it’s much more simplistic. To remove the need for users to be experts wouldn’t a defined routine be useful?

Here is the goal: Create a Power Automate flow that will handle the month end procedure in BC with minimal user input. Principal thing here is that month end is routine so why not have something to cater for it. Caveat here being that job knowledge makes up for a big portion of this finance process so not all of it can be catered for. The scope is laid out in the next paragraph.

So what makes up a month end in BC and what is the scope of this procedure?:

• User setup – change the allow from and to posting dates
• Adjust General Ledger Setup posting dates
• Post recurring journals
• Post outstanding invoices – some might fail so will be left behind as a result. The solution has been discussed previously: https://joshanglesea.wordpress.com/2020/07/31/batch-post-and-e-mail-with-power-automate-no-code/

Our first pointer is around the User Setup and this table could be home to a number of users that are in and out of scope for the procedure. So without any code the solution here is to define a “User Group” – perfect standard system placeholder. The added benefit is this table has “Company Name” included so if you want a differing solution per company you got it! 😊

Add the users that you want to be part of the allow to/from posting date change only. People in the finance team for example may need to have longer to post so you could define another group for them and create another step in the process to roll on their dates. The page for this is 9831 and you need to know this as a web service is needed – once published move on to the next section

Once page 9831 is published as a web service some ODATA filtering is needed to get what we need from the exposed data. I called my web service “MonthEndUsers” so I just add the orange part onto the end of the published web service URL: MonthEndUsers?$filter=User_Group_Code%20eq%20%27MONTH-END%27

Whilst on the web services page publish page 119 – User Setup – as this is needed too.

Head over to Power Automate and create a scheduled flow:

Flows run using UTC so if you need a specific time zone to be used check the advanced options once you have created the flow in the “Recurrence” step.

First thing needed is the dates for the posting from and to which will be dynamic each time. Add a “Compose” action for both of these and use the following formulas to get what we need:

• Start of the month = startOfMonth(utcNow(),’yyyy-MM-dd’)
• End of the month = formatDateTime(subtractFromTime(startOfMonth(addToTime(utcNow(),1,’month’)),1,’day’),’yyyy-MM-dd’)

Add a HTTP action to the flow and paste in the ODATA URL with the filter and use the GET method. Add a Parse JSON to grab the body from the HTTP trigger. Paste in a schema which you can get from going to the URL in a browser and authenticating. Add an “Apply to each” control and use the “value” of the Parse JSON. Last step is to add two PATCH requests to the User Setup page. Why two you ask? Well the page as standard won’t allow you to have a “From” date ahead of the “To” date and the body of a HTTP request is not dealt with in the order we send it so to prevent errors two requests are needed. First one to the “To” date and then the second to the “From” date.

The PATCH command needs specific syntax for filtering on the user record. At the end of the published web service URL the orange text represents one user value. The flow will use the “UserName” from the parse JSON step: UserSetup(‘UserName’). In the Header of you HTTP add the below to prevent the likelihood of errors:

General Ledger setup cannot be altered with ODATA so a different solution would be needed for that page most likely with modification. I will not cover that.

Remaining activity is the recurring journals and the General Ledger Setup. At the time of writing the standard BC connector for Power Automate has a post-journal action but it does not work with recurring journals. If your scenario is without recurring journals the standard guide on journal posting will work fine. I will cover what to do about recurring journals and I will use the batch name instead of the ID for ease of use. Publishing the General Ledger Setup page as a web service does not enable you to modify the data, it errors. As a result these two areas need development to give the final result needed.

To do this I’ve opted for a single codeunit which I will publish as a web service and use as an unbound action to complete the work by passing specific parameters:

Not a lot of code is needed here and it is more about the HTTP syntax and passing a body in the request

Unbound actions with codeunits published as web services is fairly new and highly useful. Simply publish your new codeunit as a web service and then use this logic within the URL (note the orange part changes depending on the function you need to use):

https://api.businesscentral.dynamics.com/v2.0/{tenantID}/Sandbox/ODataV4/PostReccurJrnl_postJrnl?company={companyID}

or

https://api.businesscentral.dynamics.com/v2.0/{tenantID}/Sandbox/ODataV4/PostReccurJrnl_AdjustGLSetup?company={companyID}

Within Power Automate we now have an endpoint to use and we can simply pass in the necessary parameters. Note that in this instance both will be POST actions. The body is just the name of the parameters and the required values:

I’ve tagged mine onto the end of the User date change as I already have the dates figured out and I want it to run for that time anyway 😁

Code available here: https://github.com/JAng13sea/Blogs/tree/master/Month%20End