Sharepoint reminders

[TORFS_BE]

Hi

I'm trying to achieve to get reminders for 7, 30, 60, 90 and a custom date.

Below I'm giving a summary of what we got and what we achieved so far (with help from the community) + the error message that got me stuck now.

The setup:

I have the default document library that has folders in it that will be setup with permissions so each person will only see their department.

I have created a list view that will show the documents so its easier for them to work in.

There's a column startdate (startdatum) and expire date (Vervaldatum) that is a date/time type 

People will fill in these columns by themself

As an extra column I would like to be able place a Number column so they can fill in a decimal they want. This would be named (Remind me x days before expire)

The flow that is currently active in detail;

"Apply to each" value

Compose 1

Compose 2: (this one is causing problems now)

Condition;

Each of these 4 conditions have the same value

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

Send an email;

I'm able to save the Flow itself, but when I try to test run it manually I get an error message;

Any changes I could be making to make it all work? 

for the column "reminder me in x days" we could/would be using another 'Condition' within the flow with these expressions;

(we didn't get to this point yet to test it out)

As mentioned we would be able to decide manually when we want to receive a reminder.

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

int(items('Apply_to_each')?['ColumnName'])

[grantjenkins]

The reason you're getting the error on that Compose is because you have item instead of items. It should be:

items('Apply_to_each')

I'll try to post how I'd build this flow so it's a bit more efficient (only iterate over the items that should have an email sent).

[TORFS_BE]

Hi @grantjenkins 

Thanks for taking a look into this Flow.

I've changed the "Compose 2" to what you suggest but I still get an error message.

Since you suggest to draw a more efficient flow I can wait for it first aswell.

[grantjenkins]

The error you're getting now would indicate that some of the dates are blank in your Library.

Just before I build out the flow - will your Library have the dates and the days for reminder as mandatory (all must be filled in), or are they optional?

[TORFS_BE]

Hi @grantjenkins 

Yes, the expire date will be mandatory but the last column (remind me x days before expire) (type = number) would be optional as it might be enough for them to have a reminder 7,30,60 and 90 days.

[grantjenkins]

Ah - so the reminder will go out always when it's 7, 30, 60 and 90 days before the expiry date. And in addition, if they add a number, it will send a reminder that many days out too. Is that correct?

I'm just off to bed now as almost 1:30AM for me, but will try to get something for you tomorrow (if someone else hasn't already) 🙂

[TORFS_BE]

Hi @grantjenkins 

Yes that is correct! 7, 30, 60, 90 on fixed days before the expire date

The extra column is a reminder that many days before the expire date aswell. (dynamic value)

Thanks in advance for looking into this and goodnight 🙂

[grantjenkins]

Hopefully this is what you're after.

See full flow below. I'll go into each of the actions.

Recurrence is set to run daily.

Get files (properties only) retrieves all the files. Note that I've also set the Top Count to 5000 as it will only return 100 items if you leave this blank.

Convert time zone converts todays date to the time zone set on your SharePoint site.

utcNow()

Filter array uses the values from Get files and the following expression to check the different reminder periods including the optional reminder users can set themselves. Note that you will need to paste the expression using Advanced mode.

@Anonymous(
  equals(addDays(item()?['Vervaldatum'], mul(int(string(coalesce(item()?['RemindMeXDaysBeforeExpire'], 9999))), -1), 'yyyy-MM-dd'), body('Convert_time_zone')),
  equals(addDays(item()?['Vervaldatum'], -7, 'yyyy-MM-dd'), body('Convert_time_zone')),
  equals(addDays(item()?['Vervaldatum'], -30, 'yyyy-MM-dd'), body('Convert_time_zone')),
  equals(addDays(item()?['Vervaldatum'], -60, 'yyyy-MM-dd'), body('Convert_time_zone')),
  equals(addDays(item()?['Vervaldatum'], -90, 'yyyy-MM-dd'), body('Convert_time_zone'))
)

Apply to each iterates over each of the items in the Filter array (the items we need to send an email for).

Send an email sends the email including the name of the file and the due date. The expressions used are:

//Email
item()?['Author/Email']

//Due date
formatDateTime(item()?['Vervaldatum'], 'ddd, dd MMM yyyy')

//File name with extension
item()?['{FilenameWithExtension}']

Below is a sample of the email sent out.

[TORFS_BE]

Hi @grantjenkins 

Thanks for this output of you.

While configuring I have some changes aside from you that I wasn't able to get it.

In the end I was able to Save it, but when testing I get an error on the "Filter Array"

I don't know if it matters but i'm using the default library in the Sharepoint and I first created Folders and put files inside it. There are no direct files within the library.

I assume the system is smart enough to look inside of these folders and apply the flow on it?

Below a list of the changes on my Flow and the error message I got eventually.

I"ll start showing my flow and note the differences.

Get Files (properties only)

Basic setup: Pick the correct Site Address & Library Name...

Extra: fill in the "Top Count" you mentioned by 5000 

Convert time zone

First item that is different;

-I'm not able to see the "Format string" line to add the value you mentioned. Instead there's "Time Unit" which I was able to select 'Add custom value"

-I've taken the timezone that matches me (UTC +1)

Filter Array

Second small difference

-On the "From" the value you show is just "value". When I pick it, it shows 'body/value' (not sure if it matters)

Filter Query line -> I basically copy paste the line you suggest in Advanced Mode (it turned out an expression after doing that)

Apply to each

3rd small difference

It shows "body/value" instead of just 'value' as on ur part (not sure if it matters)

I'm able to pick just 'body' but as soon as I save it, the changes to 'body/value'

Send an email (V2)

4th difference

I'm not able to add these the way you did.

If I place it as an expression, it becomes like this.

Did not get to this part yet to test ofcourse..

Eventually when I saved the Flow and tried to run it manually I got this error message on the Filter Array part.

Thanks in advance to have a look of my errors being made and trying to making this work.

[grantjenkins]

Can you try switching back to the Classic designer and see if that allows you to get it working based on my suggested solution? There are still some bugs with the new designer that cause some unexpected errors, etc.

[TORFS_BE]

Hi @grantjenkins 

I have tried it on the classic mode, but sadly the error message I've given in my post above remains on the Filter Array part.

On the classic mode, all items are named correctly as you suggested.

[TORFS_BE]

Hi @grantjenkins 

Have you got another idea what could be the cause of the error here?

Anything else I can provide you?

Thx!

[grantjenkins]

After switching to the classic designer, did you re-do the Filter array? I'd suggest doing this to see if it resolves the issue as have seen multiple people have issues with the new designer adding body/value and it causing an error.

[TORFS_BE]

Hi @grantjenkins 

Yes, i've tried to re-do the Filter Array but the error remains the same.

I even tried to manually type it, same problems.

Best regards

Brian

[TORFS_BE]

Hi @grantjenkins 

Shall I re-post the error I get in another thread to see if someone else might know an answer?

Best regards

Brian

[grantjenkins]

Only thing I can think of is that some of the internal names of your columns are different to what you've used in the Filter array expression. Can you confirm that your internal SharePoint Column names are the same as what you've used?

[TORFS_BE]

Hi @grantjenkins 

I did post on another topic and someone pointed me saying that there were files/folders with no "Vervaldatum".

Because I didn't do it on these Folders this was the problem. 

As we got one step further, we're now at the part emails being sent out.

But, im getting spammed by emails now... which is not the thing we want ofcourse.

Only one email should be triggered instead of 4-5

Is there a way we could only get one email as soon as the expiry date gets triggered?