Syntax for Expressions in NITRO Workflows

Applied to:

Crow Canyon NITRO activated sites in:

SharePoint Online and On-Premises 2013/2016/2019

Introduction

This article describes the syntax used for column placeholders, functions, variables etc. in NITRO Workflows.

Summary of syntax used in features:

  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},{UtcNow})Yes
Item column value placeholderYes
FunctionsYes
URL Query string parametersNo
WF variablesYes

Below is the syntax for using column placeholder in condition builder control in NITRO Workflows:

Syntax: [Column Display Name|Column Internal Name]

Ex: [Request Status|RequestStatus]

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

PlaceholdersExample/Scenario
[Column Display Name|Column Internal Name]Syntax to compare value between two columns with different type of operators.

Ex: [Request Status|RequestStatus]  
Variable##ValueSyntax to compare column with a variable value.

The variable value can be updated at run time using query list actions and create item actions 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]
afterchange:Used in conditions to check if the value of a column has been changed as part of the current update process.

Possible values:
1. afterchange:Fixed value
2. afterchange:[Column Display Name|Column Internal Name]
3. afterchange:[[Any]]
4. afterchange:[[None]]
beforechange:Used in conditions to check the value of a column before it was updated to another value by the current update process.

Possible values:
1. beforechange:Fixed value
2. beforechange:[Column Display Name|Column Internal Name]
3. beforechange:[[Any]]
4. beforechange:[[None]]  
Contextual placeholders  {Today}
{Now}
{UtcNow}
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.

Ex: 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 Display Name|Column Internal Name][Requester|Requester]
LookupFixed ValueCalendar
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 as per Utc
Date and Time{UtcNow}Current date and time as per site regional time settings
Date and Time[Column Display Name|Column Internal Name][Due Date|DueDate]
All other columnsFixed value
Closed, Not Started etc..
All other columns[Column Display Name|Column Internal Name][Request Status|RequestStatus]

Column Mappings

Supported placeholders:

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

Below is the syntax for using column placeholder in column mappings of add list item and update list item 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{UtcNow}Current date and time as per site regional time settings
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]

Below placeholders can be used in Microsoft Teams action in NITRO workflows

Contextual placeholders used in ‘Post an Adaptive Card’ action:

PlaceholderUse case
{UserEmail}Email of the user who has triggered the workflow from NITRO Bot.
This placeholder can be used to send adaptive card for the user who has triggered the workflow from NITRO Bot.  
{UserId}Id of the user who has triggered the workflow from NITRO bot.
This placeholder can be used to update person or group column from Bot.

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
WF variablesYes

Refer below table for syntax used in mail templates.

SyntaxExample/Scenario
[[Column Internal Name||Column Display Name]][[FirstName||First Name]]
[[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%%To print variable value in mail template.
Ex: %%VarManager##value%%

Query List Action

Supported placeholders:

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

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

Query

Supported column placeholder’s syntax in Query container.

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

Below placeholders are also supported for backward compatibility.

Syntax 2: ##Column Internal Name##

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

Ex 1:

Ex 2:

Ex 3:

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%%[Column Display Name|Column Internal Name]%%
Ex: %%[Category|Category]%%
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:

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 using query list item result

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 in NITRO Workflows.

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

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

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

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

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

This example is used to check whether 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

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

SharePoint On-Premises: Query List Action Name##$variablecollection([ColumnDisplayName|ColumnInternalName],separator)

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

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

SharePoint On-Premises: GetApprovers##$variablecollection([Approver|Approver],[Owner|Owner],;#)

Select run for as “Variable” and use ‘@{CollectionValue}’ for “Assigned To” column mappings in create item action in NITR Workflows (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 created Purchase Order Id in Purchase Request item.

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}