SharePoint on-premises - Query a list

Use the SharePoint on-premises - 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 on-premises 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.

For more information about the SharePoint on-premises connector, go to SharePoint on-premises.

Before you begin: 

Jump to:

Configure the SharePoint on-premises - Query a list action

Important: Inserting variables to the SharePoint on-premises site URL and List name fields are not supported.

  1. Add the action to the workflow and open the action configuration panel. For more information, see Add an action to the workflow.

  2. Select a Connection. If you do not have a connection, see Add a connection.

    Note: 

    Only the connections created with the List and libraries authentication method are available in the drop-down list.

  1. Type a SharePoint site URL and click Retrieve lists. If you are retrieving the lists for a SharePoint subsite, type the URL of the subsite in the configuration. Parent sites do not automatically see all lists in child sites.

    Example:https://domain/sites/hr

  2. Select the List name that you want to query.

    3. 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.

  3. Type a value for Item limit.

  4. (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:

    1. Add the SharePoint on-premises- Query a list action to the workflow.
    2. Click the action to open the action configuration panel.
    3. Select a Connection.

    4. Type a SharePoint site URL and click Retrieve lists.

    5. Select the List name that you want to query.
    6. Type a value for Item limit.
    7. For Result, do the following:

      1. Click Add variable > Create variable.

        The Insert variables panel appears.

      2. For Name, type Query results and click Create & insert.

      The output from the Query a list action is stored in the Query results object.

  2. 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. Add the Log to instance details action to the workflow.
    2. Click the action to open the action configuration panel.
    3. Hover over the Message field, and click Insert.

      4. The Insert variables panel appears.

    4. Click SharePoint on-premises.
    5. Select Query results > First item, and then select a variable.
    6. Click Insert.

      7. The variable is added to the Message field.

Retrieve items from the query results

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

In the Designer page:

    1. Add the SharePoint on-premises- Query a list action to the workflow.
    2. Click the action to open the action configuration panel.
    3. Select a Connection.

    4. Type a SharePoint site URL and click Retrieve lists.

    5. Select the List name that you want to query.
    6. Type a value for Item limit.
    7. For Result, do the following:

      1. Click Add variable > Create variable.

        The Insert variables panel appears.

      2. For Name, type Query results and click Create & insert.

      The output from the Query a list action is stored in the Query results object.

    1. Add the Loop for each action to the workflow.
    2. Click the action to open the configuration.
    3. Hover over the Target collection field, and click Insert.

      4. The Insert variables panel appears.

    4. Click SharePoint on-premises.

    5. Select Query results > Items.

    6. Click Insert.

      7. The variable is added to the Target collection field.

    1. Add the Log to instance details action to the workflow.
    2. Click the action to open the configuration.
    3. Hover over the Message field, and click Insert.

      4. The Insert variables panel appears.

    4. Click Loop for each.
    5. Select Loop for each > Current item, and then select a variable.
    6. Click Insert.

      7. The variable is added to the Message field.

SharePoint on-premises- Query a list action fields and settings

Important: Inserting variables to the SharePoint on-premises site URL and List name fields are not supported.

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 on-premises 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 on-premises 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.

If you are retrieving the lists for a SharePoint subsite, type the URL of the subsite in the configuration. Parent sites do not automatically see all lists in child sites.

Example: https://domain.com/sites/hr

(n/a)
 

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 site URL.

(n/a)
 

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>."

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 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)
  Edit

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)
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.

Object

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 Automation 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: Integer
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).