CAML Query Not working more then 115 Nested "OR" Condition

Hanumant Soft - Dev 60 Reputation points
2024-06-24T06:20:36.1866667+00:00

**Below is the api and payload i used to retrive list based on condition..
working with 115 Netsed "OR" Condition.
When need to use more then 115 "OR" Condition returns below Error.

API URL :**

https://<tenant>.sharepoint.com/sites/Corporate-Technology/sandbox/_api/web/lists/getbytitle('Business%20Apps')/GetItems

Response :

{

"error": {

    "code": "-2130575268, Microsoft.SharePoint.SPException",

    "message": "The list item could not be added because the length of the fields was too long.  Please shorten the new entries or remove some fields from this list."

}

}

Payload :

{

"query": {

"ViewXml": "\n        <View>\n            <Query>\n                <Where>\n                    <And>\n                        <Eq>\n                            <FieldRef Name='Category' />\n                            <Value Type='Choice'>Favorites</Value>\n                        </Eq>\n                        <Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera82</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>All Users</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera92</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera58</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera79</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera14</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera66</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera47</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera17</Value></Eq><Or><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera34</Value></Eq><Eq><FieldRef Name='AccessGroup' /><Value Type='LookupMulti'>TestVera44</Value></Eq></Or></Or></Or></Or></Or></Or></Or></Or></Or></Or>\n                    </And>\n                </Where>\n            </Query>\n        </View>"

}

}

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,217 questions
0 comments No comments
{count} votes

Accepted answer
  1. RaytheonXie_MSFT 33,486 Reputation points Microsoft Vendor
    2024-06-24T08:52:27.79+00:00

    Hi @Hanumant Soft - Dev,

    When SharePoint constructs the list forms, all the fields available for the list item are retrieved from the database. Lists with a large number of Lookup columns may result in complex and potentially intensive SQL statements. SharePoint will verify the length of the full SQL statement and will display this error message in case that the maximum allowed length was exceeded:

    "The list item could not be displayed/added because the length of the fields was too long. Please shorten the new entries or remove some fields from this list."

    To address this issue please reduce the number of "OR" Condition


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful