1

I'd like to know how I can filter ListItems in SharePoint rest API (2013) on empty lookup. On my list items, there is a lookup (single value, not required) and i'd like to get all items where lookup is empty (blank).

_api/web/lists/getbytitle('MyList')/items?&select=Id&$expand=MyLookUp/Id&$filter=??

Does someone has a clue ? Thx

3

Hi try giving null for value for lookup column value

_api/web/lists/getbytitle('MyList')/items?&select=Id&$expand=MyLookUp/Id&$filter=MyLookUp/Id eq null
2

REST API in SharePoint 2013 does Not support null values for filtering on list item queries.

However you can combine CAML query with REST API to get the required data.

Please refer below code : (Note : please change your query/URL):

 function _rest_Post(rquest) {
        return $.ajax({
            method: rquest.method,
            url: rquest.url,
            contentType: rquest.contentType,
            headers: rquest.header,
            data: JSON.stringify(rquest.body),
        });

    }

function GetByCaml(serviceParams) {
    var req = {
        method: 'POST',
        url: url + "/_api/web/lists/getByTitle('" + serviceParams.create.lName + "')/getitems",
        header: {
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose"
        },
        body: serviceParams.create.body,
        contentType: "application/json;odata=verbose",

    };
    return _rest_Post(req);

}

function getData(){
    var queryViewXml = "<View><Query><Where><IsNull><FieldRef Name='Project_x0020_Manager'/></IsNull></Where></Query><ViewFields><FieldRef Name='Title'/></ViewFields></View>";
    var params = {};
    params.create= {};
    params.create.lName = "MyList";
    params.create.filter = "";
    params.create.body = {
        'query':{ 
            '__metadata': { 'type': 'SP.CamlQuery' },
            'ViewXml': queryViewXml
        }
    }
    return GetByCaml(params);
};


getData().then(function(data){
    //success handler
}, function(error){
    //failure handler
});
0

Try this

_spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Title,Drink/Title,Drink/Id&$expand=Drink&$filter=Drink/Title eq '" + selectedDrink + "'";

Ref: Cascading drop down in SharePoint using REST API.

  • This is really close. If you want to find items where the lookup item hasn't been selected, e.g. NULL, filter on Id eq -1. For Drink, you would filter on Drink/Id eq -1. -1 is the default value SharePoint assigns to lookup columns that haven't yet been selected. – Wade Aug 22 '17 at 21:35

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.