New to expressions and formulas? Try Expressions: The Essentials.
You can also learn more about other types of expressions.
List Expressions
List expressions use operators that return a list or numeric value.
List Operators
- LIST(any-value[, another-value]...)
- { any-constant[, another-constant]... }
- table-name[column-name]
- any-list + another-list
- any-list - another-list
- ANY(any-list)
- AVERAGE(numeric-list)
- COUNT(any-list)
- FILTER(table-name, include-row?)
- IN(match-value, in-list)
- INDEX(any-list, number)
- LOOKUP(match-value, in-table, in-column, return-column)
- MAX(numeric-list)
- MAXROW(in-table, by-column, include-row?)
- MIN(numeric-list)
- MINROW(in-table, by-column, include-row?)
- ORDERBY(ref-list, column-name[, descending?[, another-column-name]]...)
- SELECT(table-column, include-row?[, only-distinct?])
- SORT(any-list[, descending?])
- STDEVP(this-list)
- SUM(numeric-list)
- TOP(any-list, number)
Constructing and Using Lists
In addition to basic column types, AppSheet supports typed lists, such as list of Text, list of Number, list of Price, list of Enum, etc. A list can contain zero or more values.
There are functions to construct lists and functions that use lists.
Constructing a List
- The simplest way to construct a list of constant values is by enclosing it in curly braces and listing the constant values separated by commas. {1, 2, 3}: constructs a list of constant Number values. {"Apple", "Banana", "Cherry"}: constructs a list of constant Text values.
- The LIST() function constructs a list from constants, column values, and expressions. LIST(1, 2, 3): constructs a list of constant Number values and is equivalent to {1, 2, 3}. LIST("Apple", "Banana", "Cherry"): constructs a list of constant Text values and is equivalent to {"Apple", "Banana", "Cherry"}. LIST([HomePhone], [CellPhone], [WorkPhone]): constructs a list from the specified column values of the row.
- You can construct a list from the values in a column. The list only contains distinct values. If the same value appears in multiple rows, that value only appears once in the list. Customers[Phone Number]: constructs a list of the distinct phone numbers contained in the Phone Number column of the Customers table.
- SELECT() is a powerful way to construct a list from another table. It's a stylized SQL "select-from-where" query. It constructs a list of values from a specified column of a table. However, a filter can be applied to control which rows are included. The resulting list only includes the specified column value of the included rows. SELECT(Customers[Phone Number], [State] = "WA"): returns a list of phone numbers of WA customers. Duplicate phone numbers may be present in the returned list. SELECT(Customers[State], [Country] = "USA", TRUE): returns a distinct (duplicate-free) list of states of United States customers.
- FILTER() is a special case of SELECT() which is used to construct a list of row references by applying a filter condition to a table.
- FILTER(Customers, [State] = "WA"): returns a list of customers in WA state
- FILTER(Customers, [Country] = "USA"): returns a list of customers in the USA
List Addition
LIST+ returns the combination of the first and second list.
- Customers[PhoneNumber] + {"1800-444-4444", "1800-555-5555"}: returns a combined list of phone numbers.
List Subtraction
LIST- returns the items in the first list after removing the items in the second list.
- Customers[PhoneNumber] - {"1800-444-4444", "1800-555-5555"}: returns a list of customer phone number with the second list of numbers excluded.
ANY(any-list)
ANY() picks a single arbitrary value from a list of values.
- ANY({1, 2, 3}): returns either 1, 2, or 3.
- ANY(Item[Quantity]): returns any one of the values in the Quantity column of the Item table.
- ANY(SELECT(Customers[Phone], CONTAINS([Name], "John Doe")): returns one of the values in the Phone column of Customers having the Name "John Doe".
AVERAGE(numeric-list)
AVERAGE() returns the average value of the items in the list.
- AVERAGE({1, 2, 3}): returns 2.
- AVERAGE({1, 2, 3, 4}): returns 2.5
- AVERAGE(Item[Quantity]): returns the average of the values in the Quantity column of the Item table.
COUNT(any-list)
COUNT() return the count of items in the list.
- COUNT({1, 2, 3}): returns 3.
- COUNT(LIST("Apple", "Banana", "Cherry")): returns 3.
- COUNT({"Apple", "Banana", "Cherry"}): is shorthand for the previous expression and also returns 3.
- COUNT(Customers[Phone Number]): returns the count of unique phone numbers contained in the Phone Number column of the Customers table.
- COUNT(SELECT(Customers[Phone Number], [State] = "WA"), TRUE): returns the count of unique phone numbers of WA customers.
IN(match-value, in-list)
IN() checks for the presence of a value in a list.
- IN([ColumnName], {1, 2, 3}): returns TRUE if ColumnName contains 1, 2, or 3. The list values must be constants in this form of list.
- IN([ColumnName], LIST((1 * 1), (2 * 2), (3 * 3))): returns TRUE if ColumnName contains 1, 4, or 9. This example illustrates that the list values may be either constants or expressions when the LIST() function is used.
- IN("Banana", [FruitEnumList]): returns TRUE if the column FruitEnumList contains the value, "Banana".
- OR(IN("Apple", [FruitEnumList]), IN("Banana", [FruitEnumList])): returns TRUE if the column FruitEnumList contains the value "Apple", the value "Banana", or both.
- AND(IN("Apple", [FruitEnumList]), IN("Banana", [FruitEnumList])): returns TRUE if the column FruitEnumList contains both the value "Apple" and the value "Banana".
- AND(IN("Apple", [FruitEnumList]), NOT(IN("Banana", [FruitEnumList]))): returns TRUE if the column FruitEnumList contains the value "Apple" but not the value "Banana".
INDEX(any-list, number)
INDEX() picks a single specific value from a list of values.
- INDEX({41, 27, 388}, 2): returns 27.
- INDEX(Item[Quantity], 2): returns the second value in the Quantity column of the Item table.
- INDEX(SELECT(Customers[Phone], CONTAINS([Name], "John Doe"), 1): returns the first value in the Phone column of Customers having the Name "John Doe".
LOOKUP(match-value, table-name, column-name, return-column)
LOOKUP() is similar to the VLOOKUP() spreadsheet function.
- LOOKUP("John Doe", Customers, Name, Phone): searches the Name column of the Customers table for the value "John Doe" and returns the value in the Phone column of that table. This is a compact equivalent of ANY(SELECT(Customers[Phone], [Name] = "John Doe")) where the filter is an equality condition.
MAX(numeric-list)
MAX() returns the maximum item in the list.
- MAX({1, 2, 3}): returns 3.
- MAX({3.1, 4.2, 9.3, 15.4, 32.5}): returns 32.5.
- MAX(Item[Price]): returns the maximum value in the Price column of the Item table.
MAXROW(of-table, by-column[, include-row?])
MAXROW() returns a reference to the row having the largest value in a column.
- MAXROW(Customers, SignupDate): returns the most recently created customer.
- MAXROW(Customers, SignupDate, [State] = WA): returns the most recently created customer from WA state.
If you see the error "Expression '<your expression>' could not be parsed ...", put double quotes around the field name. For example, MAXROW(Meeting, "Date"). In this case, Date is a keyword for spreadsheet formulas, which confuses the formula parser. Putting the field name Date in double quotes avoids this issue.
MIN(numeric-list)
MIN() returns the minimum item in the list.
- MIN({1, 2, 3}): returns 1.
- MIN({3, 4, 9, 15, 32}): returns 3.
- MIN(Item[Price]): returns the minimum value in the Price column of the Item table.
MINROW(of-table, by-column[, include-row?])
MINROW() returns a reference to the row having the smallest value in a column.
- MINROW(Customers, SignupDate): returns the least recently created customer.
- MINROW(Customers, SignupDate, [State] = WA): returns the least recently created customer from WA state.
If you see the error "Expression '<your expression>' could not be parsed ...", put double quotes around the field name. For example, MINROW(Meeting, "Date"). In this case, Date is a keyword for spreadsheet formulas, which confuses the formula parser. Putting the field name Date in double quotes avoids this issue.
SORT(any-list[, descending?])
SORT() returns the items in the list sorted by default in ascending order. An optional true/false second input controls if the sort should be in descending order. This function is commonly used in the Valid_If constraint of a column to control the order of a dropdown menu.
- SORT({22, 44, 33}): returns the list {22, 33, 44}
- SORT({22, 44, 33}, TRUE): returns the list {44, 33, 22}
SPLIT(text, separator)
SPLIT() returns a list of fragments of text between separator. For example, SPLIT("Red:Yellow:Green", ":") returns a list containing the values Red, Yellow, and Green.
SUM(numeric-list)
SUM() returns the sum of the items in the list.
- SUM({1, 2, 3}): returns 6.
- SUM({3, 4, 9, 15, 32}): returns 63.
- SUM(Item[Quantity]): returns the sum of the values in the Quantity column of the Item table.
- SUM([Order Details][Total]): returns the sum of the values in the Total column of the child Order Details table records. For example, this expression might appear in the App formula property of a virtual column in the Orders table. It computes the sum of all child Order Details records referenced by the Order Details reverse reference column of the Orders record.
TOP(any-list, number)
TOP() returns a list of the initial number of items for any-list.
- TOP({1, 2, 3, 4, 5}, 3) returns a list containing the values 1, 2, and 3.
- TOP(LIST("John", "Mary"), 4) returns a list containing the values John and Mary. If more items are requested than the list contains, only as many items as the list contains are returned.
- TOP(SORT(Customers[Age]), 0) returns a list with 0 items.
- TOP(ORDERBY(FILTER(Purchases, ([Customer Name] = "Alice")), Purchase Date, TRUE), 3) returns the 3 most recent purchase records for the customer named Alice. FILTER(Purchases, ([Customer Name] = "Alice")) returns the keys to all rows in Purchases where the Customer Name column value is Alice. ORDERBY(FILTER(...), Purchase Date, TRUE) sorts the keys returned by FILTER() according to the Purchase Date column value, in descending order (as directed by TRUE). TOP(ORDERBY(...), 3) returns the first 3 row keys returned by ORDERBY(), which are the 3 rows with the most recent purchase dates.
Using a List in a Column Constraint
Lists are often used with column constraints such as Editable_If, Show_If, or Valid_If. See Column Constraints.
In column constraints:
[_THIS] refers to the current column value.
[_THISROW] refers to the current row value.
- IN([_THIS], {1, 2, 3}): returns TRUE if the value in the column value is contained in the list.
- {1, 2, 3}: is a short form of the previous expression. It's equivalent to IN([_THIS], {1, 2, 3}).
- IN(USEREMAIL(), TableName[Emails]): returns TRUE if the user's email address is contained in the Emails column of table, TableName.
- COUNT(SELECT(Customers[Phone Number], [State] = [_THIS])) > 100: returns TRUE if there are over 100 customer phone numbers in the current state.
- COUNT(FILTER(Customers, [State] = [_THISROW].[State])) > 1000: returns TRUE if there are over 1000 customers in the current state.
Preventing Duplicate Field Values
You can ensure that every record in a table has a unique field value by specifying a Valid_If expression for that field.
- NOT(IN([_THIS], SELECT(Customers[State], NOT(IN([CustomerId], LIST([_THISROW].[CustomerId])))))): when used as the Valid_If condition for the State column, it ensures that every customer has a unique value for State. In this example, we assume that CustomerId is the key for the Customers table.
This expression uses [_THISROW].[CustomerId] to obtain the key value of the current row. This allows you exclude the current row from the list of rows having duplicate column values. This makes it possible for you to update the current row without reporting it as a duplicate column value.
Sorting a List of Keys with ORDERBY()
You can sort a list of keys using the ORDERBY() function. This can be used to sort the values in a Ref dropdown menu (by modifying the app formula of a Related References virtual column) or to control the order of rows displayed in a workflow rule Start expression.
ORDERBY(ref-list, column-name[, descending?[, another-column-name]]...) to sort a list of keys. The first argument must yield a list of references, i.e., a list of the keys of the rows to sort. This is followed by one or more pairs indicating a column name to order by and its ordering direction. The value TRUE indicates descending order (e.g., 9 to 0, or Z to A); FALSE indicates ascending order (e.g., 0 to 9, or A to Z). If the data should be ordered by just one column (which is the common case), the ordering direction may be omitted and defaults to FALSE (so the rows are sorted in ascending order). For example, ORDERBY([Related Orders], [Customer Name], [Order Date], FALSE).
List Expression Patterns and Examples
From the Expression Builder, follow the pattern below for a list or numeric result. See examples for further clarity.
Pattern Result Example
table_name[{column_name}] List Field Types[Address]
LIST({*},{*}) List LIST([ChangeTimestamp], "value_1")
SELECT({List}, {Yes/No}) List SELECT({"value_1", "value_2"}, [Price] = 19.99)
COUNT({List}) Number COUNT({"value_1", "value_2"})
SUM({List}) Number SUM({"value_1", "value_2"})
ANY({List}) Number ANY({"value_1", "value_2"})
Expressions in this article align with the List Expressions in Expression Builder in the app editor. Expressions may be used in various AppSheet features--initial values, app formulas, virtual columns and column constraints (Valid_If, Show_If, Required_If)--to customize app behavior and provide your users with advanced functionality.
To learn more about lists and aggregates, watch the following webinar: