When you are using Microsoft Flow and you want to retrieve an item from a SharePoint list which has more than 12 lookup columns, you’ll receive the following error:

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator

In this blogpost, I will describe a workaround which will allow you to query a list with more than 12 lookup columns.

Background information

First of all, why do we get this error? Actually, it is not SharePoint who is causing this, but SQL Server. Each lookup requires a join with another table within SQL Server so Microsoft decided to maximize the number of lookups that can be made to avoid performance degradation.
The limit was set to 8, but since the SharePoint 2013 Post June 2013 CU update, Microsoft has increased this limit to 12.

So, which columns are defined as lookup columns?

The following column types are defined as lookup columns:

• Standard lookup columns
• Managed metadata columns
• People and groups columns (These also include the Created by and Modified by fields, see below!)
• Workflow Status columns
• System generated fields
  • Created by
  • Modified by
  • Name (linked to Document)
  • Link (Edit to edit item)
  • Name (linked to Document with edit menu)
  • Type (icon linked to document)

Where does this error occur?

Since the error is generated from SharePoint, all related business applications will be affected by this limit, which are:

• SharePoint views

Well, that’s a nice error, aint it?..

• Microsoft Flow

• PowerApps

• etc.

Workaround

For SharePoint views, the solution is simple: just modify your view so that there will be no more than 12 lookup colums inside your view.

For Microsoft Flow, to get an item from a SharePoint list which has more then 12 lookup columns, you have to create a view in your SharePoint list that has a maximum of 11 lookup columns.

Why not 12?
I’m not sure, but your Flow will fail with the same error if you use 12 lookup columns in your view. I think it has something to do with Microsoft Flow querying the list and it’s using some sort of lookup to do that which also sums up to your lookup columns limit.

After your view is created, go to your Flow and add the Get Item action. Fill in your Site Address, List Name and Id.
If you keep your action like this, your Flow will fail.

You have to expand ‘Show advanced options’ in your Get item action and select the view you just created in the ‘Limit Columns by View’ field.

Save your Flow and run it. You will see it will run successfully!

You can use this option in the ‘For a selected item’ action as well to run a Flow on a list that has more than 12 lookup columns itself.

In my case, I used a list with no additional lookup columns that queried to another list which had more than 12 lookup columns, so I didn’t have to alter my ‘For a selected item’ action.

For PowerApps, I haven’t found a way to bypass the limit (yet) unfortunately.