How to manage Multiple OR Condition in CAML Query for Sharepoint List in SPFx

Hanumant Soft - Dev 20 Reputation points
2024-05-30T04:54:23.4033333+00:00

Multiple OR Condition not Working..
const camlQuery = `<View><Query>

    <Where>

       <And>

          <Eq>

             <FieldRef Name='Category' />

             <Value Type='Choice'>${currentCategory}</Value>

          </Eq>

          <Or>

             <Eq>

                <FieldRef Name='AccessGroup' />

                <Value Type='LookupMulti'>All Users</Value>

             </Eq>

             <Eq>

                <FieldRef Name='AccessGroup' />

                <Value Type='LookupMulti'>Test</Value>

             </Eq>

          </Or>

       </And>

    </Where>

 </Query></View>`

Above query is working

Below Query Throw Error :

Only 2 OR condition works..

<View>

<Query>

<Where>

  <And>

    <Eq>

      <FieldRef Name="Category" />

      <Value Type="Choice">Favorites</Value>

    </Eq>

    <Or>

    <Eq>

            <FieldRef Name="AccessGroup" />

            <Value Type="LookupMulti">TestVera82</Value>

     </Eq>

     <Eq>

            <FieldRef Name="AccessGroup" />

            <Value Type="LookupMulti">All Users</Value>

     </Eq>

     <Eq>

            <FieldRef Name="AccessGroup" />

            <Value Type="LookupMulti">TestVera92</Value>

     </Eq>

     <Eq>

            <FieldRef Name="AccessGroup" />

            <Value Type="LookupMulti">TestVera58</Value>

     </Eq>

     <Eq>

            <FieldRef Name="AccessGroup" />

            <Value Type="LookupMulti">TestVera79</Value>

     </Eq>

     

    </Or>

  </And>

</Where>

</Query>

</View>

Error : {

"code": "-2147024809, System.ArgumentException",

"message": "Value does not fall within the expected range."

}

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

2 answers

Sort by: Most helpful
  1. RaytheonXie_MSFT 32,476 Reputation points Microsoft Vendor
    2024-05-30T06:36:22.67+00:00

    Hi @Hanumant Soft - Dev ,

    Since you are not allowed to put more than two conditions in one condition group (And | Or) you have to create an extra nested group (MSDN). The expression looks like this:

    <Or>
        A
        <Or>
            B		
    		<Or>
    			C
    			D
    		</Or>
        </Or>
    </Or>
    

    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.


  2. Hanumant Soft - Dev 20 Reputation points
    2024-06-10T13:23:44.1766667+00:00

    I have used below code for the CAML query but empty result returns..actually having values to return based on condition :
    below code generate following CAML query.
    we have data for "Favorite" Category anf for "All Users" AccessGroup
    how to fix it

    <View>

    <Query>
    
    	<Where>
    
    		<And>
    
    			<Eq>
    
    				<FieldRef Name='Category' />
    
    				<Value Type='Choice'>Favorite</Value>
    
    			</Eq>
    
    			<Or>
    
    				<Or>
    
    					<Eq>
    
    						<FieldRef Name='AccessGroup' />
    
    						<Value Type='LookupMulti'>All Users</Value>
    
    					</Eq>
    
    					<Eq>
    
    						<FieldRef Name='AccessGroup' />
    
    						<Value Type='LookupMulti'>ArgosPlus</Value>
    
    					</Eq>
    
    				</Or>
    
    				<Or>
    
    					<Eq>
    
    						<FieldRef Name='AccessGroup' />
    
    						<Value Type='LookupMulti'>grp1</Value>
    
    					</Eq>
    
    					<Eq>
    
    						<FieldRef Name='AccessGroup' />
    
    						<Value Type='LookupMulti'>grp2</Value>
    
    					</Eq>
    
    				</Or>
    
    			</Or>
    
    		</And>
    
    	</Where>
    
    </Query>
    

    </View>

     async buildCamlQuery(values: string[], category: string) {
        const fieldRefXml = (value: string) => `
          <Eq>
            <FieldRef Name='AccessGroup' />
            <Value Type='LookupMulti'>${value}</Value>
          </Eq>`;
    
        const wrapWithOr = (conditions: string[]) => {
          if (conditions.length === 1) {
            return conditions[0];
          }
          return `
              <Or>
                ${conditions.shift()}
                ${wrapWithOr(conditions)}
              </Or>`;
        };
    
        const buildOrConditions = (values: string[]): string => {
          const conditions = values.map(value => fieldRefXml(value));
    
          const result: string[] = [];
          while (conditions.length > 0) {
            const batch = conditions.splice(0, 2);
            result.push(wrapWithOr(batch));
          }
    
          if (result.length === 1) {
            return result[0];
          }
    
          return wrapWithOr(result);
        };
    
        const orConditions = buildOrConditions(values);
    
        return `
          <View>
            <Query>
              <Where>
                <And>
                  <Eq>
                    <FieldRef Name='Category' />
                    <Value Type='Choice'>${category}</Value>
                  </Eq>
                  ${orConditions}
                </And>
              </Where>
            </Query>
          </View>`;
      }
    
      async filterUsingGroup(category) {
    
    
        //const userGroups = await this.getUserGroups();
        const userGroups = ['All Users', 'ArgosPlus', 'grp1', 'grp2'
          //,     'grp3', 'grp4', 'grp5', 'grp6', 'grp7'
        ]
    
    
        userGroups.forEach(element => {
          console.log(element.replace("'", "").replace("'", ""))
        });
        let camlQuery = await this.buildCamlQuery(
          userGroups
    
          //['All Users', 'ArgosPlus']
          , category);
        console.log("MERGED : ", camlQuery)
    
    
        const body = {
          query: {
            ViewXml: camlQuery,
          },
        };
    
        const options = {
          body: JSON.stringify(body),
        };
        const tenantUrl = common.getTenantUrl(
          this.props.context.pageContext.web.absoluteUrl
        );
        await this.props.context.spHttpClient
          .post(
            `${tenantUrl}/sites/Corporate-Technology/sandbox/_api/web/lists/getbytitle('${UrlConstant.businessApp.BUSINESS_APP_LIST}')/GetItems`,
            SPHttpClient.configurations.v1,
            options
          )
          .then(async (response: SPHttpClientResponse) => {
            console.log("CAML 1 : ", await response.json());
            //return response.json();
          });
    
    
      }