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.

For more information about the SharePoint Online connector, go to SharePoint Online.

Configure the SharePoint Online - Query a list action

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.

1. Open the action in the configuration panel. For more information, see Add an action to the workflow.
2. In the Configuration section, select a SharePoint Online 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. For more information about authentication methods for SharePoint Online connections, see Authentication methods for SharePoint Online connections.

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

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

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

5. Type a value for Item limit.
6. 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.

Troubleshooting

If you receive the error Cannot get value for projected field for the SharePoint Online - Query a list action, attempt to resolve the error with these troubleshooting tips.

• Verify the following in the SharePoint list used in the action:

  • Ensure that SharePoint Groups are not used in a multi-values Person column.

  • Ensure that all users configured in a multi-values Person column has a proper email/ exchange license assigned.

• If you try the troubleshooting tips provided above and still get the error, consider using the SharePoint Online - Call a SharePoint web service action instead, and exclude the columns in the SharePoint list that give the error.

 

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

1. Query the required SharePoint Online list and store the queried results in an object output.

   1. Add the SharePoint Online- Query a list action to the workfow.
   2. Select a Connection.

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

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

   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.

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

   The Insert variables panel appears.

   4. Click SharePoint Online.
   5. Select Queryresults > First item, and then select a variable.

      Tip: If the variable is greyed out, then you must specify the SharePoint site URL and List name (see step 1). If you use a variable for the SharePoint site URL and List name, no information will be able to be retrieved to populate the First item variable.

   6. Click Insert.

   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 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:

1. Query the required SharePoint Online list and store the queried results in an object output.

   1. Add the SharePoint Online- Query a list action to the workflow.
   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.

2. Use Loop for each action to loop through the Queryresults 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.

   The Insert variables panel appears.

   4. Click SharePoint Online.

   5. Select Queryresults > Items.

   6. Click Insert.

   The variable is added to the Target collection field.

3. Retrieve each item using the Log to instance details action.
   

   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.

   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.

   The variable is added to the Message field.

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.