Automatically Create SharePoint Subfolders Using Cloud Flow and Environment Variable (Part 4 of 8)

This is the fourth 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 5, Part 6, Part7, Part 8)

In some of my projects, there is a requirement to automatically create subfolders when the document location is created. In that way, the users will have a proper folder structure to upload the related documents based on the document type (e.g. Supporting Documents, Approval Letters, etc.).

In this post, you will learn about how to automatically create the subfolders based on the JSON value defined in the Environment Variable. To make it generic for all tables, the cloud flow will be triggered when the Document Location is created (which will be automatically created by the flows as mentioned in Part 1 and Part 3).

Before we start building the flow, we need to create the environment variable to store the folder structure of the subfolders.

This is the sample JSON that I use for the cloud flow in this post. The folderstructure JSON contains an array of tables with logical name, entity set name and the name of the SharePoint library (which is mostly the display name of the table). Each table contains the folders array that specifies the subfolder structure. You only need to specify the lowest level folder name and not its parent folders. E.g. if you create a SharePoint folder with "Private/Financial Statements/Bank Statements" path, it will create all x3 folders and you don't have to create "Private" and "Private/Financial Statements" folders individually.

{
  "folderstructure": [
    {
      "logicalname": "contact",
      "entitysetname": "contacts",
      "libraryname": "Contact",
      "folders": [
        {
          "name": "Public"
        },
        {
          "name": "Private/Identity Documents"
        },
        {
          "name": "Private/Proof of Address"
        },
        {
          "name": "Private/Financial Statements/Bank Statements"
        },
        {
          "name": "Private/Financial Statements/Credit Card Statements"
        }
      ]
    },
    {
      "logicalname": "lzw_application",
      "entitysetname": "lzw_applications",
      "libraryname": "Application",
      "folders": [
        {
          "name": "Approval Letters"
        },
        {
          "name": "Supporting Documents"
        }
      ]
    },
    {
      "logicalname": "incident",
      "entitysetname": "incidents",
      "libraryname": "Case",
      "folders": [
        {
          "name": "Screenshots"
        },
        {
          "name": "Error Logs"
        }
      ]
    }
  ]
}

These are all the steps included in the cloud flow for this solution.

1. Trigger and Cancel without Regarding Value
2. Filter Environment Variable
3. Initialise Variables
4. Get Related Document Location
5. Direct Folder Structure (Scenario 1)
6. Subfolder Structure (Scenario 2)
7. Create SharePoint Folder

1. Trigger and Cancel without Regarding Value

The trigger is on Create of the Document Location. As I explained in Part 3, the document location can also be created for the parent folder inside the related Contact or Account folders (for the folder highlighted below). In that case, we do not want to run this flow to create subfolders. That is why the first step in the flow is to check if the Regarding contains any data and cancel the flow if there is no value.

2. Filter Environment Variable

The next step is to filter the folderstructure JSON from the environment variable using the table type of the Regarding value. Based on my experience, the environment variable cannot be chosen from the Dynamic Value list for the Filter Array action step.

parameters('SharePoint Folder Structure (lzw_SharePointFolderStructure)')?['folderstructure']

I used the expression above to filter the folderstructure property of SharePoint Folder Structure environment variable. You can either update the expression based on the name of your environment variable or you can create a Compose step, populate with the environment variable and peek code to see the expression and add "?['folderstructure']" at the end.

