The attempted operation is prohibited because it exceeds the list view threshold

(0) Share

Report

Posted on by bradt30h

134

Hi Community,

I have indexed my SharePoint database, applied filter to respective indexed column. Everything is fine in Power Apps (no formula, delegation problem in my filter function). However, when I launch the app, I can still see pop up message below and yet application is working.

Any ideas how to get rid of this error message?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

WarrenBelz 153,051 Most Valuable Professional on at

Like (2)

Report

Hi @bradt30h ,

This message is almost always caused by a filter/lookup or sort in power apps being executed on an unindexed column in SharePoint when you have in excess of 5,000 items in the list. Please have a look at all functions initiated at App OnStart and screen OnVisble of your landing screen and ensure all relevant columns are indexed.

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
bradt30h 134 on at

Like (0)

Report

Hey @WarrenBelz ,

Thanks for your hints. I checked multiple times, apparently all column are indexed except SP default ID column. Could this be the culprit? I remember read an article from somewhere explains this will not affect as it is indexed by default.

Was this reply helpful? Yes No
bradt30h 134 on at

Like (0)

Report

@WarrenBelz , I noticed that my current list (primary) that showed error has less than 5k rows of data but it has one field which is looking up to another list (secondary) which has more than 5k rows. I am suspecting this is the issue here.
Attached the app monitor image that showing lookup error.

In the secondary list, I have added index column for respective looked up column. But the error msg remain...

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,051 Most Valuable Professional on at

Like (0)

Report

Hi @bradt30h ,

Yes that certainly could be the issue - Lookup columns are not "good friends" with Power Apps (I do not use them at all). You could try indexing the column in the other list that is being looked up, but a better idea is to get rid of the lookup column and perform the function in Power Apps. I have a blog on data structure that may be of interest to you.

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.

Visit my blog Practical Power Apps

1 people found this reply helpful.

Was this reply helpful? Yes No
bradt30h 134 on at

Like (0)

Report

Too bad, solution is already built. I am not keen to change the existing property I have. As the secondary list will always grow I am thinking to use LastN formula for last 5000 records. Not sure if this can remove the error?
I haven't had a great time in writing the formula...

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

Like (1)

Report

HI @bradt30h ,

LastN is not Delegable, so that limits you even further. You can technically index Lookup columns (I assume you have done this already), but beyond indexing at "both ends" there is nothing further I believe you can do. As I mentioned, I have a (long) list of reasons not to use Lookup columns unless they will do something not possible otherwise and if so, I suggest keeping a "shadow" text column with the value - then you can limit your Delegation and query limitations.

Was this reply helpful? Yes No
bradt30h 134 on at

Like (0)

Report

Appreciate your great help Warren,
The reason why im using lookup column is to make relational database between list. Looks like this is not a good practice moving forward. Am I correct to say that you did it using text and number column to make hardcoded relation between SP list?

Was this reply helpful? Yes No