Upcoming date notifications with Microsoft Power Automate

Overview

This integration (flow) will enable you to create automated email notifications using Microsoft Power Automate (Microsoft Flow), whenever an asset or inventory has an important upcoming date.

Prerequisites

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

• the source of the asset or inventory data e.g Excel Online or Google Sheets. You can simply export your asset or inventory data from your account then import them to the relevant platforms.
• custom field that captures the upcoming date, e.g an expiry date.
• a Microsoft Power Automate account

Preparations

Exporting the asset or inventory data

From your account, go to Manage Assets > Tagged Assets or Manage Inventory > Inventory.

On the table, go to Options > Export Data.

Setting up required custom fields

In your Tagged Assets or Inventory Data Capture settings, set up the field for capturing the date.

Importing the 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 “Due Date 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.

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

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

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 “Asset Tagging” or “Inventory Tagging”.

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.

Simply click on Add an action button.

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

Under the Condition settings, on the left-hand side, search and choose the custom field that was set up earlier, e.g the Warranty Expiry Date.

Under the same Condition settings, on the right-hand side, switch from Dynamic content to Expression.

In the Expression’s text box, enter :

formatDateTime(addDays(utcNow(), 3),'yyyy-MM-dd')

The number “3” in the expression above denotes the number of days that should be used to check whether an item is due or overdue. This number can be adjusted to suit your needs.

The expression “yyyy-MM-dd’, denotes the date format your Warrant Expiry Date is in. By default, when recording dates, the platform uses ISO 8601 format, e.g 2020-01-30. If your due dates are in other formats, adjust that expression accordingly.

In the end, the Condition settings should look like this:

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 to the recipient.

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