Unanswered

Days before a certificate expires

(0) Share

Report

Posted on by Ikaika

I'm very new to Power Automate and I attempted countless times to figure this out but failed miserably.

I'm trying to create a Power Automate workflow for a SharePoint Online list to create the number of days before a certificate expires in the "DaysBeforeExpiration" column based on the "Expiry Date" column. If the certificate is 30 days before it expires, make the row color yellow and send an email to the user. If the certificate has expired, make the row color red and send an email to the user. If anyone can lead me in the right direction it will be greatly appreciated.

Column Names:

DaysBeforeExperation: this column will hold the number of days before the certificate expires once workflow runs.

Expiry Date: This is a date column containing the date the certificate will expire.

This is the start of my flow:

Days Before Experation.png

This is the expression in the Compose action:

div(sub(outputs('Get_item')?['body/ExpiryDate'],utcNow()),86400000)

I attempted to add a Condition action and other actions and end up with a complete mess of nothing worth posting.

I'm not sure if I was on the right path, so any help will be greatly appreciated.

Thanks

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

grantjenkins 11,063 Moderator on at

Like (0)

Report

I'm not quite sure I understand what you're after.

I'm assuming you want to have a flow that runs daily and checks for items that will either expire in 30 days or have just expired - and want to then send an email to the user.

How do you know who the user is? Are you storing the email address of the user in the Title field?

Did you want to make the SharePoint List rows colored (yellow, red, etc.) based on Days Before Expiration?

I'm thinking you probably want a Calculated column in your SharePoint list for Days Before Expiration that will automatically adjust based on the Expiry Date and today's date. We can then use that to determine the color of the row using some List formatting rules.

Was this reply helpful? Yes No
Ikaika 20 on at

Like (0)

Report
@grantjenkins
Thank you for responding.
"I'm assuming you want to have a flow that runs daily and checks for items that will either expire in 30 days or have just expired - and want to then send an email to the user."
Yes. That's exactly correct.
"How do you know who the user is? Are you storing the email address of the user in the Title field?"
I was thinking perhaps using the "Created by" as a means to determine who the user is and send an email to.
"Did you want to make the SharePoint List rows colored (yellow, red, etc.) based on Days Before Expiration?"
Yes. make the SharePoint list row colors based on Days Before Expiration.
"I'm thinking you probably want a Calculated column in your SharePoint list for Days Before Expiration that will automatically adjust based on the Expiry Date and today's date. We can then use that to determine the color of the row using some List formatting rules."
SharePoint and Power Platform are all new to me and I learn well by example. Will this work for the calculated column?
=IF([Expiry Date]<TODAY(),0,DATEDIF(TODAY(),[Expiry Date],"D"))
Thank you!

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
Hopefully this will get what you're looking for.

For this example, I'm using the following SharePoint list. Note that I didn't know what you had for your Title, so I just put the name of the person. I also just used Course as a Single line of text instead of what looks to be a Lookup column in your list. Days Before Expiration is a calculated column that has the formula you provided in your last reply which works great.

=IF([Expiry Date]<TODAY(),0,DATEDIF(TODAY(),[Expiry Date],"D"))

For the colored rows, I used Format this column and set conditional formatting for the view.

Next onto the actual Power Automate (Flow).

IMPORTANT: I would definitely test with your own email address first before using the Created By Email to ensure it works as expected.

See full flow below. I'll go into each of the actions. Note that I've used a parallel branch. One branch handles items that expire today and the other branch handles items that will expire in 30 days.

The first branch handles items that expire today.

Get items Expired uses the following Filter Query.

ExpiryDate eq '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'

Apply to each Expired iterates through each of the items returned from Get items Expired and uses Send an email Expired to send the appropriate email content to each user.

The second branch handles items that will expire in 30 days.

Get items Expiring uses the following Filter Query.

ExpiryDate eq '@{addDays(utcNow(), 30, 'yyyy-MM-dd')}'

Apply to each Expiring iterates through each of the items returned from Get items Expiring and uses Send an email Expiring to send the appropriate email content to each user.

Was this reply helpful? Yes No
Ikaika 20 on at

Like (0)

Report

@grantjenkins

Looking forward to applying the workflow. I'll let you how it turns out when I'm done.

Thank you!!!

Was this reply helpful? Yes No
Ikaika 20 on at

Like (0)

Report

@grantjenkins
What's odd, is that when attempting to configure the row colors it does not give me the option to select or input "is less than or equal to", am I configuring it in the wrong place? Thanks

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

Ah - you'll need to go into List settings and click on your Calculated Column then set the data type to Number. You might also want to set decimal places to 0.

Once the data type is Number you will see different conditions (including less than or equal to) in your List formatting options.

Was this reply helpful? Yes No