Business Central Customer Approval with Power Automate 🏢✅

Recent Microsoft Dynamics Community forum post has prompted this blog:

In this post I will give an answer but also review briefly why I chose Power Automate over regular BC workflow.

BC workflow has a template for credit limit changes. That was my initial suggestion but it’s the requirement around blocking the customer which stopped me. Yes, BC workflow has record restrictions but they only stop postings. The blocked field on the customer stops document creation like orders or invoices. BC workflow doesn’t allow for other fields to be updated. Only allows for the changed one to be reverted back and altered on approval. The Power automate route however will allow you to make other alterations, or other actions, with greater ease. I’ve used a different field but here is how I’ve tackled this.

In Power Automate use the template for customer approval. This will add 80%of what is needed for the solution. Fill in any of the areas that are blank, like which database and company etc. Upon saving the flow it will then pass a workflow record to BC. You will find it in the regular BC workflows list:

The “When Event” will of course be about an approval being triggered:

That will be one of the things to alter along with a specific data condition. All to be done here in BC. The rest is for Power Automate to handle 🤘🏼
Change the when event to “A customer record is changed”. Then drilldown on the “On Condition” to setup what you need for your circumstance.
In my case I only want to handle records which meet my filter conditions and if the “Gen. Bus. Posting Group” is changed. I wouldn’t go without filters or you risk firing off approvals at daft times like when manually creating a record. Step by step field population counts as a modification

That’s the BC setup done ☑️ just the Power Automate parts now 😊

Two additions to the original template are required. They are nearly the same too. Choose the “update record” action from the Power Automate BC connector. Use the Id from the “Get Record” action above it. Choose the customer table and add the word All to the blocked field
Within the “Yes” condition, for when the request is approved, add the same action. This time the Blocked field needs to have a single space. This will revert it back to blank – which is an unblocked state

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 automated pricing group update 💷

The aim of this blog is to discuss a concept built around a specific requirement customers/prospects have been mentioning. Annually some businesses will review the level of sales with a customer to determine what band/bracket/tier/group they fall into for things like pricing, discounts or payment terms. I will use this post to tackle the idea of pricing groups i.e. spend X in a year and the customer falls into price group A, B or C. This means the customer could go up or down on those pricing groups based on their annual spend.

The chosen tool for performing this is ultimately Power Automate but some components are needed in BC. I have taken some inspiration for the final solution from @MaryThompson_WM with this video: https://youtu.be/THZeSnH-agc and @eHougaard with the Simple Object Designer: https://www.hougaard.com/product/simple-object-designer/ With both in play I was able to meet the usual objective of keeping this low code 😁

My target field in this scenario is the “Customer Price Group”. It is a field which is not available in the standard customer api page – hence why I needed the Simple Object Designer to expose it for updating. The app allows you to create custom API pages for nearly all tables in the database. It is a guided wizard type experience, making it easy for many types of users to pick up and get out what they need.
On the customer record there is a field called “Sales (LCY)” which shows the sales for a defined period of time. Here I have a “Date Filter” applied to manipulate the value. This is something you can expose using the standard api page for customerSales. Note it only returns values if the filter criteria are met – i.e. it doesn’t work like the customer api page where it returns many results, it’s more targeted. It is this value which will tell me if the customer is eligible to move into a different price group or not. Example of the endpoint with the filter and the output:
{{BCurl}}/api/v2.0/companies({{companyId}})/customerSales?$filter=dateFilter_FilterOnly ge 2017-12-31T00:00:00.0000000Z and dateFilter_FilterOnly le 2021-12-16T00:00:00.0000000Z

{
  "customerId": "8a11a07d-09cd-4080-b973-77d99d5862b9",
  "customerNumber": "10000",
  "name": "Adatum Corporation",
  "totalSalesAmount": 50922.9,
  "dateFilter_FilterOnly": null
}
To remove the need to do any modifications in BC I have used an Excel sheet with an Excel table to store the data I want to be assessed. The codes here match the values in BC and are manually maintained.

The Power Automate flow now has all of the required data components to work. Merely a case of constructing it. Check the image gallery for each step. Notes have been assigned on the flow action if pertinent.

This is the if statement in the "Set AmtBoolean" flow action:

if(and(lessOrEquals(outputs('Excel_Min_Amount_to_Float'), outputs('Total_Customer_Sales_for_Year')), greaterOrEquals(outputs('Excel_Max_Amount_to_Float'), outputs('Total_Customer_Sales_for_Year'))), true, false)

I’ve exported the flow and added it here if you don’t want to follow the steps I’ve laid out: https://github.com/JAng13sea/Blogs/blob/master/BusinessCentral-UpdateCustomerPricingGroup_20220422201332.zip