Function: Get Column Value for Version

Applies to: NITRO Workflows and Custom Actions (SharePoint Online and SharePoint On-premises(2019))

For complete list of functions, please refer to this article.

Introduction

For SharePoint lists there is a feature to append text in columns of the type ‘Multiple lines of text’. When ‘Append Changes to Existing Text’ is enabled in column settings, changes are shown as a thread in the column. The text entered earlier cannot be changed, and new text is shown above the earlier text. Each version of the item has its own value for this column (blank if not entered while saving the item).

Function ‘getcolumnvalueforversion’ can be used to get the column value for a version enabled multiple lines of text column. Value can be fetched for a specific version of the item or for all versions.

Note: Source column should be multiple lines of text column with append changes enabled. And to enable this setting, version history should be enabled for the list.

Syntax:

Without Expression Builder

$getcolumnvalueforversion(Column Name,Version,SortOrder,Format,textType)

With Expression Builder

Parameters to be defined in above function:

ColumnName:

Specify column internal name of multiline lines of text column. Only column name is supported, and column placeholders are not supported for this.

To know the column internal name, go to the list -> List Settings -> Edit the column and find the column internal name in the browser URL as shown below:

Ex: WorkLog

Version:

This parameter has below supported values.

  • All: Return column value from all the versions of the item.
  • First: Return column value from the first version of the item (earliest).
  • Last: Return column value from the most recent version (last).
  • LastNonBlank: Return column value from the latest version where value of this column is not blank. If item is saved without specifying any text for this column, then in that version of the item value of this column will be blank.
  • Enter specific version number: Return column value from the specified version (Ex: 2.0 – returns the value from item version 2.0)

SortOrder:

Specify the sorting order for the versioned data for the column. SortOrder is applicable only for version of type ‘All’. Supported values for this parameter are below:

  • LastToFirst: Column value in the latest versions of the item will be before the values entered in earlier versions (latest first). This is the default setting for this parameter.
  • FirstToLast: Values will be in ascending order of the item version (earliest first).

Format:

Column version value can be fetched with or without header. Every item version has a user and timestamp associated with it.

  • withHeader: Include name of user who created that version (user saving/editing item) and the time at which it was saved.

Example: $getcolumnvalueforversion(WorkLog,All,LastToFirst,withHeader)

  • withoutHeader: Only return column value text and not the user or timestamp.

Example: $getcolumnvalueforversion(WorkLog,All,LastToFirst,withoutHeader)

See the difference in output of “withHeader” and “withoutHeader” format in below screenshot.

  • Custom Value: This option can be used to arrange the column value, user and timestamp in a custom format. Below is the sample value for custom format:

@{UserName} – Created: {Created} \r\n Columnvalue: {ColumnValue}

Example: $getcolumnvalueforversion(WorkLog,All,LastToFirst,@{UserName} – Created: {Created} \r\n Value: {ColumnValue})

Only the below placeholders are supported in this option:

  • {UserName}: Name of the user who saved the item with the column value.
  • {Created}: Date and time when the version was created.
  • {ColumnValue}: Text value of the column.

textType:

This is an optional parameter. It is required in “Post Adaptive Card” action as plain text value appears better in the adaptive cards used in NITRO bots.

This parameter supports “Plain” and “Normal” values.

  • Plain: Returns the column value in plain text format. HTML is not used in the header and value formatting.
  • Normal: Returns the column value in normal styling that includes HTML.

See the difference in output of ‘Plain’ and ‘Normal’ text type in below screenshot:

Note:
In case of ‘Post an Adaptive Card’ action, we need to use this parameter otherwise the output will have HTML code along with text:

Example: %%$getcolumnvalueforversion(WorkLog,All,LastToFirst,withHeader)%%

Output of post adaptive card action with ‘Plain’ text type:

Output of post adaptive card action with ‘Normal’ text type:

Sample use case: Notify requester when worklog is modified.

Details:

  1. This workflow uses Tickets list that is version enabled, and “Work Log” is a multiple lines of enhanced rich text column with append changes enabled.
  2. This workflow is configured on item modified event when the “Work Log” column value is changed.
  3. Configure send mail action to notify requester with latest worklog comments.
  4. Configure post an adaptive card action to notify requester in teams with latest worklog comments.

Conditions:

Work Log:afterchange:[[Any]]

This condition indicates that value of worklog column has changed in this update to the item. If there is no change to this column then the workflow will not be executed.

Action 1: Notify requester when worklog is modified.

Configure send mail action to notify requester as shown below:

Mail template:

Placeholder used in the mail template: %%$getcolumnvalueforversion(WorkLog,Last,LastToFirst,withHeader)%%

Action 2: Send adaptive card to Requester when worklog is modified.

Configure ‘Post an Adaptive Card’ action to send adaptive card to requester.

Placeholder used in the description column below:

%%$getcolumnvalueforversion(WorkLog,Last,,withHeader,plain)%%

Sample output:

Version history of the Work Log column in Tickets list:

Column value in the email received:

Column value in the adaptive card received in Teams: