MSFlow - Lookup Column Exceeds Threshold Enforced By Administrator

In this article, we will learn how to overcome the lookup threshold limit enforced by an administrator.

Here, we are going to discuss a very common problem statement faced during the runtime of Microsoft Flow:
 
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?

 
Reason
 
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.
  1. Standard Lookup column
  2. People Pickers
  3. Managed Metadata
  4. Workflow
  5. Share With
OOTB Lookup columns
  1. Created by (both for List & Library)
  2. Modified by (both for List & Library)
  3. Name (linked to Document)
  4. Link (Edit to edit item)
  5. Name (linked to Document with edit menu)
  6. Type (icon linked to document)
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.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator

Problem statement with Get Items

 
When we run flow to get an item from a List or Library having more than 12 lookup columns, it throws the following error.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 

Resolution with Get Items

 
Create 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.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 
After setting the list View with 12 lookup columns, once I execute the flow, it succeeds.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 

Problem statement with Update Item

 
A similar issue occurs when we want to update the items using MS Flow.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 

Resolution with Update Item 

I created a View with 12 lookup columns and set “Limit Column by View” with a newly created View.

MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 
Once I executed the flow with the View which is having 12 lookup columns, it succeeded and updated the item.
 
MSFlow - Lookup Columns Exceed The Threshold Enforced By Adminstrator
 
I hope you have learned something new in this article. Stay tuned for related articles to get more insights and learning.