Syntax for Expressions in Custom Actions

Applied to:

Crow Canyon NITRO activated sites in:

SharePoint Online and On-Premises 2013/2016/2019/SharePoint Server Subscription Edition

Introduction

This article describes the syntax used for column placeholders, functions, variables etc. in Custom Actions.

Summary:

  1. Condition Builder Control
  2. Column Mappings
  3. Mail Templates
  4. Query List Actions
    • Query
    • Query builder
    • Use of query list action result
  5. Variables

Detailed steps

Condition Builder Control

Supported placeholders:

Column value/PlaceholderSupported
Fixed valuesYes
Contextual placeholders ({Today}, {Now}, {Me})Yes
Item column value placeholderYes
FunctionsYes
URL Query string parametersNo
Custom Action variablesYes

Below is the syntax for using column placeholder in condition builder control in Custom Actions.

Syntax: [Column Internal Name]

Ex: [RequestStatus]

Below table describes placeholders syntax that can be used in condition builder.

PlaceholdersExample/Scenario
[Column Internal Name]Syntax to compare value between two columns with different type of operators.
Ex: [RequestStatus]  
Variable##ValueSyntax to compare column with a variable value.
The variable value can be updated at run time using query list actions, execute script action and this updated variable values can be used in conditions, column mappings in further actions.
Ex: VarStatus##Value
Query list action name##[Column Display Name|Column Internal Name]Syntax to compare a column value with query list item column value.
Ex: GetConfiguration##[Approver|Approver]
Contextual placeholders  {Today}
{Me}
Advanced Condition
In normal conditions, left side operand is always a column from the list. Sometimes we need to compare values other than list columns. For example, the count of items fetched in a query list action. Or, value of a single query list item column. Or, value of a Workflow variable.
QL_GetApprovalLevels##$count([ID|ID]),,0
,
Column TypeSyntax/PlaceholderExample/Scenario
Person or GroupFixed ValueDisplay Name or email address of User i.e. James Restivo/james@contoso.com
Person or Group[Column Internal Name][Requester]
LookupFixed ValueCalendar
Lookup[Column Internal Name][Category]
Date and TimeFixed value i.e. MM/DD/Year03/20/2021
Date and Time{Today}Current date
Date and Time{Now}Current date and time
Date and Time[Column Internal Name][DueDate]
All other columnsFixed value
Closed, Not Started etc..
All other columns[Column Internal Name][RequestStatus]

Column Mappings

Supported placeholders:

Column value/PlaceholderSupported
Fixed valuesYes
Contextual placeholders ({Today}, {Now})Yes
Item column value placeholderYes
FunctionsYes
URL Query string parametersNo
Custom Action variablesYes

Below is the syntax for using column placeholder in column mappings of different actions.

Syntax: [Column Display Name|Column Internal Name]

Ex: [Request Status|RequestStatus]

Column TypeSyntax/PlaceholderExample/Scenario
Person or GroupFixed value

$parseuservalue(Display name/email address)
• $parseuservalue(James Restivo)
• $parseuservalue(james@contoso.com)
Person or Group[Column Display Name|Column Internal Name][Requester|Requester]
LookupFixed Value


1. $parselookupvalue(Display name of lookup item value)
Ex: $parselookupvalue(Email)
2. ID of lookup item
Ex: 5
Lookup[Column Display Name|Column Internal Name][Category|Category]
Date and TimeFixed value i.e. MM/DD/Year03/20/2021
Date and Time{Today}Current date
Date and Time{Now}Current date and time in UTC
Date and Time[Column Display Name|Column Internal Name][Due Date|DueDate]
All other columnsFixed value
All other columns[Column Display Name|Column Internal Name][Request Status|RequestStatus]

Mail Template in Send Mail actions

Supported placeholders:

Column value/PlaceholderSupported
Fixed valuesYes
Contextual placeholders ({Today}, {Now})Yes
Item column value placeholderYes
FunctionsNo
URL Query string parametersNo
Custom Action variablesYes

