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.
For a longer description, visit this WikiPedia article.
SharePoint REST Assistance
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:
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
Return specific fields only
Specify criteria of items to return
$filter=(Department eq 'HR'
specify the sort order of items returned
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.
...?$select=Id, Title, Department, Modified&$filter=(Department eq 'HR')&$orderby=Modified desc
$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>
SharePoint Exam Prep
But, if you try sometimes, you just might find...
$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'
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'
When searching for strings, then apostrophes (single quotes) need to be used:
$filter=Department eq 'HR'
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 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
status eq 'Active' and region eq 'South'
status eq 'Active' or status eq 'Pending'
less than (<)
OnHand lt 10
greater than (>)
DaysOff gt PTORemaining
greater than or equal to (>=)
DaysLate ge 0
less than or equal to (<=)
checkamount le accountbalance
not equal (!= , <>)
Status ne 'Active'
starts with ([Prefix]*)
ends with (*[suffix])
SharePoint Calculated Fields and $orderby
Calculated fields can NOT be used in $orderby
This whole court is out of order!
$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.
Multiple fields can be specified by separating with commas.
$orderby=LastName, FirstName, Age desc
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.
- Using the REST api in a call to an HTTP web service
- Looping through the results
- Common errors received
Do you use REST?
Thanks for reading!
If you found this useful, support this author via bitcoin or ethereum:
Sign up for a wallet, and get $10 free when you deposit $100: https://www.coinbase.com/join/ClarkSteveB