cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mathenea
New Member

Send email reminder based on multiple filters Sharepoint list

I'm trying to build a flow and having trouble getting it accomplished. I attempted to use a template and modify with no luck. 

I have a list of Items and based on several conditions below I would like an email to go out with a reminder to update the item(s). 

My Fields are: 

Status: (Drop down option) 

Staff: (Person lookup)

Period 1 Due Date: (Date field)

Period 1 Data: (Numeric Field)

Period 2 Due Date: (Date field)

Period 2 Data: (Numeric Field)

Period 3 Due Date: (Date field)

Period 3 Data: (Numeric Field)

Period 4 Due Date: (Date field)

Period 4 Data: (Numeric Field)

 

I would like a reminder email alert to go out 14 days prior to each of the dates in the Due Date Columns. I would also like an email to go out if the Period X Data field is blank and it is after the due date. It would only go out if the record has the "Active" Status. 

I attempted to build the flow with conditions, but that isn't working. 

 

2 REPLIES 2
RezaDorrani
Dual Super User II
Dual Super User II

Hi @mathenea,

 

you can query SP list using filter query to get data based on multiple conditions and then according send out an email.

Check - https://youtu.be/yeAnuTB85eg

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

DAllen365
Resolver III
Resolver III

If I am hearing you correctly, the Flow configuration will depend on how you want to do the emails and the complexity of the filter on the get items.  I am hearing you need 3 different emails.  It sounds like you've attempted this, can you provide some screenshots of your Flow and where you are stuck?  For ease I would do 3 different get item actions though if the emails are the same, you could do one massive filter with all the conditions but I think its easier to break it up:

  1. Have a scheduled Flow that runs every day at 8am or 5pm or whatever (if you need the 3 emails to go at different times, you would need separate Flows to trigger at a different time (unless you paused for it).
  2. Get utcnow() and set time zone if necessary to variable TODAY
  3. Scope 1 - Prior Dates
    1. Get all items where Status = Active AND Period 1 date = today-14 days OR Period 2 date = today -14, etc.  
    2. Count the items using length()
    3. If count is greater than 0
    4. Foreach item
      1. Fire email
  4. Scope 2 - Blank data
    1. Get all items Status = Active AND Period 1 data is not null OR Period 2 data is not null, etc.  
    2. Count the items using length()
    3. If count is greater than 0
    4. Foreach item
      1. Fire email
  5. Scope 3 - Post Dates
    1. Get all items where Status = Active AND Period 1 due date >= today OR Period 2 due date >= today, etc.  
    2. Count the items using length()
    3. If count is greater than 0
    4. Foreach item
      1. Fire email

I'm sure there are different ways to do it, this is just one way.  For the get today expression, I use:

convertTimeZone(utcNow(),'UTC','Eastern Standard Time','yyyy-MM-dd')

 

You will also need to do an adddays() function for the today-14.  I've got a few Flows where I do almost this exact process for sending reminders.  The trickiest part is likely the odata filter query on the get and placement of parenthesis.  You also need to decide if you want one email to send a table of all item data, or a separate email per item. 

Screenshot 2020-12-29 210459.png

Hope this points you in the right direction.  Otherwise let us know where you are stuck so we can try to help.
----------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,282)