SharePoint Online - Query a list

Use the SharePoint Online - Query a list action A tool for building the processes, logic, and direction within workflows. to query a list of items within a specified SharePoint Online site. You can add conditions to limit the returned query results.

For example, you can use this action to automate a reimbursement process. The accounts team may look for employees who have raised reimbursement requests. You can also set conditions to query employees from a location or team.

Before you start

What is SharePoint Online?

SharePoint Online is a cloud-based service hosted by Microsoft that helps organizations share and manage content, knowledge, and applications. You can create sites to share documents and information with colleagues, partners, and customers. For more information about SharePoint Online, see Introduction to SharePoint Online.

How do I handle files in my workflow?

You can start a workflow whenever a new or updated file is detected (example: Box - New file), upload files on start or at any point in the workflow, generate documents from tagged templates, send and receive files via email, and manipulate files. File handling can be set up in your workflow start event The connector and event for triggering the workflow to run, including required configuration. An example is Box - New file, which triggers the workflow to run when a new file is uploaded to the specified folder. and actions A tool for building the processes, logic, and direction within workflows..

Note: Your tenancy's default file storage location must be configured to hold files for later reference by the workflow. This location is used for uploaded files and for files stored as output from actions.

Handle files in your workflow start event

Start your workflow when a new or updated file is detected in a service; for example, see Box - New file and Connectors
- Store a file reference for use later in the workflow (file share connector start events)
  File share connector start events, such as Box - New file, require you to add each start event variable that you want to reference later in the workflow. The variables provided vary by start event. See Connectors.
  In the Designer page:
  1. On the canvas, click Start event.
    The configuration panel for Start event appears.
  2. Select a connector, from the Start from drop-down list.
  3. Select an event from the Event drop-down list.
    The Source section is displayed.
    Note: The Source section is available only for some events.
  4. Click Add variables.
  5. Select the predefined variables that you want to add to your workflow.
    Note:
    To select all variables, click Select All.
    If you know the variable and you want to find it faster, type the variable name in the Filter options field.
  6. Click Add.
    The Variables dialog box appears, displaying the variables you have selected.
    Note: If you want to edit the selection of variables, hover over the variable, click , and then click Show Details. Edit the variable name in the Name field or click .
Upload files when filling in a form from:
- Salesforce, using Nintex Drawloop DocAutomation for Salesforce
- The web or an app
Note: Your tenancy's default file storage location must be configured to hold files for later reference by the workflow.
Programmatically upload files for a Component workflow A workflow that can be started from outside the tenancy; the workflow uses the "Component workflow" start event.; see Nintex - Component workflow

Note: Your tenancy's default file storage location must be configured to hold files for later reference by the workflow.

Handle files in your workflow actions

Generate documents from tagged templates using Generate document
Send and receive files via email; for example, see Assign a task
Manipulate files in services (see Connectors):
- Get, store, move, copy, or delete files using file share connector actions A workflow action that performs a basic operation through a connected system, such as Salesforce or Box., such as Box - Move a file
- Get and attach files in Salesforce
- Get and store files in SharePoint
- Signatures: Get and store signed documents using DocuSign or Adobe Sign.
Store a file for use later in the workflow
For example, store any email attachments received by the task assignee.

In the Designer page:
1. On the design canvas, click the action you want to configure.
2. Find the output field for storing the file or set of files.
  
  For example, to store attachments received in task assignee email replies when they may attach multiple files, use the Assign a task Multiple file attachments option for the Store output field.
3. In the output field, do one of the following:
  - Select a variable.
    For output storing multiple files, select a collection variable. For output storing a single file, select a file variable.
    Note: Your tenancy's default file storage location must be configured to hold files for later reference by the workflow.
  - Specify a file path.
    You can use any combination of text and variables, beginning with the root folder. If available, use the folder browse icon on the right of the path field to navigate to the folder and any subfolders that you want, and then edit the path as desired to include variables and different text.
  Availability of variable or path options for storing files varies by action. See Connectors and Workflow actions.
Reference a stored file
For example, reference the new Box file that started the workflow to attach it to a task email notification or reference an image received by the task assignee to generate an invoice document.

Note: Your tenancy's default file storage location must be configured to hold files for later reference by the workflow. This location is used for uploaded files and for files stored as output from actions.

In the Designer page:
1. On the design canvas, click the action you want to configure.
2. Find the field where you want to reference the stored file.
3. In this field, do one of the following:
  - Select the file variable associated with the stored file.
    For example, select the variable for the new Box file that started the workflow or select the variable storing Multiple file attachments output from a task action.
  - Specify the file path where the file is stored.
    You can use any combination of text and variables, beginning with the root folder. If available, use the folder browse icon on the right of the path field to navigate to the folder and any subfolders that you want, and then edit the path as desired to include variables and different text.
    View example
    Both a file variable and a file path are used in the example image below for the Dropbox Business - Copy a file action. A file variable is selected for Source and a file path is specified for Destination.
    The variables "CountryVariable" and "Program" insert the values into the file path as determined by earlier workflow logic (in this case, fields in a submitted form). For example, if the person submitting the form selects Australia for country and specifies Inspire2017 for program, then the resulting file path is "/SpecialProgramNDAs/Australia/Inspire2017NDA.docx."
  Availability of variable or path options for referencing files varies by action and field. See Connectors and Workflow actions.

