List expressions and aggregates

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. Expressions in this article align with the Lists Expressions in the Expression Builder of the Editor. The Expression Builder can be found anywhere you are able to enter a formula/expression, noted by a little "flask" symbol next to it. Clicking on the flask will bring up the Expression Builder. The Expression Builder is "context-aware," i.e. it shows you expressions that are relevant to the specific table you are editing. Also included in the builder is an "instant" expression checker, to verify that the expression is valid.

List expressions

List Expressions utilize operators that return a list or numeric value. AppSheet conditions are not a 1:1 match with Google Sheets functions; however, in some cases the formatting similarity may help you construct your expressions. Alternatively, if the formatting is not similar, the Google Sheets function page may provide context for use of the function. If available, see the Google Sheets function link in parentheses next to applicable operators.

Lists operators

  • LIST({*},{*})  
  • COUNT({List})  (COUNT)
  • SUM({List})  (SUM)
  • MIN({List})  (MIN)
  • MAX({List}) (MAX)
  • AVERAGE({List}) (AVERAGE)
  • STDEVP({List}) (STDEVP)
  • ANY({List})
  • SELECT({List},{Filter Yes/No}, {Optional Distinct Yes/No}) 
  • LOOKUP({MatchValue}, {TableName}, {MatchColumn}, {ReturnColumn})
  • MAXROW({TableName}, {MaxByColumn}, {Filter Yes/No})
  • MINROW({TableName}, {MinByColumn}, {Filter Yes/No})

 

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. Lists enable powerful expressions.

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 string values.

  • The LIST function constructs lists of constants, field 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 string values and is equivalent to {"Apple", "Banana", "Cherry"}.
    • LIST([HomePhone], [CellPhone], [WorkPhone])  --- constructs a list of phone values from the specified fields in a record.
  • List from Column 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 is 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

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

  • MINROW is like MAXROW, but it 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.



Check for 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. The list values may be either constants or expressions when the LIST function is used.


 
Aggregate List functions

  • COUNT(<list of anything>)  --- return the count of items in the list.
    • COUNT({1, 2, 3})  --- returns 3.
    • COUNT(LIST("Apple", "Banana", "Cherry"))  --- returns 3.
    • COUNT(Customers[Phone Number])  --- returns the count of unique phone numbers contained in the Phone Number field of the Customers table.
    • COUNT(SELECT(Customers[Phone Number], [State] = “WA”), true)  --- returns the count of unique phone numbers of WA customers.

  • SUM(<list of numeric type>)  --- 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.

  • MIN(<list of numeric type>)  --- 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.

  • MAX(<list of numeric type>)  --- returns the maximum item in the list.
    • MIN({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.

  • AVERAGE(<list of numeric type>)  --- 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.

  • ANY(<list of anything>)  --- 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".
       
  • LOOKUP({Lookup Value},{Table Name},{Column Name to Search},{Column Name to Return})  --- 
    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". If the value is found, LOOKUP returns the value in the Phone column of the Customers table. This is a compact equivalent of ANY(SELECT(Customers[Phone], [Name] = "John Doe")) where the filter is an equality condition.


Using a list in a column constraint:

Lists are often used with column constraints such as Valid_If or Show_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 is equivalent to IN([_THIS], {1, 2, 3})
  • 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.

 

Lists 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"})

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.