Azure Logic Apps is a cloud-based service provided by Microsoft Azure that allows you to automate workflows and integrate systems, applications, and data across cloud and on-premises environments. It provides a visual design interface to create and manage workflows, making it easier for users to design and execute business processes without writing extensive code.
Problem Statement
In our current inventory management system, there is a need to implement an automated solution that periodically identifies and sends email notifications for the top 3 low-stock products in each location. This initiative aims to improve proactive stock management, reduce the risk of stockouts, and enhance overall operational efficiency.
We have [Production].[low_quantity_product_by_location] view which would give the Top 3 low stock products for every location.
We can use two approaches to send emails using Azure Logic
- Create an HTML Table in the Logic App
- Create HTML Table code in SQL itself
In this blog, I am going to explain both the approach
Approach 1: Create an HTML Table in the Logic App
- To send an email we need data by location on each row to iterate over the location name
To send email for each location.
- For each location, this query generates a JSON array (json_data) containing JSON objects representing low-quantity products, with each object containing the product name and its quantity. The result will be a set of rows, each corresponding to a location and its associated JSON array of low-quantity products
- In Azure Logic App we use SQL connector to execute SQL query as an action
- We initialize a variable to store the JSON array. It will be updated later for every location.
- Then For every Location Name we update the value using the JSON function on the json_data column returned from every query
- We then create HTML table using HTML Table data operation action setting columns as automatic
- We use this Create HTML Table output and send email
Here is the mail that will be received by the Inventory Manager
Approach 2: Create HTML Table code in SQL itself
- In this approach we will create HTML code as column value using SQL itself. The advantage of this approach is we can customize table appearance as we are building code from scratch.
- Like the earlier approach we use Execute SQL statement operation and use the following code
- In this we don’t need to initialize and set variables as we don’t need to create an HTML table as it is created
- Finally, we Send Email Action to send emails by every location
Summary
Azure Logic app provides integrations with various services and helps companies automate their workflows using custom business logic.