The famous "5000 items" issue HOW do we manage?

[Anonymous]

Hello experts, 

I am having a SharePoint list customized powerapp used for storing supplier data. 

Now I am supposed to perform a stress testing of the app. So I've dumped >5000 valid records into the list. 

Ofcourse as per the standard practices, I have created indexing for most used columns. Now a day has passed when I've clicked on "Reindex list" & on another document library "Reindex library". 

Still every time I open the app, it displaying the error on notification: "The requested operation is invalid. Server Response: SupplierDetails failed: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. clientRequestId: 2bda7ea4-2ae1-4fd7-a728-fa6be964b54a serviceRequestId: 2bda7ea4-2ae1-4fd7-a728-fa6be964b54a"

This notification blinks for almost 7-8 times. But my data gets saved properly in the list. Note: I am having many LookUp() commands in my code. 

Please make detailed some suggestions. @R3dKap @PhilD @PowerAddict  

[v-xida-msft]

Hi @Anonymous ,

How many LookUp type columns existed in your SP List? More than 12?

If there are more than 12 LookUp type columns in your SP List, I think your issue is related to it. Currently, there is an known limits with the LookUp columns amount in a SP List -- You could only include 12 LookUp types columns at most in a SP List.

Please check the following articles for more details:

https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59?ocmsassetID=HA102771361&CorrelationId=882180f2-e718-42c6-8dbc-0bd33e9558c9&ui=en-US&rs=en-US&ad=US#_Toc346115854

https://docs.microsoft.com/en-us/archive/blogs/spses/sharepoint-20102013-list-view-lookup-threshold-uncovered

As an fixed solution, you need to decrease the amount of LookUp type columns in your SP List, instead, you could add a Text type column to store these values, then within your canvas app, you could use the LookUp function to retrieve corresponding columns data from the related SP List based on the Text type column value.

Best regards,

[Anonymous]

Hi @v-xida-msft Kris, 

I reviewed my list structure again and deleted the unwanted one lookup. 

So now, including the Created By & Modified By columns total count is 10. 

I refreshed the app and error is still appearing when we load the app or perform screen changes/ patch/ refresh data source. 

Please suggest.  

[Drrickryp]

Hi @Anonymous 

I agree with @v-xida-msft  almost.  A better solution is to create a second list and use it's ID as a numeric field in the first list, say secondlistID.  It is much easier to work with this model for one to many relationships with respect to adding and removing items from the second list and ID columns are indexed by default. The text value can be found by Lookup(secondlist, ID=ThisItem.secondlistID, textvalue).

[Anonymous]

Hi @Drrickryp , 

Many Thanks for suggesting ideal way. But shouldn't this be working fine if we have the number of lookup's under control? 

Because at this stage I am not in position to change list structure and change the powerapps code everywhere. 

Regards.  

[Drrickryp]

Hi @Anonymous 

I believe that is what the architects said about some tower in Italy.

[v-xida-msft]

Hi @Anonymous ,

I agree with @Drrickryp 's thought almost. It is an known limit within SP List itself, there is no way to fix this issue within PowerApps canvas app side.

Actually, PowerApps canvas app inherits data access permission from SP List itself. So if you want to add more than 12 LookUp type columns in your SP List, I afraid that there is no way to achieve your needs currently.

As an alternative solution, you could consider add normal data type (Text, Number) instead of the LookUp type in your SP list to store the value. Then within canvas app, you could use LookUp function to retrieve corresponding value from other SP List based on the stored value.

Best regards,

[Anonymous]

@v-xida-msft I agree that you both agree on each other's suggestions, but points to be clarified here is: 

-  We are having 10 lookup fields, less than limit suggested by Microsoft and I don't want to increase them;  is that also potential to cause error? 
[Point to be noted: there was no such error displayed when I was having 11 lookup columns in the list. It only came when we added more than 5k records.] 

-  Are we not suppose to have lookup fields at all .... if we wish to develop powerapps solution? 

-  Is it mandatory that column indexing be done prior to addition of 5k items? What if we apply it afterwards? 

-  If Indexing done & run, when will it reflect on powerapps?   

-  Does LookUp() function breaks due to 5k items like Filter() function?  

Regards. 

[WarrenBelz]

@Anonymous ,

I will join the party briefly and give my humble opinion.

Unless your users are using a DataSheet SharePoint view for bulk updates (something mine do not get near), there is absolutely no need for Lookup columns as exactly the same function can be achieved in Power Apps connected to the relevant lists. As well as your particular issue, the Delegation, Filtering and writing to the fields make them highly problematic at best, and often roadblocks. I would urge to you consider some nice plain text fields and do all the "grunt" in Power Apps. 

We run a substantial SharePoint backend (50+ lists with up to 40,000 items and/or files in many) and about the same number of Power Apps and have got by fine without them.

[v-xida-msft]

Hi @Anonymous ,

Please consider turn on the "Formula-level error management" option within Advanced Settings of App settings of your app, then re-publish your app, check if the issue is solved.

The index you added in your SP List would improve the retrieve performance when you pull data from your SP List in your canvas app. The amount of data source records would affect the data retrieve and data process speed within canvas app.

If you specified Delegable Filter formula within your canvas app, the 5k items would not affect your canvas app. If the formula you specified in your app faced a Delegation issue, you would face the Delegation limit in your canvas app.

More details about the Delegation issue in PowerApps, please refer to the following link:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Best regards,