Welcome to the concluding entry of our series, where we’ve explored the integration of Python, Azure Function Apps, and Microsoft Business Central to reveal insightful sales trends. In this final part, we focus on constructing a Power Automate workflow, designed to send automated alerts to decision-makers when sales trends deviate beyond set thresholds, providing them with the crucial data and charts derived from our Python Function App.
Previously, we navigated through the creation of a Python Function App and developed a custom connector to bridge our Python script with Power Automate. The resulting chart, abundant with sales data and a pivotal trend line, provides a detailed visual representation of an item's performance in the Business Central environment. Notably, the slope information returned by the function app becomes an essential decision-making tool, ensuring that alerts are data-driven and actionable.
In this final installment, our goal is to create a Power Automate workflow, starting with a manual trigger for initial testing, which can later be adapted into an automated workflow tailored to your specific operational needs. From setting up tolerance levels and iterating through item numbers, to calculating monthly sales and utilizing our custom connector, each step is a crucial component in developing an automated, insightful alert system.
As we work through defining variables, iterating through item numbers, calculating monthly sales, and especially triggering alerts based on slope variations, we’re creating more than an automated process. We’re developing a system that ensures your decision-makers are consistently informed with real-time, actionable data, particularly when sales trends shift beyond predefined limits.
Join us in this final segment as we weave together Python, Azure Function Apps, and Power Automate into a unified, automated, and insightful alert system. Here, data is transformed from mere numbers into a compelling narrative, ensuring every shift in your sales trend is monitored, analyzed, and communicated, enabling your business to navigate adeptly through the dynamic market conditions.
Let's delve into creating a workflow that not only automates alerts but also ensures your decision-making is consistently informed and data-driven.
The Business Need and High Level Solution
Business leaders require instantaneous alerts when a Business Central item's sales trajectory either descends below a defined minimum threshold or ascends above a specified maximum. Upon meeting these conditions, an email notification, inclusive of a chart illustrating the item's historical sales and associated trend, should be promptly dispatched to the decision-maker to facilitate immediate, informed action.
The Power Automate Workflow
1. Create a Manually Trigger a Flow workflow to test. Later on you have the option to convert the workflow to one that will trigger on an event or trigger based upon a pre-defined schedule.
2. Setup tolerance level and array variables which will be later in the workflow.
3. Setup working variables that will be used later when we iterate through item numbers.
4. Find all the Business Central item numbers that you want to analyze. You have the option to filter based upon category, sales volume, or some other criteria. Note that for the purposed of the demo I set the maximum number of item numbers returned to 1.
5. We then add an Apply to each with the value pointing to the Find output from the last step. From this point forward all steps until the end of the workflow will reside within this loop. Zero the monthNumber, xArray, and yArray at each pass of the loop.
6. We will then find the first invoice based upon the selected item number. Then insert an Apply to each where we build the first day of the month.
The Initialize first Day of the Month code:
convertTimeZone(outputs('Get_First_Posting_Date'), 'UTC', 'Eastern Standard Time', 'yyyy-MM-01')7. We will then find the last invoice based upon the selected number. We save the posting date in an Apply to each loop.
8. We then insert a Do until loop that iterates through all the months between our earliest month and last month. We build out the last day of the month and then find all item ledger transactions within our date range.
The Set Last Day of the Month code:
subtractFromTime(startOfMonth(addToTime(variables('firstOfMonth'),1,'month')),1,'day')The Filter Query code:
formatdatetime(variables('firstOfMonth'),'yyyy-MM-dd')
formatdatetime(outputs('Set_Last_Day_of_the_Month'),'yyyy-MM-dd')9. Continuing in the Loop Through All Months we sum the sales amount for the records we captured. We use a Select to capture the Sales Amount (Actual) column. Make sure to use the map button on the Map of the Select. Then we build the output in JSON. We then sum the sales from the JSON. Finally we add the month to the xArray and the sales total to the yArray.
The JSON code:
{
"root": {
"Numbers": @{body('Select')}
}
}The Sum Sales code:
formatnumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'),'0')10. In the last steps within the Loop Through All Months we calculate the next month and build the first day of the next month. We then increment to the next month.
The Calculate the Next Month code:
addToTime(variables('firstOfMonth'),1,'month')11. Outside of the Loop Through All Months loop we insert two Join steps. They insert commas between the elements of the arrays.
12. Finally, we call GetGraphics our custom connector that we built in the prior blog. We feed the x parameter the output from the Join xArray and the y parameter the output from the Join yArray.
The Get Slope code:
float(outputs('GetGraphics')?['body/slope'])13. In our final section we perform a downward sales trend check on the slope value that was returned from our GetGraphics call. If the slope is greater than the threshold that we set at the beginning of the workflow then we send the decision maker an email with the item trend chart information. If this test fails then we perform an upward sales trend check much the same type as the downward trend check. In our case the decision maker wants to know if the upward sales trend is beyond expectations.
The Create Image 1 code:
concat('<img src="data:image/jpeg;base64,',outputs('GetGraphics')?['body/image'],'" alt="My Image" />')Run the workflow when you are done and you should see an email that looks something like this.
Conclusion
In this conclusive entry of our series, we have woven together the threads of Power Automate, Azure Function Apps, and Python to illuminate the path towards automated, data-driven decision-making in the context of Business Central's item sales trends. From the meticulous creation of a Python script, its metamorphosis into an Azure Function App, to the formulation of a custom connector and the assembly of a Power Automate workflow, our journey has been both enlightening and complex, navigating through various facets of sales trend analysis.
We have successfully unveiled a method wherein decision-makers can be immediately alerted to pivotal sales trend shifts, underpinning the vitality of timely and informed decision-making in a dynamic business landscape. The merger of Python’s analytical prowess with the robust, automated workflows of Power Automate has opened up a gateway to a realm where data is not just observed but actively participates in shaping business strategies.
In retrospect, our series has not only aimed to be a guide but a catalyst, inspiring you to explore, innovate, and perhaps, create your own hybrid solutions that merge analytical and automation capabilities to steer your business confidently through the ever-fluctuating market waves. Whether it's customizing workflows to your specific needs or tweaking the analytical depth of your Python scripts, the foundation laid in this series is versatile and ripe for expansion.
As we cap off our series, we extend our gratitude for joining us on this intricate journey and hope that the knowledge disseminated finds its place in your future analytical and automation endeavors. May your data always steer you towards prosperous decisions, and may your workflows streamline the path towards them.
Embark on your own journeys, explore the myriad of possibilities, and remember: the confluence of data and automation is a canvas, awaiting your innovative stroke. Here's to insightful decisions, strategic growth, and a future where every trend, dip, and peak is an opportunity waiting to be seized.
Until our paths cross again, happy data analyzing and automating!
Comments