PowerApps 中的 Filter、Search 和 LookUp 函数Filter, Search, and LookUp functions in PowerApps

查找中的一个或多个记录Finds one or more records in a table.

说明Description

Filter 函数查找表中满足公式的记录。The Filter function finds records in a table that satisfy a formula. 使用 Filter 可查找匹配一个或多个条件的一组记录并丢弃不匹配的记录。Use Filter to find a set of records that match one or more criteria and to discard those that don't.

LookUp 函数查找表中满足公式的第一条记录。The LookUp function finds the first record in a table that satisfies a formula. 使用 LookUp 可查找匹配一个或多个条件的单个记录。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. 结果为 true 的记录将包含在结果中。Records that result in true are included in the result. 除常规公式运算符外,还可对子字符串匹配项使用 inexactin 运算符。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.

Search 函数在表中查找其某个列中包含某个字符串的记录。The Search function finds records in a table that contain a string in one of their columns. 字符串可能出现在列中的任何位置;例如,搜索“rob”或“bert”会在包含“Robert”的列中找到匹配项。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. FilterLookUp 不同,Search 函数使用单个字符串而非公式进行匹配。Unlike Filter and LookUp, the Search function uses a single string to match instead of a formula.

FilterSearch 返回包含与原始表相同的列的表和匹配条件的记录。Filter and Search return a table that contains the same columns as the original table and the records that match the criteria. LookUp 用公式将记录归约为单个值后,仅返回找到的第一条记录。LookUp returns only the first record found, after applying a formula to reduce the record to a single value. 如果未找到记录,FilterSearch 将返回表,LookUp 将返回空白。If no records are found, Filter and Search return an empty table, and LookUp returns blank.

是 PowerApps 中的一个值,与字符串或数字类似。Tables are a value in PowerApps, just like a string or number. 可以向函数传递表,也可以从函数返回表。They can be passed to and returned from functions. FilterSearchLookUp 不会修改表。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

条件允许时,PowerApps 将把筛选和排序操作委派给数据源,并按需对结果进行分页。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 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.

如果委派不可行,PowerApps 将仅拉取一小组记录进行本地操作。If delegation is not possible, PowerApps will pull down only a small set of records to work on locally. 筛选和排序函数将对缩减的一组记录执行操作。Filter and sort functions will operate on a reduced set of records. 中的可用内容可能并不完整,这可能使用户感到困惑。What is available in the Gallery may not be the complete story, which could be confusing to users.

请参阅委派概述了解详细信息。See the delegation overview for more information.

语法Syntax

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

  • Table - 必需。Table - Required. 要搜索的表。Table to search.
  • Formula(s) - 必需。Formula(s) - Required. 计算表的每个记录时所依据的公式。The formula by which each record of the table is evaluated. 此函数返回计算结果为 true 的所有记录。The function returns all records that result in true. 可以引用表中的列。You can reference columns within the table. 如果提供多个公式,则通过 And 函数将所有公式的结果合并。If you supply more than one formula, the results of all formulas are combined with the And function.

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

  • Table - 必需。Table - Required. 要搜索的表。Table to search.
  • SearchString - 必需。SearchString - Required. 要搜索的字符串。The string to search for. 如果为空白或者是空字符串,将返回所有记录。If blank or an empty string, all records are returned.
  • Column(s) - 必需。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. 但是,列名称必须是静态的,并且不能使用公式计算。However, the column names must be static and cannot be calculated with a formula. 如果在任意这些列的数据中找到了作为部分匹配项的 SearchString ,将返回完整记录。If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

备注

对于列名称带空格的 SharePoint 和 Excel 数据源,请将每个空格指定为“_x0020_”。For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". 例如,将“Column Name”指定为“Column_x0020_Name”。For example, specify "Column Name" as "Column_x0020_Name".

LookUp( Table, Formula [, ReductionFormula ] )LookUp( Table, Formula [, ReductionFormula ] )

  • Table - 必需。Table - Required. 要搜索的表。Table to search. 在 UI 中,此语法显示为函数框上方的“源”。In the UI, the syntax is shown as source above the function box.
  • Formula - 必需。Formula - Required. 计算表的每个记录时所依据的公式。The formula by which each record of the table is evaluated. 此函数返回计算结果为 true 的第一条记录。The function returns the first record that results in true. 可以引用表中的列。You can reference columns within the table. 在 UI 中,此语法显示为函数框上方的“条件”。In the UI, the syntax is shown as condition above the function box.
  • ReductionFormula - 可选。ReductionFormula - Optional. 此公式会对找到的记录进行计算,然后将记录缩减为一个值。This formula is evaluated over the record that was found, and then reduces the record to a single value. 可以引用表中的列。You can reference columns within the table. 如果没有使用这个参数,此函数将返回表中的完整记录。If you don't use this parameter, the function returns the full record from the table. 在 UI 中,此语法显示为函数框上方的“结果”。In the UI, the syntax is shown as result above the function box.

