Share via

Flow Troubleshooting: The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.

  • Article

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.

 

See the below list for your reference.

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.

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.

After setting the list View with 12 lookup columns, once I execute the flow, it succeeds.

Problem statement with Update Item

A similar issue occurs when we want to update the items using MS Flow.

Resolution with Update Item 

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

Once executed the flow with the View which is having 12 lookup columns, it succeeded and updated the item.