Applies to: SharePoint Online and On-Premises

Introduction

Query List action is used to fetch the items meeting the required filter criteria and then do some action on those items. For example, we can fetch tasks that are ‘Open’ and are of a particular ‘Type’ and then send a reminder mail to assignee of each task. The filter conditions are specified in the Custom Action settings and cannot be changed during execution. More details on this action are here: https://www.crowcanyon.help/article/470/

‘Query list (Search)’ action allows user to specify the search text and select the required items when custom action is executed. User selected items can then be processed in subsequent actions like normal Query List action.

Detailed Description

Search Type

Select the search type to fetch the items from the list:

• NITRO Search: Uses CAML query to search the matching items from SharePoint list. It uses both the conditions specified in ‘Filter Items By’ option and the user entered text.
• SharePoint Search: This will search on indexed content to find the relevant matching items. For large lists, this can give better results.
• Both: User will have the option to select the search type during action execution.

Selection Type

Whether to get a single item or a collection of items. This depends on the business use case.

Select Query Site URL

Select the site of the list from which items have to be fetched. Items can be fetched from any site within the site collection. Based on site selection, next drop-down for the lists will be populated.

Query List

Select list from which items have to be fetched. Please note that next option depend on this selection.

Filter Items By

List View

Fetch items from the list selected in the ‘Query List’ option based on the selected ‘List View’. Items will be fetched based on the conditions specified in the list view conditions and the search text entered by the user.

Query Builder

Fetch items from the list selected in the ‘Query List’ option based on the conditions specified in the Query Builder and the search text entered by the user.

Column placeholder syntax in the conditions: %%[Column Display Name|Column Internal Name]%%

Ex: %%[ID|ID]%%

For more details about the syntax used in query list action, refer to this article.

Query

Fetch items from the list selected in the ‘Query List’ option based on the specified CAML query and the search text entered by the user. Please refer CAML query syntax from this article.

Sample CAML query:

<View>
<Query>
<Where>
<Eq>
<FieldRef Name='RelatedItems' LookupId='TRUE'/>
<Value Type='Lookup'>%%[ID|ID])%%</Value>
</Eq>
</Where>
</Query>
</View>

Item Selection Mandatory

Whether selecting one or more items from search result is mandatory for the user to continue Custom Action execution.

Continue Custom Action Execution

This option can be used to continue or stop the execution of the Custom Action based on whether items are selected in the Query List search result or not.

Always: This is the default option and execution of subsequent actions is not dependent on whether any items are selected in the Query List search results or not.

Only if item(s) selected: This option will continue the custom action execution flow only when one or more items are selected in the Query list action. This is used in common scenario where we are fetching items using Query List search action and then there is an Update Item action to update all the items selected from the query list search results. If there are no items selected in Query List search results, then it is not required to run the action to update them. However, note that if there are any other actions after Update Item action then those too will be skipped. Hence, use this option if all subsequent actions should be skipped if no items are selected in Query List Search action.

Only if no item selected: This option will continue the custom action execution flow only when no items are selected from the Query List search action.

Note: All subsequent actions will be skipped if execution is stopped at the Query List action.

Grid Settings

Search result selection limit

Specify the number of items user can select from the search results. The maximum number of items a user can select is 500.

Display Columns

Select the columns to be shown in the search results grid.

First sort by the column

Select the column to sort the items in search results.

Items Per Page Limit

Specify the number of items to be displayed per page in the search results.

Variable mappings

Configure variables to update the query list search results. Follow below syntax to update the variable value with Query list search results for single item and item collection.

Item:

Syntax:

1. {CurrentAction}##[Column Display Name|Column Internal Name] OR
2. Query list action name##[Column Display Name|Column Internal Name]

Item Collection:

Only functions that aggregate the values are supported for Query list items (item collection): $variablecollection, $count, $sum.

For more details about the syntax, refer to this article

Using Query list search action results

We can use the query list search action results in different places for single and multi-items selection.

Query list search (Single Item):

Below query list search action is configured to select a single item. We can use this search result in different places. Below table describes the syntax and the use case.

Syntax	Use cases
{CurrentAction}##Column Internal Name or {CurrentAction}##[Column Internal Name|Column Display Name]	This syntax is used to update the variable value with Query list action search result
Query list action name##Column Internal Name OR Query list action name##[Column Display Name|Column Internal Name] Ex: Select Parent Ticket##[ID|ID]	This syntax is used to get the column value from a query list search action result. This can be used in Custom Action conditions and column mappings of Add/Update list item actions

Query list search (Multiple Items):

We can use the selected items in the query list search action results in below places in Custom Actions:

• Run loop for selected items in the query list search action results.

• Run below actions on the selected items from the search results.
  • Add List Item
  • Update List Item
  • Delete List Item
  • Invoke Web Service
  • Invoke Workflow
  • Parse CSV
• Functions
  • QL Count
  • QL Sum
  • QL First Item
  • QL Last Item
  • QL Variable Collection

Sample use cases

Use case1: Link to Problem (Item)

Details:

1. This custom action uses “Tickets” and “Problems” lists.
2. “Tickets” list has a lookup type column pointing to “Problems” list.
3. This action is configured on the Tickets list.
4. Configure query list search action to find the required problem from the “Problems” list.
5. Configure update item action on Ticket item to update ‘Related Problem’ lookup column with the selected problem from the search results in #4.
6. Configure update item action to update the worklog of the selected problem.

Custom Action

Action 1: Search problem

Configure query list search action to get open problems.

Settings to store the Id of the selected problem in the variable. This Id can then be used to update the lookup column in the Ticket item.

Action 2: Update related problem in Ticket

Configure update item action to update the ‘Related Problem’ in Ticket.

Action 3: Update worklog in problem

Configure update item action to update the worklog in problem.

Use case2: Link Tickets to Problem (Item Collection)

Details:

1. This custom action uses “Tickets” and “Problems” lists.
2. “Tickets” list has a lookup column pointing to “Problem” list.
3. This action is configured on the Problems list.
4. Configure a query list search action to select the required Tickets from “Tickets” list.
5. Configure update item action to update ‘Related Problem’ lookup column in the selected Tickets from the query list search results in #4.
6. Configure update item action to update the worklog in selected problem.

Custom Action:

Action 1: Get Open Tickets

Configure query list search action to get open Tickets. ‘Filter Items By’ section is configured to show only those Tickets that are not resolved or closed. This way user will only be able to select open Tickets for linking to the problem.

Action 2: Update problem in selected Tickets

Configure update item action to run for user selected Tickets in the QL search results in action#1 above to update the ‘Related Problem’ lookup column in Ticket.

Action 3: Update worklog in problem

Configure update item action to update the worklog in problem. Note the expression builder syntax to get a comma separated list of Ids of all the Tickets linked to this problem.