ShortPoint REST API: Selecting, Filtering, Sorting Results in a SharePoint List

Modified on: Mon, 6 Dec, 2021 at 3:03 PM

When setting up the REST API connection for your SharePoint intranet, it might happen that you will need to perform some modifications on the data retrieved by REST API, for example, selecting or filtering specific items.

In this solution article, we will shed light on the most common parameters that you can use for such operations while forming the REST API URL to get data: $select, $orderby, $filter and $expand.

For the purposes of this article, we will take the following REST API URL as an example, and modify it according to the certain operations explained:

‍{siteUrl}/_api/web/lists/getbytitle('Employees')/items

The above REST API request returns all items with all possible fields from a SharePoint list called "Employees", located in the SharePoint site.

Make sure to update the {siteUrl} and the list title values to match your needs.

Note:If you would like to become more familiar with the ShortPoint REST API, please check our solution article: Connection Type: REST API.

Table of Contents

Selecting items

By forming the REST API URL to get the items (for example, URL), it will get all the fields available in that SharePoint list. However, it might happen that there are too many columns with data, but you only need to get some of them. In such cases, you can select specific fields to return for your result items using the $select parameter.

The syntax for the $select parameter is as follows: $select=Field1, Field2, Field3.

Provided that you only need to return the ID, Title and Employee values from the list, your REST API URL might look like this:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee

Sorting items

If you are getting a list of items as a REST API response, you might want to sort them on a specific field name. For this operation, you will need to use the $orderby system filter parameter, specifying if you want to get the sorting in ascending (asc) or descending (desc) order.

The syntax for the $orderby parameter is as follows: $orderby=(ColumnInternalName order).

For example, if you want to sort the results on the Employee field, your REST API URL might look similar to this:

Ascending order:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee asc

‍Descending order:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee desc

Hint:If you have difficulties with finding the correct internal name of a specific column, check this third-party article for some help: List of internal names for SharePoint fields.

Filtering items

If you want to get only the results that match some specific condition(s), you may use the $filter parameter.

The syntax for the $filter parameter is as follows: $filter=(ColumnInternalName operator value).

Operators

Below you may find the operators supported in the SharePoint REST service, as well as those you cannot use:

Operator	Meaning	Not supported operators
Numeric comparisons
Lt	Less than	Arithmetic operators (Add, Sub, Mul, Div, Mod)
Le	Less than or equal
Gt	Greater than
Ge	Greater than or equal	Basic math functions (round, floor, ceiling)
Eq	Equal
Ne	Not equal
String comparisons
startsWith	Filters all the records that starts with certain characters in the specific column	endsWith
startsWith		replace
substringof	Filters all the records that contain with certain characters in the specific column	substring
substringof		tolower
Eq	Equal	toupper
Ne	Not equal	trim
Ne	Not equal	concat
Date and time functions
day()	Converts a date string to a day of the month	DateTimeRangesOverlap operator
month()	Converts a date string to a month
year()	Converts a date string to a year
hour()	Converts a date string to an hour	Querying whether a date time falls inside a recurrent date time pattern
minute()	Converts a date string to a minute
second()	Converts a date string to a second

Here are some examples of using $filter parameter:

Filtering by Title:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= Employee eq ‘parth'

Filtering by ID:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= ID eq 2

‍Filtering by Date:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=Start_x0020_Date le datetime'2016-03-26T09:59:32Z'

‍Title name starts with the letter P:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=startswith(Title,‘P’)

‍Return all items from the "Employees" modified in May:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=month(Modified) eq 5

‍You can also use multiple filters using the and operator to combine them, and wrapping each filter condition in a parenthesis:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=(Modified le datetime'2016-03-26T09:59:32Z') and (ID eq 2)

Expanding for a person or lookup fields

When dealing with a person or lookup fields, only the ID will be returned. Using the $expand parameter, you can get the corresponding values based on the ID.

Important:You need to make sure that the columns you would like to get, should be indicated in the $select query first (for example, $select=Author/Title&$expand=Author/Id).

The syntax for $expand operator is as follows: $expand= Field/Id

‍Here are some examples:

‍Lookup field: a City column is a lookup to Title column in the "Employees" list, and we would like to get it in the results:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee,company,city/Id&$expand= city/Id

‍People field: the "Employees" list contains a custom field Author, and returns the AuthorId in response. To get the title of the Author, we need to expand the Author field:

{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=Author/Title&$expand=Author/Id

Note:If you need to expand more than one field, you can do that using a comma separator between the fields (e.g., $expand=Author/Id,file).

That's it. Now it's time for you to get into REST-ing and deal with the data results filtering and sorting like a true professional!

Visit our dedicated courses on ShortPoint Academy

Click Here!

To search more helpful articles,
Visit our Support Site

Visit support site Submit a ticket

Visit our dedicated courses on ShortPoint Academy

Click Here!

See all 31 topics

Start a trial

Ignite your vision. Install ShortPoint directly on your site, or play in sandbox mode. No credit card required.

Get started today

World’s best intranet sites are designed using ShortPoint

Thousands of companies using ShortPoint everyday to design, brand and build award winning intranet sites.

Get started Learn more