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/… Commented 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
    Commented 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
    Commented 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
    Commented Jan 27, 2019 at 13:05
  • @PageF.P.T change the positon of "<RowLimit Paged="TRUE">100</RowLimit>" after <Query>
    – myomyo
    Commented 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 you have read our privacy policy.

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