Send email reminder based on multiple filters Sharepoint list

[mathenea]

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. 

[RezaDorrani]

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]

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. 

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.