List query column lookup threshold error

Copper Contributor

Hi all,

(This is for SP Online, not on-site SP)

 

I keep getting this error:

 

"The query cannot be completed because the number of lookup columns it contains exceeds the column lookup threshold."

 

I believe the threshold is 12 for SP Online, and it can't be configured.

 

I execute the query using the GetListItems method.

 

StackOverflow hasn't been much help (I haven't explicitly asked the question on there, though, just from previous questions that have been answered), the only advice to current questions are to reduce the amount of fields in the query (which I can't do), or to make multiple API calls to retrieve the field:value key:value pairs and then merge the data from the payloads - which certainly isn't ideal and I'd like to avoid it if at all possible (unless it's the only way?)

 

Anyone ever had this issue before, and know some way I can edit the query to still return all the necessary field values?

 

Please let me know if you need me to post the query.

 

Thanks!

6 Replies

Hi @SunVolt ,

 

i am afraid that the lookup value threshold of a view is a hard limit that you can not get arround. It was configurable in the on premise Version of SharePoint, but in SharePoint Online you are stuck with maximal 12 lookup columns. 

You can only have 12 lookup columns per view and lookup columns also include all "Managed Metadata Fields" and "Person Fields" (even "CreatedBy" and "ModifiedBy"). There fields are internally also lookups to some hidden lists.

You have three options to deal with more than 12 lookup columns in a view:

1) Remove unnecessary  lookup fields from your view (If you don't explicitly specify a given view, you get the fields from the default view in a query)


2) Create different views with less than 12 lookup columns and merge the results in code.


3) Duplicate some lookup column fields on that list as text columns.

Then on each update of the SharePoint list item have a flow update the value of the text field to the content of the lookup field. 
Then use that field in the view


Best Regards,
Sven

Hi @SvenSieverding ,

Thank you for your timely reply, apologies for not stating it earlier, but the issue isn't to do with the SharePoint UI with Views, but rather within the code itself when programmatically attempting to load all of the columns for the list in a custom app, whereby it's not possible to trim the columns.

Please let me know if you need more clarification,

Thanks again.

Hi @SunVolt,

 

You're welcome.

 

"View" does not necessarily mean UI here but is more like the "select" statement of an SQL query.

 

If you load SharePoint data programmatically you get the fields from the default view per default in your result. But you can change the view fields in your query programmatically if you want to select different fields.

For example you can select the columns  "Title, MyField2 and MyField3" like this,  if you are using a REST API Odata call 

https://<tenant>.sharepoint.com/sites/<site>/_api/web/lists/GetByTitle('List')/items?$Select=Title,MyField2,MyField3


The "GetListItem" Endpoint is a SOAP Endpoint. Here you can define the ViewFields inside of the XML call:

<ViewFields>
   <FieldRef Name='Title' />
   <FieldRef Name='MyField1' />
   <FieldRef Name='MyField2' />
</ViewFields>


If you are using the Client Side Model (CSOM) libraries

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><ViewFields><FieldRef Name='Title'/>" + 
                    "<FieldRef Name='MyField1'/><FieldRef Name='MyField2'/>" + 
                    "</ViewFields></View>";

ListItemCollection collListItem = oList.GetItems(camlQuery);

clientContext.Load(collListItem);

 

 

How exactly are you trying to access the list items? Which programming language are you using? Or are you building a PowerApp?


Best Regards,
Sven


@SvenSieverding 

Hi Sven,

Apologies it has been so long, I haven't worked on this in quite some time.

 

Thanks again for your reply.

 

To answer some of your queries:

Language: C# .NET
I'm not building a PowerApp, no.

 

So I'm currently using the SharePoint CSOM to return SharePoint list items like so:

var oList = context.Web.Lists.GetByTitle(listName);

ListItemCollection collListItem = oList.GetItems(camlQuery);

context.Load(collListItem);
context.ExecuteQueryRetry();

 

The camlQuery of a functioning request:

(NOTE: arbitraryFieldNames are not the same field)

