These are functions to find one or more records in a table.
|Matches by a formula or a single string *||Multiple formulas accepted||Returns multiple
records or a single record
|Returns the subset of the original dataset or one value||No record found||Function to check if no record found|
|Lookup||Formula||No||First Single||One value||Blank||IsBlank|
*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.
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.
“When to use” examples
Duplicate detection based on more than one column – Filter.
Retrieve a subset of the dataset filtered by one or more than one column – Filter.
Text search case-insensitive in multiple columns – Search.
Link to tables via reference – Lookup.
When possible, PowerApps will delegate filter and sort operations to the data source and page through the results on demand.
no delegation: retrieves a subset of records, performs functions and operations on that subset of records.
delegation: retrieves a subset of records, performs functions and operations on whole dataset, then retrieve a result subset.
Whole data set contains the record for Adam Smith. The default subset for 500 records doesn’t contain the Adam Smith record. The function Search for “Adam” string will NOT retrieve the Adam Smith record if:
- The data source doesn’t support delegation
- The function/data source combination doesn’t support delegation.
It will only search in the subset of records.
Data sources vary on what functions and operators they support with delegation. 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. The delegation list details which data sources and operations can be delegated.
1 thought on “Filter, Search, and Lookup functions in PowerApps compared.”
Reblogged this on Nishant Rana's Weblog and commented:
Very informative !