PA flow to trigger email 5 days before date showing in SP List column

[_GBH_]

Can anyone advise on the following?

We have a PA flow which generates an email with attachment (from a shared outlook mailbox) once a new entry is added to a SP list. The List has a "Target date for completion" (date column) and "Completion of work received" (choice) column, default blank. The team would like a follow up email to be generated 5 days before the "Target date for completion" date is reached, on the condition that the "Completion of work received" column is blank. Can anyone help me with the steps required, including the expression/formula I would need to use in order to trigger the email 5 days before the "Target date for completion" date? Many thanks  

[annetoal]

Write an expression using addDays and -5 as the value.

Here's an example using someone else's outputs. You can use this, but insert your own value where it says 'Get UTC...'

You also notice a formatting string has been added at the end. This is optional but you may find it useful.

addDays(outputs('Get_UTC_Date_and_Time_now'),-5,'MM-dd-yyyy')

If this helped, please mark it as a Solution.

Anne

[David_MA]

The process I will show is for sending a notification 28 days prior to an end date. You should be able to easily adapt it to five days.

1. Create a flow that runs every day
2. Follow this with a Get items action
3. In the Get items action use a Filter query to get the items will expire in five days based on the field that stores the date (note, in a filter query, you must use the internal name of the field, which will contain no spaces):
   
4. The first expression used above is: addDays(startOfDay(utcNow()),28,'yyyy-MM-dd')
   The second expression used above is: addDays(startOfDay(utcNow()),29,'yyyy-MM-dd')
5. You just need to change 28 to 5 and 29 to 6 for your use case. Do not change the date format in the expressions as you must use the ISO8601 date format in filter queries.

[_GBH_]

Hi @David thanks so much! I think I've understood correctly, but added screenshots to check. Have I understood how/where to add the filter queries correctly?

I also need to add in the condition (I think it needs to be a condition) that the email should only be triggered if there is a "Yes" in the "Completion of work received" (choice) column. Could you please advise of where it is best to add that? Or in fact whether that is even possible? 

[David_MA]

I cannot see your expressions, but you want to use ge (greater than or equal to before the first expression) and lt (less than) before the second expression. @annetoal shows to use negative values, but I don't think that is what you want, but maybe it is. In my example I need to send a notification out 28 days (four weeks) before an contractor's end date to see if their contract needs to be extended. I know using positive numbers works for my needs. In fact, I just got a notification from this workflow today from where I gave the example.

As you can see above, the end date for this request is June 23, which is 28 days from today.

[_GBH_]

@David_MAwith yours and bing chat's help I have corrected the filter query to (field_13 eq '@{addDays(startOfDay(utcNow()),5,'yyyy-MM-dd')}') which has triggered the email! Thanks so much, have a great day!