I have a small requirement where in i want to filter the list data based on some columns but i cannot wrap my head around the Rest Api Query.

So, i want to filter data based on few columns which are

  • Should Cost
  • Actual Model Start
  • SC Start Date
    Now i want to filter data based on if these columns are empty return me the result. I have the query down below please help me on this.

      /_api/web/lists(guid'4BFF0A20-43A2-4444-881C-18932D468E54')/items? $select=Project_x0020_Name,Planned_x0020_Completion_x0020_D,Actual_x0020_Completion_x0020_Da,Should_x0020_Cost_x0020_Modeler/Title&$filter=Should-Cost eq '' and Should_x0020_Cost_x0020_Modeler/Title eq '"+employeeName+"'&$expand=Should_x0020_Cost_x0020_Modeler/Id"


  • What is the type of Should Cost field? Single line of text or number? Oct 22 '18 at 8:56
  • it is Number Field Oct 22 '18 at 9:00

If you want empty/null value then you can you below query to filter by null.

_api/web/lists/getbytitle('CountryList')/items?$select=Title, Country&$filter=(Country eq null)

So now your query look like below,

"{sp Web Url}/_api/web/lists(guid'4BFF0A20-43A2-4444-881C-18932D468E54')/items?$select=Project_x0020_Name,Planned_x0020_Completion_x0020_D,Actual_x0020_Completion_x0020_Da,Should_x0020_Cost_x0020_Modeler/Title&$filter=Should-Cost eq null and Should_x0020_Cost_x0020_Modeler/Title eq '"+employeeName+"'&$expand=Should_x0020_Cost_x0020_Modeler/Id"

OData does not support filtering on null values in SharePoint REST GET Method in.

However you can pass CAML query in REST POST Method or you can achieve same using JSOM.

You can find both the examples here.

  • does this answers your question? Nov 15 '18 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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