Expression builder in Custom Actions

Applies to:

NITRO activated sites in SharePoint Online and On-Premises

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:

  1. Current Item
  2. Variables
  3. Function
  4. Fixed Value
  5. Contextual
  6. 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:

  1. Current Item
  2. Property
  3. Query List Actions
  4. Actions
  5. Variables
  6. Function
  7. Fixed Value
  8. 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:

  1. Invoke Web Service
  2. Query List

Example:

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

  1. QL – Single Item
  2. 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

  1. Use add Item action result in other actions using expression builder
  2. Loop query list item collection to create items
  3. 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