NITRO activated sites in SharePoint Online and On-Premises
This article describes the usage of expression builder in column mappings of different actions in Custom Actions and NITRO Workflows. Earlier syntax of functions and placeholders continues to be supported. Expression builder is an easier way to configure complex mappings. Function parameters are directly configurable in UI. Unlike earlier syntax, Expression builder supports nested functions.
Expression builder supports below types of expressions in Custom Actions and Workflows
- Current Item
- ‘Run For’ Item
- Loop Control Item
- Fixed Value
To use expression builder in column mappings, go to the actions configured in custom actions/NITRO Workflows, ‘Add column mappings’ and click ellipses icon to define the expression for column value.
Expression builder in Custom Actions
Select this expression type to use current list item (on which the custom action/NITRO Workflow is running) column placeholders in column mappings.
2. ‘Run For’ Item
This expression type will be shown only for actions (like add and update item actions) that are configured to run for query list item collection or variable collection.
Select this expression type to loop the query list item collection column placeholder in column mapping.
For sample use cases, refer ‘Use Case 2’ and ‘Use Case 3’ below in this article.
3. Loop Control Item
Select this expression type to use the current iteration value of the loop item in the column mappings.
Note: There are below different type of loops in Custom Actions and in NITRO Workflows. This expression type will return different output for different type of loops.
For more details about the loop feature, refer this article: https://www.crowcanyon.help/article/696/
We can use this loop type to iterate the execution for a fixed number of times. We can specify the range using ‘Start Number’ and ‘End Number’ in the loop control settings.
Below is the sample loop configuration for ‘Number’ type. In this configuration, static numbers are defined in ‘Start Number’ and ‘End Number’.
As per the below screenshot, it will iterate the execution for 5 times as the ‘Start Number’ starts from 1 and on each iteration, ‘Start Number’ will be incremented by 1.
When we use the ‘Loop Control Item’ expression type in the column mappings, output will be like below:
- 1st Iteration: 1
- 2nd Iteration: 2
- 3rd Iteration: 3
- 4th Iteration: 4
- 5th Iteration: 5
We can use this loop type to iterate the execution based on a date range. We can specify the ‘Start Date’ and ‘End Date’ in the loop control settings.
Below is the sample loop configuration for ‘Date’ type. In this configuration, static dates are defined in ‘State Date’ and ‘End Date’
For example, loop configurations are like below:
- Start Date: 07/05/2022
- End Date: 11/05/2022
- Increment: 4
- Unit: Week
In this example ‘Run loop for date type’ is an action name of the loop control
- 1st Iteration: 7/05/2022
- 2nd Iteration: 8/2/2022
- 3rd Iteration: 8/30/2022
- 4th Iteration: 9/27/2022
- 5th Iteration: 10/25/2022
We can use this loop type to iterate the execution for each item in the result of a query list action. We can configure the query list loop type for single item and multiple items.
Below is the sample loop configuration for ‘Query List’ type.
‘Loop Control Item’ expression contains all columns data for the query list item. To get the specific column information from the loop item, we need to use ‘Get Property Value’ function.
Query list action:
Run loop for above query list items:
Loop control item properties used in the column mappings:
Column Mappings used in above action:
We can use variables to save the column values and use them in required actions in Custom Actions and NITRO workflows.
For example, here we have defined a variable with name ‘VarallUsers’. It is assigned the value from ‘Assigned Staff’ column on the item. ‘Assigned Staff’ is a multi-selection enabled person or group column. After this value is assigned, variable ‘VarallUsers’ will have a ‘collection’ of all users who are there in the ‘Assigned Staff’ column on the item.
We can use ‘Loop Control Item’ in expression type to create task for each user saved in above variable collection.
Assigned To column mapping:
We can define the variables in custom actions/NITRO Workflows to save the values and then use this variable result in conditions and column mappings of other actions. Select this expression type to use the result of a variable in column mappings.
Select this expression type to use functions in column mappings. Based on the selected function, we need to define the input parameters. These parameters are of below types:
In Custom Actions and Workflows:
- Current Item
- ‘Run For’ Item
- Loop Control
- Loop Control Item
- Query List Actions
10. Fixed Value
Also, we can use nested functions in expression builder.
Nested function is defining a function within the function. We can configure nested functions in expression builder as shown below:
In below example, we have configured ‘Format Value’ function inside ‘String Concatenation’ function in expression builder.
Approval task for Onboarding request – 12 is approved by: James Restivo
6. Fixed Value
Select this expression type to use fixed values in column mappings.
Select this expression type to use the contextual placeholders in column mappings.
Supported contextual placeholders:
- Current Action
Use this contextual placeholder in expression builder to update the variables within the same action.
In custom actions and Workflows, we can update the variables in below actions:
- Invoke Web Service
- Query List
- Create Item Action
- Get the response from Invoke Web Response and update it in a variable.
How to configure different functions using expression builder
Syntax for functions in expression builder is same as functions in Custom Actions/NITRO Workflows. Only the difference is instead of typing the expression value for functions in column mappings, we can define the expression value by selecting the functions and parameters from the expression builder.
Case 1: Functions using ‘Current Item’ as input parameter
Case 2: Functions using ‘Run For’ Item as input parameter
Case 3: Functions using ‘Loop Actions’ as input parameter
Case 4: Functions using ‘Property’ as input parameter
Case 5: Functions using ‘Actions’ as input parameter
Case 7: Functions using ‘Query List Actions’ as input parameter
To use the Query list item column placeholder in column mappings, use ‘Get Property Value’ function as shown below:
In below example, ‘GetItem’ is a query list action and configured the query list item column placeholder as input parameter in ‘Calculate Date’ function.
Query list action:
Similarly, we can configure all other functions as shown above.
Number Loop: To get values like currentindex, StartNumber, endNumber and Increment from ‘NumberLoop’ use below function
To get value of Current iteration number, configure expression builder like below:
To get value of currentindex of ‘NumberLoop’, configure expression builder like below:
Similarly, configure ‘Get Iteration Value’ function using expression builder to get value of endNumber, Increment, currentindex.
To get value of current iteration date, configure ‘Get Iteration Value’ function using expression builder like below:
To get value of StartDate, Configure Expression Builder like below:
Similarly, configure ‘Get Iteration Value’ function using expression builder to get value of endDate, currentindex, increment.
To get the loop control values like currentindex, endindex and query list columns value use ‘GetIterationValue’ function.
To get Title column value of QueryLoop item, configure expression like below:
Similarly, configure function using expression builder to get value of other columns of QueryLoop Item.
We can get the loop control values like currentindex, endindex and Variable Loop current iteration value using below function.
To get current iteration value of variable loop, configure expression builder like below:
Similarly, configure other column mapping like below:
Query list action functions
We can fetch the single item or multiple items from a list using query list action in Custom Action and NITRO Workflows. We can use below functions based on the output of a query list action (Item or Item collection)
In this example, we have configured two query list actions.
- QL – Single Item
- QL – Multiple Items
QL – Single Item
Function: Get Property Value
Use this function in expression builder to update a column value with the value from a query list item column.
QL – Multiple Items
Below query list action fetches multiple items from Tickets list.
Below functions can run on multiple items from output of a query list action.
QL Count and QL Sum
This function performs the count operation on items returned from query list action item collection.
This function performs the sum operation on values returned from “Cost” column for all items returned from query list action.
QL First Item and QL Last Item
QL First Item:
This function is used in Query List actions to capture the first item ID in an item collection.
QL Last Item:
This function is used in Query List actions to capture the last item ID in an item collection.
QL Variable Collection
This function gets the collection of values from parameters and combines them into a single value by joining the collection of values with a separator.
In below use case, it will retrieve users from ‘Assigned Staff’ column in each item of the query list action and combines them into a single value by joining ‘;#’ and updates all the users in a single multi selection enabled person or group column.
Sample Use Cases
- Use add Item action result in other actions using expression builder
- Loop query list item collection to create items
- Create items using variable collection
Use Case 1: Use add item action result in other actions using expression builder
- This custom action uses “Purchase Requests” and “Purchase Orders” list.
- “Purchase Requests” has a lookup column to “Purchase Orders” list.
- This action is configured on “Purchase Requests” list.
- Configure create item action to create a Purchase Order.
- Configure update item action to update “Purchase Order” lookup column in Purchase Request with the purchase order created in above #4.
Action 1: Create Purchase Order
Configure create item action to create purchase order. Use expression builder to copy the column values from Purchase Request to Purchase Order.
Column value expression:
Similarly configure all other column mappings as shown below:
Action 2: Update purchase order in purchase request
Configure update item action. Use expression builder to update Purchase Order lookup column in Purchase Request item as shown below:
Column value expression:
Use Case 2: Loop query list item collection to create items
- This custom action uses “Requests”, “Tasks” and “Approval Levels” lists.
- Requests list has associated tasks. “Tasks” list has a lookup column pointing to “Requests” list.
- This action is configured on “Requests” list.
- Configure query list action to get all items from “Approval Levels” list.
- Configure create item action to create tasks for all approval levels found in #4.
Custom Action: Create approval tasks
Action 1: GetApprovalLevels
Configure query list action to get the items from approval levels list.
Action 2: Create tasks
Configure create item action for above query list action as shown below. In this action, we have configured ‘Assigned To’ and ‘Approval Level’ column mappings from loop item.
Use Case 3: Create items using a variable collection
In this sample use case, we have configured a invoke web service action and result of invoke web service action is saving in a variable. This variable is then used to create items in a list.
Web Service used in this example is a publicly available REST API: https://reqres.in/api/users
For this example, we have defined a variable “VarUsers”. This variable will be used to store the user records from the web service response.
Custom Action: Create user records from web service
Action 1: GetUsers
Configure Invoke Web Service action as shown below. Please note “Define Variable Mappings” section at the bottom. Service response is a JSON and we need to extract the values as per the structure of the JSON and put it in variables for subsequent usage.
Configure the JSON path function in expression builder as shown below to extract the response from invoke web service action and update it in a variable.
This expression builder uses ‘CurrentAction’ as expression type as the response of web service is available in current action.
Web Service Response:
Response of invoke webservice action is given below. ‘VarUsers” variable will have the highlighted part from response as it is the extracted ‘data’ element from the response:
Please note that JSON path will be different as per the response format of your API.
Action 2: Create users
Configure create item action for above variable collection as shown below.
Define column mappings as shown below:
To get the property value from a variable collection, use ‘Run For’ item in expression builder as shown below: