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

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.

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.

triggerOutputs()?['body/_regardingobjectid_type']

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

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

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>

 

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.

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

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.

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