Syntax

FILTER(table-name, select-row?)

Arguments

  • table-name (table name): the name of the table to search as a literal Text value (quoted or unquoted); may not be an expression.
  • select-row? (Yes/No expression): an expression, evaluated for each row of the table, that returns TRUE or FALSE indicating whether the row should be included (TRUE) or excluded (FALSE) in the results.

Return Value

List of Ref: a list containing the key of each row from the table for which the select-row? expression evaluated TRUE. The list is in an arbitrary, unsorted order; any apparent ordering should be considered coincidental.

Examples

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

FILTER("Products", ([Price] < 100)) returns keys to rows of the Products table in which the Price column value is less than 100 (i.e., products priced less than $100).

FILTER("Students", TRUE) returns keys to all rows of the Students table. Equivalent to SELECT(Students[Student ID], TRUE) if Student ID is the key column for Students. See also: SELECT().

FILTER("MyTable", FALSE) : returns an empty list because the select-row? expression will always return FALSE, excluding all rows.

Open Issues Assigned to Me

FILTER(
  "Issues",
  AND(
    ([Status] = "Open"),
    ([Assigned to] = USEREMAIL())
  )
)
  1. FILTER("Issues", ...) gets list of keys from select rows in the Issues table.
  2. AND(..., ...) limits the rows selected to only those that match both of the criteria.
  3. ([Status] = "Open") limits the selection to only those rows in which the Status column value is Open (i.e., only open issues).
  4. ([Assigned to] = USEREMAIL()) further limits the selection to only rows with an Assigned to column value equal to the current app user's email address (i.e., issues assigned to me).

See also: AND(), USEREMAIL()

Orders with Special Instructions

FILTER(
  "Orders",
  AND(
    ISNOTBLANK([Special Instructions]),
    ([Date Done] >= [_THISROW].[Begin Date]),
    ([Date Done] < [_THISROW].[End Date])
  )
)
  1. FILTER("Orders",  ...) gets a list of row references (values of the key column)  for select rows in Orders.
  2. AND(..., ..., ...) limits the results to only those rows that match all of the conditions.
  3. ISNOTBLANK([Special Instructions]) limits the selection to only rows that contain special delivery instructions.
  4. ([Date Done] >= [_THISROW].[Begin Date]) limits the count to only rows with a Date Done column value no earlier than the report's Begin Date column value.
  5. ([Date Done] < [_THISROW].[End Date]) further limits the rows to those with dates before the report's end date.

See also: AND(), ISNOTBLANK()

Common Problems

FILTER(Text, ([Ticket ID] = [_THISROW].[Ticket ID])) produces the error, Expression [...] could not be parsed due to exception: #VALUE!. In this example, the table name, Text, has significance within the internals of AppSheet and causes confusion. Any table name that matches an AppSheet or Excel function name may produce this problem. To fix, quote the problem name: FILTER("Text", ([Ticket ID] = [_THISROW].[Ticket ID]))

FILTER("Events", ([Venue] <> [Wanted Venue])) produces the error, Unable to find column [...]. Column references within the select-row? expression (e.g., [Venue]) are to the row being considered as the table is searched. To access columns outside the row being considered, such as when using FILTER() from within a column constraint, app formula, initial value, or format rule, reference the external column using _THISROW: FILTER("Events", ([Venue] <> [_THISROW].[Wanted Venue]))

See Also

LOOKUP(), MAXROW(), MINROW(), ORDERBY(), SELECT()

Did this answer your question?