Issue: Filtering cannot work with 2 fields as shown in the documentation

[mhm_alpha68]

Hello Admins,

I have encountered issues with CAML filtering where I still couldn't it get working as I wanted. I have been trying to filter a list with 2 fields, Name and Created Date as screenshots attached below. It is filtered with a Lookup which is based on Status field. Then can filter with 2 fields. I have followed the exact instruction from its documentation but still didn't work for some reason.

2 issues here.

• CAML query somehow couldn't filter specific date in search (text) field eventhough if it's just to filter by 1 field, Created.
• But when I try to filter by 2 fields dynamically in the same search field which wrap them inside "And" tag, it cannot work as it throws errors that cannot perform operation more than 1 field.
  
  

Screenshots:

Below is an example code of mine. Not sure what's wrong with it.

// filter List when Search field changes.
fd.field('SearchFilter').$on('change', function () {
searchFilterDT();
});
 
// search filter function.
function  searchFilterDT() {
let  searchVar = fd.field('SearchFilter').value;
let  currentStatus = fd.field('dd_list1').value;
var  filterStatus = dt.filter;

if (filterStatus !== '' || currentStatus !== "All") {
if (searchVar) {
var  filterName = "<And>";
// add existing filter value.
filterName += filterStatus;
// filtering conditions.
// created date & name.
filterName += "<Contains><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'>" + searchVar + "</Value></Contains>";
filterName += "<Contains><FieldRef Name='RequestorName'/><Value Type='Text'>" + searchVar + "</Value></Contains>";
filterName += "</And>";
} else {
var  filterName = '';
}
} else {
if (searchVar) {
// var filterName = "<Or>";
// filterName += "<Contains><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'>" + searchVar  + "</Value></Contains>";
// filterName += "</Or>";
var  filterName = "<Contains><FieldRef Name='RequestorName'/><Value Type='Text'>" + searchVar + "</Value></Contains>";
} else {
var  filterName = '';
}
}

// when search field is empty on change, reset list with its original status.
if (filterName === '') {
if (fd.field('dd_list1').value === "Completed") {
filterName = "<Contains><FieldRef Name='RequestStatus'/><Value Type='Text'>Completed</Value></Contains>";
}
if (fd.field('dd_list1').value === "In Progress") {
filterName = "<Contains><FieldRef Name='RequestStatus'/><Value Type='Text'>Assigned</Value></Contains>";
}
if (fd.field('dd_list1').value === "Rejected") {
filterName = "<Contains><FieldRef Name='RequestStatus'/><Value Type='Text'>Rejected</Value></Contains>";
}
if (fd.field('dd_list1').value === "All") {
filterName = "";
}
}
dt.filter = filterName;
dt.refresh();
}

This code section where CAML query wouldn't work at all from your documentation.

var  filterName = "<And>";
// add existing filter value.
filterName += filterStatus;

// filtering conditions.
// created date & name.
filterName += "<Contains><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'>" + searchVar + "</Value></Contains>";
filterName += "<Contains><FieldRef Name='RequestorName'/><Value Type='Text'>" + searchVar + "</Value></Contains>";
filterName += "</And>";

So, the code works to filter list by Name field, but the main issue here, it won't work filtering by Created field for specific date and multiple fields with code above.

Can you help me how to fix this issue here? Appreciate your help. Thanks.

[Nikita_Kurguzov]

Dear @mhm_alpha68,
You're taking the text value from search field and try to compare to a date type, it wouldn't work. If you want to filter by date, it needs to be in ISO 8601 format, like this:

      <And>
         <Geq>
            <FieldRef Name='Created' />
            <Value Type='DateTime'>2019-09-01T12:00:00Z</Value>
         </Geq>
         <Leq>
            <FieldRef Name='Created' />
            <Value Type='DateTime'>2019-10-05T12:00:00Z</Value>
         </Leq>
      </And>

[mhm_alpha68]

Hi,

Why do I need to add two query statements here? I don't get it. I just need to search a specific date here.

How do you suggest me to apply that in my code above?
And how can I make the query value dynamic instead of hardcoded to filter by date in the search field?

Thanks for clarifications.

[Nikita_Kurguzov]

Dear @mhm_alpha68,
This is just an example of formatting for the Date type field, the queries themselves are not important here, I'm just showing what the date format looks like in these types of queries.

I am talking specifically about this format here:
2019-09-01T12:00:00Z

If you want to filter by date as text, you'll need to create another column in the List or Library (it can be hidden from view), and copy the text value of date field inside of it, then you can filter by this field like by any other text field.