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.

5
  • In case you want to use REST API to get more than 5000 records. Refer this solution - sharepoint.stackexchange.com/questions/217254/… Jan 26, 2019 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, 2019 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, 2019 at 8:16
  • Hi @myomyo tried adding <View> but it returned a lot of unneeded items as well.
    – Page F.P.T
    Jan 27, 2019 at 13:05
  • @PageF.P.T change the positon of "<RowLimit Paged="TRUE">100</RowLimit>" after <Query>
    – myomyo
    Jan 27, 2019 at 13:11

1 Answer 1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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