示例Examples

以下示例使用 IceCream 数据源The following examples use the IceCream data source:

公式Formula 说明Description 结果Result
Filter( IceCream, OnOrder > 0 )Filter( IceCream, OnOrder > 0 ) 返回 OnOrder 大于零的记录。Returns records where OnOrder is greater than zero.
Filter( IceCream, Quantity + OnOrder > 225 )Filter( IceCream, Quantity + OnOrder > 225 ) 返回 QuantityOnOrder 列的总和大于 225 的记录。Returns records where the sum of Quantity and OnOrder columns is greater than 225.
Filter( IceCream, "chocolate" in Lower( Flavor ) )Filter( IceCream, "chocolate" in Lower( Flavor ) ) 返回其 Flavor 名称中出现单词“chocolate”(不区分字母大小写)的记录。Returns records where the word "chocolate" appears in the Flavor name, independent of uppercase or lowercase letters.
Filter( IceCream, Quantity < 10 && OnOrder < 20 )Filter( IceCream, Quantity < 10 && OnOrder < 20 ) 返回 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" )Search( IceCream, "choc", "Flavor" ) 返回其 Flavor 名称中出现字符串“choc”(不区分字母大小写)的记录。Returns records where the string "choc" appears in the Flavor name, independent of uppercase or lowercase letters.
Search( IceCream, "", "Flavor" )Search( IceCream, "", "Flavor" ) 因为搜索词为空,所以将返回所有记录。Because the search term is empty, all records are returned.
LookUp( IceCream, Flavor = "Chocolate", Quantity )LookUp( IceCream, Flavor = "Chocolate", Quantity ) 搜索 Flavor 等于“Chocolate”的记录,其中有一个此类记录。Searches for a record with Flavor equal to "Chocolate", of which there is one. 对于找到的第一个记录,将返回该记录的 QuantityFor the first record that's found, returns the Quantity of that record. 100100
LookUp( IceCream, Quantity > 150, Quantity + OnOrder )LookUp( IceCream, Quantity > 150, Quantity + OnOrder ) 搜索 Quantity 大于 100 的记录,其中有多个此类记录。Searches for a record with Quantity greater than 100, of which there are multiple. 对于找到的第一个 Flavor 为“Vanilla”的记录,将返回 QuantityOnOrder 列的总和。For the first record that's found, which is "Vanilla" Flavor, returns the sum of Quantity and OnOrder columns. 250250
LookUp( IceCream, Flavor = "Pistachio", OnOrder )LookUp( IceCream, Flavor = "Pistachio", OnOrder ) 搜索 Flavor 等于“Pistachio”的记录,其中不存在此类记录。Searches for a record with Flavor equal to "Pistachio", of which there are none. 由于未找到匹配项,Lookup 将返回空白。Because none were found, Lookup returns blank. 空白blank
LookUp( IceCream, Flavor = "Vanilla" )LookUp( IceCream, Flavor = "Vanilla" ) 搜索 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 }{ Flavor: "Vanilla", Quantity: 200, OnOrder: 75 }

搜索用户体验Search user experience

在许多应用中,你可在搜索框中键入一个或多个字符来对较大数据集中的记录列表进行筛选。In many apps, you can type one or more characters into a search box to filter a list of records in a large data set. 键入时,列表中仅显示与搜索条件匹配的记录。As you type, the list shows only those records that match the search criteria.

本主题其余部分中的示例演示了搜索包含此数据的列表(名为 Customers)的结果:The examples in the rest of this topic show the results of searching a list, named Customers, that contains this data:

若要将此数据源创建为集合,请创建一个 按钮 控件,并将其 OnSelect 属性设置为以下公式:To create this data source as a collection, create a Button control and set its OnSelect property to this formula:

ClearCollect( Customers, Table( { Name: "Fred Garcia", Company: "Northwind Traders" }, { Name: "Cole Miller", Company: "Contoso" }, { Name: "Glenda Johnson", Company: "Contoso" }, { Name: "Mike Collins", Company: "Adventure Works" }, { Name: "Colleen Jones", Company: "Adventure Works" } ) )ClearCollect( Customers, Table( { Name: "Fred Garcia", Company: "Northwind Traders" }, { Name: "Cole Miller", Company: "Contoso" }, { Name: "Glenda Johnson", Company: "Contoso" }, { Name: "Mike Collins", Company: "Adventure Works" }, { Name: "Colleen Jones", Company: "Adventure Works" } ) )