We will use item()?['entitysetname'] property to filter because the Dataverse trigger output only contains the Entity Set name for the table type of Regarding lookup. Again, the Regarding (Type) available in the Dynamic Value list is incorrect and you will have to use the _regardingobjectid_type property in the expression to get the entity set name of the Regarding lookup (you can read more about it in Alex's post).

triggerOutputs()?['body/_regardingobjectid_type']

3. Initialise Variables

In this step, we will initialise three variables for three parameters required by the SharePoint Create new folder action. The SharePoint Site Address will be initialised with empty value and populated in the steps below. The SharePoint Library will be populated with the libraryname property of the folderstructure JSON from the environment variable which will be the output of the Filter Array step.

first(body('Filter_SharePoint_Folder_Structure_array'))?['libraryname']

The Folder Name can be populated with the Relative URL from the trigger output.

4. Get Related Document Location

This query is a bit complicated. This step is not only to get the value for SharePoint Site Address variable but also to retrieve the whole folder path if the current document location is created inside the related Contact or Account folder as mentioned in Part 3.

parentsiteorlocation_sharepointsite($select=absoluteurl),parentsiteorlocation_sharepointdocumentlocation($select=relativeurl;$expand=parentsiteorlocation_sharepointdocumentlocation($select=relativeurl;$expand=parentsiteorlocation_sharepointsite($select=absoluteurl)))

You can copy the Expand Query above to use in your flow. The following is the FetchXML equivalent of the Expand Query. The main objective of the query is to retrieve all necessary data in a single query. With this query, we can retrieve the absoluteurl of the SharePoint site (for Scenario 1) if the current document location is directly under the SharePoint document library (e.g. https://sharepointsite/sites/contact/Jim Glynn). The query will also return the relativeurl of the related parent locations (for Scenario 2) if the current document location is created inside the related Contact or Account folder (e.g. https://sharepointsite/sites/contact/Jim Glynn/incident/Test Case).

<fetch>
  <entity name="sharepointdocumentlocation">
    <attribute name="relativeurl" />
    <filter>
      <condition attribute="sharepointdocumentlocationid" operator="eq" value="00000000-0000-0000-0000-000000000000" />
    </filter>
    <link-entity name="sharepointsite" from="sharepointsiteid" to="parentsiteorlocation" link-type="outer">
      <attribute name="absoluteurl" />
    </link-entity>
    <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" link-type="outer">
      <attribute name="relativeurl" />
      <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" link-type="outer">
        <attribute name="relativeurl" />
        <link-entity name="sharepointsite" from="sharepointsiteid" to="parentsiteorlocation" link-type="outer">
          <attribute name="absoluteurl" />
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

5. Direct Folder Structure (Scenario 1)

If the current document location is directly under the SharePoint document library (Scenario 1), the value for the following expression will contain a value. In that case, we can populate the SharePoint Site Address variable with that value.

outputs('Get_Document_Location_by_ID')?['body/parentsiteorlocation_sharepointsite/absoluteurl']

 

6. Subfolder Structure (Scenario 2)

If the current document location is created inside the related Contact or Account folder (Scenario 2), the expression for the SharePoint Site Address variable will be as follows. (which is under x2 additional parentsiteorlocation_sharepointdocumentlocation)

outputs('Get_Document_Location_by_ID')?['body/parentsiteorlocation_sharepointdocumentlocation/parentsiteorlocation_sharepointdocumentlocation/parentsiteorlocation_sharepointsite/absoluteurl']

For Scenario 2, the SharePoint Library will not be the libraryname property of the current table. It will be the library name of the parent Contact or Account. That is the reason why another Filter Array step is required to filter by the logical name using the relativeurl of great grandparent document location.

outputs('Get_Document_Location_by_ID')?['body/parentsiteorlocation_sharepointdocumentlocation/parentsiteorlocation_sharepointdocumentlocation/relativeurl']

Then, use the following expression to set the SharePoint Library variable.

first(body('Filter_SharePoint_Folder_Structure_array_for_SharePoint_Library'))?['libraryname']

The Folder Name is also not just Relative URL from the trigger output and it should also be combined with relativeurl from parent document location grandparent document location to build the whole folder path.

@{outputs('Get_Document_Location_by_ID')?['body/parentsiteorlocation_sharepointdocumentlocation/relativeurl']}/@{outputs('Get_Document_Location_by_ID')?['body/relativeurl']}/@{triggerOutputs()?['body/relativeurl']}

7. Create SharePoint Folder

The final step is to loop through the folders array of the JSON with the following expression and create a folder for each folder name using Create New Folder action from SharePoint connector. The Site Address, Library and parent folder paths can be populated with the variables from the steps above.

first(body('Filter_SharePoint_Folder_Structure_array'))?['folders']

Summary

Automatically creating the subfolders under the SharePoint document folder for the tables can be achieved with Power Automate cloud flow by storing the folder structure in the environment variable and retrieving the folder path from the related document locations.