Is power app canvase support showing more than 5000 items from sharepoint list

(0) Share

Report

Posted on by johnjohn123

3,506

We have a SharePoint list which contains 42,864 ++ items:-

and we have created a built-in power app which show a canvas listing all items, as follow:-

So my question is whether the canvas will display all the 42000 items or will only show max of 5000? and will the search and sort works on all the 42000 items or only on 5000 items?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Neither answer is correct. By default Power Apps will only show 500 records from a data source. That limit can be expanded to 2,000. To show records from a larger data source you need to use a delegable function that can select a subset of records on the server. Filter() is a delegable function when used with the right data types and operators.

But even if you use a delegable function you won't see all 42,000 records in a gallery. A Gallery will show the first 100 items a data set that is returned. When you scroll through that 100 it will add the next 100, showing 200 and so on. So you would have to scroll down through the entire list 50 times to show 5,000 items. you would have to scroll down through 100 records manually 420 times to show all 42000. Accessing that many records in a gallery isn't feasible.

But if you use an appropriate delegable function you can select a meaningful subset of records from the entire 42000 record data set and display them.

Delegation - Understand delegation in a canvas app - Power Apps | Microsoft Docs

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

Hi @johnjohn123 ,

There are two "limits" that need to be considered when using SharePoint as a data source for Power Apps. The first is the Delegation limit (maximum 2,000) where all queries need to be Delegable to return a result from the entire record set. The second (which you have alluded to) is the 5,000 query limit, where any field queried has to be a indexed column in SharePoint.

Search however is not Delegable and will not work unless you collect all 42k records - it can be done, but will take quite a while and probably needs an additional field for a unique numeric identifier other than ID. I have the process in this blog section.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@WarrenBelzso if we have a big SharePoint list, then it is not feasible to use Power app canvase to show and search all items?

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@Pstork1 ,

Thanks for the expansion of my post - yes you will get 100 at a time and it takes a long time to scroll through them - though I am not sure what was incorrect in my summary. Also the 5,000 item limit (which was the question) needs the queried column to be indexed in SharePoint and is something I need to deal with at times.

@johnjohn123 ,

To answer your question, you can also collect the entire list (though 42k will be a cup of coffee waiting). I do one of about 14k and the Search in the combo box works quite well (but you will need the extra numeric identifier column)

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

Actually, my post was about 5 minutes before yours based on the time stamp. But the important thing is that the question gets answered

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1& @WarrenBelz so by default the search field inside the power app canvas:-

will not search the underlying 42,000 items? unlike the list search inside a SharePoint list?
second question , as users might not need to see all the 42000 items, but they would expect that when they do a search using the search field inside the power app to get all the related results from the 42000 items.. so can we force the search to actually search all the 42K items?

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (2)

Report

@johnjohn123 ,

Please read my blog on Delegation - Search() is not Delegable. The only way (as I mentioned) which is really not a practical solution if you want to search all 42k records is make a big collection - it would take some time to run and the biggest one I have ever done (to test) is about 20k which took about 30 seconds. To do this however you need a unique numeric identifier other the the ID in the data.

The only thing I can suggest is if you can get a sub-set of the data less than 2,000 items using a delegable filter or if you are looking for a Text field use StartsWith() in for the search box contents, which is Delegable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@WarrenBelzOK so let say i use StartsWith() which is delegable ,, and i have more than 5,000 items matches the StartsWith() condition .. so what will happen in this case? will i face the SharePoint threshold error?

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@johnjohn123 ,

As long as the field you are filtering with StartsWith() is an indexed column in SharePoint, it should work, but I am not sure how you are going to display 5k records effectively.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No