Query List
This action will retrieve an item,item collection or aggregates based on a lookup column, related items, list views, query builder or query. The output of this action can then be used in other actions (like Create Item or Update Item). For example, you may want to cancel all Tasks related to a request when that request is cancelled. Or for each of these tasks meeting some criteria, you may want to create an item in another task list.
The Query List action would precede another action, if that Query List is to be used in that other action. "Query List" itself just generates a list of items based on a CAML query so that this list can be used in the other actions: Create Item, Update Item, Delete Item, and Manage Permissions.
This action is also used to check if items with certain criteria are present or not. For example, you can check if all related tasks of a request are closed and if so then change the status of the request.
Query List Action in Designer Workflow:
Query List Action in Legacy Workflow:
Query List Action in Custom Action:
Action Name: enter a unique name for query list action.
Action Description: enter a description query list action.
Type:
A Query List can be generated as
•a single Item (Type = Item)
•a collection of Items (Type = Item Collection) or
•to put aggregate values fetched items in variables so that they can be used in other actions ( Type = Aggregates)
Note: Query list of type aggregation will not be visible in other actions normal places where we use Query list of type (Item or Item Collection like in loop control, run for query list etc.)
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 depends on this selection.
Get Item(s) From:
•Lookup Column:
In Workflow: This option will show a drop-down to select the lookup columns from the list of the item on which workflow is configured. Note that these lookup columns should be for the list selected in the ‘Query List’ option above. A lookup column contains (or references) items from another list (lookup list specified in the lookup column settings). This option will read these items from the lookup column of the item selected in the ‘Run for’ option:
In Custom Action: This option will show a drop-down to select the lookup columns from the list of the item selected in ‘Run for’ option.
•Related Items:
In Workflow: For this scenario, list selected in the ‘Query List’ should have a lookup to the list of the item on which workflow is configured. For example: Fetch items from 'Purchase Orders' list based on selected lookup column and view below. Lookup column in 'Purchase Orders' should be of 'Purchase Requests' list.
In Custom Action: For this scenario, list selected in the ‘Query List’ should have a lookup to the list of the item selected in the ‘Run for’ option.
Item Limit: number of items returned from the top of result set as per the conditions and sorting order. Maximum value supported is 100.
Scope: it specifies the scope for returning list items and list folders in a list view. This option will be visible if the list selected in query list is folder activated or document library. It supports 4 scope as described below:
• DefaultValue: all list items and all list folders in the current list folder are returned in the list view
• Recursive: all list items in the list are returned in the list view
• RecursiveAll: all list items and all list folders in the list are returned in the list view
• FilesOnly: all list items in the current list folder are returned in the list view
Note: To enable folder option in custom list
Go to list -> List settings -> Advanced settings -> Folders
By activating this feature, you can create folder in custom list by clicking 'Folder' in 'New' button dropdown
•List View:
In Workflows: 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 View conditions.
In Custom Action: Fetch items from the list selected in the ‘Query List’ option based on the selected ‘List View’. In this case it is not necessary that there are any lookup column relations between list of the item selected in the ‘Run for’ option and the list selected in the ‘Query List’ option. Items will be fetched based on the conditions specified in the View conditions
•Query Builder:
In Workflow: Fetch items from the list selected in the ‘Query List’ option based on the conditions specified in the Query Builder.
In Custom Action: Fetch items from the list selected in the ‘Query List’ option based on the conditions specified in the Query Builder. In this case it is not necessary that there are any lookup column relations between list of the item selected in the ‘Run for’ option and the list selected in the ‘Query List’ option.
Column placeholders from current list can be specified in the conditions.
Placeholder Syntax: %%[Column Display Name|Column Internal Name]%%
E.g. %%[ID|ID])%%
for more details refer this article
•Query:
In Workflow: Fetch items from the list selected in the ‘Query List’ option based on the specified CAML query.
In Custom Action: Fetch items from the list selected in the ‘Query List’ option based on the specified CAML query. In this case it is not necessary that there are any lookup column relations between list of the item selected in the ‘Run for’ option and the list selected in the ‘Query List’ option. Please refer CAML query syntax from this article.
,
Column placeholders from the current list can be specified in the query.
Placeholder Syntax: %%[Column Display Name|Column Internal Name]%%
E.g. %%[ID|ID])%%
Query Syntax:
<Where>
<Eq>
<FieldRef Name='LookupFieldInternalName' LookupId='TRUE' />
<Value Type='Lookup'>##ParentFieldLookupName##</Value>
</Eq>
</Where>
Sample CAML Query:
<Where>
<Eq>
<FieldRef Name='RelatedTicket' LookupId='TRUE' />
<Value Type='Lookup'>##ID##</Value>
</Eq>
</Where>
Continue Workflow Execution
This option can be used to continue or stop the execution of the workflow based on whether items are fetched in the Query List action or not.
Always: This is the default option and execution of subsequent actions is not dependent on whether any items were fetched in the Query List action or not
Only if item(s) found: This option will continue the workflow execution only when one or more items are fetched in the Query list action. This is used in common scenario where we are fetching items using Query List action and then there is an Update Item action to update all the items fetched. If there are no items found in Query List 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. So use this option if all subsequent actions should be skipped if no items are found in Query List action
Only if no item found: This option will continue the workflow execution only when no items are fetched in the Query List action. Consider a workflow configured on Task list to set the status of the Task as Closed. And requirement is to Close the related Request of the Task if all Tasks of the Request are closed (Task has a lookup to Request). In this case, after updating the Task status as Closed, we can add a Query List action to check if all Tasks related to the Request are already Closed. Query List action will try to fetch all ‘Open’ tasks for the Request and if no such tasks are found then it can continue execution. After this Query List action there will be another task to update the Request status as Closed. And this update action will run only if previous Query List action didn’t fetch any open Tasks.
Note: All subsequent actions will be skipped if execution is stopped at the Query List action.
Please refer sample use case of Query list from this article
Delay Execution: it will delay action execution for specified seconds.
Variable Mappings: If 'Type' in query list action is selected as 'Item' or 'Item Collection' only then 'Variable Mapping' option will be visible. User can define variable in workflow by clicking 'Variable' button available in ribbon
In query List action variable mapping, user can set the value for variables defined in above step.
Aggregate Settings: If 'Type' in query list action is selected as 'Aggregate' then 'Aggregate Setting' option will be visible.
It will put aggregate values of selected query list columns in variables. and these variable can be used in further actions.
To add aggregate value in variable, click 'Add' button
Column: Select query list column from dropdown
Operator: Select aggregate operator from dropdown. Aggregate operator will come according to column selected. for example: for 'Number' type column supported aggregate operators are 'Count', 'Minimum', 'Maximum', 'Sum', 'Average'. For 'Single line text column' supported aggregate operators is 'Count'.
Variable: Select variable from dropdown ( all variables defined in workflow will be visible here) to store aggregate value of column.
• For more detail on expression builder refer to - Expression Builder
• For more information about Functions, refer to - Functions
Information on CAML Queries