Handling large number of LookUp columns from Sharepoint list in Power Apps

(1) Share

Report

Posted on by KrishR

431

Hello Power Users,

I have a Sharepoint list with more than 25 lookup columns and would like to display them in canvas app. I get the error message as "The requested operation is invalid. The query cannot be completed because the number of look up columns it contains exceeds the lookup column threshold enforced by the administrator".

Can anyone please help me on how to handle in such cases or suggest the best way to implement the functionality.

Thank you so much in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report

@KrishR

The SharePoint Online Lookup Column Threshold is 12. Unfortunately this cannot be changed in SP Online to my knowledge, I believe this may be raised only in the on-premise version of SharePoint if I'm correct.

However, I believe this particular threshold applies to a View, not the whole List.

Try to create a view that has a subset of Lookup columns - try having it with just 12 or fewer Lookup Columns in it that you need to use in the Canvas App. Then, use only this specific view in Power Apps Canvas App (the one you just made that has the subset of Lookup columns), and it may work

Even if the SP List itself may have more than 12 Lookup Columns, you may be fine.

In case you also have another issue with the above workaround and also still need to work with more than 12 Lookup Columns at once in the Power Apps Canvas App, you may try creating two views, each with 12 or fewer Lookup columns, and using both views somehow separately on the same data source. This may enable you to use more than 12 Lookup columns at once.

However, first check if you even need more than 12 Lookup columns at once in the app - if not, a single view with only the 12 or fewer Lookup columns you really need to use in the Canvas App may suffice.

See if it helps @KrishR

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report

@KrishR

I checked further and it is not currently possible to select a specific SharePoint List view in Power Apps. You can try to use Filter function or try to use ShowColumns and specify only a few particular columns, choosing fewer than 12 Lookup columns, however since the data source already contains more than 12 Lookup columns I don't think anything like that works.

Unfortunately I am not sure of any other way than to split the lists themselves into multiple lists with 12 or fewer Lookup columns and using multiple Lists as data sources in your Canvas App.

Was this reply helpful? Yes No
OlegK 47 on at

Like (0)

Report

If I have 10 lookup / person fields - NO issues.

After adding the 11th lookup / person field - there are issues with getting data in Power Apps.

With in the SharePoint list - NO issues, working fine having ALL the fields in any Views.

NOTE (as I understood): The limit is 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by")

Found - "Another option is to use indexed columns and filtering to keep the view size for the list to 5,000 items or less."

If you have MORE then 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by"), INDEXING doesn't help ! - verified.

SO, the only way to avoid errors in Power platform - to have LESS than 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by").

1 people found this reply helpful.

Was this reply helpful? Yes No
OlegK 47 on at

Like (0)

Report

"I believe this may be raised only in the on-premise version of SharePoint if I'm correct" - YES, with the note:

"Warning: You may burden your SharePoint server by increasing these default threshold values as it increases CPU, Memory loads on the server. So, be careful!"

Was this reply helpful? Yes No