List query column lookup threshold error

Bronze Contributor

Jan 03, 2023

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

SunVolt

Copper Contributor

Aug 30, 2023

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.

SvenSieverding
Bronze Contributor
Aug 30, 2023
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
- SunVolt
  Copper Contributor
  Sep 11, 2023
  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.

Forum Discussion

List query column lookup threshold error

Resources