Weekly document review email reminder

[RWalker]

Hello,

Some background:

We have a document library which contains our policies, procedures, supporting information, etc. and all of these documents have a review date (date column) and custodian (person column) who ensures the document is correct, up-to-date, etc.

I'm trying to create a flow which will run weekly (Monday morning at 8:30am) and will send an email with a consolidated list of documents which are due for review to the relevant custodians.

i.e. each custodian with documents due will receive one email with a list of documents, ideally with a link to them.

So far, I've been trying to create a variable (documents due this week, today + 7 days), get the documents properties from the library and then filter the array for those with a review date less than or equal to the variable, create a HTML table and insert this into an email:

I'm not sure on a couple things:

• I don't trust that this is returning all of the documents it should, more than likely my failing with the variable / filtering.
• How would I have this sent to the relevant custodian? I can only seem to choose dynamic content from the initial Get files (properties only) - is that right?

I would appreciate any guidance on this - not sure if I'm approaching this completely wrong and there's a better solution, or I just haven't got it figured out yet?

Thanks!

[v-siky-msft · Accepted solution]

Hi @RWalker ,

Please modify the flow as follows.

1. create a 'Get files (properties only)' step under Recurrence Tigger

2. initialize an Array variable named 'varPerson' to store Person field Emails (Approver column in this case)

3. create an 'Apply to each' step to loop through value of 'Get files (properties only)' action

4. within the loop, Append 'Approver Email' to Array variable

5. out of the loop, initialize another Array variable named 'varHTMLTable' to store metadata of file

6. create another 'Apply to each 2' step to loop through all distinct Approver Emails with the following expression.

union(variables('varPerson'),variables('varPerson'))

7. within 'Apply to each 2', create 'Apply to each 3' step to loop through value of 'Get files (properties only)' action

8. in 'If yes' branch, append file metadata to 'varHTMLTable' in JSON format as follows.

{
  "FileName":@{items('Apply_to_each_3')?['{FilenameWithExtension}']} ,
  "ID": @{items('Apply_to_each_3')?['ID']},
  "Link": @{items('Apply_to_each_3')?['{Link}']}
}

9. create HTML table based on 'varHTMLTable' under 'Apply to each 3' step

10. set 'varHTMLTable' to [] to clear the Array

11. create a 'Send an email (V2)' step, enter expression Item() in To field and select output of 'create HTML table' step.

Here is the detailed flow steps:

Hope this helps.

Sik

[RWalker]

Hi @v-siky-msft 

That's fantastic, thank you!

I've managed to get it working and filter for only those documents that are due or overdue with two caveats:

1. This doesn't seem to cycle through all documents, and in testing I'm only receiving emails for the same 15 approvers out of 40 that are in the document library.
   For those that it does send the email, it isn't including all documents either.
   Is this just a case of the Apply to each steps being limited to 100 items, or is there a way around this? At this stage we have 1244 documents in the library which would need to be checked and added to the table if they are due/overdue.
2. Is there any way to have the flow not send an email if there are no documents for review / the table contents are empty?

Thank you so much again for your help here! It's certainly helped my understanding grow in this area.

[v-siky-msft]

Hi @RWalker ,

The maximum limits of Apply to Each action is 5000. The 100 you mentioned is because Get Files action only retrieves the first 100 items by default. 

Please manually set the Top value to the maximum of row number. For instance:

Hope this helps.

Sik

[RWalker]

That's fantastic, thank you very much again!

[v-siky-msft]

Regarding to the second question, Add a Condition step above the 'Create HTML Table' and 'Send an Email' actions to check if the varHTMLTable is empty or not. For example:

Hope this helps.
Sik

[mnhill88]

Hello! I tried using this exact format and got an error at the append to array variable under the first apply to each. The error is "Action 'Append_to_array_variable' failed"; "of type 'Array' cannot be appended to the variable 'varPerson' of type 'Array'. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'.

For my approver column in the document library I used a person or group column and added people within my organization. I'm assuming this is the problem since I don't have an email spelled out?

Any help is appreciated!