Business Central Planned Service Order Calendar ๐Ÿ—“๏ธ

If you have worked with the Service Management area of NAV/BC then you will have greater insight into the upcoming words and images I’ve thrown together. Within BC service you can log a service contract and assign items to be serviced on a routine basis

Image here shows some key fields which drive that functionality – “Service Period” is a date formula field showing how often we expect a planned service (here it is 1M to indicate once a month). “Next Planned Service Date” and “Contract Expiration Date” then act as the Start and End to the planned services. It is possible to not set an expiration date. For the “Calendar” idea I am about to layout you need that data in place – or you adapt the code/BC data to use defined planning horizon (end date).

All seems fine here I’m sure, but the mild inconvenience behind this post is that only 1 “planned” service order can exist at a time. BC uses the below processing only report – “Create Contract Service Orders” – to create the “planned” service orders. There is another two fields used within this routine called “Last Planned Service Date” and “Last Service Date”. These act as the control to ensure you have just the “required” service order generated by the routine.

*note that this routine does check the service management setup to see how far in the future in can create service orders for.

I’m not bringing this up because I have an issue with standard. Having recently discussed this with prospective users of BC, they want to use a calendar view to aid with resource planning. BC can handle resource allocations when the service order exists only. So what do you do in this case? One possible option is to look at a reporting based solution and just let BC get on with how it automatically creates the required service orders as and when – will certainly stop getting loads of clutter in the tables which hold the data.

The part of BC that has a vague similarity to what I want is Deferral Schedules. Confused? ๐Ÿ˜ต Let me show this:

Before a deferral is posted you can preview what it will produce. In the case of the example it is for 6 months and it was produced data for that – but only in this view. This is in effect what I think would be useful with service contracts so you can plan from that. I’m opting to produce something in Power BI on this occasion so I can display data in a matrix visual. This means a page (api or regular as a web service) is required. For this I’ve taken some inspiration from how the standard BC API handles Sales Order Lines. The table the API is based on is a temporary table and it isn’t even the Sales Order one:
For me the page isn’t that straight forward in how it eventually gets populated but it does it with the assistance of a codeunit in the OnFindRecord trigger of the page. For my page I have adopted a similar approach – but kept it simple. A link to the code is at the end of the post.
This is the kind of view I was able to generate because I have my API page producing data for future dates based on the “Service Period” defined per service contract line. Note that BC allows for service items to appear on multiple service contracts. So where I have a count of 2 (yellow dots) the service item is scheduled for two different services that month. It’s something standard BC just can’t generate right now and it gives so much more information to a user. I’m sure there are some other use cases for this concept – interested to see what others come up with. You could produce a matrix page in BC with the concept too ๐Ÿ‘

Link to my code: https://github.com/JAng13sea/Blogs/tree/master/Service%20Calendar

Business Central Multi Company Approvals โœ…โŒ๐Ÿ”€

I have some things to state upfront as there are things to justify for why the solution is shaped in a certain way. The premise of the post is to solve the fact a user who works across many companies might have many approvals. The standard UX is a big driver for why I’ve chosen the topic. If using out the box BC approvals you can get emails. However, they donโ€™t have any action buttons.

So youโ€™re forced to login. Youโ€™d have to do that for all companies you have approvals for. If youโ€™re using Power Automate approvals you do have action buttons in emails. You do have to read every email though to access them.

Is there a better way?! ๐Ÿค”

I figured the “newish” company hub app would be a good starting point. Have you seen it? A user can view all user tasks they have in each company they have access. Sounds close to what I am thinking ๐Ÿง. However, I found the table for this itโ€™s not extendable. The method for how it gathers user tasks seemed fairly involved. I liked the idea of shoehorning it in there but felt that an easier route existed. Access to the detail from the role centre is certainly the way to go though. But access elsewhere is fine too.

Company Hub view users can access

My answer is to use a PowerApp embedded in a Power BI report. Handles plenty of the circumstances required. Here is a version of the app running through some basic functions it could offer to a user:

I have added few other considerations at the end of the post. I will cover off the main steps I took to create the above experience – **which covers approvals controlled by BC Workflows only**. A full explanation of how the entire thing was built would be best by video. In absence of that I will adopt a high level approach for readability, calling out key components.

