1

I recently start working with ODATA in SharePoint 2013 and I have a list with the following fields:

  • Employee : Person or Group
  • ChildName : Text

Let's say, I want to get all children of "Ray", How can I achieve this with OData?

The following url returns all items and it has just EmployeeId and ChildName.

http://[My SharePoint]/_api/web/lists/Getbytitle('MyList')/items

If I just changed the above url like below, now I can see other information for my Employee like sip, username, etc. but it just works for only one item.

http://[My SharePoint]/_api/web/lists/Getbytitle('MyList')/items(1)/FieldValuesAsHtml

By the way, using expand did not help me.

Thanks in advance ^-^

5
+25

You will need the Id of the Employee whose children you want to fetch.

Syntax:

https://[My SharePoint]/_api/web/lists/MyList/items?$select=ChildName&$expand=FieldValuesAsText/Employee&$filter=Employee eq 1

This will return the 'ChildName' field of all items in which the user with Id 1 occurs in the 'Employee' field

Update:

For getting the Id of current user, you can use the _spPageContextInfo.userId property in your code. This is a global variable in SharePoint pages which holds the current user's Id which you can use in the above query. So your task can be achieved without you knowing the name of the current user.

  • Thanks Vardhaman, unfortunately it doesn't help me out. Employee field type is "Person or Group" in SharePoint. I need to drill down the employee field to filter based on current user. – Matin Habibi Jul 29 '13 at 10:07
  • Yes true. But in SharePoint 2013 REST, you can drill down the Person field (which is internally a lookup field) using only the LookupId and not the LookupValue. My code is indeed drilling down the Employee field but it is using the Id and not the value. – Vardhaman Deshpande Jul 29 '13 at 17:42
  • Please check the updated answer to get the Id of the current user. – Vardhaman Deshpande Jul 29 '13 at 21:04
  • Thanks again and sorry for delay in response. My problem actually is how drilling-down employee Id. your answer unfortunately doesn't solve my problem :( – Matin Habibi Aug 3 '13 at 5:54
1

You will want to use a filter, for example:

?$filter=CreatedBy/Name eq 'system account'

Similar to expand, filters can drill into an object.

  • Yup, I want to use a filter but your solution doesn't work :( Thanks – Matin Habibi Jul 29 '13 at 9:58
0

Try this format

http://[My SharePoint]/_api/web/lists/Getbytitle('MyList')/items?select=Title/FieldValuesAsHTML

In my tests it shows all items in the list with all the viewable fields. Might have to change the select option from Title to a column in your table.

0

If you want to select all children for a particular employee and you know the employee id, you can use this OData query string and directly only select the children with the employee id.

_api/web/lists/Getbytitle('children')/items?$filter= employeeId eq 1000$select=Id,Title

We could also do a join with the Employee list and query the child records via the look up field using this query string which returns the employee information as well as the child information.

_api/web/lists/Getbytitle('employee')/items?$filter= Id eq 1000&$expand=children&$select=Id,Title,children/Id,children/Title

For reference on SharePoint OData queries with look up fields, use this link:

http://www.ozkary.com/2015/10/sharepoint-2013-api-odata-for-lookup-fields.html

hope it helps

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.