Daily e-mail with filtered items from list

[Joseph Nierenberg]

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.

[#2 · Accepted solution]

@Joseph Nierenberg 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

[#3]

@RobElliottThank 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.

[#4]

@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]

[#5]

@Joseph Nierenberg 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)

[#6]

@RobElliott  Thank you! That did it. Works near perfectly now. My header shows curly brackets, where yours does not, but otherwise it all works exactly as I'd hoped. Thank you very much.

Two questions: In the Select step, why is there a quote mark after the final (Due) line, instead of a period? And is there a primer somewhere that I could either read or use as a reference, that would have instructed me how to accomplish this?