Unanswered

Flow for sending an email 30 days prior the ExpirationDate of a certificate

(0) Share

Report

Posted on by Sparty

Hello all.

I realize this topic has been posted and asked about a lot before, but I have been through all the relevant examples and I can't get anything to work.

I'm new to flows and PA so I can't determine whether the problem is my library setup or that I'm doing things wrong, but your help would be very much appreciated (before I completely lost my mind)

In my sharepoint site's Documents i have a Certificates folder with sub-folders to sub-folders, where the certificates are located and categorized by client and then type. (so a certificate would be located in: Documents>Certificates>Client>Certificate Type>Certificate.pdf

I want the flow to read the ExpirationDate column and send an email to certain addresses if any Cert is due to expire in the upcoming 30 days displayed in an html table, and for it to send only one email not one for each.

I tried using Get Items action but it does not read the library (only shows me the Teams Lists).

The only thing reading my library is get files (properties only) but somehow even in the off chance that i manage to make it work it sends blank email.

I am not a programmer/developer of any short. So how do i guide it to read every document inside every sub folder?

Probably i am confusing you now, and sorry about that. I would appreciate any kind of help!

If it helps i have set my locale to my country and the utc is UTC+2.

Edit: Date format is changed site wide to: 'dd/MM/yyyy'

Thanks in advance!

Categories:

Using flows

I have the same question (0)

All responses (16)

Answers (0)

Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

@Sparty I do not think you need to check folder here. You can simply create one calculated column 'Expiry Alert' in your document library and use this formula (=ExpiryDate-30) here 'ExpiryDate' is my date column in document library. Please make sure your calculated column return type is date type only.

After that build one simple flow like this-

Note- Since calculated column return type is date type that will add some default time. So in my case its 18:30. i am adding 5:30 hrs to make it correct date.

Current Date in condition-

UTCNOW('yyyy-MM-dd')

Let me know if you need further help.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No
Sparty 20 on at

Like (0)

Report

@Hardesh15 Hey! Thanks for the assist. It run smoothly. Sadly it did not help though. Wrote and did everything as your proposed solution but nothing was send to my email. And to made sure i created a couple of test docs expiring today but nothing, even when i used 'is less than or equal' to Condition the Outputs.

Was this reply helpful? Yes No
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

@Sparty Please use compose action and pass date from calculated column and share screen of compose output. i want to see date format from calculated column.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No
Sparty 20 on at

Like (0)

Report

Sure here you go:

Just one more question, that i overlooked in your previous answer mate. You said "So in my case its 18:30. i am adding 5:30 hrs to make it correct date.", why do i need to add time with a formula to a sharepoint site/library that is in my local time/current locale?

And also i my date format is set to 'dd/MM/yyyy' as i stated on my original post.

I am just so frustrated with something so simple taking so long to do and activate.

Was this reply helpful? Yes No
Sparty 20 on at

Like (0)

Report

@Hardesh15 Any update on the above? Thanks.

Was this reply helpful? Yes No
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

@Sparty i can understand your frustration. Please use the date format as yyyy-MM-dd as sometimes flow is confused with dd/MM/yyyy date format. it will not have any impact on your process. Also what is in your utcnow()?

After these changes run your flow and share screens. I want to see outputs.

Please use equal in condition.

Second question- All Date and time are UTC standard so you have to adjust your local time accordingly.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No
Sparty 20 on at

Like (0)

Report

@Hardesh15 Thanks for the clarifications my friend.

"Second question- All Date and time are UTC standard so you have to adjust your local time accordingly. "
I converted it back to UTC since international clients were added.

"Also what is in your utcnow()?"
As i stated on my original post i have to experience with Sharepoint and coding in general. I am still in training by my company but this project can not wait to go live. I can manage basic flows but this still i need to educate more but no time to do so. And if you do not mind me asking what it should be in my utcnow()?

"I want to see outputs."
It does not let me see raw outputs or the body after the flow was run, only to download them.

Waiting yours, and thanks once more for trying to solve this and assist.

Was this reply helpful? Yes No
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

@Sparty When you run flow and if it runs successfully then click on any action it will expand where you will see input and ouput.

See the difference in utcnow().

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No
Sparty 20 on at

Like (0)

Report

@Hardesh15 Thanks for the above and apologies for my late reply.
I managed to finally move every single certificate on SPO library and index them accordingly to company's req.

I tried to test your above solution to a single folder for starters (i made sure to use docs that expiring the next 30 days ofc), but to no avail. Pls see below:

Also pls find below formulas:

Was this reply helpful? Yes No
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

@Sparty are you passing this in utcnow('yyyy-MM-dd')? Please check 'mm' in your compose that should be 'MM'.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No