Inventory Threshold Notification with Microsoft Power Automate

You are here:
Table of Contents

Overview

This integration (flow) will enable you to create automated email notifications using Microsoft Power Automate (Microsoft Flow), whenever your inventory stock level falls below a pre-defined threshold.

Prerequisites

In order to set this flow up, you will need:

  • the source of inventory data e.g Excel Online or Google Sheets. You can simply export your inventory data from your account then import them to the relevant platforms.
  • inventory custom fields that capture the stock and the threshold levels
  • a Microsoft Power Automate account

Preparations

Exporting the inventory data

From your account, go to Manage Inventory > Inventory.

On the table, go to Options > Export Inventory Data.

Setting up required custom fields

In your Inventory Data Capture settings, set up two fields; one for recording the stock level e.g Quantity, and the other for recording the threshold level, e.g Reorder Point.

When adding inventory data to your account, those two fields should be populated accordingly, for each inventory record.

Importing the inventory data

On Excel Online

If you are using Excel Online, upload the exported CSV file to your One Drive, then open it.

Once opened, insert a table by selecting all the records then go to Insert > Table.

In the prompt that appears, ensure “My tables has headers” checkbox is selected.

Under the Table Tools menu, click on Design, then change the Table Name to something more recognizable, as it will be need on further steps.

On Google Sheets

If you are using Google Sheets, upload the exported CSV file to your Google Drive.

Setting up the automation

In your Power Automate account, go to My Flows.

On the new page, click on New, then choose Scheduled – from blank.

In the prompt that will appear, enter a title for the flow, e.g “Inventory Threshold alert”.

Then, specify how often you would like to receive notifications,e.g once per day.

On the new page, click on the New Step button, then depending on your choice, search, and select Excel or Google Sheets connector.

Excel Example

Under Excel Actions, choose “List rows present in a table”.

Excel example

If using Google Sheets, you would choose “Get rows”, then follow the prompts to choose the location of the file, and sheet.

Google Sheets example

With Excel, you would then be presented with a prompt to choose the location of the file, and the name of the table.


Once that is done, click on the New Step button, then search for “Ventipix Asset & Inventory” connector.

Under its Actions menu, choose “Retrieve an asset or inventory record”.

In the new prompt, under “Select a service“, choose “Inventory tagging /Import Inventory”.

In the Barcode/NFC Tag value field, choose Dynamic content then select “Barcode/NFC Tag Id” field from the spreadsheet’s title names.

After selecting the “Barcode/NFC Tag ID” dynamic content, you will be presented with an “Apply to each” prompt.

Expand the “Retrieve an asset or inventory record” prompt by clicking on the same title.

If your inventory data have location values, repeat the same step of choosing dynamic content for the “Location” field.

Once that is done, click on Add an action button.

In the new prompt, search for “Control” connector, then choose “Condition”.

Under the Condition settings, you will want to search and choose the custom fields that were set up earlier, i.e Quantity, and Reorder point.

In the end, you should have it set up like so:

When Quantity is less than reorder point, then further actions should take place.

When Power Automate sees the condition is met, you want to set it up to follow the “Yes” branch.

Click on the Add an action button then choose a Mail connector.

Under your preferred mail connector’s Actions menu, choose the option that sends emails. For example:

After that compose the message using dynamic content fields. For example:

Once that’s done, save the flow set up, ensure there are no warnings under the flow checker, then test.

The expected results, as long as the conditions are met, would be an email should be sent.

From then on, as you reduce inventory in your account, the actions you have under the “yes” branch will be processed accordingly.

Note – under the “yes” branch, you can still add more actions such as, have a new spreadsheet created with all the low inventory items or have the low inventory items deleted from the source spreadsheet, etc.