FILTER()

Returns select rows of table or slice as a list of Refs. The list contains the key of each row from the data set for which the select-row? expression evaluates as TRUE. 

Sample usage

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

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

FILTER("Students", TRUE) returns keys to all rows of the Students data set. 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 data set.
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 data set name, Text, has significance within the internals of AppSheet and causes confusion. Any data set 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 data set 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]))

Syntax

FILTER(dataset, select-row?)

• dataset - Name of the table or slice (the "data set") to search as a literal Text value (quoted or unquoted). May not be an expression.
• select-row? - A Yes/No expression, evaluated for each row of the data set, that returns TRUE or FALSE indicating whether the row should be included (TRUE) or excluded (FALSE) in the results.

Notes

The list is in an arbitrary, unsorted order; any apparent ordering should be considered coincidental.

See also

LOOKUP()

MAXROW()

MINROW()

ORDERBY()

REF_ROWS()

SELECT()