cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ginnaskuttery
Frequent Visitor

Is there any way to read more than 5000 items from sharepoint list in powerapps

Is there any way to read more than 5000 items from sharepoint list.

 

When i tried using delegate function 'Startswith' on a text field it was happening more than 5000nitems were loading ina lazy load manner, but now working for other delegate functions like eq,In, contains..etc. 

 

Also can we achive the same using MS flow and REST API?

 

Thanks

Ginnas

5 REPLIES 5
v-xida-msft
Community Support
Community Support

Hi @ginnaskuttery ,

Could you please share a bit more about your scenario?

Do you want to retrieve/read more than 5000 items from your SP list within your app?

 

If you want to retrieve/read more than 5000 items from your SP list within your app, please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/pt-br/blog/powerapps-now-supports-working-with-more-than-256-items-i...

 

Just as above blog mentioned, if you want to filter your SP List which has more than 5000 items, you need to add a Index to corresponding column (the column you want to filter by) in your SP list.

More details about adding a Index to a column in SP list, please check the following article:

https://support.office.com/en-ie/article/add-an-index-to-a-sharepoint-column-f3f00554-b7dc-44d1-a2ed...

 

In addition, if you want to read more than 5000 items from sharepoint list using MS Flow and REST API, I afraid that there is no way to achieve your needs in Microsoft currently.

Please check and see if the following blog would help in your scenario:

https://blogs.msdn.microsoft.com/svarukala/2017/08/08/iterate-and-get-all-items-from-a-sharepoint-la...

 

If you would like this feature to be improved, please consider submit an idea to PowerApps Ideas Fourm:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

Best regards,

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


@ginnaskuttery wrote:

Is there any way to read more than 5000 items from sharepoint list.

 

When i tried using delegate function 'Startswith' on a text field it was happening more than 5000nitems were loading ina lazy load manner, but now working for other delegate functions like eq,In, contains..etc. 

 

Also can we achive the same using MS flow and REST API?

 

Thanks

Ginnas


They are likely a number of ways you could perhaps use insofar using Flow with a potentially a number of REST APIs.

 

For example, per the Working with lists and list items with REST site, a SharePoint query such as the following should work using eq to refine the information returned using the $filter query string parameter for the API call:

http://site url/_api/web/lists/getbytitle('ListName')/Items?&$filter=LookupColumnId eq 1

 

Equally the SharePoint RenderListDataAsStream API equally can be leveraged to return all sorts of data from SharePoint lists, by way of example as I have illistratrated in a couple of my own blog posts:

 

Furthermore, with v2.0 of the Microsoft Graph APIs, and number of SharePoint endpoints have been implemented that enable you to retrieve list item info as has been detailed here by Chaks from Microsoft, following which I equally posted a follow-up blog demostrating a sample app implemented levegaing these new APIs:

Hi

Thanks for the response.

 

The scenario which we are trying is we have site directory list with around 20,000 items. We are trying to create a PowerApp to search this site directory. User can search for any site names in a text box based on the input once enter the search button result should show as search result in below Gallery.

 

Currently we are unable to achieve that with Delegate filtering functions. Also MS Flow with REST API option also not returing results in case the items limit is more than 5000.

 

Thanks

Ginnas

I haven't tried with the Sharepoint API, but with large-ish SQL Tables, I sometimes do the following. Once the data is in a Collection, there are no Delegation issues.

Also consider caching the 20k records on-device for better performance. @PaulD1 's most recent YouTube video has AWESOME insights on how to do this type of thing (I've yet to implement, but can't wait to try!).

 

Concurrent(
    ClearCollect(col1, Filter('[dbo].[bigSqltable]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[bigSqltable]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[bigSqltable]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[bigSqltable]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[bigSqltable]', recordID >= 8001 && recordID <= 10000)),
    ClearCollect(col6, Filter('[dbo].[bigSqltable]', recordID >= 10001 && recordID <= 12000)),
    ClearCollect(col7, Filter('[dbo].[bigSqltable]', recordID >= 12001 && recordID <= 14000)),
    ClearCollect(col8, Filter('[dbo].[bigSqltable]', recordID >= 14001 && recordID <= 16000)),
    ClearCollect(col9, Filter('[dbo].[bigSqltable]', recordID >= 16001 && recordID <= 18000)),
    ClearCollect(col10, Filter('[dbo].[bigSqltable]', recordID >= 18001 && recordID <= 20000)),
    ClearCollect(col11, Filter('[dbo].[bigSqltable]', recordID >= 20001 && recordID <= 22000)),
    ClearCollect(col12, Filter('[dbo].[bigSqltable]', recordID >= 22001 && recordID <= 24000)),
    ClearCollect(col13, Filter('[dbo].[bigSqltable]', recordID >= 24001 && recordID <= 26000)),
    ClearCollect(col14, Filter('[dbo].[bigSqltable]', recordID >= 26001 && recordID <= 28000)),
    ClearCollect(col15, Filter('[dbo].[bigSqltable]', recordID >= 28001 && recordID <= 30000)),
    ClearCollect(col16, Filter('[dbo].[bigSqltable]', recordID >= 30001 && recordID <= 32000)),
    ClearCollect(col17, Filter('[dbo].[bigSqltable]', recordID >= 32001 && recordID <= 34000)),
    ClearCollect(col18, Filter('[dbo].[bigSqltable]', recordID >= 34001 && recordID <= 35000))
);
ClearCollect(colCombined, 
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18
)

 

Might work in your case?

See post here too.

Good luck!

chaide345
New Member

Can you please explain what is going on with this project and process 

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 (3,365)