Configure the SharePoint Online - Query a list action

The steps required to configure the action are given below. The SharePoint Online - Query a list action fields and settings table describes all the required and optional configuration fields.

Add the action to the workflow and open the configuration panel.
1. Find the action in the SharePoint Online action group in the action toolbox.
1. Drag the SharePoint Online - Query a list action onto the designer canvas.
2. Click the action to open the configuration.
Select a Connection.

Note: Only the connections created with the List and libraries authentication method are available in the drop-down. For more information about authentication methods for SharePoint Online connections, see Connectors.

Type a SharePoint site URL and click Retrieve lists.

Example:https://domain.sharepoint.com/sites/hr
Select the List name that you want to query.

Caution: Querying and retrieving items from lists that contain 12 or more columns of the People, Lookup, or Managed Metadata type can cause a list view threshold error.

Type a value for Item limit.
(Optional) Set conditions for the item(s) that you want to query.
Note:
- If the list contains more than 5000 items, an index must be created for the column. For more information, see Add an index to a SharePoint column.
- For conditions involving columns with multiple values, you need to create conditions for each available value and select And or Or options accordingly.
1. Click Add condition.
2. For each condition do the following:
  1. For When, select the column from the selected list.
  2. Select an Operator to compare the values in the When and Value fields.
  3. For Value, type a value that you are expecting in the column (field) specified for When.
  4. Click Save condition. The condition is added to the action.
3. To configure query behavior for multiple conditions, select all or any.

Retrieve the first item from the query results

The following example shows how the first item that is returned from the query can be retrieved. The following actions are used in this example:

SharePoint Online - Query a list (this action)
Log to instance details action

In the Designer page:

Query the required SharePoint Online list and store the queried results in an object output.
1. Drag the SharePoint Online- Query a list action onto the designer canvas.
2. Select a Connection.
3. Type a SharePoint site URL and click Retrieve lists.
4. Select the List name that you want to query.
5. Type a value for Item limit.
6. For Result, do the following:
  1. Click Add variable > Create variable.
    The Insert variables panel appears.
  2. For Name, type Queryresults and click Create & insert.
  The output from the Query a list action is stored in the Queryresults object.
Retrieve the first item using the Log to instance details action
This step shows how to retrieve a variable from the first item that is returned from the query. The output of Query a list action, Queryresults, includes a First item object and we can use that here:
1. Find the action in the Operations action group in the action toolbox.
  
  or
  
  Type the action name in the Search field at the top of the action toolbar.
2. Drag the Log to instance details action onto the designer canvas.
3. Click the action to open the configuration.
4. Hover over the Message field, and click Insert.
5. Click SharePoint Online.
6. Select Queryresults > First item, and then select a variable.
7. Click Insert.

Retrieve items from the query results

The Loop for each action can be used to retrieve items from a SharePoint Online list as described in the steps below. The following actions are used in this example:

SharePoint Online - Query a list (this action)
Loop for each action
Log to instance details action

In the Designer page:

Query the required SharePoint Online list and store the queried results in an object output.
1. Drag the SharePoint Online- Query a list action onto the designer canvas.
2. Select a Connection.
3. Type a SharePoint site URL and click Retrieve lists.
4. Select the List name that you want to query.
5. Type a value for Item limit.
6. For Result, do the following:
  1. Click Add variable > Create variable.
    The Insert variables panel appears.
  2. For Name, type Queryresults and click Create & insert.
  The output from the Query a list action is stored in the Queryresults object.
Use Loop for each action to loop through the Queryresults object.
1. Drag the Loop for each action onto the designer canvas.
2. Click the action to open the configuration.
3. Hover over the Target collection field, and click Insert.
4. Click SharePoint Online.
5. Select Queryresults > Items.
6. Click Insert.
Retrieve each item using the Log to instance details action
1. Find the action in the Operations action group in the action toolbox.
  
  or
  
  Type the action name in the Search field at the top of the action toolbar.
2. Drag the Log to instance details action onto the designer canvas.
3. Click the action to open the configuration.
4. Hover over the Message field, and click Insert.
5. Click Loop for each.
6. Select Loop for each > Current item, and then select a variable.
7. Click Insert.

SharePoint Online - Query a list action fields and settings

Important: Before inserting variables in the action configuration, you must manually type or select the values and populate other dependent fields in order to retrieve the output data. Once configured, you can insert variables to the required fields.

Section	Field	Description	Variable types
(unlabeled)	Connection Credentials and other settings required to pass data between the workflow and the indicated service, which could be a cloud service, business application, or content store.	SharePoint Online connection for the list you want to query. To refresh the available connections , click .	(n/a)
Source list	SharePoint site URL	The URL of the SharePoint Online site containing the list you want to use. To retrieve the lists from the SharePoint site, type the SharePoint site URL and click Retrieve lists. Example:https://domain.sharepoint.com/sites/hr	Boolean, Collection, DateTime, Decimal, Integer, Text
	List name	The name of the list you want to query. The drop-down list is limited to lists and document libraries within the site specified in SharePoint Online site URL.	Boolean, Collection, DateTime, Decimal, Integer, Text
	Item limit	Highest number of rows to return in the query. Accepts either typed text or an inserted variable.	Integer
