Create a flow to send email when expiration dates are x days out

[bneal]

I am looking to create an automated flow that on Monday at 9:00 AM it will check to see if the Expiration Date is less than 90 days away. 

For example, if todays date is 2/3/2023 and the warranty expiration date is 5/1/2023 it would send to the email identified to let me know. 

Any assistance as I am not familiar with expressions and this is my hang up. 

Thanks!

[David_MA]

You can adapt this to meet your needs.

What it does:

1. Recurrence: I run my flow daily, and I think you will need to as well otherwise you will keep getting notified again and again once the expiration date has passed.
2. Compose Link to Flow: you can ignore this.
3. Compose Date Filter 1: contains the expression: addDays(startOfDay(utcNow()),28)
   1. You would change 28 to 90
4. Compose Date Filter 2: contains the expression addDays(startOfDay(utcNow()),29)
   1. You would change 90 to 91
5. Get items End Date: gets the items from my list where the End Date field is 28 days from today (in your instance, 90 days) using a filter query:
   
6. In the above, your query would begin with the first end date using the name of the field for your expiration date. Note: this needs to be the internal name of the field and not the display name. Internal names do not have spaces. The spaces get converted to x0020 as you see in my filter query.
7. Compose Count: this is just a length expression to count how many items were return by the Get items action. It uses the expression length(outputs('Get_items_End_Date')?['body/value'])
8. Condition:  I use the Output of the above compose action to check that more than zero items were returned:
   
9. Finally, put whatever you need to do next under the If yes side. Which in your case will also be an Apply to each action that contains a send e-mail action.

You don't really need to use the compose actions to generate the expressions for the filter or condition, but I like putting them in the flow so I can see the actual values that have been returned in the flow runs.

[bneal]

Following up on this as I am stuck at the GetItems and got an error. 

See attached as I made a couple changes on this. I tested the two compose date filters and tested successfully. 

The actual field is called DueDate.

Thoughts?

[bneal]

@David_MA any thoughts on the error (s) I am receiving? 

Thanks,

Brandon

[David_MA]

It is because you are using the filter query from my flow. You need to filter it based on the fields from your list using the names of those fields.

[bneal]

@David_MA 

Apologies on that, still fairly new on this one. Below are the columns I am using. 

Can you update the filter to match the column name and I can update with the preferred name? Like Warranty Expiration is "DueDate".

Thanks,

[David_MA]

Based on what you said you need to do, I think you just need to filter on the warranty date. You will need to check the internal name of your Warranty Expiration date field as the filter query uses the internal name of the field and not the display name. Internal names do not contain any spaces. You can get the internal name by going into the list settings like in your screen shot and clicking on the field name. The internal name will be at the end of the URL after &Field=

Most likely, one of these two filter queries will work:

1. WarrantyExpiration ge '@{outputs('Compose_Date_Filter_1')}' and WarrantyExpiration lt '@{outputs('Compose_Date_Filter_2')}'
2. Warranty_x0020_Expiration ge '@{outputs('Compose_Date_Filter_1')}' and Warranty_x0020_Expiration lt '@{outputs('Compose_Date_Filter_2')}'

In the above, the underline purple text would be from the two Compose actions for generating the two dates for the filters. You'd insert them from the Dynamic content tab.

[bneal]

@David_MA 

The compose express is: length(outputs('Get_items_End_Date')?['body/value'])

The flow runs successfully but it used the condition "NO", which is odd because I have a couple under 90 days that are set to expire

The first date is the purchase date and the second one with no year is expiration (understands its 2023). 

Maybe I misinterpret my request but looking for anything less than 90 days from expiring? or maybe I have them switched up? I really do appreciate the communication on this.

Thanks,
Brandon

[bneal]

@David_MA do I need to change the formula to look within those 90 days? Any other thoughts?

Thanks,

Brandon

[David_MA]

I am not sure what you mean by it used the condition, "NO".

[David_MA]

What is the error?

[David_MA]

If you want to look for items 90 days out, your filters would be:

1. Compose Date Filter 1: contains the expression: addDays(startOfDay(utcNow()),90)
2. Compose Date Filter 2: contains the expression addDays(startOfDay(utcNow()),91)

If ran today with the above filters set on your warranty expiration date field, it should get items where the warranty expiration date is May 15, 2023. If the image you posted with the date fields are the items in your list, you have nothing expiring in May, which is why it returned nothing when you ran it February 10. May 11, 2023, would have been 90 days from February 10.

It is not finding items under 90 days, it is finding items on the 90-day mark. If you want everything that is less than 90 days, just use the second expression and in your filter and get rid of the first one.

[bneal]

@David_MA apologies for the confusion and there is no error. What I mean by the condition "No" is that the SharePoint List clearly identifies a date within 90 days of the current time. So if I run today, I want it to look within the 90 days so anything expiring between 2/14 and 5/14 should have been identified.

Does that change the way the flow is designed?

It does within the 90 days but also got ones that were expired back in 2022 and 2021. is there a way to define just the current date and within 90 days out? don't want an over excessive amount of emails.

Thanks,

[bneal]

@David_MA made a copy of the flow and used in a different SharePoint List and only 4 items came back within those 90 days. Would it be a setting in the List? Those devices showing the expiration date past due, along with a couple others. Note that it did pull within 90 days of today, but older ones too.

Thoughts?

Thanks,

[David_MA]

What I think you want is a rolling 90-day window for your filter. For example, you want everything where the warranty expiration is between today and May 15 (90 days from today). If so, you would adjust the filter on your date field to be:

The startOfDay expression is :

startOfDay(utcNow())

And the addDays expression is:

addDays(utcNow(),91)