cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anabananei
Level: Powered On

Query cannot be completed due to number of lookup columns

Hello!


I created a list of projects proposal that requires an approval with 10 columns for my team. Once an item is created it will send an approval status for that. Once I approve the project, it will auomatically create an item in our organization wide SharePoint list of projects. This list has more than 10 columns. The flow fails due to "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator".

 

I've been searching for an answer around the net but I found no solutions.

 

I tried to limit the dynamic entries into 6 in the "create item" action but the flow still fails. Is it because the second list has more than 10 columns that's why "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator" is prompting?

 

Please advise.


Thanks,
Ana

10 REPLIES 10
Community Support Team
Community Support Team

Re: Query cannot be completed due to number of lookup columns

Hi @anabananei,

 

I have made a test on my side,by default the limit of number of lookup columns is 8 per list, as is refered in the link below:

https://social.technet.microsoft.com/Forums/ie/en-US/ba240e0c-2435-4215-bb70-41b374634172/the-query-...

 

You could try to increase lookup column limit from 8 refer to link below:

https://sharepoint.stackexchange.com/questions/132050/how-to-increase-lookup-column-limit-from-8

 

Best regards,

Alice

anabananei
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Hi @v-yuazh-msft,

 

Thank you for your answer. If my understanding is correct regarding lookup columns, these are columns such as persons/groups, choice, workflow status, etc. that has a lookup function. In my situtation, the list itself has a total of 35 columns--combination of single line of text, person/groups, choice, date. Out of 35 columns, there are 15 lookup columns (persons/groups, choice). What confuses me is that flow is filling up not more that 8 lookup columns and the flow still fails. Does flow fail regardless of the amount of lookup columns you fill in if the tootal number of lookup columns exceeds the threshold amount of 8?

 

Thank you,

Ana

anabananei
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Hi @v-yuazh-msft,

 

I would also like to ask if there is a repercussion if the flow fails in the test mode but it still gets the job done. In my case, the flow fails when I run it in test mode but it still creates the item in the second list. Its just that the succeeding actions after the "create an item" will not proceed. I don't have any actions to add after the "create an item" action. 

 

Is this bad practice or an acceptable one?

 

Thanks,

Ana

jduga
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Hello. @anabananei and @v-yuazh-msft -

 

I am in a similiar situation. My flow is saying "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold..." even though the view only has 6 columns and I have other flows that work fine even though they go look at views with more than 6 columns. But, anyways, my flow says it fails, but it updates the item. It wasn't completing any steps under that one, so I just re-ordered them so that the step where it fails ("Update Item") is last. My flow shows it fails each time, but it actually completes every step in the flow. I've been working on this for the past 2 hours and I just don't feel like messing with it anymore.

 

What would be the issue with letting the flow mark itself as "Failed" besides the annoying pop-ups that say the flow failed? And if this is not good practice, then what should I do instead?

anabananei
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Hello @jduga ,

 

Can you post the screenshot of your flow? I managed to fix mine by limiting the lookup column to be updated by dynamic values to 6 or 8.

 

Thanks!

jduga
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

1.png2.png3.png4.png

 

Here it is.

anabananei
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Hello @jduga ,

 

The reason why Flow fails is that there are too many lookup columns you've been adding dynamic content (at least in my case and observation). In the update item, Is there a lookup column in your list that does not change but you still add dynamic content because the field is required?

 

Here is the workaroud I made:

 

I changed the column property of the lookup column to Required=NO so the dynamic content I have to add in the update action will be reduced (just make sur that data from that column is static from the time the item was created). The next challenge would be the form itself. Since we set the required value of the column to NO, then its expected that it will not be required when someone fills up a new item.

 

What I did was I customized the form using Powerapps and set each field to required (In Powerapps only) but in flow, its still not required. This ensures the person creating a new item fill out the required field and notifies the user if he/she missed to fill out something, but in the backend of your flow, that field is is not required. From there you can limit the number of dynamic content being added in your lookup columns and hopefully it runs successfully.

 

Let me know if you need further clarification.

 

Thanks! 

jduga
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

Thank you! When we mentioned lookup columns, I was simply looking at the number of columns in the view. I didn't take into consideration the number of them within the form being the issue. PowerApps is not turned on in my company yet, so I can't use this workaround yet, but at least I'm aware of it. Thank you for your help!

anabananei
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

It turns out, the root cause would be the number of dynamic content you are adding in the update action. Can you try switching the property to Required=NO, then try to reduce the number of dynamic content in your flow. To just validate my assumption.

jduga
Level: Powered On

Re: Query cannot be completed due to number of lookup columns

I'll work on this!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 210 members 6,205 guests
Recent signins:
Please welcome our newest community members: