Power Apps cannot use SharePoint lists with more than 12 loocup fields (person or group fields)

When using a SharePoint list with multiple “person or group” columns from Power Apps, the error
“Error retrieving data from the network: The query cannot be completed because the number of lookup columns included exceeds the lookup column threshold.”
occurred, so I looked into the details.

スポンサーリンク

Up to 12 lookup columns can be displayed in a SharePoint list view at one time

Up to 12 lookup type columns (including user or group columns) can be displayed in the SPO list view at the same time.

Additional lookup columns can be added beyond 12, but an error occurs when trying to display them all in the view at once

Power Apps can only handle lists with up to 12 lookup columns.

Due to the aforementioned limitation on the SharePoint side, Power Apps can only use SPO lists with a maximum of 12 lookup columns.

This 12 includes “created by” and “update by” that are added by default, so in effect only 10 lookup columns can be added to the SPO list used from Power Apps.

In this issue, we have examined this restriction in detail.

Advance Preparation: Creating SharePoint Lists

First, create an SPO list with 10 additional user or group columns.
* Including “created by” and “update by”, there are a total of 12 lookup columns in this SPO list.
Connect this list to Power Apps and you are ready to go.
At this point, the listings can be manipulated without problems.

Add 13th lookup column to SharePoint list

Now let’s add an 13th lookup column to the SPO list.
Then an error occurs because the view can only display up to 12 items.
If I hide “created by” and “update by,” they appear fine on the SPO list side.

Using SPO list with 13 lookup columns from Power Apps

When the SPO list is updated to the latest in Power Apps,
The user name is no longer displayed in the gallery, indicating that an error has already occurred behind the scenes. If you try to do a SubmitForm in this state,
Patch function error occurs.
Upon review of the detailed error, “The query cannot be completed because the number of included lookup columns exceeds the lookup column threshold.” is displayed.
Incidentally, when a variable is assigned with UpdateContext, no error is displayed, but a message that is secretly a Network error is displayed in the variable view.

Countermeasures (workarounds)

Because of the requirement on the SharePoint side, it is difficult to address this on the Power Apps side.

The same error occurs when specifying the columns to be retrieved with the ShowColumns function as shown in the image.
* It may be no surprise that the ShowColumns function is a function that cannot be delegated in the first place.

So, the only countermeasure would be to avoid using lookup columns whenever possible and store unique values (email addresses for user or group columns) in other data types, such as strings.

Extra: However, the addition itself is done.

However, I noticed later that the addition of data to the SPO list itself seems to be successful.

So, it seems that the correct understanding is that it can be operated but not acquired (displayed).

However, errors will occur anyway, so it would be better not to use the lookup column too often.

Reference Site

Copied title and URL