1

I created a CAML query that I need to use as a workaround for REST API and the 5k threshold limit: However when I was testing it using the SharePoint CAML Query Helper Online, it returns the correct result which is one record.

But when I use it in my code, it returns a lot of records that do not fit the filters I have used.

enter image description here

This is my CAML query which returns the correct data:

<Query><Where><And><And><And><And><And><And><And><And><And>
   <Eq><FieldRef Name="IsCurrentBooking" /><Value Type="Boolean">1</Value></Eq><Eq>
   <FieldRef Name='NG_Venues' LookupId='True' /><Value Type='LookupMulti'>${adjVenue}</Value></Eq></And>
   <Geq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Geq></And>
   <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${end}</Value></Leq></And>
   <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${start}</Value></Geq></And>
   <Leq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Leq></And>
   <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Leq></And>
   <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Geq></And>
   <Neq><FieldRef Name="NG_BookingStatus" /><Value Type="Text">Cancelled</Value></Neq></And><Neq>
   <FieldRef Name="NG_BookingStatus" /><Value Type="Text">Rejected</Value></Neq></And></Where>
   <RowLimit Paged="TRUE">100</RowLimit>
</Query>

and I use it like:

let start=moment(startDate).toISOString();
    let end=moment(endDate).toISOString();

     items.forEach(v=> {
                let adjVenue= v.AdjVenue.ID;
                let message= v.AlertMessage;
                console.log('adjVenue: ',adjVenue);
                console.log('message: ',message);


                const xml = `<Query><Where><And><And><And><And><And><And><And><And><And>
                          <Eq><FieldRef Name="IsCurrentBooking" /><Value Type="Boolean">1</Value></Eq><Eq>
                          <FieldRef Name='NG_Venues' LookupId='True' /><Value Type='LookupMulti'>${adjVenue}</Value></Eq></And>
                          <Geq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Geq></And>
                          <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${end}</Value></Leq></And>
                          <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${start}</Value></Geq></And>
                          <Leq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Leq></And>
                          <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Leq></And>
                          <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Geq></And>
                          <Neq><FieldRef Name="NG_BookingStatus" /><Value Type="Text">Cancelled</Value></Neq></And><Neq>
                          <FieldRef Name="NG_BookingStatus" /><Value Type="Text">Rejected</Value></Neq></And></Where>
                          <RowLimit Paged="TRUE">100</RowLimit></Query> `;
                const dataFilter: CamlQuery = {
                ViewXml: xml,
                };

                prom.push(dataFilter);

              });
              let promises = prom.map(df => {
                  return pnp.sp.web.lists.getByTitle("EventBookingDetails").getItemsByCAMLQuery(df);
                });
              return Promise.all(promises).then(res => {
                console.log(res);
                let rslt;
                res.forEach(r => {
                  console.log(r, r.length);
                  if(r.length > 0){
                    console.log(r[0]);
                    console.log(items);
                    let adjItem;
                    if(r[0].NG_Venues[0].ID){
                      adjItem = items.filter(i => i.AdjVenue.ID == r[0].NG_Venues[0].ID);
                    } else {
                      adjItem = items.filter(i => i.AdjVenue.ID == r[0].NG_VenuesId);
                    }

                    console.log(adjItem);
                    result.result=true;
                    result.message=adjItem[0].AlertMessage;
                    rslt=result;
                  } else {
                    result.result = false;
                    result.message = "";
                    rslt=result;
                  }
                });            
                return rslt;
              });

Also, Everytime I add IncludeTimeValue='TRUE', results are also wrong.

  • In case you want to use REST API to get more than 5000 records. Refer this solution - sharepoint.stackexchange.com/questions/217254/… – Rohit Waghela Jan 26 at 17:32
  • hi @RohitWaghela not sure how that would apply to what i need. what the previous code is using pnp sp js – Page F.P.T Jan 27 at 3:52
  • 1
    Hi I thnik,you have to add <view> at the beginning of the xml and </view> at the end – myomyo Jan 27 at 8:16
  • Hi @myomyo tried adding <View> but it returned a lot of unneeded items as well. – Page F.P.T Jan 27 at 13:05
  • @PageF.P.T change the positon of "<RowLimit Paged="TRUE">100</RowLimit>" after <Query> – myomyo Jan 27 at 13:11
1

Thanks to @myomyo for giving the idea. i have changed the caml query to include query and view:

<View><RowLimit>100</RowLimit><Query><Where><And><And><And><And><And><And><And><And><And>
                      <Eq><FieldRef Name="IsCurrentBooking" /><Value Type="Boolean">1</Value></Eq><Eq>
                      <FieldRef Name='NG_Venues' LookupId='True' /><Value Type='LookupMulti'>${adjVenue}</Value></Eq></And>
                      <Geq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Geq></And>
                      <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${end}</Value></Leq></And>
                      <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${start}</Value></Geq></And>
                      <Leq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Leq></And>
                      <Leq><FieldRef Name="NG_EventDate" /><Value Type="DateTime">${start}</Value></Leq></And>
                      <Geq><FieldRef Name="NG_EndDate" /><Value Type="DateTime">${end}</Value></Geq></And>
                      <Neq><FieldRef Name="NG_BookingStatus" /><Value Type="Text">Cancelled</Value></Neq></And><Neq>
                      <FieldRef Name="NG_BookingStatus" /><Value Type="Text">Rejected</Value></Neq></And></Where>
                      </Query></View>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.