cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JOAS_Niels
Helper I
Helper I

"Get Items" including subfolders

Hi guys,

 

I've built a flow that uses the "Get Items" connector to get files from our library. This works, except that it does not include files in subfolders. Is there a property that I can set or a workaround to include the subfolders in the query?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

I managed to find a solution to my problem and wanted to share it.

 

I used a http request to get all files across folders with the following URI/query:

 

/_api/web/lists/getbytitle('Documents JOAS')/items?$select=FileLeafRef,FileRef,Id,Title,Expiratiedatum,Modified,ServerRedirectedEmbedUrl&$filter=(Expiratiedatum ge '@{outputs('Today_+_x_days')}') and (Expiratiedatum lt '@{outputs('x_Days_+_1')}' )

 

This gets all files with an expiration date greater or equal to the variable "Today_+_x_days" and less than the variable "x_Days_+_1".

View solution in original post

19 REPLIES 19
Gristy
Resident Rockstar
Resident Rockstar

Unfortunately get items does not support this.

 

You will need to use Send HTTP Request to SharePoint function to call the REST API.

 

/_api/web/lists/getbytitle('yourlibname')/getitems(query=@v1)?@v1={"ViewXml":"<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Where></Query></View>"}

 

once you have run it successfully copy the output of the action into a parse json action.Annotation 2019-12-02 215807.png

v-bacao-msft
Community Support
Community Support

 

Hi @JOAS_Niels ,

 

Please try this method:

42.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, Gristy! I will try this.

Hi @v-bacao-msft ,

 

Why would this give me all files in the subfolders?

 

Hi @JOAS_Niels ,

 

 

If you just need to subfolders, then you can replace is equal to with is not equal to.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-bacao-msft ,

I think you misunderstand. I would like the query to loop through the files in the root folder, but also in de subdirectories. Now it only gives me the files in root, not those in subdirectories.

Gristy
Resident Rockstar
Resident Rockstar

Hey JOAS_Niels i think bacao is a automated bot, as it has not read your query properly.

 

How did you go with my solution? i tested it and it worked great so you should not have trouble with it.

 

Hi @Gristy ,

 

I wish I was an automated bot, but I am not.😅

I did misunderstand @JOAS_Niels's  needs before.

@Gristy , the method you provided is really great.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Gristy 

 

I'm trying to get a result from the http request, but I'm getting this error:

 

 

Spoiler

"error": {

"code": 502,

"source": "europe-002.azure-apim.net",

"clientRequestId": "c7d94b1e-f693-472d-bd33-5712f50c4d9c",

"message": "BadGateway",

"innerError": {

"status": 502,

"message": "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\r\nclientRequestId: c7d94b1e-f693-472d-bd33-5712f50c4d9c\r\nserviceRequestId: 93261e9f-b047-1000-c5eb-ebb3f9d8b5b0",

"source":

 

I have already created an index on a column, but this hasn't resolved the error yet.

Gristy
Resident Rockstar
Resident Rockstar

Hi,

 

That wont help you, it is because your query is returning more than 5000 items!

 

try change query to this otherwise filter so you get less than 5000

 

<View Scope='RecursiveAll'><RowLimit>5000</RowLimit><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Where></Query></View>

Ah, I kind of came to the same conclusion. But this is whole new territory for me. Can you maybe help me translate/convert this filter query (which I use now in the Get Files step) to a correct URI?

 

@{concat(concat('Expiratiedatum',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('Expiratiedatum',' lt ','''', outputs('x_Days_+_1'),''''))}

 

If that's even possible. I want all files that have an expiration date (Expiratiedatum) that is within a predefined number of days.

The row limit does not make any difference unfortunately. I'm now figuring out how to tailor the query to my needs.

This is too hard for me. I can't find good documentation about this. Any tips or suggestions anyone?

Gristy
Resident Rockstar
Resident Rockstar

my example works perfectly and meets the requirement you set out ignore the other examples as they do not do what you are after but having more than 5000 items you are going to run into problems. Maybe look at splitting your document library's up more and you wont run into these issues.

If I only could include "Expiration date is not null", then I would have a workable solution. Then I wouldn't have more than 5000 records.

I managed to find a solution to my problem and wanted to share it.

 

I used a http request to get all files across folders with the following URI/query:

 

/_api/web/lists/getbytitle('Documents JOAS')/items?$select=FileLeafRef,FileRef,Id,Title,Expiratiedatum,Modified,ServerRedirectedEmbedUrl&$filter=(Expiratiedatum ge '@{outputs('Today_+_x_days')}') and (Expiratiedatum lt '@{outputs('x_Days_+_1')}' )

 

This gets all files with an expiration date greater or equal to the variable "Today_+_x_days" and less than the variable "x_Days_+_1".

View solution in original post

panand99
Impactful Individual
Impactful Individual

Hello @JOAS_Niels 

 

Your api query is looking good. I have a similar requirement where I needs to fetch the files which is created within 24 hours and between 24-48 hours in all files/folders and in sub folders.

 

Could you please help me on this to write a good query.

 

Thanks

Hi @panand99,

 

I'm using the addDays expression in the variables "Today_+_x_days" and "x_Days_+_1" to calculate the dates between which the files expire. Instead of the using a positive number in the this expression, you can also use a negative number, to use it for dates in the past.

 

Example:

addDays(outputs('Today_+_x_days'),1,'yyyy-MM-dd')
(This adds one day to the date of variable "Today_+_x_days")

 

You should replace "Expiratiedatum" with "Created" (date) of course.

 

I hope this helps.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,288)