Create OData queries (x-ntx-query-builder)
|
In this example, you will add the x-ntx-query-builder Specification Extension A Nintex-specific OpenAPI Specification key that allows special functionality within Nintex Xtensions. to generate an OData (Open Data Protocol) query to filter a SharePoint list in the action's configuration fields. Note: Nintex Automation Cloud provides SharePoint Online actions out-of-the-box. This example is provided only as a guide to using OData queries, and will not function as an Xtension. Also see the how-to on x-ntx-query-builder. |
Some operations A single request to a third-party API. Operations often become actions in the workflow designer. might use an OData query to provide capabilities such as requesting specific data from a large set of data, or a subset of results. For example, returning all the items in a SharePoint list that were added by a specific person, or during a specified date range. Just like x-ntx-dynamic-schema, the fields you're filtering on may change with the data, so they can't be coded into the OpenAPI specification like normal parameters A piece of information passed to a third-party API during a request..
x-ntx-query-builder constructs a query in the standard OData format, either with pre-defined fields, or using x-ntx-dynamic-schema to retrieve the available fields from the API. In the Workflow designer, the action configuration has a set of conditions that can be built using simple Field - Operator - Value structures. When configuring the action, the designer can create and add as many conditions as they need.
Note: To use x-ntx-query-builder, the API's operation must support OData queries. If you want to dynamically retrieve the available fields, the API must also provide an operation that returns the data structure you want to use in JSON format. Both operations must be defined in the OpenAPI specification.
For more information on OData queries, see www.odata.org.
Tip: Want the short version? Check out our OpenAPI Specification quick reference for quick definitions of parameter types, authentication, file handling and Specification Extensions.
Query-builder
The x-ntx-query-builder Specification Extension is added to a parameter object, to tell Nintex Automation Cloud that this parameter is an OData query string to be generated at runtime. Inside the x-ntx-query-builder object, you define the schema of the data you are querying. If you want to dynamically retrieve the data schema from the API, use x-ntx-dynamic-schema inside the x-ntx-query-builder schema object, and define:
- Which 'helper' operation to get the data structure for the query from.
- The path in the response The return from a third-party API after a request has been made by the client. object to the structure.
- What parameters to pass to the helper operation, if any.
Create the Xtension
Step 1: Create the basic OpenAPI Specification
Create an OpenAPI Specification that:
- Uses OAuth2.0 authentication A two-step authorization protocol that both identifies the requestor, and allows a user to grant access to a third-party account without revealing their credentials to the requesting software. with three scopes to modify and read sites, and provide extended-time access to resources.
For more information on using OAuth2 authentication, see Add OAuth2 authentication .
- Defines an operation /ListItemRetrieve to retrieve an item from a SharePoint list that uses the item id as a parameter, and two helper operations with x-ntx-dynamic-values:
- An operation /GetSites to retrieve the available sites for the SharePoint URL.
- An operation /GetLists/All to retrieve the available lists from the SharePoint site.
For more information on using x-ntx-dynamic-values, see x-ntx-dynamic-values.
- Defines an operation /ListItemQuery to retrieve items from a SharePoint list that uses the same two helper operations /GetSites and /GetLists/All with x-ntx-dynamic-values as well as the following two parameters in the query:
- An integer max for the maximum number of items to return.
- A string filter for the query to use when searching for items.
- Defines an operation /GetFields/Query that retrieves the fields available in a SharePoint list using the list name as a parameter and the helper operation /GetSites with dynamic-values.
- Defines a set of data structures for Sites, Lists, ItemRetrieve and ListItemQueryResults.
For more information on creating definitions, see Streamline with references.
Note: If additionalProperties is not defined for an object, it defaults to true, allowing workflow designers to add arbitrary properties to that object.
{
"swagger": "2.0",
"info": {
"version": "1.0.0",
"title": "SharePoint Online"
},
"host": "SHAREPOINTSITE.azurewebsites.net",
"basePath": "/api",
"schemes": [ "https" ],
"produces": [ "application/json" ],
"paths": {
"/ListItemRetrieve": {
"get": {
"tags": [
"List item",
"Retrieve"
],
"summary": "Retrieve an item",
"description": "Retrieve an item",
"operationId": "ListItemRetrieve",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetListsAll",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
}
},
"value-path": "Name",
"value-title": "Name"
}
},
{
"name": "itemId",
"in": "query",
"description": "Item ID",
"required": true,
"x-ms-summary": "Item ID",
"type": "integer"
}
],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/ItemRetrieve"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/ListItemQuery": {
"get": {
"tags": [
"List item",
"Query"
],
"summary": "Query a list",
"description": "Query a list",
"operationId": "ListItemsQuery",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetListsAll",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
}
},
"value-path": "Name",
"value-title": "Name"
}
},
{
"name": "max",
"in": "query",
"description": "Maximum number of rows to return",
"required": true,
"x-ms-summary": "Maximum number of rows to return",
"type": "integer",
"default": 1000
},
{
"name": "filter",
"in": "query",
"description": "",
"required": false,
"x-ntx-summary": "",
"type": "string"
}
],
"responses": {
"200": {
"description": "Query Result",
"schema": {
"$ref": "#/definitions/ListItemQueryResult"
}
},
"default": {
"description": "Operation Failed."
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetSites": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Sites" ],
"summary": "Get sites",
"description": "Get sites",
"operationId": "GetSites",
"produces": [ "application/json" ],
"parameters": [],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/Sites"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetLists/All": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Lists" ],
"summary": "Get lists and libraries",
"description": "Get lists and libraries",
"operationId": "GetListsAll",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string"
}
],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/Lists"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetFields/Query": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Fields" ],
"summary": "Get fields schema",
"description": "Get fields schema",
"operationId": "GetFieldsSchema",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string"
}
],
"responses": {
"200": {
"description": "OK"
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
}
},
"definitions": {
"Sites": {
"type": "array",
"items": {
"type": "object",
"additionalProperties": false,
"properties": {
"Id": {
"type": "string"
},
"Title": {
"type": "string"
},
"Url": {
"type": "string"
}
}
}
},
"Lists": {
"type": "array",
"items": {
"type": "object",
"additionalProperties": false,
"properties": {
"Id": {
"type": "string"
},
"Name": {
"type": "string"
}
}
}
},
"ItemRetrieve": {
"type": "object",
"additionalProperties": false,
"x-ms-dynamic-schema": {
"operationId": "GetFieldsRetrieve",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
},
"listName": {
"parameter": "listName"
}
},
"value-path": "Schema/Items"
},
"x-ms-summary": "Add fields"
},
"ListItemQueryResult": {
"type": "object",
"additionalProperties": false,
"properties": {
"count": {
"x-ms-summary": "Number of items returned",
"type": "integer"
},
"ids": {
"x-ms-summary": "Store item ID's",
"type": "array",
"items": {
"type": "integer"
}
}
}
}
},
"securityDefinitions": {
"functions_auth": {
"type": "oauth2",
"flow": "accessCode",
"authorizationUrl": "https://api.example.com/oauth2/authorize",
"tokenUrl": "https://api.example.com/oauth2/token",
"scopes": {
"Sites.ReadWrite.All": "modify sites",
"Sites.Read.All": "read sites"
"offline access" : "Access to resource on behalf of the user for an extended time"
}
}
}
}
Step 2: Add x-ntx-query-builder to the parameter
In the filter parameter of the ListItemQuery operation, add the x-ntx-query-builder object with an empty schema object.
{
"name": "filter",
"in": "query",
"description": "",
"required": false,
"x-ntx-summary": "",
"type": "string",
"x-ntx-query-builder": {
"schema": {
}
}
}
Step 3: Define the schema of the query-builder object
The x-ntx-query-builder specification extension needs to know what fields it can use to build a query. You can either:
- Define the schema explicitly in the OpenAPI specification, the same as you would define any object schema.
- Use x-ntx-dynamic-schema to request the schema from the API.
In this example, we will use x-ntx-dynamic-schema to request the schema from the /GetFields/Query operation we defined earlier:
- Add an x-ntx-dynamic-schema object inside the schema of the x-ntx-query-builder object.
- Define the operationID that x-ntx-dynamic-schema should use as GetFieldsSchema, the operationID for /GetFields/Query.
- Add a parameters object.
- Inside the parameters object, create objects for the siteUrl and ListName parameters we need to pass to the /GetFields/Query helper operation. Make sure the name of the parameter is the same as it is in the helper operation.
- Inside the siteUrl and ListName objects, create a parameter key with a value of the name of the parameter as defined in the /GetFields/Query helper operation.
- After the parameters object, tell x-ntx-dynamic-schema where to find the schema in the response data using the value-path key.
For more information on using x-ntx-dynamic-schema, see x-ntx-dynamic-schema.
"x-ntx-query-builder": {
"schema": {
"x-ntx-dynamic-schema": {
"operationId": " GetFieldsSchema ",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
},
"listName": {
"parameter": "listName"
}
},
"value-path": "Schema/Items"
}
}
}
The OpenAPI Specification
The complete OpenAPI Specification.
Note: Nintex Automation Cloud provides SharePoint Online actions out-of-the-box. This example is provided only as a guide to using OData queries, and will not function as an Xtension.
{
"swagger": "2.0",
"info": {
"version": "1.0.0",
"title": "SharePoint Online"
},
"host": "SHAREPOINTSITE.azurewebsites.net",
"basePath": "/api",
"schemes": [ "https" ],
"produces": [ "application/json" ],
"paths": {
"/ListItemRetrieve": {
"get": {
"tags": [
"List item",
"Retrieve"
],
"summary": "Retrieve an item",
"description": "Retrieve an item",
"operationId": "ListItemRetrieve",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetListsAll",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
}
},
"value-path": "Name",
"value-title": "Name"
}
},
{
"name": "itemId",
"in": "query",
"description": "Item ID",
"required": true,
"x-ms-summary": "Item ID",
"type": "integer"
}
],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/ItemRetrieve"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/ListItemQuery": {
"get": {
"tags": [
"List item",
"Query"
],
"summary": "Query a list",
"description": "Query a list",
"operationId": "ListItemsQuery",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetListsAll",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
}
},
"value-path": "Name",
"value-title": "Name"
}
},
{
"name": "max",
"in": "query",
"description": "Maximum number of rows to return",
"required": true,
"x-ms-summary": "Maximum number of rows to return",
"type": "integer",
"default": 1000
},
{
"name": "filter",
"in": "query",
"description": "",
"required": false,
"x-ntx-summary": "",
"type": "string",
"x-ntx-query-builder": {
"schema": {
"x-ntx-dynamic-schema": {
"operationId": " GetFieldsSchema ",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
},
"listName": {
"parameter": "listName"
}
},
"value-path": "Schema/Items"
}
}
}
}
],
"responses": {
"200": {
"description": "Query Result",
"schema": {
"$ref": "#/definitions/ListItemQueryResult"
}
},
"default": {
"description": "Operation Failed."
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetSites": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Sites" ],
"summary": "Get sites",
"description": "Get sites",
"operationId": "GetSites",
"produces": [ "application/json" ],
"parameters": [],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/Sites"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetLists/All": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Lists" ],
"summary": "Get lists and libraries",
"description": "Get lists and libraries",
"operationId": "GetListsAll",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string"
}
],
"responses": {
"200": {
"description": "OK",
"schema": {
"$ref": "#/definitions/Lists"
}
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
},
"/GetFields/Query": {
"get": {
"x-ms-visibility": "internal",
"tags": [ "Helper", "Fields" ],
"summary": "Get fields schema",
"description": "Get fields schema",
"operationId": "GetFieldsSchema",
"produces": [ "application/json" ],
"parameters": [
{
"name": "siteUrl",
"in": "query",
"description": "SharePoint site URL",
"required": true,
"x-ms-summary": "SharePoint site URL",
"type": "string",
"x-ms-dynamic-values": {
"operationId": "GetSites",
"value-path": "Url",
"value-title": "Url"
}
},
{
"name": "listName",
"in": "query",
"description": "List name",
"required": true,
"x-ms-summary": "List name",
"type": "string"
}
],
"responses": {
"200": {
"description": "OK"
},
"default": {
"description": "Operation Failed"
}
},
"security": [
{
"functions_auth": [
"Sites.ReadWrite.All",
"Sites.Read.All"
]
}
]
}
}
},
"definitions": {
"Sites": {
"type": "array",
"items": {
"type": "object",
"additionalProperties": false,
"properties": {
"Id": {
"type": "string"
},
"Title": {
"type": "string"
},
"Url": {
"type": "string"
}
}
}
},
"Lists": {
"type": "array",
"items": {
"type": "object",
"additionalProperties": false,
"properties": {
"Id": {
"type": "string"
},
"Name": {
"type": "string"
}
}
}
},
"ItemRetrieve": {
"type": "object",
"additionalProperties": false,
"x-ms-dynamic-schema": {
"operationId": "GetFieldsRetrieve",
"parameters": {
"siteUrl": {
"parameter": "siteUrl"
},
"listName": {
"parameter": "listName"
}
},
"value-path": "Schema/Items"
},
"x-ms-summary": "Add fields"
},
"ListItemQueryResult": {
"type": "object",
"additionalProperties": false,
"properties": {
"count": {
"x-ms-summary": "Number of items returned",
"type": "integer"
},
"ids": {
"x-ms-summary": "Store item ID's",
"type": "array",
"items": {
"type": "integer"
}
}
}
}
},
"securityDefinitions": {
"functions_auth": {
"type": "oauth2",
"flow": "accessCode",
"authorizationUrl": "https://api.example.com/oauth2/authorize",
"tokenUrl": "https://api.example.com/oauth2/token",
"scopes": {
"Sites.ReadWrite.All": "modify sites",
"Sites.Read.All": "read sites"
"offline access" : "Access to resource on behalf of the user for an extended time"
}
}
}
}