<View>
   <ViewFields>
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
   </ViewFields><Query> 
   <Where>
      <And>
        <Eq>
          <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
          Type='Integer'>21</Value>
        </Eq>
        <Eq>
           <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
           Type='Integer'>25</Value>
        </Eq>
      </And>
   </Where></Query><RowLimit>30</RowLimit>
</View>

 

The camlQuery of a non-functioning request:

(NOTE: arbitraryFieldNames are not the same field)

 

It's the same camlQuery as the functioning request, just a lot more ViewFields, i.e.:

 

<View>
   <ViewFields>
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
   </ViewFields>
   <Query>
      <Where>
      <And>
         <Eq>
            <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
            Type='Integer'>21</Value>
         </Eq>
         <Eq>
            <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
            Type='Integer'>25</Value>
         </Eq>
      </And>
      </Where>
   </Query>
<RowLimit>30</RowLimit>
</View>

 

This query then returns "The query cannot be completed because the number of lookup columns it contains exceeds the column lookup threshold." error.


The solution I'm currently working on is to synchronously execute REST API requests.

 

This works well, however, the oData JSON looks like so:

 

{{
  "odata.metadata": "https://<tenant>.sharepoint.com/sites/<site>/_api/$metadata#SP.ListData.<redacted>&$select=Title,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField",
  "value": [
    {
      "odata.type": "SP.Data.<redacted>",
      "odata.id": "<redacted>",
      "odata.etag": "\"6\"",
      "odata.editLink": "Web/Lists(guid'<redacted>')/Items(42)",
      "Title": "<redacted>",
      "arbitraryField": [],
      "arbitraryField": null,
      "arbitraryField": "<redacted>",
      "arbitraryField": [
        9
      ],
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": true
    }
  ]
}}

 

{{
  "odata.metadata": "https://<tenant>.sharepoint.com/sites/<site>/_api/$metadata#SP.ListData.<redacted>&$select=arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField",
  "value": [
    {
      "odata.type": "SP.Data.<redacted>",
      "odata.id": "<redacted>",
      "odata.etag": "\"6\"",
      "odata.editLink": "Web/Lists(guid'<redacted>')/Items(42)",
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null
    }
  ]
}}

 

And the requests continue until all fields have been returned.

 

Is there any way to merge and then deserialize this JSON back into a SharePoint ListItem object?

 

The reason for this is my custom API method requires a ListItemCollection of which it then converts each SharePoint ListItem in the ListItemCollection into my own custom object, so it would take some work to rewrite everything or create new methods to handle the parsing of this oData JSON.

 

Something I thought of that might work, but would potentially be quite messy and slow:

 

Stick to CSOM, and could I perhaps reduce the camlQuery to say 10-20 fields, execute the request, and once I have the same list item just with different fields, can I somehow merge them all into one list item (as they will all have the same ID)? I'm guessing performance would take a big hit, though, if there are many list items with the same number of fields... There could be anywhere from 1-10 list items to maybe 1000 or so.

 

Please let me know if you require more info on anything.

 

Thanks again.

Hi @SunVolt ,


in that case i would rewrite the mapping module to take in multiple lists with different properties and merge them together in a custom object and pass that to your API. The CSOM entities are lazily loaded and I don't think that you can instantiate them manually.

But perhaps you can get around all of that. You only seem to be loading two elements. If you only need a few items it might be easier to fetch them by ID

List myList = context.Web.Lists.GetByTitle("Listtitle");

ListItem listItem1 = myList.GetItemById(21);
ListItem listItem2 = myList.GetItemById(25);


context.ExecuteQuery();


Try that. You might get arround the view limitation like that. A view and fetching an item directly behave differently.

Best Regards,
Sven

Hi Sven,

Thanks very much for this, it seems to work okay for individual items. I do have to retrieve an arbitrary number of list items at a time, however, but I'll see if I can rustle something up with the GetItemById method. I'll let you know how it goes.

Thanks again.