Create flow to review data and email document owner

[YaseenJabbar]

Hello, 

I am trying to get a flow to create a notification if an item is due today or was due previously, and then send this email to the document owner from a shared mailbox. 

As in the screenshot below, I have the Last Review Date, then Review Frequency in days (some documents are 30 days, 90 days, 365 days, etc), and then a calculated column called Next Review Date that adds the data in Last Review Date + Review Frequency to make it work. 

Basically, someone will upload a document to this library. They will then assign themselves or another person (or persons) as the Document Owner. The Last Review Date is automatically labelled as the date they uploaded it (ie today). The hope is that when it is 7 days before Next Review Date, an automated email will go out to the Document Owner to tell them that they need to take a look at the file and do what they want with it.

I've tried using the default "email reminder flow" but that doesn't seem to give me the option to change where the email comes from and who the email goes to - in addition whenever I try to point it to the Next Review Date column, the whole thing breaks even if I leave it pointed at me.

Any help would be appreciated. 🙂 

[annajhaveri]

@YaseenJabbar  i think you need to create a  scheduled flow which runs daily. Then add Get Items action to get items from list where Next Review Date is equal to Today's date + 7 days. Then add apply to each action to loop through result of Get Items, inside Apply to each add Send Email action to send email notification to Document Owner

Example of Get Items:

Filter Query example, Replace Created with your column name "NextReviewDate"

Created eq '@{addDays(utcNow(),7,'yyyy-MM-dd')}'

Example of apply to each and send email

[YaseenJabbar]

Hi Anna, thank you for your help.

I currently get this error. 

[annajhaveri]

@YaseenJabbar  there would not be any spaces in the name Next Review Date, i think it should be NextReviewDate. To confirm to go your list/library, then go to List/Library settings page (more settings), click on the field Next Review Date. Now look at the URL in browser, you will find Field=<name of field>, the name you see there should be used in filter query instead of Next Review Date that you have currently used.

[YaseenJabbar]

Hello Anna,

Thank you for your reply.

The URL ends in the following: 

However, after putting that into the flow, like this:

I now get this error:

For reference, the column information looks like this:

[annajhaveri]

@YaseenJabbar  yes my apologies i missed mentioning that filter query doesnt work on calculated column by design in SharePoint. So there are two options now:

1. Get Items using no filter query, which will get all list items, then use Filter Array action to get the items where Next Review Date is matching. This option has limitation as Get Items cannot fetch all items from list if list keeps on growing with hundreds/thousands of items. And will eventually make the flow slow.

2. Instead of using calculated column, use normal date column and set its value from a new flow whose trigger is when new item is created/modified based on the event when you want to set value of Next Review Date, and Set value of next review date from the flow.

[YaseenJabbar]

Hi Anna. Sorry for late reply - I was off work for a while. I have tried the first option, but it spits out all items instead of some items. 

How would I do the second option? 

[annajhaveri]

@YaseenJabbar  for second option, i would need to know when do you want to set value of Next Review Date? When new item is created or when new item is created and any update made in last review date value?

[YaseenJabbar]

Hello Anna,

Thank for getting in touch. Apologies for lateness as it was my Xmas break. I hope you had a good one too.

I would like to set Next Review Date value to be when new item is created and/or any update made in last review value. 

ie if a document is uploaded today, the Last Review Date is automatically today (28/12/2022, for example) - but can be changed to reflect a value in the past (eg 01/01/2001). Then the appropriate amount of time (30 days, 365 days) should be added to form the Next Review Date.

FYI - one of the parameters has changed for it to be 28 days prior to the Last Review Date.

Ergo, once it is 28 days before Next Review Date, an email needs to be sent out from [Group Mailbox] to the Document Owner or to [Group Mailbox] to alert someone the document is up for renewal.