Find SharePoint Folder with Particular Name Among Multiple Document Libraries Using Cloud Flow (Part 7 of 8)
Published Sunday, February 20, 2022
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.
- Trigger and List Applications + Document Locations
- List folders under each Document Location
- Find Document Location with "Approval Letters" subfolder
- List all files under the "Approval Letters" subfolder
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.
@{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'])) 
length(body('Filter_array_for_Approval_Letters_folder')) 
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.