Components required and where I got some of my details. The order of them is important:

Code for the new api page can be copied from here: https://github.com/JAng13sea/Blogs/blob/master/Multi%20Company%20Approvals/Pag57250.MultiCompApprovalActions.al

Why do we need the custom actions? Doesn’t the BC connector for Power Automate already have those? Yes, they do. However, they are for approvals done via Power Automate only. I am yet to figure out a company based difference in the Dataverse table to bring this type of experience solely for approvals done via Power Automate. **Watch this space**

Using the new API page and the companies endpoint as queries in your PBI report file. Follow Steven Renders blog to ensure the approvalActions is for all companies.
Create a relationship between the two tables with the “Company Name” value being the link
So that a user only sees their approvals add in security so the approverEmail must match up with the user that is logged in and using the report.

Now the Power Apps visual can be added to the report. Use this guide if it is a new experience: https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/powerapps-custom-visual

A copy of my PowerApp and the power automate flow used can be found here: https://github.com/JAng13sea/Blogs/blob/master/Multi%20Company%20Approvals/MultiCompanyApprovalsApp_20220525155716.zip

One of the big takeaways from the app design is that the user works with a collection instead of the direct data from the PBI report. When the app starts the initial collection is made and that is displayed to the user.
Other components that are crucial are the action buttons to approving, rejecting and substituting. Each button is the same and only the “Type” value is altered on each. Idea being that a new collection for only the entries we need to action are taken and sent to Power Automate. The original collection which is what the user sees is then updated. This is because the PBI report doesn’t get refreshed constantly – it will be on a defined schedule:

Now for more detail on the Power Automate flow. In a standard scenario the connector for BC is not dynamic to a company. In this scenario it has to be. It is a general possibility to pass custom text to connectors in Power Automate and they can be based on variables you setup. The same as setting a header on a HTTP call.

Conclusion: This method certainly falls into the low code bracket but I also think it has caveats which might not work if you have to scale it out to many users. Users do often set aside a time in their working day or week to do approvals so having a one stop shop approach is a bonus. The refreshing of the data is a drawback because of it being held together and formed by the PBI report. On the other hand it it such a small and discreet thing a user has to do so living with some level of drawbacks might suffice because the standard experience is far worse IMO. The time and effort to do approach this in AL is probably worth it in the long run and it’s an obvious thing to have in the “Company Hub” app. The nicest takeaway is the ability to make the BC connector in Power Automate be company dynamic. Have to see what other scenarios crop up where that could be used ๐Ÿ˜

I think this should be in the Company Hub app and I have logged it as an idea which will need votes: https://experience.dynamics.com/ideas/idea/?ideaid=658e8d1c-e0dc-ec11-bea1-0003ff45e08b

Business Central Data Dictionary ๐Ÿ“–

I’ve been asked about this topic a few times recently, by colleagues and customers – where do I get a Business Central data dictionary or database schema from? This is a very brief post on how I tackled this. My default answer has typically been Jet Reports because the sample reports come with one. You don’t need Jet Reports to have access to it. Just go to the website and download it – https://insightsoftware.com/sample-reports/data-dictionary/. It is saved in such a way that it is just a plain Excel file, and looks much like this:

This could fulfill your needs perfectly well, however you can’t refresh it, unless you have Jet Reports. What if you have custom tables or tables from appsource extensions? Is there another free method to get hold of the database tables and fields? For this we will use a Power BI report. Big caveat is that we will need to customise the system ever so slightly. There are two ways you can go here. Either create API pages (best practice) or create standard list page objects. I did both and oddly had much faster results from the none API pages – weird ๐Ÿ˜–. To the extent I built my below sample from the standard list pages:

Something along these lines could be the final result. The PBIX I have created is also available to try out if you don’t want to build the pages or use the Jet Report suggestion.
I have taken complete inspiration from the Jet Report example and created pages in the report based on theirs.
This is just a small subset of what could be generated from the these two system tables.

Code for the two pages (in both styles) and the PBIX I took the screen grabs from are available on GitHub here: https://github.com/JAng13sea/Blogs/tree/master/Data%20Dictionary

How is the Power BI option free? The entry level account is free: https://docs.microsoft.com/en-gb/dynamics365/business-central/admin-powerbi-setup and that is all I had to build the report.