expiration notification

[3cdoug]

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

I want to use this filter to give me 30 days prior to my expiration date? Any Pointers?

[TheRobRush]

can you show the rest of your flow, and an explanation of what you want to happen after your filter etc? Filter Array is only really useful in certain situations so just want to be sure we get you on right path

[3cdoug]

Essentially, I want to send a weekly email that sorts through my sharepoint list and will compile a list of expired certifications. The attached pdf has the rest of the flow minus that recurrence, and the end send email. Also please refer to the previously mentioned formula for the filter not the one on the pdf

[TheRobRush · Accepted solution]

So what I would do is something like this.

When I launched my flow I would run a convert time zone to set my target date (In my example I go 7 days because my list has entries pulled off pretty regularly)

to do that we go

that base time has an expression in it of

addDays(utcNow(),-7)

Then in our get items we filter

EventDate lt 'body('Convert_time_zone')'

So i am looking for any item in MY list column of EventDate that is longer than 7 days ago. this pulls anything older.

We then create our html table from the value output of the get items, put custom columns and name your html table headers and link the names of the columsn you want in your table

now email out the output of this and you get something liek

so basically you can do the same thing but instread of adding -7 to your utc() in first step, add 30, and then use the exp column in the filter and it will return anything to you that has an expiry less than 30 days in future

[3cdoug]

So the only error that I am getting now is that my expiration date column is a calculated column. When users submit a date the expiration date is automatically calculated and I am receiving the error that 

"The filed 'ExpDate' of type 'Calculated cannot be used in the query filter expression'"

[TheRobRush]

yah i dont think calculated columns are able to be filtered generally, even in powerapps. What you would want to do in that case is go off the creation date, or edit date etc. Are these expiration dates always the same for each entry? like every time someone puts in a request it adds an expiration 90 days form submission? if that is case instead of doing the +30 youd just adjust the number to make sure it looked at creation days that are far enough away that theyd be fired off by your calculated column (or if the calculated column is based off some other date column, use that column)

You basically will just recreate the calculation in the process we already set up, so you get same result.

[TheRobRush]

for example. I put in an item and give it the date of 5/1/2023, I have a calc column that adds 90 days to this, so I am looking at like 8/1/203

So normally I would want my  flow to start telling me about this item on 7/2/2023 right?

well since our calc column is just date+90 and we want to know when that is 30 days away, instead of today+30 for our base time we would go, today()-60 i think? (this is 60 behind today and 30 ahead which is 90 total) so anything older than today-60 in the original column would have been within 30 of the calc

[3cdoug]

Okay I am understanding your point with this. My expiration date (calculated column) is set to calculate a year from when the document is uploaded, which is user input within my app. So once I figure out how to determine the new expiration date without using a calculated column I can use that with what I query by

[TheRobRush]

if its a year from date they put it in, then the 30 day warning should be at like any input date older than give or take 335 days

[3cdoug]

Thats it, at least from the test data that I have. Thank you so much 😎

So to bug you some more, is there any way to filter it some more? So say if I want to create an alert for 60, 90, or however many days can I just add to the basetime formula or should I just create another parallel branch in my flow for those other time intervals?