Using REST API – Recently I was given the requirement that I had to get all items from a list and generate a report, but only if the workflow column of one of the items on the list was not null or empty. The first REST call I wrote looked like this:

var svcUrl = getSiteUrl() + "/_api/lists/getbytitle('Judges Master')/items?$select=Judges_x0020_Master_x0028_1_x002&$filter=Judges_x0020_Master_x0028_1_x002 ne ''";

When I executed the rest call it did not work. Here is the error I received:

Blog-REST-API

If we analyze the error, it is pretty clear that a normal REST call isn’t going to cut it, because just like a multi-valued lookup column, a ‘URL’ type column is also not filterable. I did make some futile attempts like expanding the URL column using the ‘expand’ property and then applying the filter, but nothing worked.

Now, the other alternatives that we can try are:

  1. Search REST API
  2. ListData.svc
  3. CAML Query

I opted for the third alternative since it is something that I have used before for filtering non-filterable columns. Here is the CAML Query and I have put a null filter on the workflow column.

var svcUrl = getSiteUrl() + '/_api/web/Lists/GetByTitle(\'Judges Master\')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><IsNotNull><FieldRef Name=\'Judges_x0020_Master_x0028_1_x002\' /></IsNotNull></Where></Query></View>"}';

$.ajax({
type: "POST",
headers: {
"accept": "application/json;odata=verbose",
"content-type": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val()
},
url: svcUrl,
success: function(data){
if (data.d.results.length >= 1) {
console.log(data);
},
failure: function(data) {
}
});

I didn’t get the chance the find out if the URL field can be filtered using the Search REST API or ListData.svc. If anyone reading this post has filtered a ‘URL’ type column using the Search REST API or ListData.svc please share in the comments section below.

Visit the AAJ Technologies website