Applies to:
NITRO activated sites in SharePoint Online, On-Premises (SharePoint Server Subscription Edition)
Description
This article describes the usage of expression builder in column mappings of different actions in Custom Actions.
Expression builder supporting below types of expressions:
- Current Item
- Variables
- Function
- Fixed Value
- Contextual
- Loop Item
To use expression builder in column mappings, go to the actions configured in custom actions, ‘Add column mappings’ and click ellipses icon to define the expression for column value.
1.Current Item
Select this expression type to use current list item (on which the custom action is running) column placeholders in column mappings.
2. Variables
We can define the variables in custom actions 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.
3. Functions
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:
- Current Item
- Property
- Query List Actions
- Actions
- Variables
- Function
- Fixed Value
- Contextual
Also, we can use nested functions in expression builder.
Simple function:
Nested function:
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.
Sample Output:
Approval task for Onboarding request – 12 is approved by: James Restivo
4. Fixed Value
Select this expression type to use fixed values in column mappings.
5.Contextual
Select this expression type to use the contextual placeholders in column mappings.
Supported contextual placeholders:
- Current Action
- ListId
- ListName
- Me
- Now
- SiteCollectionId
- SiteCollectionURL
- Today
- UtcNow
- WebId
- WebUrl
Current Action:
Use this contextual placeholder in expression builder to update the variables within the same action.
In custom actions, we can update the variables in below actions:
- Invoke Web Service
- Query List
Example:
- Get the response from Invoke Web Response and update it in a variable.
6.Loop Item
This expression type will be shown only when we run actions 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.
How to configure different functions using expression builder in Custom Actions
Simple Functions
Syntax for functions in expression builder is same as functions in custom actions. 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.
Calculate Date
Syntax: $calcdate([ColumnDisplayName|ColumnInternalName],number,Days/Weeks/Months/Years)
Case 1: Functions using ‘Current Item’ as input parameter
Case 2: Functions using ‘Variable’ as input parameter
Case 3: 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:
String Concatenation
Similarly, we can configure all other functions as shown above.
Query list action functions
We can fetch the single item or multiple items from a list using query list action in Custom Action. 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
QL Count:
This function performs the count operation on items returned from query list action item collection.
QL Sum:
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
In this sample use case, we have configured a custom action to create Purchase Order from a Purchase Request and update the created Purchase Order in Purchase Request using expression builder.
This sample use case assumes that Purchase Requests and Purchase Orders lists already exists.
Custom Action:
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:
Expression builder:
Use Case 2: Loop query list item collection to create items
In this sample use case, we have configured a custom action to create tasks for the approvers defined in approval levels list.
This use case assumes that ‘Approval Levels’ and ‘Tasks’ lists are already exists
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 queas shown below. In this action, we have configured ‘Assigned To’ and ‘Approval Level’ column mappings from loop item.
Expression builder:
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.
Variable mapping:
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 ‘Loop Item’ in expression builder as shown below
