Threshold sharepoint list error

(0) Share

Report

Posted on by anuradhac01

Hi All,

There is an existing Power App built on SharePoint lists as the backend.

We are trying to update some of the Power App screens for some new columns.

Recently, a Lookup column Industry_Partner was added a Dim_Outcomes SharePoint list.

Industry_Partner was added as a lookup column from SharePoint list - Data_Partners - column "Name". There is an index already created on Name column.

The SharePoint list - Data_Partners already has 6471 items.

Now, on my Power apps, when I am trying to refresh the data source to get Industry_Partner column added on the Edit screen, it is not adding the new column to the data source and giving an error -

The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited

I looked at various forums and information available - Mostly it talks about adding index which I did but that did not help. There are articles mentioning about filtering data by using flags such as active flag, etc. In the current scenario, I am not even able to add a column to the datasource in Power App. If anyone has any solutions to this problem, pls suggest.

Please note I am new to Power Apps and SharePoint lists, so any help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Sort by

WarrenBelz 155,535 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @anuradhac01,

Firstly, you have started in the right direction by indexing the Name column in Data_Partners. Have you also indexed Industry_Partner in Dim_Outcomes ?

Was this reply helpful? Yes No
anuradhac01 20 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz - The index is created on Name column in Data_Partners and not on Industry_Partner in Dim_outcomes.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,535 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@anuradhac01,

Then try that first, however if this does not work (which I suspect will happen), the issue (one of many) with Lookup columns is possibly that Power Apps is attempting to read all of the Data_Partners list to resolve the relationship data and is not (like a direct Delegable query to Data_Partners ) accessing the index.

So the next question you need to ask yourself is "Do I really need to use a Lookup column here ?". They are generally a complete waste of time with Power Apps and cause nothing but unwanted and unnecessary complication and restrictions - I never use them.

All you need to do is access the Name column in Data_Partners directly in Power Apps and make Industry_Partner in Dim_Outcomes a Text column.

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Suggested answer

MS.Ragavendar 7,249 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@anuradhac01,

The threshold issue is the most common issue in the sharepoint, it have 2 cases.

Volume of Data

If the item count exceed more than 5000 records this will happen this applicable both to on-premise and online environment of sharepoint, with onpremise we could able to turn off this option from central admin however this option doesn't have in the sharepoint online environment.

Only way is we need to index the columns which we are using in the filter query accordingly.

You can index maximum of 20 columns, once you add all the column which you are using in the filter query, try to reindex the list.

The microsoft job will pick this and index in the next cycle accordingly.

Variety of Data-

Maximum 12 columns can be lookup in the sharepoint list which included (created by and modified by) .

If you creating more than that this error will happen.

Only work around for this you need to change the datatype of the sharepoint column.

🏷️ Please tag me @MS.Ragavendar if you still have any queries related to the solution or issue persists.

✅ Please click Accept as solution if my post helped you solve your issue and help others who will face the similar issue in future.

❤️ Please consider giving it a Like, If the approach was useful in other ways.

Was this reply helpful? Yes No
anuradhac01 20 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz and @MS.Ragavendar.

Looks like the way forward is -
"All you need to do is access the Name column in Data_Partners directly in Power Apps and make Industry_Partner in Dim_Outcomes a Text column."

Is there any special handling I would need to do in Power App if I am going to remove Industry_Partner as lookup in Dim_Outcomes and make it as a text in terms of relationship handling between two tables. I understand I would need to make Industry_Partner as combo box and pick partners from Dim_Partners but anything else??

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,535 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Hi @anuradhac01,

When you are using SharePoint with Power Apps, you need to "unlearn" the concept of server-side data relationships as you understand them - SharePoint is not a relational database. With some very limited exceptions (which do not include your intended usage), Lookup columns do not do anything that cannot be done directly in Power Apps and (as I mentioned) can cause a lot of unwanted issues.

Power Apps can access both lists directly, so making the Items of a Combo Box or Gallery from the second list is simply a matter of referring to it. I will however add another potential issue if you are trying to use a Combo Box - the in-built Search function is limited to your Data Row Limit, so you might want to have a read of this blog of mine on dealing with large lists.

Writing back to the Text field in the first list is simply a matter of having the Data Card Update (if you are using SubmitForm ) as ComboBoxName.Selected.FieldName

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No