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.
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.
