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
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:
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()) ) )
FILTER("Issues", ...)gets list of keys from select rows in the
AND(..., ...)limits the rows selected to only those that match both of the criteria.
([Status] = "Open")limits the selection to only those rows in which the
Statuscolumn value is
Open(i.e., only open issues).
([Assigned to] = USEREMAIL())further limits the selection to only rows with an
Assigned tocolumn value equal to the current app user's email address (i.e., issues assigned to me).
Orders with special instructions
FILTER( "Orders", AND( ISNOTBLANK([Special Instructions]), ([Date Done] >= [_THISROW].[Begin Date]), ([Date Done] < [_THISROW].[End Date]) ) )
FILTER("Orders", ...)gets a list of row references (values of the key column) for select rows in
AND(..., ..., ...)limits the results to only those rows that match all of the conditions.
ISNOTBLANK([Special Instructions])limits the selection to only rows that contain special delivery instructions.
([Date Done] >= [_THISROW].[Begin Date])limits the count to only rows with a
Date Donecolumn value no earlier than the report's
Begin Datecolumn value.
([Date Done] < [_THISROW].[End Date])further limits the rows to those with dates before the report's end date.
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
FILTER("Events", ([Venue] <> [_THISROW].[Wanted Venue]))
dataset- Name of the table or slice (the "data set") to search as a literal
Textvalue (quoted or unquoted). May not be an expression.
Yes/Noexpression, evaluated for each row of the data set, that returns
FALSEindicating whether the row should be included (
TRUE) or excluded (
FALSE) in the results.
The list is in an arbitrary, unsorted order; any apparent ordering should be considered coincidental.