Retrieve parent folder name of item
Retrieving the parent folder name of an item in a SharePoint list within Power Automate can be challenging. Especially when the folder path contains multiple folders. This article will show how you can use the REST API for this.
Inspiration
This question from JDeng:
I have created a trigger to go alert me when a new file is added to my SharePoint. I am alerting myself by sending a message to my Teams chat. I want the message to include, dynamically, what is the name of the folder that contains the new file.
Power Users Community thread: Get Name of Folder Containing a New File.
Expressions
My first response was to solve this with an expression. The solution I proposed was using skip, split, sub and length functions to retrieve the third folder of the Folder Path.
This will work great when your folder structure is always the same. However, I realised this is very unlikely in most cases.
Surely there must be an easier way of retrieving a parent folder?!
FieldValuesAsText in REST API
Turns out, yes there is.
I started exploring the REST API and had a look at the FieldValuesAsText property. I have used it before and it sometimes just shows additional properties compared to the regular item. And this was the case again. The FieldValuesAsText contains a string value for ParentUniqueId.
The ParentUniqueId can be used in a GetItemByUniqueId method to retrieve the parent item. With a $select query parameter you can only retrieve the FileLeafRef, which should be the folder name.
Flow setup
In this example the parent folder is retrieved from a newly created file and the name is shared via a Teams message.
1. Add a When a file is created (properties only) trigger action.
a. Select your Site Address
b. Select your Library Name.
2. Add a initialize variable action (optional).
This variable is used in the GetByTitle method in the REST API requests. This variables is optional because you can also add the list name value directly in the URI fields.
a. Provide a Name, I used DocumentLibraryName
b. Select String as type
c. Provide a value. It should be the same name as in 1b.
3. Add a Send an HTTP request to SharePoint action.
This action will retrieve the ParentUniqueId property value of the item.
a. Select your Site Address
b. Use the GET method
c. Use the URI from the codesnippet below
d. Use the Headers from the codesnippet below
4. Add a second Send an HTTP request to SharePoint action.
This action will retrieve the FileLeafRef property value of the parent item. A slice function is used to remove the curly bracket characters.
a. Select your Site Address
b. Use the GET method
c. Use the URI from the codesnippet below
d. Use the Headers from the codesnippet below
5. Add a Get my profile (V2) action (optional).
6. Add a Post message in a chat or channel action.
a. Select Post as Flow bot
b. Select Post in Chat with Flow bot
c. Use the Mail dynamic content property from the Get my profile action in the Recipient field or just lookup a person in case you are not using the Get my profile action from step 5.
d. Use the following expression for the Message field.
That is it for the setup of this example.
Happy testing!
Hi Dennis,
I have a problem which can be solved by using the expression you mentioned on top. Because the folder hierarchy in my used case is pretty logical, I would like your suggestion getting it implemented in power automate.
Let’s say I upload invoices to a sharepoint directory named as per Month say July corresponding to Project X (Parent Folder) in country A (Child Folder).
Project X -> Country A -> July
I need to update a sharepoint list, which shows the Project name, country, month, Invoice Number (File name) and correspondingly give the users the option is update the status of the invoice (Paid, Uploaded, Declined)
The problem I have is, to get the columns (Project name, country, month and Invoice number) populated automatically.
It should look something like this:
Project Country Invoice number Status
X A 123 Uploaded
X A 456 Paid
Y A 367 Declined
I have seen the solutions with RESTApi and FSObjType but I am not able to visualize or get steps to make such a flow work.
Could you give me hint how to separate these folder name “string values” for each level and just show it in the list table?
Thanks a lot in advance
Best,
Harman
Hi Dennis
Love this code, however what if i want to go from the other direction, i.e
Documentlibrary\folder1\folder2\folder3\file
Documentlibrary\folder1\folder2\file
Documentlibrary\folder1\folder2\folder 3\folder 4\file
Documentlibrary\folder1\folder2\folder 3\folder 4\folder 5\file
i want to be able to capture to a variable the value of “folder 2” no matter how deep the file is saved.
Thanks
Rhys
Hi Harman,
I would use a replace function on the {Path} property and replace the / by a comma charachter.
After that you can append each item to an array variable and use that to create or update the items in the related list.
Hi Rhys,
In that case I would use an expression with a split on the {Path} property instead with [2] index because it will always be the 3rd item of the split outputs.
split(triggerOutputs()?['body/{Path}'], '/')[2]Hi Dennis,
How about if i need ot retrieve the name of multiple files rather than a single file.
What would that look like?
Thank you for your work.
Can you give us the code for the Slice function. It’s missing from your walk trought.
Thank you.
It is part of the codesnippet for the URI field of step 4c, I have extracted it for you below:
slice(body('Send_an_HTTP_request_to_SharePoint')['ParentUniqueId'], 1, -1)