Using the REST api in a SharePoint 2013 Workflows: Part 1 - OData Syntax

What is REST?

REST = REpresentational State Transfer

TL;DR: A web style of architecture, where the focus is on component roles and a specific set of interactions between data elements, rather than implementation details, to induce performance, scalability, simplicity, ease of maintenance, and reliability. RESTful systems usually communicate via HTTP using the same verbs, like GET, POST, PUT, DELETE.

A web service can offer an API (application programming interface) that adheres to the REST architectural constraints. These are called RESTful APIs, and typically offer the user (or Caller of the API) functionality that extends being just the user interface.

For example, Google Maps has a RESTful api that returns information about a particular location.

http://maps.googleapis.com/maps/api/geocode/json?address=Cinderella%20Castle&sensor=false

For a longer description, visit this WikiPedia article.


SharePoint REST

SharePoint 2013 introduces a REST service that is comparable to the existing SharePoint client object models. Developers can interact remotely with SharePoint data by using any technology that supports REST web requests. This means that developers can perform Create, Read, Update, and Delete (CRUD) operations using REST web technologies and standard Open Data Protocol (OData) syntax.

For more information: Get to know the SharePoint 2013 REST service

An example of using REST in SharePoint can be demonstrated by making a call to a RESTful api that returns items from a list. Both the following (potential) perform the same task:

http://myserver.sharepoint.com/sites/mysite/_api/web/lists/getbytitle('My%20List')/items
http://myserver.sharepoint.com/sites/mysite/_api/web/lists/getbyid(guid'0BC6B574-42CF-1DA3-E8B3-159097A518ZZ')/items

Call Description

The urls above invoke a call to the SharePoint REST api that looks for list with the name "My List" or the GUID provided, and then returns all of the items within the list.

Obviously this may not be the best idea to return EVERY item in a list, but this will get your juices flowing, and give you a base understanding of the world of REST.

OData Query Operators

