Microsoft Flow is a very powerful and useful tool. Flow provides reliable solutions for automating processes and supporting business solutions. Flow can synchronize, collect and create data and much more. When creating a Flow that is connected to a SharePoint List you might encounter one of the limits of Flow. In this blog post I will explain how to work with the lookup column threshold limit.
What is the lookup column threshold limit?
The lookup column threshold limit it the maximum number (12) of lookup columns you can collect using an action or trigger in Flow. When you go over this limit you will 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.”
The threshold limit is set by Microsoft and cannot be changed. The reason this limit exists is to make sure the performance of the Flow is up to standards. You can also encounter this limit in PowerBI, but this post only focus on Flow.
The Lookup columns are
- Lookup
- Managed metadata (single and multi-value)
- People and group (single and multi-value)
- Workflow status
- Created by (people and group)
- Modified by (people and group)
Working around the limit
When using an action to collect data from a SharePoint list you can limit the gathered columns by selecting a view in the action.
- Add the Get Items action.
- Click on Show advanced options.
- Select the view with the required columns (less than 12 lookup columns).
- For certain SharePoint triggers you can also do this.
very interesting article and can add to my knowledge, thank you
You are most welcome.
Thank you!!!! This advice was invaluable!
Hello, I had the same problem and your solution worked. Thank you.
But now I have other problem in my flow which is not working after that. Maybe you have some thoughts what it might be or what to try to resolve it 🙂
Very generally explained – I have a SP list and flow which is triggered by HTTP request, next step is Get Item and other steps below is set permission levels with REST API. All was working before, but now in one step (ensure user) after refreshing Variables value I receive after run an error 400 “The query string \”logonName\” is missing or invalid (API Method – POST, Uri – _api/web/EnsureUser(@v)?@v=’@{variables(‘BusinessOwnerClaim’)}’)
Is it something you can comment on the basis on this information?
Hi Ben,
Its really nice information .
I have one case while we create new list its have by default below columns with look up and look
1. Look up columns ( 8 Look Up)
Item child Count
Folder Child Count
Label setting
Retension label
Retention label Applied
Lable applied by
App Creted By
App Modified By
2. Person Or Group (2)
Created By
Modified By
Total 10 column by default now if I create more 2-3 column as lookup with other list
total 14 (Including by default + my costume created)
I used in power automate its working fine. So i have little confusion Is SP Online not include default lookup columns ?
Regards
Just popping by to say thanks for this! It’s been driving me nuts trying to figure out how to move my SP2010 Workflows to Flow when this error constantly comes up. This has been a lot of help!
So, interestingly enough, I have tried to apply this on a flow, but, even when I only have two columns in the view that I am using, I am getting this error message. I am dumbfounded on how to move past this.
Thank you for this article! It is very helpful.
Once the record is created,I want to update 26 lookup fields. Is there any ways to achieve?
The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold.
clientRequestId: 8c81c2ae-a8cf-4016-b140-3449649baf6f
serviceRequestId: d1434aa1-6010-3000-bbc2-7ee0ca2fba2f