cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rfla
Level 10

SharePoint too many lookup columns cause query failure PowerApps

Hi, I understand that the below link is related to flow. I am having a similar issue for PowerApps when connection to a SP online list.

 

https://powerusers.microsoft.com/t5/Community-Feedback/SharePoint-too-many-lookup-columns-cause-quer...

 

@carlosag replied in that tread this:

 

Based on the information here:
By default the limit is 8 lookups per list.  As you have seen this limit does not apply to farm admins.  You can change the limit for other users in the Resource Throttling settings of the web application in Central Administration.  But making changes to the resource limit can seriously impact performance.

Now I am wondering if the suggestion above also is related to SP Online?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: SharePoint too many lookup columns cause query failure PowerApps

Hi Rfla,

 

Yes, it shares this quality as SharePoint online also has lookup column threshold of 12 for now. But please notice what is said here:

https://blogs.msdn.microsoft.com/spses/2013/12/02/sharepoint-20102013-list-view-lookup-threshold-unc...

"Note : The List view Lookup Threshold has been revised to Default of 12 in SharePoint 2013 , after applying the June 2013 CU update. http://support.microsoft.com/kb/2817414

Any new Web-Application created will have List View Lookup Threshold set to Default =12 , While the values on Existing Web-apps needs to be modified Manually ."

 

This applies to SharePoint online too. Lists created before this timeline in SharePoint online will share the List View Lookup Threshold set to Default =8. Lists created after this timeline in SharePoint online, will have List View Lookup Threshold set to Default =12. 

 

With on-premises SharePoint, these throttles and limits can be changed on the Resource Throttling page in Central Administration. But on SharePoint online, increasing the List View Threshold is not permitted

 

Hope this could help.

 

Regards,

Mona Li

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Community Support Team
Community Support Team

Re: SharePoint too many lookup columns cause query failure PowerApps

Hi Rfla,

 

Yes, it shares this quality as SharePoint online also has lookup column threshold of 12 for now. But please notice what is said here:

https://blogs.msdn.microsoft.com/spses/2013/12/02/sharepoint-20102013-list-view-lookup-threshold-unc...

"Note : The List view Lookup Threshold has been revised to Default of 12 in SharePoint 2013 , after applying the June 2013 CU update. http://support.microsoft.com/kb/2817414

Any new Web-Application created will have List View Lookup Threshold set to Default =12 , While the values on Existing Web-apps needs to be modified Manually ."

 

This applies to SharePoint online too. Lists created before this timeline in SharePoint online will share the List View Lookup Threshold set to Default =8. Lists created after this timeline in SharePoint online, will have List View Lookup Threshold set to Default =12. 

 

With on-premises SharePoint, these throttles and limits can be changed on the Resource Throttling page in Central Administration. But on SharePoint online, increasing the List View Threshold is not permitted

 

Hope this could help.

 

Regards,

Mona Li

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: SharePoint too many lookup columns cause query failure PowerApps

@v-monli-msft Thank you for this information. 

 

We are on SharePoint Online and are planning heavy adoption of PowerApps. Is there a way to set a limit to the number of lookup columns on the SharePoint side? I wouldn't want my users to create an important list that won't play nice with Flow and PowerApps.

Another question. I would like to prune down a giant list to fit within the 12 lookup limit but I am having trouble knowing which types count toward the 12 item limit. what SP list columns count toward the 12 lookup column limit? I have about 20 Choice columns. Is that what is taking me over?

 

Highlighted
Anonymous
Not applicable

Re: SharePoint too many lookup columns cause query failure PowerApps

This is NOT a solution.

 

It is an admission of a product failing, with no workaround or future fix given.

 

Limiting PowerApps to 12 "lookup" columns renders PowerApps useless when, according to MS documentation, the following column types are deemed to be "lookup columns" ...

 

https://blogs.msdn.microsoft.com/spses/2013/12/02/sharepoint-20102013-list-view-lookup-threshold-unc...

 

Quote

1. What classifies as Lookup columns: Standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns , Workflow Status , Created by , Modified by ( people ) are obviously counted as lookup columns .

2. Additionally following columns shows on list view also work as lookup columns , Name ( linked to Document) , Link (Edit to edit item) , Name ( linked to Document with edit menu), type ( icon linked to document)

Unquote

Anonymous
Not applicable

Re: SharePoint too many lookup columns cause query failure PowerApps

