OData substringof or startswith returning all items

Question

I'm trying to filter my results from a Rest Call.

$.ajax({
    type: "GET",
    headers: {
        "Accept": "application/json;odata=verbose"
    },
    dataType: "JSON",
    url: _spPageContextInfo.webServerRelativeUrl + "/_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$startswith('Title','" + request.term + "') eq true",
    success: function (data) {
    },
    error: function (ex) {
    }
});

In my Contacts List i'm trying to retrieve the Title and the Id for Items which start with a String or which have the String somewhere in it, here for example it is the Name of somebody.

I also tried it with substringof:

"/_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$substringof(" + request.term + ",'Title') eq true"

which delivers also the same result.

It gives me all List Items from the List and no Filtering is applied. I build the Url for the Rest after looking here Programming using the SharePoint 2013 REST service Like the Schema given there I think the Url looks ok, but it not seems so :)

Edit:

Applying the $filter like in the OData Uri Conventions gives me the following error:

{"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The query is not valid."}}}

Tried it with following Query Strings:

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof(m,'Title') eq true

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m','Title') eq true

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m',Title) eq true

Solution 1

I've managed to get the filter with substringof returning the correct results when I removed the "eq true".

Using one of your query strings, it should work like this:

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m',Title)

I haven't checked any other functions, but at least, the same happens with startswith function.

Solution 2

For anyone looking at this question, I can report that

/_api/web/lists/GetByTitle('Applications')/items?$filter=startswith(Title,'1AAJ') 

IS working for me.

Comments

[Mark]

please see my edit. When I apply the $filter it gives me an error that the query is not in a correct format

[Rolfvm]

Maybe it is because of the combination/order of the $select, it seems it should work. sharepoint.mindsharpblogs.com/NancyB/Lists/Posts/…. Could you try with just the filter?

[Mark]

Tried it with only the filter the same Error is returning. Theres probably something missing but I don't get it. When i'm looking at the conventions it looks ok

[Mark]

Tested it also again with that string, but it is only returning the same error "The query is not valid." when i'm using the $filter tag

[lyndon hughey]

The contains parameter definitely works, as referenced at odata.org/getting-started/basic-tutorial. It's one of the easier filter partial matches. I don't have Sharepoint 2013, so I can't test against it, but it work for anyone using a modern and standard implementation of OData.

[Alberto S.]

Sorry my fault, I thought the topic was related to SP 2013. Please can you edit you answer so I can remove my downvote? thanks