Creating A Flow To Alert When Minimum Stock Product Was Reached

Today, we are going to create a flow to notify us when a minimum stock product was reached using Microsoft Flow and SharePoint to manage the Products and the Orders.

First of all, let's create two lists in SharePoint.

Products
 (Custom list), with the fields,
  • Title (default)
  • Stock (Number field)
  • MinStock (Number field) - Will storage the minimum of stock that we need to have

Orders (Custom list), with the fields,
  • Title (default)
  • Product (Lookup at Products list)
  • Quantity (Number field) - Will storage the quantity of the product in this order

Now, we are going to create our Flow at https://flow.microsoft.com,

Create a flow starting without a sample and select the trigger "When an item was created" from SharePoint Connector. Now, we have to enter the SharePoint site URL and the list name. We need to trigger this flow when an order is created. The functional plan is that when a person orders a product in the system, this flow will run to remove the quantity ordered from product stock and check if it is less than the minimum, so we need to send a notification.

 

Now, we are going to add an action to Get Items from Products list, to check the number of products in stock and check if in this new order, the value will be less than the minimum.

 

To get just the product that we want, I put a filter syntax at the field "Filter" of the Get items action, using the following syntax.
 
ID eq [The Product field of my new created Item]
 
So, will have to get the information by the Created Item Product Id field

Now, we are going to create a variable to store the calculation of the product ordered and the stock amount. At this time, we will create a float variable.

 

Now, let's create a foreach loop into Get Items action.

 

Inside the foreach, we are going to use a "Compose" action to subtract the value ordered from the total stock amount of product. We are going to use an expression to make this subtraction.



The final formula will be -

sub(int(items('Aply_for_each')?['Stock']), int(triggerBody()?['Quantity']))

The sub is to subtract two numbers, and the int is to convert the values to an integer type. This formula will get the stock value and subtract the quantity ordered.

Now, we are going to use the Compose result to set our FinalValue variable.

 

Now, we are going to check (adding a condition statement) if the result value is less than or equal to MinStock value of our product in our Products list. If the condition is YES, we have to send the notification. Because the ordered product reaches the minimum stock value, create an e-mail notification action.



Done! Now, we have a simple stock management in which we can implement a lot of other possibilities. To test this flow, create an order with a product quantity that will be greater than the minimum stock, and you will receive this notification.

 

Hope you enjoyed this post. I hope to create a lot of other things starting with this. See you later!