FILTER()

Select rows of table or slice

S

Written by Steve Coile Updated over a week ago

Examples

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 (i.e., 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 (i.e., 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().

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-name,select-row?)

Arguments

dataset-name (table or slice name): the 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? (Yes/No expression): an 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.

Return Value

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