如此示例中所示,可在屏幕底部的库控件中显示记录列表。As in this example, you can show a list of records in a Gallery control at the bottom of a screen. 在靠近屏幕顶部的位置,可添加名为 SearchInput文本输入控件,以便用户能够指定对哪些记录感兴趣。Near the top of the screen, you can add a Text input control, named SearchInput, so that users can specify which records interest them.

当用户在 SearchInput 中键入字符时,系统将自动筛选库中的结果。As the user types characters in SearchInput, the results in the gallery are automatically filtered. 在本例中,库被配置为显示客户名称(不是公司名称)以 SearchInput 中的字符序列开头的记录。In this case, the gallery is configured to show records for which the name of the customer (not the name of the company) starts with the sequence of characters in SearchInput. 如果用户在搜索框中键入 co,库将显示以下结果:If the user types co in the search box, the gallery shows these results:

若要基于 Name 列进行筛选,需将库控件的 Items 属性设置为以下公式之一:To filter based on the Name column, set the Items property of the gallery control to one of these formulas:

公式Formula 说明Description 结果Result
Filter( Customers, StartsWith( Name, SearchInput.Text ) )Filter( Customers, StartsWith( Name, SearchInput.Text ) ) 筛选 Customers 数据源,查找搜索字符串出现在 Name 列开头的记录。Filters the Customers data source for records in which the search string appears at the start of the Name column. 测试不区分大小写。The test is case insensitive. 如果用户在搜索框中键入 co,库将显示 Colleen JonesCole MillerIf the user types co in the search box, the gallery shows Colleen Jones and Cole Miller. 库不会显示 Mike Collins,因为该记录的 Name 列不以搜索字符串开头。The gallery doesn't show Mike Collins because the Name column for that record doesn't start with the search string.
Filter( Customers, SearchInput.Text in Name )Filter( Customers, SearchInput.Text in Name ) 筛选 Customers 数据源,查找搜索字符串出现在 Name 列中任意位置的记录。Filters the Customers data source for records in which the search string appears anywhere in the Name column. 测试不区分大小写。The test is case insensitive. 如果用户在搜索框中键入 co,库将显示 Colleen JonesCole MillerMike Collins,因为搜索字符串都出现在所有这些记录的 Name 列中的某个位置。If the user types co in the search box, the gallery shows Colleen Jones, Cole Miller, and Mike Collins because the search string appears somewhere in the Name column of all of those records.
Search( Customers, SearchInput.Text, "Name" )Search( Customers, SearchInput.Text, "Name" ) 与使用 in 运算符类似,Search 函数从每条记录的 Name 列中的任意位置搜索匹配项。Similar to using the in operator, the Search function searches for a match anywhere within the Name column of each record. 请注意,必须将列名称括在双引号内。Note that you must enclose the column name in double quotation marks.

可以扩展搜索,使其包括 Company 列和 Name 列:You can expand your search to include the Company column as well as the Name column:

公式Formula 说明Description 结果Result
Filter( Customers, StartsWith( Name, SearchInput.Text ) || StartsWith( Company, SearchInput.Text ) )Filter( Customers, StartsWith( Name, SearchInput.Text ) || StartsWith( Company, SearchInput.Text ) ) 筛选 Customers 数据源,查找 Name 列或 Company 列以搜索字符串(例如 co)开头的记录。Filters the Customers data source for records in which either the Name column or the Company column starts with the search string (for example, co). 如果任一 StartsWith 函数为 true,则 || 运算符trueThe || operator is true if either StartsWith function is true.
Filter( Customers, SearchInput.Text in Name || SearchInput.Text in Company )Filter( Customers, SearchInput.Text in Name || SearchInput.Text in Company ) 筛选 Customers 数据源,查找 Name 列或 Company 列任意位置包含搜索字符串(例如 co)的记录。Filters the Customers data source for records in which either the Name column or the Company column contains the search string (for example, co) anywhere within it.
Search( Customers, SearchInput.Text, "Name", "Company" )Search( Customers, SearchInput.Text, "Name", "Company" ) 与使用 in 运算符类似,Search 函数筛选 Customers 数据源,查找 Name 列或 Company 列任意位置包含搜索字符串(例如 co)的记录。Similar to using the in operator, the Search function searches the Customers data source for records in which either the Name column or the Company column contains the search string (for example, co) anywhere within it. 如果希望指定多个列和多个 in 运算符,那么 Search 函数会比 Filter 更易于读取和写入。The Search function is easier to read and write than Filter if you want to specify multiple columns and multiple in operators. 请注意,必须将列的名称括在双引号内。Note that you must enclose the names of the columns in double quotation marks.