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?
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:
You could try to increase lookup column limit from 8 refer to link below:
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?
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?
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?
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.
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.
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!
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.
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!