Rest/Odata $Filter not working on certain keys

Question

I am trying to query some information from a SharePoint list. Now, I am trying to use odata to get it. my problem is that for some reason, $filter is not working with all my keys in that list :S. Here is an example:

This works OK:

...ccm/_api/Lists/GetByTitle('Circuit%20Deliveries')/Items?$filter=Title eq 'AUG'

But this one doesn't work (HTTP 400 bad request)

...ccm/_api/Lists/GetByTitle('Circuit%20Deliveries')/Items?$filter=Delivery_x0020_Date eq null

Nevertheless, the field name is OK, since I am able to use select and orderby with the same field name. Here is the XML output in IE for 2 'Titles' named AUG:

...ccm/_api/Lists/GetByTitle('Circuit%20Deliveries')/Items?$filter=Title eq 'AUG'&$select=Title,Delivery_x0020_Date,Status_x0020__x002d__x0020_Calcu

<?xml version="1.0" encoding="utf-8" ?> -
<feed xml:base="/ccm/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss"
  xmlns:gml="http://www.opengis.net/gml">
  <id>5a2630a8-c00a-4baa-bd43-3912d9264df8</id>
  <title />
  <updated>2017-09-08T10:58:12Z</updated>
  <entry m:etag="" 33 "">
    <id>Web/Lists(guid'5ad8ca39-191a-4586-bdef-7bc4830eced9')/Items(332)</id>
    <category term="SP.Data.Circuit_x0020_DeliveriesListItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" href="Web/Lists(guid'5ad8ca39-191a-4586-bdef-7bc4830eced9')/Items(332)" />
    <title />
    <updated>2017-09-08T10:58:12Z</updated>
    <author>
      <name />
    </author>
    <content type="application/xml">
      <m:properties>
        <d:Title>AUG</d:Title>
        <d:Delivery_x0020_Date m:type="Edm.DateTime">2017-03-06T06:00:00Z</d:Delivery_x0020_Date>
        <d:Status_x0020__x002d__x0020_Calcu>Circuit Ready for Production Use on 03/06/2017</d:Status_x0020__x002d__x0020_Calcu>
      </m:properties>
    </content>
  </entry>
  <entry m:etag="" 58 "">
    <id>Web/Lists(guid'5ad8ca39-191a-4586-bdef-7bc4830eced9')/Items(333)</id>
    <category term="SP.Data.Circuit_x0020_DeliveriesListItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" href="Web/Lists(guid'5ad8ca39-191a-4586-bdef-7bc4830eced9')/Items(333)" />
    <title />
    <updated>2017-09-08T10:58:12Z</updated>
    <author>
      <name />
    </author>
    <content type="application/xml">
      <m:properties>
        <d:Title>AUG</d:Title>
        <d:Delivery_x0020_Date m:null="true" />
        <d:Status_x0020__x002d__x0020_Calcu>Site Survey completed on 09/09/2016</d:Status_x0020__x002d__x0020_Calcu>
      </m:properties>
    </content>
  </entry>
</feed>

As you can see I was able to successfully query and select both fields from the list and filter by Title. Nevertheless, I would like to further filter on Delivery_x0020_Date being empty (or null).

I searched around and some people were saying that filtering by null on dates is not always working. But in my case is a bit more than that, because even if I try to filter by another key(Status_x0020__x002d__x0020_Calcu) it will return Bad Request

Is there a requirement on what type of keys you can filter on? Thank you

Rohit Waghela Rohit Waghela · Accepted Answer · 2017-09-08T17:23:48

REST API in SharePoint 2013 does Not support null values for filtering on list item queries. (Refer this MSDN link)

You will have to find some other workaround.

however, We can combine the CAML query with REST API to get the required data in Javascript object model. Refer this link - REST API to filter on null.

But I am not sure about the c# code.

Rest/Odata $Filter not working on certain keys

1 Answers