Column value expressions

Value of a column in the current table.

For example:

• In a virtual column : CONCATENATE([FirstName], " ", [LastName])
• In a slice filter condition : [Status] = "Complete"

All values in a column of a table.

For example: IN("Complete", Order[Status])

 [RefColumn].[Column] 

Value of the column Column  in the row referenced by RefColumn . RefColumn  must be of type Ref . See Dereference expressions .

For example:  [Order ID].[Status] = "Complete"

All values of the column Column  in the rows referenced by ListRef . ListRef  must be of type List , with subtype Ref .

For example:  [Related Orders][Status]

Value of the current column of the current row. Most often used in a column constraint ( Editable if ,   Required if , Show if , or Valid if ).

For example:

AND((LEN([_THIS]) > 3), CONTAINS([_THIS], "@"))  returns  TRUE  if the current column value (such as with  Valid if ) is at least three characters long and contains at least one  @  symbol.

Value of a column in the current table when the context is set to a different table in the expression. If you just specify [_THISROW]  (and omit the [Column] ), the value of the key column is returned.

For example:

FILTER("Orders", ([Customer] = [_THISROW].[Customer]))  returns keys to rows in the  Orders  data set in which the  Customers  column value is equal to the  Customers  column value of the current form (that is, orders for this customer).

SELECT(Orders[Order ID], ([Customer] = [_THISROW].[Customer]))  returns the  Order ID  column values (the row keys) for rows in the  Orders  data set in which the  Customer  column value is equal to the  Customer  column value of the current form. 

Value of a column in a parent or grandparent record.

Can only be used with:

• Embedded templates. See Access columns in parent and grandparent records .

• Nested SELECT() functions.
  
  For example, if you specify the following expression for a virtual column in the  TableRoot table:
  
  SELECT(Table1[Column], ANY(SELECT(Table2[Column], ANY(SELECT(Table3[Column])))
  
  Then:

  • [Column]  references  Table3 (innermost scope)
  • [_THISROW].[Column]  references  TableRoot (outermost scope)
  • [_THISROW - 1].[Column]  references  Table2  ( n scopes from the inner most scope)
  • [_THISROW - 2].[Column]  references  Table1 .

Value of a column just after it is updated within the context of a data change.

Note: Similar to [_THIS].[COLUMN] . Use _THISROW_AFTER if you want to use the value as it was after the change was made, but before any changes that were made during a bot execution.

The After value contains all field values obtained from the sheet or database record after it is applied, and includes:

• All virtual column values. The virtual column values are recomputed when the After value is retrieved. This ensures that the virtual column values reflect the most current field values.
• Field values that are computed by Google Sheets, Microsoft Excel, or SmartSheet worksheet formulas. This allows you to display or reference the field values that were computed by these worksheet formulas. 

For full details, see Access column values before and after an update .

For example, to display the after value of the  Priority  column in your template.

 The new value of Priority is <<[_THISROW_AFTER].[Priority]>> 

Value of a column just before it is updated within the context of a data change.

The Before value contains all field values obtained from the sheet or database record before it is applied, and includes all virtual column values. The virtual column values are recomputed when the Before value is retrieved. This ensures that the virtual column values reflect the most current field values.

For full details, see Access column values before and after an update .

For example, to display the before value of the  Priority  column in your template:

 The old value of the Priority columns was <<[_THISROW_BEFORE].[Priority]>>