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

ginnaskuttery · ‎04-10-2019

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

v-xida-msft · ‎04-10-2019

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 · ‎04-11-2019

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

Leveraging Graph APIs in PowerApps & Flow – Photos

ginnaskuttery · ‎04-19-2019

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 · ‎04-19-2019

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!