Email reminders based on Expire date in Sharepoint

[TORFS_BE]

Hi 

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!

[v-mengmli-msft]

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

[TORFS_BE]

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!

[v-mengmli-msft]

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

[TORFS_BE]

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.

[v-mengmli-msft]

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

[TORFS_BE]

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.

[v-mengmli-msft]

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

[TORFS_BE]

Hi @v-mengmli-msft 

Thanks for picking it up again.

The column was DateAndTime already.

Is there any extra information I could provide?

[v-mengmli-msft]

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

[TORFS_BE]

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.

[v-mengmli-msft]

Hi @TORFS_BE ,

Sorry, I mean put compose inside 'apply to each'.

The expression contains 'items('Apply_ to_each')', if you do not put compose inside 'apply to each', it cannot get the value of column.

Just like this:

Inside 'apply to each' and above Condition.

Or you can show me the output in 'Get files' here:

PS. 'testText' is my column name.

Best regards,

Rimmon

[TORFS_BE]

Hi @v-mengmli-msft 

Thanks for trying to continue to make this work eventually.

I placed the Compose correct now and when I try to run, I get an error on "Compose 2".

Again, I have no clue how to give you the output of the "Get Files" operation, sorry.

Maybe not relevant but i'm going for it, maybe its affecting the system or not.

I have made a library with Folders for each department with permissions on.

In these folders are documents with the column we want to make it work.

And I have a list view created that shows no Folders but just the documents the user has permissions on.

I assume, but not sure the system is smart enough to grab these files within those folders as I state in the "Get Files" to use the "Documents" Library.

[v-mengmli-msft]

Hi @TORFS_BE ,

Please click the 'get files' action and you will see the output.

[TORFS_BE]

@v-mengmli-msft 

I have posted the file but my reply was removed I believe.

I have some screenshot of the timestamp tho;

Does that work for you too?

[v-mengmli-msft]

Does it the output of 'get files' action?

I can't see this screenshot about the output of Vervaldatum column. You can check my previous reply, what I want to know is the output of this column in 'get files' action.

I noticed that your compose1 ran successfully. Could you please show me the output of it?

[TORFS_BE]

Hi @v-mengmli-msft 

I'm not able to share the output, it gets deleted because it contains sensitive data I assume.

The dateformat shown in my file/output is "2023-11-08" for example tho.

[v-mengmli-msft]

Sorry for late reply.

It seems that the value in correct format.

Could you please show me the expression in your condition?

I just want to check it over to make sure it is correct.

[TORFS_BE]

Hi @v-mengmli-msft 

Thanks for giving it another go!

Here's the information;

all 4 conditions are equal to the one below;

div(sub(ticks(string(items('Apply_to_each')?['Vervaldatum'])), ticks(string(utcNow()))),864000000000)

[TORFS_BE]

@v-mengmli-msft 

Do you have any other suggestions or should I look for another solution here? 🙂

Best regards