Finds one or more records in a table.

Description

The Filter function finds records in a table that satisfy a formula. Use Filter to find a set of records that match one or more criteria and to discard those that don't.

The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.

For both, the formula is evaluated for each record of the table. Records that result in true are included in the result. Besides the normal formula operators, you can use the in and exactin operators for substring matches.

Fields of the record currently being processed are available within the formula. You simply reference them by name as you would any other value. You can also reference control properties and other values from throughout your app. For more details, see the examples below and working with record scope.

The Search function finds records in a table that contain a string in one of their columns. The string may occur anywhere within the column; for example, searching for "rob" or "bert" would find a match in a column that contains "Robert". Searching is case-insensitive. Unlike Filter and LookUp, the Search function uses a single string to match instead of a formula.

Filter and Search return a table that contains the same columns as the original table and the records that match the criteria. LookUp returns only the first record found, after applying a formula to reduce the record to a single value. If no records are found, Filter and Search return an empty table, and LookUp returns blank.

Tables are a value in PowerApps, just like a string or number. They can be passed to and returned from functions. Filter, Search, and LookUp don't modify a table. Instead, they take a table as an argument and return a table, a record, or a single value from it. See working with tables for more details.

Delegation

When possible, PowerApps will delegate filter and sort operations to the data source and page through the results on demand. For example, when you start an app that shows a Gallery control filled with data, only the first set of records will be initially brought to the device. As the user scrolls, additional data is brought down from the data source. The result is a faster start time for the app and access to very large data sets.

However, delegation may not always be possible. Data sources vary on what functions and operators they support while the PowerApps formula language is relatively rich. If complete delegation of a formula isn't possible, the authoring environment will flag the portion that can't be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can't be delegated.

PowerApps will delegate what it can but will only pull down a small set of records to complete the work locally: at most 500 records. Filter and sort functions will continue to operate but with a reduced set of records. What is available in the Gallery may not be the complete story, which could be confusing to users. Aggregate functions, such as Sum and Average, may only operate on a portion of the data source and therefore may not give the result that's expected.

See the delegation overview for more information.

Syntax

Filter( Table, Formula1 [, Formula2, ... ] )

  • Table - Required. Table to search.
  • Formula(s) - Required. This formula is evaluated for each record of the table, and the result includes those records that result in true. You can reference columns within the table. If you supply more than one formula, the results of all formulas are combined with the And function.

Search( Table, SearchString, Column1 [, Column2, ... ] )

  • Table - Required. Table to search.
  • SearchString - Required. The string to search for. If blank or an empty string, all records are returned.
  • Column(s) - Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

LookUp( Table, Formula [, ReductionFormula ] )

  • Table - Required. Table to search.
  • Formula - Required. This formula is evaluated for each record of the table, and the first record that results in true is returned. You can reference columns within the table.
  • ReductionFormula - Optional. This formula is evaluated over the record that was found, reducing the record to a single value. You can reference columns within the table. If this parameter is not supplied, the function returns the full record from the table.

Examples

The following examples use the IceCream data source:

Formula Description Result
Filter( IceCream, OnOrder > 0 ) Returns records where OnOrder is greater than zero.
Filter( IceCream, Quantity + OnOrder > 225 ) Returns records where the sum of Quantity and OnOrder columns is greater than 225.
Filter( IceCream, "chocolate" in Lower( Flavor ) ) Returns records where the word "chocolate" appears in the Flavor name, independent of uppercase or lowercase letters.
Filter( IceCream, Quantity < 10 && OnOrder < 20 ) Returns records where the Quantity is less than 10 and OnOrder is less than 20. No records match these criteria, so an empty table is returned.
Search( IceCream, "choc", "Flavor" ) Returns records where the string "choc" appears in the Flavor name, independent of uppercase or lowercase letters.
Search( IceCream, "", "Flavor" ) Because the search term is empty, all records are returned.
LookUp( IceCream, Flavor = "Chocolate", Quantity ) Searches for a record with Flavor equal to "Chocolate", of which there is one. For the first record that's found, returns the Quantity of that record. 100
LookUp( IceCream, Quantity > 150, Quantity + OnOrder ) Searches for a record with Quantity greater than 100, of which there are multiple. For the first record that's found, which is "Vanilla" Flavor, returns the sum of Quantity and OnOrder columns. 250
LookUp( IceCream, Flavor = "Pistachio", OnOrder ) Searches for a record with Flavor equal to "Pistachio", of which there are none. Because none were found, Lookup returns blank. blank
LookUp( IceCream, Flavor = "Vanilla" ) Searches for a record with Flavor equal to "Vanilla", of which there is one. Since no reduction formula was supplied, the entire record is returned. { Flavor: "Vanilla", Quantity: 200, OnOrder: 75 }