0

I have the following list and document library in the same SharePoint site:

SharePoint List (Policy Inventory):

Sharepoint List

SharePoint Document Library (Policy-Working-Documents): Sharepoint Document Library

My current flow filters for items in the list with a review date that is +120 days from today, then it gets the files from the document library sub-folder named Test-Folder.

I am really struggling with the following:

  1. Filtering the Get Files output so as to keep only the files with a Name that is also contained in the output of Get Items?

  2. Send an Outlook email that contains the documents from #1 as an attachment or link. The body of the email should include the Document Name and Review Date which are provided in the Policy Inventory list.

My current flow:

enter image description here

Troubleshooting

Current Flow:

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here Note that ReviewDocs Body is available in the Dynamic Content for the Apply to each - ReviewItem, but I selected the Body from PolicyReview120 as instructed. After doing this, I am unable to select the Body from ReviewDocs in the From field in the Filter array

12
  • How does Policy-Inventory list link to the Policy-Working-Document? If they are not link, how do you envision linking them? A) you get the list items B) Now you need to get the files from the library based on the list items. Yes, once you get the files, you can attach to an email. Please provide some sample data to understand this. Oct 10 '20 at 1:47
  • @MatiurRahman I have added screenshots of my List and Document Library. I was thinking of somehow using Apply to each with a condition to see if the Name in the document library is contained in the Get Items output then send an email and within the email attach the document. Please note, the list and document library are much larger outside of this sample. Oct 10 '20 at 3:33
  • Sure.. I will take look into it little later today. You might already know that there's a limitation as to how many items you can retrieve from the list/library and process in the Flow with Apply to each loop. Regardless, best option is to filter the data in the SharePoint first. Do you think you can filter using [Created] date so that not to bring a whole lot of old records into the Flow? Oct 10 '20 at 14:44
  • I don't see any space at the beginning or at the end of string {Name} and {DocumentName}. However, there is space within the string. Not sure whether it is a single or multiple spaces. Can you click on the "Edit in advance mode" under [Filter array] and hard code value such as "test 120 day" on the both side of the [is equal to] as the filter in the condition and try again. Just don't use the dynamic content and see if the Filter array is working. Also, you can troubleshoot by creating a new list item/uploading a new doc without any "spaces" in the DocumentName/Name. Oct 11 '20 at 13:27
  • Also, you may check whether the correct dynamic properties been selected under [Filter array]. 1) Body should be the JSON object from Policy-Working-Documents, that is, Output of the [ReviewDocs] action. And filter query should match as I described in my response. But let's confirm whether hard-coded value is working or not and then we'll focus on the dynamic content. Oct 11 '20 at 13:34
1

Here are the steps that can be used to fulfil the requirements:

  1. Get [Policy Inventory] Items with a Filter Query, as currently you're doing

  2. Create JSON object from the output from step 1

  3. Get all Documents' Properties from [Policy-Working-Documents]/Test-Folder. Filter Query can be applied if a meta data field is available in the library

  4. Create Document property JSON object collection from the output from step 3

  5. Loop through all the list items in the collection in step 2

  6. While looping through, Filter the Review Docs collection in step 4

  7. Add [Select] action to Select properties to be included in the email

  8. Add [Apply to each] after [Select] and construct an email to send.

Please see the screenshots showing the Flow Steps

enter image description here

Detail 1

enter image description here

Detail 1.1

enter image description here

Sample of email sent- can be further customized

enter image description here

List and Library data used to TEST enter image description here

enter image description here

Further Enhancement

The document under review can be attached to the email by retrieving the content of the file by adding [Get file content] before sending emails. This requires [File Identifier] which is already included in the [Get Files (properties] action output. This Property need to be added to the [Select] action and then can be used in the [Apply to each] action below it.

7
  • Thank you very much for such a detailed response. I will try and implement your solution later tonight. Oct 10 '20 at 22:09
  • It took a while for me to do it. But it worked for me. Let me know if you have any questions Oct 10 '20 at 22:10
  • I think I have it right up until Send an email (V2). In the body of the email, the Dynamic content is not showing the items from the above Select map (DocumentLink, ReviewDate, not DocName)... Any ideas? My Filter array in the Apply to each - ReviewItem shows Outputs as [] -- there is nothing in the body. It is blank. That is probably the problem? The Filter Array is made of of: From = the Body from PolicyReview120 string(item()['{Name}']) on the left is equal to string(items('Apply_to_each_-_ReviewItem')['DocumentName']) on the right Oct 11 '20 at 2:05
  • Before checking values in the Send Email, can you compare output from action PolicyReview120, property [DocumentName], with Property [Name] from ReviewDocs? If they look matching, you can use trim() and toUpper() functions in the Filter array, like toUpper(trim(string(item()['{Name}']))) =toUpper(trim(string(items('Apply_to_each_-_ReviewItem')['DocumentName']))) Oct 11 '20 at 4:18
  • Hmmm. They matched so I edited the Filter array to include toUpper for both and still getting no output. I have attached screenshots to my original post under Troubleshooting at the bottom if you have time to take a look at them. Oct 11 '20 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.