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! 😊
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:
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:
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):
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:
Code available here: https://github.com/JAng13sea/Blogs/tree/master/Month%20End