cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ginnaskuttery
Level: Powered On

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

4 REPLIES 4
Community Support Team
Community Support Team

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

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.

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


@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:

ginnaskuttery
Level: Powered On

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

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

seadude
Level 10

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

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!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 193 members 5,030 guests
Please welcome our newest community members: