Find SharePoint Folder with Particular Name Among Multiple Document Libraries Using Cloud Flow (Part 7 of 8)

This is the seventh post in a series of Automating SharePoint Integration with Dataverse using Power Automate. You can check out the other posts via these links (Part 1, Part 2, Part 3, Part 4, Part 5, Part 6, Part 8)

In the Part 3 post, I have explained how to automatically create subfolders under the Document Library. If the requirement is to process/send the documents stored in one of the subfolders automatically created, it is a relatively simple process by retrieving the Document Location of the row and picking up all the files from the subfolder under the whole path

It is harder when the users are creating more Document Locations manually to store the documents based on the specific requirement each row is related to more than one Document Location. Now, the challenge is to find the correct folder out of multiple Document Locations. Thanks to Benedikt Bergmann for the inspiration of this blog post.

These are all the steps included in the cloud flow for this solution. The following sample scheduled flow would be running at a specific time to query the list of Applications with "Pending Approval Notification" status and send the documents from the "Approval Letters" subfolder of the application. This post will focus on how to find the documents from the "Approval Letters" subfolder out of multiple Document Locations related to the Application.

1. Trigger and List Applications + Document Locations
2. List folders under each Document Location
3. Find Document Location with "Approval Letters" subfolder
4. List all files under the "Approval Letters" subfolder

🔗 1. Trigger and List Applications + Document Locations

The flow trigger is Recurrence based on the specific schedule.

This is the FetchXML used to query the list of Applications with "Pending Approval Notification" status. In the same FetchXML, related Document Location and its parent Document Locations are retrieved as <link-entity> to get the complete URL of the SharePoint folder.

<fetch>
  <entity name="lzw_application">
    <attribute name="lzw_applicationid" />
    <filter>
      <condition attribute="statuscode" operator="eq" value="859130000" />
    </filter>
    <link-entity name="sharepointdocumentlocation" from="regardingobjectid" to="lzw_applicationid" alias="DL4">
      <attribute name="relativeurl" />
      <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" link-type="outer" alias="DL3">
        <attribute name="relativeurl" />
        <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" link-type="outer" alias="DL2">
          <attribute name="relativeurl" />
          <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" link-type="outer" alias="DL1">
            <attribute name="relativeurl" />
            <link-entity name="sharepointsite" from="sharepointsiteid" to="parentsiteorlocation" link-type="outer" alias="SPS">
              <attribute name="absoluteurl" />
            </link-entity>
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Similar to the diagram below, the folder structure of the Application is based on the Contact. That is the reason why there are x4 <link-entity> tags for sharepointdocumentlocation. The alias DL4 is for the Document Location directly related to the Application. DL3 is for "lzw_application" subfolder. DL2 is for the Document Location of the Contact. DL1 is the "contact" document library and SPS is to query the SharePoint site URL.

🔗 2. List folders under each Document Location

For each Application retrieved from the previous List Rows, list the Document Location folder to see if the folder contains the specific folder (in the next step). List folder action from SharePoint connector requires two parameters. The Site Address can be populated with the absoluteurl value of the SPS alias (SharePoint site) from the result.

@{items('Apply_to_each_Document_Location_of_Application')?['SPS.absoluteurl']}

The File Identifier parameter needs to be populated with the complete folder path and it can be achieved by concatenating the relativeurl of the related Document Location (DL4) and its parent Document Locations (DL1, DL2, DL3). encodeUriComponent function needs to be used to replace some of the characters (Space with %20, / with %2F, etc.) to make the value acceptable by the File Identifier parameter.

encodeUriComponent(concat('/', items('Apply_to_each_Document_Location_of_Application')?['DL1.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL2.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL3.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL4.relativeurl']))

🔗 3. Find Document Location with "Approval Letters" subfolder

From the output of the List folder action, filter the item where the Name is "Approval Letters". After that, the flow will only process only the Document Locations which has the "Approval Letters" folder by checking the number of items from the output of the filter step.

length(body('Filter_array_for_Approval_Letters_folder'))

🔗 4. List all files under the "Approval Letters" subfolder

The step is quite similar to the one in step 2. The only difference is the '/Approval Letters' at the end of the folder path to query all documents under the "Approval Letters" subfolder. Then, the files can be processed or sent in the subsequent step.

encodeUriComponent(concat('/', items('Apply_to_each_Document_Location_of_Application')?['DL1.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL2.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL3.relativeurl'], '/', items('Apply_to_each_Document_Location_of_Application')?['DL4.relativeurl'], '/Approval Letters'))

Summary

When we automate SharePoint Integration with Dataverse using Power Automate, we need to handle multiple scenarios to cover different situations such as creating Document Locations manually. When handling such scenarios, related Document Location and its parent Document Locations can be retrieved in a single FetchXML query by using link-entity.