Syntax for using column placeholder in mail templates in Send Mail action

Syntax 1: [[Column Internal Name||Column Display Name]]

Ex: [[FirstName||First Name]]

Syntax 2: [Column Display Name|Column Internal Name]

Ex: %%[Assigned Staff|AssignedStaff]%%

Contextual placeholders supported in mail templates:

SyntaxDescription
[[Today Site||Today]]Date as per site regional settings
[[view:View ID||View Name]]To print list view items in the email.
Note: This should be used only for scheduled reports workflows
Ex: [[view:113619f5-40e9-4336-85b2-57b457b6673e||All Open Tasks]]  

To find view id, navigate to the list -> list settings -> Edit the view and find the view Id in the browser URL
[[VersionHistory:ColumnInternalName||ColumnDisplayName]]To print item version history from a version enabled multiple lines of text column.
Ex: [[VersionHistory:WorkLog||Work Log]]
%%Query list action name##[Column Display Name|Column Internal Name]%%To print query list action result in mail template.
Ex: %%QLItem##[Modified By|Editor]%%
%%Variable name##Value%%%%Variable name##Value%%
To print variable value in mail template.
Ex: %%VarManager##value%%
%%Column Placeholder%%
To print column placeholder value in mail template.

Ex: %%[Assigned Staff|AssignedStaff]%%

To print functions with column placeholders value in mail template, use below syntax:
Syntax: %%Function Name([Column Display Name|Column Internal Name])%%

Ex: %%$formatvalue([Category|Category])%%

Query List Action

Supported placeholders:

Column value/PlaceholderSupported
Fixed valuesYes
Contextual placeholders ({Today}, {Now})Yes
Item column value placeholderYes
FunctionsYes
URL Query string parametersNo
Custom Action variablesYes

Syntax for using column placeholder in “Query” and “Query Builder”.

Query

Supported column place holder’s syntax in Query container.

Syntax 1: %%[Column Display Name|Column Internal Name]%%

Below placeholder is also supported for backward compatibility.

Syntax 2: %%[Column Internal Name]%%

Ex 1:

Ex 2:

Query Builder

Syntax for using column placeholder in query builder is:

Syntax: %%[Column Display Name|Column Internal Name]%%

Ex: %%[Request Status|RequestStatus]%%

Special type of syntax needs to be used for lookup, person or group and date and time column types in query builder. Refer below table.

In this table, to know query list column types and current list column types in Query builder, refer above image

Query list column TypeCurrent list column TypeSyntax
Single line of text  Lookup%%$parsefieldvalue([Column Display Name|Column Internal Name],Value)%%
Ex: %%$parsefieldvalue([Category|Category],Value)%%
LookupSingle line of text%%[Column Display Name|Column Internal Name]%%
Ex: %%[Category|Category]%%
LookupLookuplookupid:%%$lookupid([Column Display Name|Column Internal Name])%%
Ex: lookupid:%%$lookupid([Category|Category])%%
IDLookuplookupid:%%$lookupid([Column Display Name|Column Internal Name])%%
LookupIDlookupid:%%[ID|ID]%%
Person or GroupPerson or Group userId:%%$lookupid([ColumnDisplayName|ColumnInternalName])%%
Date and TimeFixed daysOffsetDays:+/-Number of days  
All other columnsAll other columns%%[Column Display Name|Column Internal Name]%%  

Example for Date and Time with fixed days:

Use of query list action result

SyntaxUse cases
{CurrentAction}##Column Internal NameThis syntax is used to update the variable value with Query list action result (Single item)
Query list action name##Column Internal Name
OR
Query list action name##[Column Display Name|Column Internal Name]  
This syntax is used to get the column value from a query list action result.
This can be used in workflow conditions and column mappings of Add/Update list item actions
Item:
Variable Mappings:

To update the variable value with query list item result, use below syntax

Syntax:

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

