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:
- Condition Builder Control
- Column Mappings
- Mail Templates
- Query List Actions
- Query
- Query builder
- Use of query list action result
- Variables
Detailed steps
Condition Builder Control
Supported placeholders:
| Column value/Placeholder | Supported |
| Fixed values | Yes |
| Contextual placeholders ({Today}, {Now}, {Me}) | Yes |
| Item column value placeholder | Yes |
| Functions | Yes |
| URL Query string parameters | No |
| Custom Action variables | Yes |
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.
| Placeholders | Example/Scenario |
| [Column Internal Name] | Syntax to compare value between two columns with different type of operators. Ex: [RequestStatus] |
| Variable##Value | Syntax 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 Type | Syntax/Placeholder | Example/Scenario |
| Person or Group | Fixed Value | Display Name or email address of User i.e. James Restivo/james@contoso.com |
| Person or Group | [Column Internal Name] | [Requester] |
| Lookup | Fixed Value | Calendar |
| Lookup | [Column Internal Name] | [Category] |
| Date and Time | Fixed value i.e. MM/DD/Year | 03/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 columns | Fixed value | Closed, Not Started etc.. |
| All other columns | [Column Internal Name] | [RequestStatus] |
Column Mappings
Supported placeholders:
| Column value/Placeholder | Supported |
| Fixed values | Yes |
| Contextual placeholders ({Today}, {Now}) | Yes |
| Item column value placeholder | Yes |
| Functions | Yes |
| URL Query string parameters | No |
| Custom Action variables | Yes |
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 Type | Syntax/Placeholder | Example/Scenario |
| Person or Group | Fixed value | $parseuservalue(Display name/email address) • $parseuservalue(James Restivo) • $parseuservalue(james@contoso.com) |
| Person or Group | [Column Display Name|Column Internal Name] | [Requester|Requester] |
| Lookup | Fixed 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 Time | Fixed value i.e. MM/DD/Year | 03/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 columns | Fixed value | |
| All other columns | [Column Display Name|Column Internal Name] | [Request Status|RequestStatus] |
Mail Template in Send Mail actions
Supported placeholders:
| Column value/Placeholder | Supported |
| Fixed values | Yes |
| Contextual placeholders ({Today}, {Now}) | Yes |
| Item column value placeholder | Yes |
| Functions | No |
| URL Query string parameters | No |
| Custom Action variables | Yes |
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:
| Syntax | Description |
| [[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/Placeholder | Supported |
| Fixed values | Yes |
| Contextual placeholders ({Today}, {Now}) | Yes |
| Item column value placeholder | Yes |
| Functions | Yes |
| URL Query string parameters | No |
| Custom Action variables | Yes |
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 Type | Current list column Type | Syntax |
| Single line of text | Lookup | %%$parsefieldvalue([Column Display Name|Column Internal Name],Value)%% Ex: %%$parsefieldvalue([Category|Category],Value)%% |
| Lookup | Single line of text | %%[Column Display Name|Column Internal Name]%% Ex: %%[Category|Category]%% |
| Lookup | Lookup | lookupid:%%$lookupid([Column Display Name|Column Internal Name])%% Ex: lookupid:%%$lookupid([Category|Category])%% |
| ID | Lookup | lookupid:%%$lookupid([Column Display Name|Column Internal Name])%% |
| Lookup | ID | lookupid:%%[ID|ID]%% |
| Person or Group | Person or Group | userId:%%$lookupid([ColumnDisplayName|ColumnInternalName])%% |
| Date and Time | Fixed days | OffsetDays:+/-Number of days |
| All other columns | All other columns | %%[Column Display Name|Column Internal Name]%% |
Example for Date and Time with fixed days:
Use of query list action result
| Syntax | Use cases |
| {CurrentAction}##Column Internal Name | This 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:
- {CurrentAction}##[Column Display Name|Column Internal Name]
- Query list action name##[Column Display Name|Column Internal Name]
Below syntaxes are also supported as backward compatibility.
- {CurrentAction}##Column Internal Name
- 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 type | Query list item column type | Example/Scenario |
| Person or Group | Text 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 |
| Lookup | Text 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 text | Lookup 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/Placeholder | Supported |
| Fixed values | Yes |
| Contextual placeholders ({Today}, {Now},{UtcNow}) | Yes |
| Item column value placeholder | Yes |
| Functions | Yes |
| URL Query string parameters | No |
| WF variables | Yes |
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}
