Reminder Emails While Status = In Progress

[scnative]

SharePoint list where Status column = In Progress or Complete. 

If Status = In Progress, and 7 days has passed, I want an email reminder to trigger, otherwise if Status = Complete, no email. 

I created a column titled "7" that calculates the +7 days date. 

I'm using an Automated flow When New Item Added "Do Until" status = Complete, but it's only firing when the item is first entered.  It's not running daily. 

Using utcNow('MM/dd/yyyy') to compare to the "7" date.  MM/dd/yyyy is how the date columns are displayed. 

What am I missing (see image)?

[tom_riha]

I think for reminders it’s better to create a ‘Scheduled’ flow that will run once a day, filter all relevant items and send notifications. A flow with the following structure:

• Scheduled trigger, run daily
• Get items, with filter query: 7 eq ‘utcNow...’ and status eq ‘In progress’
• Apply to each
  • Send an email

[scnative]

Thanks.  @tom_riha I looked up data on "apply to each" but am unsure what is wrong, as the flow failed. 

Is it supposed to add 2 of the "apply to each" actions?  Or did I fat-finger it?

I'm confused on which value I am applying to each and why?

[tom_riha]

One 'Apply to each' action should be enough, the one called 'Apply to each 2' in your flow. By default 'Apply to each' action needs an array as input, which is the 'value' dynamic content coming out of the 'Get items'.

I guess the 2nd Apply to each (called 'Apply to each') with 'Status' as an input is failing since 'Status' is not an array.

In general, when building Power Automate flows the flow will add the 'Apply to each' actions by itself when needed. You need to add it manually only if you're using your own array variables.

[scnative]

Me again @tom_riha.  🙄

Flow failed:  The expression "  Status eq 'In Progress' and 7 eq ‘utcNow('MM/dd/yyyy')’  " is not valid. 

I've read that when reporting utcNow the month should be capitalized MM- does it matter?
Could the error be because the 7 date column is calculated?

[tom_riha]

That's a good point, it'll not work with calculated column. Calculated columns are not supported in the Filter Query, if you need to do some calculation you must do it directly in the Filter Query and use the original date column. So instead of just utcNow(...), you need to extend it by addDays() expression to do the calculation (the code below will remove 7 days from today's date).

addDays(utcNow(),-7,'MM/dd/yyyy')

Month should be capital M, lower-case m are minutes.

[scnative]

Hi @tom_riha, 

Okay so get this.  During one of my tests a few minutes ago that failed on input, it showed the OLD list name.  Hasn't since.  I think this is why it's failing.  I was asked to change the list name couple days ago.  I've had issues with Power Automate and Power Apps not updating accordingly when column name changes.  They retain the old names and I have to rebuild stuff to compensate.  Anyway, I didn't copy it when I saw it, thought I could reproduce, but it's hasn't. 

What is your recommendation?  It's really tiring to get these "well, that's just how it is!" errors.  Apps seem shoddy, at best.  

Thanks!

[tom_riha]

I think renaming lists and columns shouldn't be a problem in Power Automate, it doesn't use display names of lists and columns, it's using GUID for lists and internal names for columns. These are fixed for the whole existence time of the list/column. The only way to change them would be to delete and create new list/column.

I can't talk for Power Apps though, I don't like them very much as they're very 'shoddy' as you say and I totally agree. 

[scnative]

Agreed.  I'm still getting this error, though. 

The expression "Status eq 'In progress' and 7 eq 11/06/2020" is not valid. Creating query failed.

Where the date is calculated in the Filter Query

addDays(utcNow(),7,'MM/dd/yyyy')

(changed to +7 due to requirements)

[scnative]

@tom_riha 

What could be causing this?  

[scnative]

@tom_riha 

Could it be because it says there's no dynamic content, thus no way for me to add the column Status that way.  I manually typed it in.  

[tom_riha]

Is ‘7’ still calculated column or you changed it to date only column?

The Status column is a choice column? In the query filter you don’t need dynamic content, you have to type in everything by yourself and the Status part seems fine to me. 
Maybe you could try to run only the Status filter, then only the date filter, and you’ll see which one fails. 

[scnative]

@tom_riha  Hi again. 

I wasn't aware calculated column needed to be changed to date only if the formula is ODATA. 

How do I extract the date as a date, versus the calculated?   I've been looking up tuts on it, but cannot figure it out and need to get this out the door as soon as possible. 

[tom_riha]

The calculated column must be based on some date where you add the 7 days. Use the same date column you use in the calculated field in the filter.

E.g. let's call it AssignedDate, then currently your calculated column 'seven' will be something like  'seven' =[AssignedDate]+7. Then you're trying to use the following formula in the filter: 'seven' == [today], which won't work (due to the calculated column). You need to do the calculation on the other side of the formula: AssignedDate == [today]-7, that means AssignedDate == addDays(utcNow(),-7,'MM/dd/yyyy').  <= that's an expression.

The right side of the formula in the filter must be always a basic SP column, all operations must be done on the left side.

[scnative]

@tom_riha 

I REALLY appreciate your patience with me on this. 

So I'm an idiot, as is evident... 😂  I see your point now.  

I have an Input Date (not calculated) column.  So, applying the calc in ODATA on *that* column is what I need to be working on. 

I tried:  

Status eq 'In progress' Input Date eq addDays(utcNow(),-7,'MM/dd/yyyy' and it failed. 

I tried putting single quotes around the addDays... and it failed, too.  

[tom_riha]

Almost there. 🙂

1. you're missing 'and' between the formulas

2. you must use internal name of the 'Input Date' column (check column settings, it's at the end of the URL after 'Field=')

3. I'm not sure if the addDays(... was entered as an expression, but it must be an expression

It should look in a similar way as in the message 6, including all apostrophes.

[scnative]

@tom_riha 

I added single quotes around the UTC expression and the flows are succeeding!!!!!  

THANK YOU, THANK YOU, THANK YOU!!!!!  

I will post a recap shortly to keep all correct data in one post.  If you could copy/paste what I post as a reply to this, then you'll get credit for the knowledge and I'll mark as solution. 

[scnative · Accepted solution]

@tom_riha 

Got it working. THANK YOU, THANK YOU, THANK YOU. I cannot express how much this has been a journey, and you’ve been my flow Sherpa! 

I am recapping your help here so it's condensed for solution. 

1. Do not use a calculated column.  Plain date column only. 
   
   
2. Create a Scheduled flow to run daily. 
   
   
3. Add "Get Items" and select Site Address and List Name.
   
   
4. In the Filter Query section, manually type:  Status eq 'In progress' and Today eq then use the expression builder to add:  addDays(utcNow(),-7,'MM/dd/yyyy')  (where In Progress and Today are your column titles)
   
   
5. After it has inserted the expression, manually type single quotes around the pink box:

6. Add an Apply to Each step after the above. *Select an output from previous steps will be dynamic Value.

7. Add Send an Email and enter dynamic details.  

8. Click Save.

Here's how it looks completed:

[davidkarn]

Hi all,

I am new to Power Automate and would like to explore it because it is interesting. 

My case is, I want to send an email notification to admins that will occur every 10:00 in the morning everyday, if there are 'In-progress' items that are unassigned to them.

Just want to ask what is the issue on my first flow. I'm having an error.

Any help or advice is well appreciated. Thank you