REST API Filter by LoginName from user list not working

Question

I have a list A, which has a column owner of type "Person or Group". I am using REST API to query this list(using jquery).

I would like to use column owner to filter records by username. I have a user/group picker from which I get username in this format: i:0#.f|membership|username

The query I am trying is:

http://site//_api/web/lists/getbytitle('A')/items?$select=column1,column2$filter=owner/LoginName eq 'i:0#.f|membership|username'

But I am getting error 500 with message

"One or more field types are not installed properly. Go to the list settings page to delete these fields."

I would like to know what I am donig wrong

Also is there any other way to filter using username?

Answer 1

For lookup columns, you have to use $expand. So the query URL should look like

_api/web/lists/getbytitle('A')/items?$select=column1,column2&$filter=owner/LoginName eq 'i:0#.f|membership|username'&$expand=owner

There are some special character in i:0#.f|membership|username, so you have to encode it before passing in query URL.

encodeURIComponent('i:0#.f|membership|username')

#Modified Query URL

"_api/web/lists/getbytitle('A')/items?$select=column1,column2&$filter=owner/LoginName eq "+encodeURIComponent('i:0#.f|membership|username')+"&$expand=owner"

Answer 2

The special characters coming in the REST api query need to be encoded before using.

The javascript out-of-box encodeURIComponent() method can be used.

encodeURIComponent('i:0#.f|membership|[email protected]')

In my scenario, encodeURI() method didn't worked properly.

Use the updated complete REST query given below.

https://sp2013/_api/web/lists/getbytitle('A')/items?$select=column1,column2$filter=owner/LoginName eq + encodeURIComponent('i:0#.f|membership|[email protected]')

Answer 3

function GetListData(select)
{
 var siteURL = _spPageContextInfo.webAbsoluteUrl;
 var listname = "MyListName";
 var currentUserId=_spPageContextInfo.userId; //is a user number Then needs to use /ID
 var url = siteURL + "/_api/web/lists/getbytitle('" + listname + "')/items?$select=ID,Name,Title,MyUserField/ID&$filter=substringof('"+currentUserId+"',MyUserField/ID)&$expand=MyUserField/ID";


$.ajax({
     url: url,
     method: "GET",
     headers: { "Accept": "application/json; odata=verbose" },
     success: function (data) {

        var items = data.d.results;
         alert(items.length);

        for(var i = 0; i < items.length;i++) {           
            var item=items[i];

            //console.log(item.Title);
        }       
     },
     error: function (data) {  
                           alert("error");     
                             }
 });
}