web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Josh Anglesea D365BC Blog / Business Central automated ...

Business Central automated pricing group update

Views (4)
JAngle Profile Picture JAngle 133

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


This was originally posted here.

Comments

*This post is locked for comments