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?

[3cdoug]

To anyone that comes across this thread continue to look at the following comments as I'm sure your question will be answered. Give this gentleman a thank you 🖖

[TheRobRush]

In regards to the multi day filter, yes, though it gets slightly more complicated. I will send an example tomorrow as I am on a time sensitive project right this moment

[3cdoug]

You Sir are an angel sent from the heavens above 👼

[TheRobRush]

Alright, strap in, this is a much longer ride. Bright side though, you should learn alot about FIltering Arrays and JSON in PowerAutomate.

So first off,  since we will be hitting a tiered version of our filter we have 30/60/90 day qualifiers, we can delete the first convert time zone we lead with last time.

Instead we will lead with our get items, and simply pull anything with the submission date column that is older than 270 days (this would be roughly our 90 day threshold) like so

So in Filter Query type the column name date is in (in mine EventDate) lt '  '

 Place cursor between the '' and in ecpression bar that pops up on lower right press expression, then type

addDays(utcNow(),-270)

and hit Ok

Now we are going to add 3 filter arrays, each one slightly different.

Filter array is here

I have named mine, 30Days 60Days 90Days

The from of all 3 is the value from Get _Items, or if you want to type it

outputs('Get_items')?['body/value']

The second line for each is

30Days

@lessOrEquals(item()?['EventDate'], addDays(utcNow(), -330))

60Days

@And(greaterOrEquals(item()?['EventDate'], addDays(utcNow(), -329)),lessOrEquals(item()?['EventDate'], addDays(utcNow(), -300)))

90Days

@greaterOrEquals(item()?['EventDate'], addDays(utcNow(), -299))

Do notate that the 30&90 day reference EventDate 1x, and the 60 references it 2x, change to date column you are referencing

NOW at this point we pause, run our flow, and grab some info. Here is how.

Step 1 - Run the flow

Step 2 - Check the results

Step 3 -  Pick the 30 60 or 90 day OUTPUTS, it does not matter which, and copy EVERYTHING in the Output section

We will now add 3 Parse JSON steps, find it here

Now we will have 3 of these in total and I have named mine Parse90JSON, Parse60JSON, & Parse30JSON

for each you simply point the content to the body of each of our previous creations, 90 will be body('90Days'), 60 the 60Days, etc AND for the schema, press generate from sample and paste in the output that we copied and pasted from the test run above. it will look like this

After this we will place 3 Create HTML Tables

I have named mine 90HTML, 60HTML, 30HTML

The from of each points to the body('Parse90JSON') /60 /30 etc

And I put two custom rows, header is schoolname for my example. and expires is

addDays(item()['EventDate'],365,'d')

this takes that first column with original date, and adds 365 to it so the expiry date will be displayed in the email

it looks like this  when done

Then lastly in my email I just reference these tables, and for my example wrote 30 days, 60 days, 90 days above them in the email just to show each.

Final result was

original test record dates were

There you go!

[3cdoug]

So I am getting a Parse failure. I receive no explanation, it says this is new to the automate community. I guess I messed up somewhere

[TheRobRush]

yes.... somewhere haha

 try going through it again and make sure things that went in expressions ar ein expressions etc, and double check that you copy pasted righ tinfor form that intitial run early on

[3cdoug]

Do i copy and past everytime in the schema or just once?

[TheRobRush]

just the first time setting it up, and then use same schema paste for each of the steps in the flow cause they will all match

[TheRobRush]

it's to avoid havign to type the structure by hand, would be a pain

[3cdoug]

so when i do the test my 60 day filter doesnt have an output, only the 30 and 90. Is that a problem?