SharePoint Online list lookup column limit
Here, we are going to discuss a very common problem statement faced during the runtime of Microsoft Flow: Show The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator. This problem statement occurs whenever Microsoft Flow executes over SharePoint Online list or library where the Lookup column exceeds the limit of 12 columns. The administrator cannot change the threshold limit at SharePoint Online, either at the Tenant or Site Collection level. Why does Microsoft set the magic number 12 as the threshold limit?Each lookup column creates a join with other tables. So, Microsoft decided to set the limit at 12 to avoid performance degradation. Let's get started with detailed resolution. Custom lookup column can be created using the below types.
Lookup column is quite useful but we should be cautious about the threshold limit while designing the schema. I created a list with 13 lookup columns. See the below list for your reference. Problem statement with Get ItemsWhen we run flow to get an item from a List or Library having more than 12 lookup columns, it throws the following error. Resolution with Get ItemsCreate a list or library view with 12 or fewer than 12 lookup columns and set the view at the Get Items action. During a custom View creation, we can ignore the OOTB column or the ones that are not required for this operation. After setting the list View with 12 lookup columns, once I execute the flow, it succeeds. Problem statement with Update ItemA similar issue occurs when we want to update the items using MS Flow. Resolution with Update ItemI created a View with 12 lookup columns and set Limit Column by View with a newly created View. Once I executed the flow with the View which is having 12 lookup columns, it succeeded and updated the item. I hope you have learned something new in this article. Stay tuned for related articles to get more insights and learning. |