SOLVED

Daily e-mail with filtered items from list

Frequent Contributor

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.

5 Replies
best response confirmed by Joseph Nierenberg (Frequent Contributor)
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:
0-SP-List.png

The flow has a recurrence schedule trigger set to run from Monday to Friday at 9am: 

 

1-Flow.png

 

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.

 

2-Flow.png

 

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.

3-Flow.png

 

Next add a Create HTML table action and select the output from the Select section of the dynamic content box.

4-Flow.png

 

Finally, add the email action and select the output of the Create HTML table action from the dynamic content box:

5-Flow.png

 

The email looks like this:

6-Email.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

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

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

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

 

internalColumnName.png

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

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