Command
Use
Example
$select
Return specific fields only
$Select=Id, Title
$filter
Specify criteria of items to return
$filter=(Department eq 'HR'
$orderby
specify the sort order of items returned
$orderby=Modified desc
Query Operators allow for targeting the information returned, and how it is returned. The first operator must follow a question mark(?), and subsequent follow an ampersand(&).

You can't always get what you want...

REST api calls use the OData Query Language, which allows for targeting specific fields and data, and specifying the sort order.

For example:

...?$select=Id, Title, Department, Modified&$filter=(Department eq 'HR')&$orderby=Modified desc

$select

$select allow you to return a specific subset of fields from a list.

In this example, only four of the List's fields are returned: Id, Title, Department, and Modified. Note that Id and Modified are SharePoint system fields that are unchangeable by anyone. Thus they will never change. As of this writing, referring to these fields requires respecting the case, aka case-sensitive, so take note that they use proper case Id, Modified. Not ID, id, nor modified.

[Title] is another potential problem, because most anyone can change the name of the Title field. Many times, when a new Custom List is created in SharePoint, the first thing the developer will do is change the name of the field to reflect something about the List. So, when you look at the list, you may see "Department Name", but because this field was born with the name Title, it retains that name for programmatic purpose. When in doubt, do a straight return of all of the items, and inspect the list of field name starting at the <m: properties>

The final naming pitfall comes from having spaces in a field name. In order to represent a space on the web, SharePoint converts the space to _x0020. It's probably not a coincidence that it uses the number 20, as %20 is also used to represent spaces on the web. This is one of the reasons why it is important to NEVER create a list or field name with a space in it. (The way it is created is the way it lives forever.)

Observing Field Names

.../_api/web/lists/getbytitle('Listname')/Items

[Scroll down to find this]

<m:properties>
  <d:FileSystemObjectType m:type="Edm.Int32">0</d:FileSystemObjectType> 
  <d:Id m:type="Edm.Int32">1</d:Id> 
  <d:ServerRedirectedEmbedUrl /> 
  <d:ContentTypeId>0x0100E309BA4C9EA9A748A5E3988FC8FD4B69</d:ContentTypeId> 
  <d:Title>Hillary for Prison</d:Title> 
  <d:My_x0020_Awesome_x0020_Field_x00>Lock her up!</d:My_x0020_Awesome_x0020_Field_x00> 
  <d:ID m:type="Edm.Int32">1</d:ID> 
  <d:Modified m:type="Edm.DateTime">2016-07-21T21:42:12Z</d:Modified> 
  <d:Created m:type="Edm.DateTime">2016-07-21T21:31:08Z</d:Created> 
  <d:AuthorId m:type="Edm.Int32">970</d:AuthorId> 
  <d:EditorId m:type="Edm.Int32">970</d:EditorId> 
  <d:OData__UIVersionString>1.0</d:OData__UIVersionString> 
  <d:Attachments m:type="Edm.Boolean">false</d:Attachments> 
  <d:GUID m:type="Edm.Guid">17d16d22-06ab-4d65-bdb3-ecd931b6565a</d:GUID> 
  </m:properties>

But, if you try sometimes, you just might find...

$filter

$filter is used to restrict the number of items that are returned from a list. As with the $select, the system-stored field name must be specified, and then OData syntax is followed.

$filter=Id eq 1
$filter=Department eq 'HR'

Searching for:

Numbers:

When searching for a number, no delimiter is required, but if apostrophes are used, then they are ignored.

$filter=Id eq 1
$filter=Id eq '1'

Strings

When searching for strings, then apostrophes (single quotes) need to be used:
$filter=Department eq 'HR'

Dates

When searching for a Date, or DateTime, then apostrophes need to be used, but also preceded by the word datetime:

$filter=Modified gt datetime'2016-07-20T23:59:59Z'

Parenthesis

Parenthesis are not required, and can be used for readability or order of operations

$filter=(Id eq 1)
$filter=(Id eq 1 and sky eq blue) or (stage eq 'left')

Never use MultiValue Fields... EVER!

Because multi-value lookup fields are returned as a string of multiple values, there is no way to query for them

Operators

Operator
Notes
Example
and
multiple requirements
status eq 'Active' and region eq 'South'
or
optional requirements
status eq 'Active' or status eq 'Pending'
 
 
 
lt
less than (<)
OnHand lt 10
gt
greater than (>)
DaysOff gt PTORemaining
ge
greater than or equal to (>=)
DaysLate ge 0
le
less than or equal to (<=)
checkamount le accountbalance
ne
not equal (!= , <>)
Status ne 'Active'
startswith
starts with ([Prefix]*)
startswith(Title, 'twin-soft)
endswith
ends with (*[suffix])
endswith(Title, 'easy-bins.com')
Operators allow you to narrow your $select criteria https://msdn.microsoft.com/en-us/library/hh169248(v=nav.90).aspx

SharePoint Calculated Fields and $orderby

Calculated fields can NOT be used in $orderby

This whole court is out of order!

$orderby

$order allows for the specification of the sort order of the returned items. The implicit order is ascending, or asc, but descending, desc, can be explicitly declared.

$orderby=CompanyName
$orderby=Modified desc

Multiple fields can be specified by separating with commas.

$orderby=LastName, FirstName, Age desc


Examples Recap

1. Return ALL fields and ALL items:
http://myserver.sharepoint.com/sites/mysite/_api/web/lists/getbyid(guid'[Your List GUID]')/items
http://myserver.sharepoint.com/sites/mysite/_api/web/lists/getbyid(guid'[Your List GUID]')/items?$select=*

2. Return specific fields and ALL items:
.../_api/web/lists/getbyid(guid'[Your List GUID]')/items?$select=Field1, Field2, etc

3. Return ALL fields and specific items:
.../_api/web/lists/getbyid(guid'[Your List GUID]')/items?$select=*&$filter=(State eq 'NY') and (VotedFor eq "Trump")

4. Return ALL fields and ALL items and specify sort order: (Modified Date: Newest to Oldest)
.../_api/web/lists/getbyid(guid'[Your List GUID]')/items?$orderby=Modified desc

Building a SharePoint 2013 Workflow to make a REST api Call

Hopefully the preceding sections have defined what and how you can query your content using an OData URI. The following sections describe how to prepare for making the call, making the call, storing the values returned, and then looping through the values to finally make something happen.

Oxford, Merriam-Webster, Random House...What's a dictionary?!

In SharePoint 2013, there is a new variable data type that is required when using a call to the REST api.

From: 'Understanding Dictionary actions in SharePoint Designer 2013' (jj554504)
"SharePoint Designer 2013 has a new variable type called Dictionary. The Dictionary variable type is a container designed to hold a collection of other variables. For example, your workflow might need to store more than just the name of the employee. It might also need to store his address and birth date. If you do not use the Dictionary variable you will have to create multiple stand-alone variables. This can quickly become difficult to organize and difficult to work with in the logic of the workflow. A Dictionary variable allows you to store multiple data points in a single variable."

This type follows the concept of an old-fashioned 1 x n array of data, and most anything can be captured and stored into a dictionary variable. They are weak typed, meaning that no declaration of types (string, integer, date, etc) must explicitly occur prior to the assigning of content to the dictionary.

So, the first step to making a call to the REST api is to create a dictionary variable.

After starting a new workflow, create a new "Build Dictionary" action. This "dictionary" that is being built is designed to communicate to the

But wait, there's more!

This is only the first phase of this primer on the REST api in SharePoint Workflows.

Coming Soon:

  • Using the REST api in a call to an HTTP web service
  • Looping through the results
  • Common errors received

REST Poll

Do you use REST?

See results without voting

Thanks for reading!

If you found this useful, support this author via bitcoin or ethereum:

BitCoin: 14k7jDikmMxkyqhV8kYoFARSj45W2PqJs8
Ethereum: 0xE080fc27E032a56e1b46a8e4dd637889F03AF32F

Sign up for a wallet, and get $10 free when you deposit $100: https://www.coinbase.com/join/ClarkSteveB

More by this Author


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working