Unanswered

Email reminders based on Expire date in Sharepoint

(0) Share

Report

Posted on by TORFS_BE

i'm trying to create a system for our company and did some googling but nothing really comes up that can do the trick for me or seem to be working.

Ok our plan is to create a Library with all possible contracts from each department in. Id be working with folders to fix all permissions so they wont see eachothers contracts. Eventually work with a view that doesnt show folders but just the documents.

Example as below;

Pretty basic information so far;

Every contract that will be inserted will have a specific expire date ("Vervaldatum" on my screenshot).

I want to be able to send out reminders before, for example 90 days, 60, 30, 7 and final day it will expire. (I did not manage to get this working btw 😥)

On TOP of those reminders, if possible, I would like to add a colum so the person uploading the contract can set an extra reminder to be send out (For example: add a column 'Remind me in' and the value would be 200 -> the system would remind the person 200 days before the expiry date by email)

I'm really new to Power Automate and I don't always understand the actions its doing or how its build to fully understand each step. If there is a detailed guide out there and you can share it with me, appreciated!

Categories:

Building flows

I have the same question (0)

All responses (20)

Answers (0)

Sort by

v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @TORFS_BE ,

Do you want to send emails 7 days, 30 days, 60 days and 90 days before the deadline?
Please try this:
Create a flow that runs once a day, extracts all files in a folder, and calculates the number of days between today and the deadline to send an email that satisfies any condition under condition.
Expressions for calculating the number of days.
div(sub(ticks(string(items('Apply_to_each')?['DeadlineColumnName'])), ticks(string(utcNow()))),864000000000)
For two hundred days, you can use this method as well.

Thanks for reading!
Rimmon

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report
Copy link

Link copied!

hi @v-mengmli-msft

Thanks for the reply!

I have tried to copy paste the flow you suggested me.
However, I came across an error message trying to save it.
I'm pretty sure the error is on my side instead of your suggestion. I'm confused regarding the "Apply_to_each"? Do I need to fill in something here like the "DeadlineColumnName"?

Also, my library has department folders in it, with files in it.
The flow you suggest is smart enough to look for the files in these folders ? 🙂

Below the example I tried to create;

As for the 200 days one;
this column should be dynamic and not fixed 200 days for example.
Is there a way to setup a system so whatever the person inserts it will calculate this and remind the person on the fixed day it stated in this column? 200 was just an example from my side.

Thank you for helping me!

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @TORFS_BE ,

The Condition action should be inside 'Apply to each' not outside. Please review my flow.
You don't need to fill 'DeadlineColumnName' in the expression, it is an example for you replace it with your date column name.
I'm a little confused about "this column should be dynamic and not fixed 200 days for example". Maybe you can show me an example.

Best reagrds,
Rimmon

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

Again, thanks for the reply!

Ok, my mistake to not see the flow was set up different, my bad.
Anyhow, I retry to copy paste the flow you suggest. This time I was able to "SAVE" but as soon as I try to run it, I get error messages.

Yet, i'm still confused on what each part will do.

This is what my flow does when I try to re-create.
I don't see any value output stated on ur screenshots at the "Apply to each". Yet I cannot continue if I don't insert anything.
The value I picked was the "Vervaldatum" column. As soon as I select it, it adds another "Apply to Each" below it.

when I try to test the flow, i get these messages in detail;

As for the 200 days thing;
Here's my example;

Id create a column so people can pick when they want a reminder by themself and the flow would calculate this by itself and send out the email when asked in this column.
I hope this is clear enough for you.

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @TORFS_BE ,

Please do not select the "Vervaldatum" dynamic content, the expression I provided can get the value of this column.
Please add 'apply to each' action and just select the 'value' dynamic content, then put the condition action inside the 'apply to each'.

The function of important steps:
'apply to each': Performs operations on each of files.
The expression:
items('Apply_to_each')?['DeadlineColumnName']//get the date of current record in 'apply to each' string(items('Apply_to_each')?['DeadlineColumnName'])//convert date to string ticks(string(items('Apply_to_each')?['DeadlineColumnName']))//caculate ticks about the date sub(ticks(string(items('Apply_to_each')?['DeadlineColumnName'])), ticks(string(utcNow())))//Calculate the tick difference between two date div(sub(ticks(string(items('Apply_to_each')?['DeadlineColumnName'])), ticks(string(utcNow()))),864000000000)//Convert the results to days.

For your second question, you can add another condition inside 'apply to each', using an expression to calculate the number of days from the file created time to today, and if it equals the value of the column in your table, then send an email.

The expression in condition2
div(sub(ticks(string(items('Apply_to_each')?['Created'])), ticks(string(utcNow()))),864000000000)
int(items('Apply_to_each')?['ColumnName'])

If my answer solves your problem, please accept it as a solution. Thanks!😀

Rimmon

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

Sorry here I go again, thanks for the time looking into this for me.
I get another error message as soon as I try to test the Flow.

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @TORFS_BE ,

What is type of 'Vervaldatum'? Is that text type?
Please change the type to DateAndTime(not include time) and run flow manually.

Best regards,
Rimmon

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

Thanks for picking it up again.
The column was DateAndTime already.

Is there any extra information I could provide?

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @TORFS_BE ,

Thanks!
I just wondering why our date columns are displaying dates in different formats.
The date in my column:
Perhaps this led to the error.
Please add two compose action and copy the expression:
Compose1:
items('Apply_to_each')?['Vervaldatum']
Compose2:
formatDateTime(items('Apply_to_each')?['Vervaldatum'],'yyyy-MM-dd')
Then run flow and show me output of compose even it is an error.

Best regards
Rimmon

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

I'm not sure if I placed the "Compose" action correct tho.. but i'm not able to save right now.
(I tried to place it after and before the "Apply to each" action.

Was this reply helpful? Yes No