SharePoint Online, filter more than 5'000 items (list view threshold workaround)?

[Rockietm]

Greetings!

I have a list (SharePoint Online) with and a PowerApps form, where I replaced text field with dropdown, like in this great video:
https://www.youtube.com/watch?v=43ekj5MlNJU
Field changed by Shane Young is like lookup: he deletes text input and replaces with dropdown; dropdown values are requested by power apps.

I added empty value (with "-" in title), and filter related items from same list:

SortByColumns(
Filter(
[@'MyList'],(FieldValue="-" || FieldValue=DataCardValue1.Text)
),
"DocumentNumber", Ascending
)

DataCardValue1.Text is a Key value on my form, so I get only items that have same value in FieldValue that I have in DataCardValue1.Text (or dash, if there is no related items at all).

Here is dropdown example on Edit form:

and here it is in PowerApps editor:

Now I generated 9'000 test items in my text environment and get this error on PowerApps form:

The requested operation in invalid .
The attempted operation is prohibited because it exceeds the list view threshold.

As I understand one of the operations in non-delegable.. How can I refactor this formula to support more then 5'000 items?

Best regards

[TheRobRush]

easiest way around delegation is to make multiple collect passes on something that will narrow the group down. like pass 1 do everything in january 2021, pass 2 february 2021 etc until you have cycled through all of your data, and then work off the collection instead of the splist, and send data back in a patch when complete

[Rockietm]

Hi Rob!

Thank You for Your response! It is very complicated to me, I'm newbie in power apps. My data is not segmented, I have only one field with number inside (not dates.. in fact there are documents and additional agreements, related with these documents. So I filter all additional agreements by document number (agreements related with parent document). And there will be more than 5'000 items after few years)

Can't I convert this to delegable? Sorting could be dropped, only values are important..

SortByColumns(
Filter(
[@'MyList'],(FieldValue="-" || FieldValue=DataCardValue1.Text)
),
"DocumentNumber", Ascending
)

My results are always less then 5'000 - only few documents returned in fact (I'll have 0-5 values in this dropdown..)

Best regards

[TheRobRush]

Check out this post from the past where @mr-dang explains how you can bypass row limitations 

https://powerusers.microsoft.com/t5/PowerApps-Forum/500-item-limit-in-CDM-entity-search-filter-need-... 

alternatively there's also flow solutions

https://www.tachytelic.net/2020/12/power-automate-sharepoint-5000-items-easy/ 

If you want to make your list delegable be sure to follow these guidelines

which you can find for future reference at

PowerApps Delegable Functions & Operations for Sharepoint 

[Rockietm]

Thank You much Rob! I'll save Your image

Strange, indexing FieldValue column helped (List Settings -> "Indexed columns")! If I create additional index in List settings - the error dissappears. If I drop it - appears again.

So we need to make target column indexed 🙂

Best regards

[TheRobRush]

Absolutely, indexing your column that will be referenced will help a lot. Glad to see you were able to resolve that.