Conditions	Add conditions/Edit conditions	Conditional statements to use for the query. For example, to query for stale items, you might configure a condition "Modified is before <yourDateVariable>." Note: For conditions involving columns with multiple values, you need to create conditions for each available value and select And or Or options accordingly. Displays the condition builder to define new conditions or edit existing conditions. Add conditions - Displayed when there are no existing conditions specified for the action. Edit conditions - Displayed when there are existing conditions set for the action. You can edit the conditions. The Condition builder supports nested conditions, enabling you to create conditions within another condition.	(n/a)
Condition builder	When	Displays the items from the Column field of the selected SharePoint list. Note: The following columns are not supported in the Condition builder: Calculated Hyperlink Managed Metadata Multiple lines of text Note: If the list contains more than 5000 items, an index must be created for the column. For more information, see Add an index to a SharePoint column.	Text, Decimal, Integer, Boolean, DateTime, Collection
	Operator	How the item from the When drop-down and Value are compared. Select from the drop-down. The operators available in the drop-down depend on the data type of the item selected from the When drop-down.	(n/a)
	Value	The value you are expecting in the column (field) specified for When. The data type of Value must match the data type of the item selected in the When drop-down.	(varies)
	Add	Adds a new condition. When adding a new condition you have to select the level of the condition. Select Nested condition to nest the new condition within the condition above it.	(n/a)
	And/Or	The connecting operator to use for the conditions. If you select: And: The workflow starts when the item that is added matches all the conditions. Or: The workflow starts when the item that is added matches any one of the conditions.	(n/a)
		Displays the following options menu for a condition. The following options are available: Promote: Promotes the condition to a higher level. Promote option is shown if the condition is nested within another condition and allows you to move it to a higher level. Demote: Demotes the condition to a lower level. If you demote a condition it is moved to a lower level making it nested within the condition immediately above it. Delete: Deletes the selected condition.	(n/a)
Sorting	Sort by	Allows you to select the column by which you want to sort a query result. The columns displayed in the drop-down list are associated with the SharePoint Online list you specify in the List name field. You can also insert a variable, which you can use to specify either the display name or internal name of the column. You can sort a column in ascending or descending order: Ascending: Sort the list items of a column in ascending order. Descending: Sort the list items of a column in descending order. The sort order you select is applicable to the Item ID(s), Items, and First item output variables. Note: Sorting the Managed Metadata column is not supported.	(n/a)
Output	Result	Stores the result of the action as an object with the following variables: Number of items returned: Integer variable to store the total number of items retrieved from this query. Item ID(s): Collection variable to store the ID(s) of all the returned items. Items: Collection of objects with all columns from that list. First item: Object variable that contains the properties of the first item in a query. Note: Item ID(s), Items, and First item can be arranged, depending on the sort order selected in the Sorting section.	Object A variable that groups several related variables of any type into a hierarchical structure. Objects can contain other Objects.

Valid values for added fields, by column type

The values you enter for added fields from list items must meet formatting requirements for the related column type.

Column type (SharePoint Online)*	Value entry guidance (Nintex Workflow Cloud)
Choice	<choice 1> Example: Blue Supported variable A placeholder that stores changes each time the workflow runs, such as the information entered in a form, or the name of a file. You can also create variables to store information you use in the workflow, such as an email address. types: Text Note: Multiple-selection columns cannot be updated using the action Update an item. That is, Choice type SharePoint columns configured with the "Checkboxes (allow multiple selections)" option for "Display choices using" setting, cannot be updated. Single value only. New columns are configured with the "Display choices using" option of "Drop-down Menu."
Currency	<decimal value> Example: 25.50 Supported variable types: Decimal
Date and Time	<date or date/time> Example: 20170602 Supported variable types: DateTime For updates to date-only columns, removes time.
Hyperlink or Picture	<URL>,<description> Example: https://www.nintex.com,Nintex Supported variable types: Text
Lookup	<ID of list item in selected list> Example: 1 Supported variable types: String
Managed Metadata	<unique identifier of the term in the term store> Example: 883f304208f84f78bbe6d48d7e34c494 Supported variable types: Text
Multiple lines of text	<text and/or HTML> Example: <b>ACME</b> invoice OR ACME invoice (HTML is supported when "Enhanced rich text" is enabled for the SharePoint column.) Supported variable types: Text
Number	<integer> Example: 1 Supported variable types: Integer
Person or Group	<person or group ID>; <person or group ID> Example: 1;2 Supported variable types: Integer
Single line of text	<text> Example: ACME Invoice Supported variable types: Text
Task Outcome	<choice 1> Example: Approved Supported variable types: Text Single value only. Typically used to update task lists.
Yes/No	<true/false> Example: True Supported variable types: Boolean
For more information about column types, see List and library column types (Microsoft article).