Forum Discussion
Daily e-mail with filtered items from list
I cannot figure out how to create a Flow that will do the following. I have a Microsoft List in a SPO site. There are multiple columns, and one column is Priority and one column is Due Date. I would like to send an e-mail each day with all items from the list that are either (a) high or critical priority and (b) due on or before today. Is this possible? I'd appreciate any suggestions.
JosephNierenberg yes this is not difficult. In my example list there are 3 items that are Critical or High priority AND are overdue:
The flow has a recurrence schedule trigger set to run from Monday to Friday at 9am:
The first action is to get the items from the list. You need to add a filter query so it only brings back those that are critical or high and overdue. The filter query is as follows with an expression for utcNow()
(Priority eq 'High' or Priority eq 'Critical') and Due le '@{utcNow()}'. Make sure the utcNow() expression is inside single quotes.
Add a Select action
{
"Title": select Title from the dynamic content box
"Priority": select Priority Value from the dynamic content box
"Due": "select Due from the dynamic content box"
}
The reason you select Priority Value and not just Priority is that the Priority column is a choice column. CLick the T next to the first line of the Map grid to open JSON formatting.
Next add a Create HTML table action and select the output from the Select section of the dynamic content box.
Finally, add the email action and select the output of the Create HTML table action from the dynamic content box:
The email looks like this:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
JosephNierenberg yes this is not difficult. In my example list there are 3 items that are Critical or High priority AND are overdue:
The flow has a recurrence schedule trigger set to run from Monday to Friday at 9am:
The first action is to get the items from the list. You need to add a filter query so it only brings back those that are critical or high and overdue. The filter query is as follows with an expression for utcNow()
(Priority eq 'High' or Priority eq 'Critical') and Due le '@{utcNow()}'. Make sure the utcNow() expression is inside single quotes.
Add a Select action
{
"Title": select Title from the dynamic content box
"Priority": select Priority Value from the dynamic content box
"Due": "select Due from the dynamic content box"
}
The reason you select Priority Value and not just Priority is that the Priority column is a choice column. CLick the T next to the first line of the Map grid to open JSON formatting.
Next add a Create HTML table action and select the output from the Select section of the dynamic content box.
Finally, add the email action and select the output of the Create HTML table action from the dynamic content box:
The email looks like this:
Rob
Los Gallardos
Microsoft Power Automate Community Super UserRobElliottThank you for explaining this so fully! It's bumping against the border of my abilities, but I'll give it a try tonight and test. I'll report back here with questions or to mark your reply as the answer.
RobElliottThe "Get Items" step keeps failing. After resolving a few issues, I was unclear whether "Due Date" as a column head required quote marks and, if so, which ones. So I changed the column to "Due," which avoids the issue. An Access db using that list tracked the change. But the Flow test keeps saying that the column doesn't exist. I've waited two hours to retry, but same result. Here is the message:
Column 'Due' does not exist. It may have been deleted by another user.
clientRequestId: [omitted]
serviceRequestId: [omitted]JosephNierenberg changing the column name doesn't change the internal name of the column. Go to your List Settings screen, select the column then look at the address to see what the field= is at the end of the address. Use that in your query.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
