Sharepoint List with more than 5000 entries and delegation issue

[avi2017]

Hi Gurus

I have a sharepoint list ( LIST1) with 5000 rows . My initial screen have a gallery from Sharepoint LIST1 along with a search text on field Name . 

Issue : When I  search for Name in gallery screen , some Names wont show up even though Name is in LIST1. Delegation issue . Please let me know how to get all Name to the gallery 

LIST1 ( Sharepoint List - Not all rows are shown . Total number or rows 5000 + ) 

Power Apps Galley Screen Shot 

[WarrenBelz]

Hi @avi2017 ,

Firstly, Name is not a good title for a field - it is a Reserved Word in Power Apps, however providing it is a text field, as long as it is indexed in SharePoint, to 5,000 item limit should not be an issue. Delegation is about something else (500-2,000 items on non-delegable queries).

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.

[v-qiaqi-msft · Accepted solution]

Hi@avi2017,

Based on the issue that you mentioned, do you want to use the Search() function to filter the Gallery based on the 'Name' column?

Could you please share a bit more about the scenario, which formula you use to search the Name field, the Search(), or the StartsWith()?

Actually, the Search() function is delegable in PowerApps, but it is nondelegable in the SharePoint connector. 

Power Apps delegable functions and operations for SharePoint

The following Power Apps operations, for a given data type, may be delegated to SharePoint for processing (rather than processing locally within Power Apps).

POWER APPS DELEGABLE FUNCTIONS AND OPERATIONS FOR SHAREPOINTItem Number Text Boolean DateTime Complex [1]

Filter	Yes	Yes	Yes	Yes	Yes
Sort	Yes	Yes	Yes	Yes	No
SortByColumns	Yes	Yes	Yes	Yes	No
Lookup	Yes	Yes	Yes	Yes	Yes
=	Yes	Yes	Yes	Yes	Yes
<, <=,<>, >, >=	Yes [2]	No	No	Yes	Yes
StartsWith	-	Yes	-	-	Yes
IsBlank	-	No [3]	-	-	No

You can see that the Search() function even not exist in this list. So this explains why some Names won't show up even though Name is in LIST1, because the Search()function is nondelegable.

Since you have successfully searched for some names, I assume that the 'Name' column is a Text type.

If you want to show up all the names in the Gallery, I think the StartsWith() could be a better choice.

I have a test on my side, please take a try as below:

Set the Items property of the Gallery as below:

Filter(SPlist,StartsWith(Name,TextInput3.Text))

Note: The Filter() and the StartsWith() could be delegable with the SP list. 

As an alternative solution, you could consider the collection. You could transfer your SP list data into a collection and then use your previous formula. Please try to check if it works.

Hope it could help.

Best Regards,

Qi

[avi2017]

Thank you @v-qiaqi-msft 

Tried this and looks like its working .

SortByColumns(Filter('LIST1',StartsWith(NAME,NameSearch_1.Text)),"DATE",Ascending)

Question : My list will expand to more than 10,000 in few months .Will this formula work when the number of rows in list doubles?

[v-qiaqi-msft]

Hi@avi2017,

My bad. I don't explain to you that the SharePoint protects server performance by query throttling lists above 5,000 records. It means that you could only query 5,000 records from SharePoint.

If you are dealing with a list that has more than 5,000 items in SharePoint, you should make sure that you add an index to the column that you want to sort by.

From "List Settings" click on the “Indexed columns” link under the Columns section.

Note that you can create up to 20 indexes in a list.

Click to create a new index.

If you have a particularly large list you can create a second column to create a compound index. Compound indexes can enable you to speed up queries across related values. However, Indexing more than one column may not get the boost in performance you are expecting when using more than one indexed column in a query.

As an option, you could consider creating views for the index column. Add indexes to the columns that you want to sort by, or filter by.

Now you could do a StartsWith() query up to 10,000  items.

Further, only the following data type we support to create an index:

For more details you could check the following articles:

https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44...

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

Hope it could help.

Best Regards,

Qi