Below syntaxes are also supported as backward compatibility.

  1. {CurrentAction}##Column Internal Name
  2. Query list action name##Column Internal Name
Column Mappings/Conditions:

Below syntax can be used in condition builder control and column mappings of Custom Actions.

Syntax: Query list action name##[Column Display Name|Column Internal Name]

This syntax can be used for same type of columns in both source and target lists in column mappings and conditions.

Below table describes some of the use cases and syntax for different type of columns in source and target list column mappings and conditions.

Target column typeQuery list item column typeExample/Scenario
Person or GroupText columns that have value of user display name or email address    Get query list item from Ticket’s list and update ‘Assigned Staff’ person or group column with “Requester Email” (single line of text) from query list item.

Ex: $parseuservalue(Query list action name##[Requester Email|RequesterEmail])  

‘Requester Email’ placeholder can contain below values: James Restivo james@contoso.com
LookupText columns that have the value of a lookup item    Get query list item from ‘Category’ list and update the ‘Category’ lookup column in Ticket’s list with query list item title.

$parselookupvalue(GetCategory##[Title|Title])

‘Title’ column placeholder will have ‘Email’ value
Single line of textLookup
Person or Group
Get query list item from ‘Tickets’ and update ‘Staff Email’ single line of text column with ‘Assigned Staff’ (Person or Group) from query list item.

Ex: $formatvalue(GetTicket##[Assigned Staff|AssignedStaff])
 
$formatvalue(GetTicket##[Category|Category])  
Advanced Conditions:

Below syntax can be used in advanced condition:

Syntax: Query list action name##[Column Display Name|Column Internal Name],,Value

Ex 1: QL_GetApprovalLevel##$count([ID|ID]),,0

This example will check whether the items found for the query list action or not.

Ex 2: QL_GetApprovalLevel##[Approvers| Approvers],,

This example is used to validate the query list item column value empty or not empty

Item Collection
Variable Mappings, Column Mappings and Conditions

Only certain functions are supported for query list items (item collection) like $variablecollection, $count, $sum.

$variablecollection:

This function is used to get the collection of column values from query list items.

Syntax:

Query List Action Name##$variablecollection(separator,[ColumnDisplayName|ColumnInternalName])

Ex: Combine “Approver” and “Owner” column values in multi selection enabled person or group from Query list items and create tasks for each user.

GetApprovers##$variablecollection(;#,[Approver|Approver],[Owner|Owner])

Select run for as “Variable” and use ‘@{CollectionValue}’ for “Assigned To” column mappings in create item action in Custom Actions (see screenshot below).

$sum:

This function performs the sum operation on values returned from parameters. This can only be used on query list action to sum the values from multiple items.

Syntax: QuerylistActionName##$sum([FieldDisplayName|FieldInternalName])

Ex: GetPurchaseItem##$sum([Cost|Cost])

$Count:

This function performs the count operation on values returned from parameters. This can only be used on query list action to count the values from multiple items.

Syntax

QuerylistActionName##$count([ColumnDisplayName|ColumnInternalName])

Ex: GetApprovalLevel##$count([ID|ID])

Syntax to read column value from create Item action

Create item action can be act as query list action. We can use below syntax to update the created item values in further actions.
Ex: We have a workflow that will run on Purchase Requests list, it will create Purchase Order when Purchase request is approved.

In this case, we can use below syntax and update the Purchase Order Id in Purchase Request item in the same action

Syntax: Create item action name##column placeholder

Variables

Supported placeholders:

Column value/PlaceholderSupported
Fixed valuesYes
Contextual placeholders ({Today}, {Now},{UtcNow})Yes
Item column value placeholderYes
FunctionsYes
URL Query string parametersNo
WF variablesYes

Column Mappings and Conditions

To read value from a variable use below syntax:

Syntax: Variable Name##Value

Ex: VarAccountType##Value

Column Mappings (Run for variable collection)

Below syntax is used to create items for variable collection.

Syntax: @{CollectionValue}