Given that this is a NON-solution, may I continue to investigate possible workarounds on this thread?

 

Is it possible to create a PowerApps data source that points to just a view of the SP list, rather than the whole list, and where that view does not exceed the 8/12 limit?

 

If that is possible, then can we have several data sources in one PowerApp, with each one providing data to sepearate BrowseGallery controls, so that none of the queries / data sources exceeds the 8/12 lookup limit?  ould these BrowseGalleries be combined in one screen?  Or would separate screens be required, one for each BrowseGallery?

nickduxfield
Level 8

Re: SharePoint too many lookup columns cause query failure PowerApps

I wanted to share my experiences with Sharepoint Online Lists together with PowerApps and Flow.

Tips:

  1. Stick to simple type columns
  2. Avoid complex type columns (Gotcha)
    1. I learned that there are 4 complex type lookup columns added by default in SharePoint lists (12-4=6). Created Date, Modified Date, Created By People and Group, Modified By People and Group.
    2. I can't confirm, but I also learned that "Workflow Status Columns" are also complex type. Those columns are hyperlinks, which require a display name, and a URL. This is a biggie, as if you are like me, you were trying to do heavy lifting workflows until you could learn to use flow and proper form building with powerapps. Therefore you would have added, removed and re-added the Sharepoint Designer Workflows, which would've created a fresh new "Workflow Status Column" every time. These columns are system, read only and not deletable in the SharePoint Online UI. However you can do so in On Prem. If you use a workflow template of 2010, content type, the columns are un-deletable, howvwer a lists based 2013 template does provide deletable columns. Obsolete workflow status columns add to the lookup column threshold lets say (12-4-4-1), you had 4 re-adds of the same Sharepoint designer workflow and you keep the last 1.
      1. Workaround: Learn how to use powerapps and flow to replace SharePoint designer Workflow as they dont create extra status columns.
      2. Dive into visual studio workflows.
      3. Learn how to remove status columns.
      4. Try sharepoint 2013 list based templates etc.
    3. If you have previous lists that you really want to include in Powerapps and Flow but they have many obsolete workflows attached. Migrate those list items to new list and retain the attributes of created by etc if needed.. (I needed as I based by filters on created by user etc.)
      1. In your lists, replace any complex columns with simple columns.
        1.  Dates could possibly become number fields.
        2. Lookup columns may be converted to single line of text, then in the app convert the control to allowed values and ennumerate or filter a list. The same can be done to choice fields.
        3. People columns can be split into Fist Name Last Name, Display Name, Email and even UserID.
          1. You should be able to filter on USER().Email against the simple fields.
      2. In your powerapp, duplicate app, remove sources , add new lists, change references to sources.
        1. Be careful on your method of migration of items that you dont migrate the status columns too. Or, you have content type workflow assignments that auto add the workflow and statuses on your new list. Disable the associations at the start to per list.

Hope this heps.

Rememeber, adapt, adjust and simplify where possible.

RK
Level: Powered On

Re: SharePoint too many lookup columns cause query failure PowerApps

I went from 9 down to 8 and it is still erroring???

nickduxfield
Level 8

Re: SharePoint too many lookup columns cause query failure PowerApps

Have you had SharePoint designer Workflows on this list?

SharePoint Lists have -4 already

Modified, Modified By

Created, Created By.

 

Show me a screenshot of the List preferably in SharePoint Designer with Hidden fields on and I'll be able to work it out.

Anonymous
Not applicable

Re: SharePoint too many lookup columns cause query failure PowerApps

I've encountered this problem over the past few years many times as our organization was an early adopter of Power BI. Our main data source is a SharePoint Online list that acts as an Order Management System- which means there are several lookup columns, which exceeds this threshold. I've worked around this in the past 4 years by connecting to the OData service behind the SPO list. I don't see Odata as a connector option when creating a Power App. Is this on the roadmap? Pruning our SPO list down to meet the threshold is not an option, as it would take away several core funtinoalities of the system itself.

nickduxfield
Level 8

Re: SharePoint too many lookup columns cause query failure PowerApps

This is much more real world, good on you for sharing.

Have you tried Odata queries directly into PowerApps, Ms Flow definitely supports OData back to a sharepoint list.

At one stage I thought of some intermediate list to try and work with the list that has multiple lookups. After just 30mins I think I realized the futility.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 332 members 2,695 guests
Please welcome our newest community members: