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

 

Context

In addition to basic column types, we have a meta-type List_Of_X (eg: List Of Number, List Of Enum, etc) that represents a (potentially empty) list of unique values. This becomes really important for more powerful expressions.

There are functions to construct lists and functions that use lists:

Construct a list:

  • The simplest way to construct such a list is by explicitly writing it  {1, 2, 3} or {[ColumnA], [ColumnB]}
  • The LIST function is syntactically equivalent to an explicit list --- LIST(1,2,3) is the same as {1, 2, 3}
  • A table column list – the values are constructed from the unique values in a specific column of a specific table. Eg: Customers[Phone Number] returns a list of unique phone numbers in the Customers table
  • SELECT is a more powerful way to construct a list from another table. It is a stylized SQL select-from-where query. It returns a single list of values from one column of a table. However, a filter can be applied to eliminate some of the rows.
    • SELECT(Customers[Phone Number], [State] = “WA”)  --- returns a list of phone numbers of WA customers
    • SELECT(Customers[State], [Country] = “USA”, true)  --- returns a distinct (duplicate-free) list of states where there are customers
  • FILTER is a special case of SELECT and it 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 is a function that returns a reference to the row with the largest value in some column. For example:
    • 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 just like MAXROW, only it returns the row taking the smallest value in some column.

Use a list (however it is constructed):

The most common use case is to check if a value is in a list:

  • IN([ColumnName], {1, 2, 3})
  • IN([_THIS], {1, 2, 3})  --- a special case used for column constraints like Valid_If and Show_If
  • For column constraints, we accept a short form of the previous expression which is just the list… so {1, 2, 3} is treated as IN([_THIS], {1, 2, 3})

 Aggregate functions

  • COUNT(<list of anything>)
  • SUM(<list of numeric type>)
  • MIN(<list of numeric type>)
  • MAX(<list of numeric type>)
  • AVERAGE(<list of numeric type>)
  • ANY(<list of anything>) 

The behavior of COUNT(), SUM(), MIN(), MAX(), and AVERAGE() are self-evident.

ANY() picks a single value from a list of values. For example, if there is a table called Customers, each of whom has a Name and a Phone Number, the expression ANY(SELECT(Customers[Phone], CONTAINS([Name], "John Doe")) gets the phone number of a customer identified by a filter expression.

The LOOKUP function is similar to the VLOOKUP spreadsheet function. LOOKUP("John Doe", Customers, Name, Phone) is exactly the same as ANY(SELECT(Customers[Phone], [Name] = "John Doe")). Note that it is a compact way to express ANY(SELECT(...)) expressions where the filter is an equality condition.

Use a list in a column constraint:

In many usage scenarios, a SELECT expression is used within a column constraint (eg: in a Valid_If or a Show_If condition). In these scenarios, the filter condition used in the SELECT function can utilize not just the columns of the table (Customer) but also the column values from the context in which it is used. [_THIS] refers to the cell/column from the context and [_THISROW] refers to the row from the context. See Column Constraints.

    • A sample Valid_If condition for a column that accepts a State:   COUNT(SELECT(Customers[Phone Number], [State] = [_THIS])) > 100 --- this says the State is valid only if the number of customers in that state > 100 
    • A sample Show_If condition for a subsequent column in a form that first asks for a State: COUNT(FILTER(Customers, [State] = [_THISROW].[State])) > 1000 --- ask this question only for states with a lot of customers

 

Common and complex expressions

Common expressions

  • Customers[Phone Number]: returns a list of unique phone numbers in the Customers table
  • LIST(1,2,3): returns 1,2,3
  • SELECT(Customers[Phone Number], [State] = “WA”): returns a list of phone numbers of WA customers
  • COUNT({Dogs,Cats,Birds}): returns the number 3.
  • COUNT({3,4,9,15,32}): returns the number 5.
  • SUM({3,4,9,15,32}): returns the number 63.
  • MIN({3,4,9,15,32}): returns the number 3.
  • MAX({3.1,4.2,9.3,15.4,32.5}): returns the number 32.5
  • AVERAGE({1,2,3,4}): returns the number 2.5

Complex expressions

  • for Valid_If: COUNT(SELECT(Customers[Phone Number], [State] = [_THIS])) > 100:  the State is valid only if the number of customers in that state > 100
  • for Show_If: COUNT(SELECT(Customers[Phone Number], [State] = [_THISROW].[State])) > 1000: ask this question only for states with a lot of customers

 

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.