Flow: Working around the lookup column threshold limit

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.
Share

9 Replies to “Flow: Working around the lookup column threshold limit”

  1. 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?

  2. 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

  3. 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!

  4. 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.

  5. Once the record is created,I want to update 26 lookup fields. Is there any ways to achieve?

Leave a Reply to Kadi Cancel reply

Your email address will not be published. Required fields are marked *