了解委派Understand delegation

PowerApps 包括一组用于数据表筛选、排序和整理的功能强大的函数:FilterSortAddColumns,等等。PowerApps includes a powerful set of functions for filtering, sorting, and shaping tables of data: Filter, Sort, and AddColumns functions to name just a few. 可以使用这些函数,让用户重点访问其所需的信息。With these functions, you can provide your users with focused access to the information they need. 对于具有数据库背景的用户来说,使用这些函数相当于编写数据库查询。For those with a database background, using these functions is the equivalent of writing a database query.

若要生成有效的应用,关键是尽量减少需要转到设备的数据量。The key to building efficient apps is to minimize the amount of data that needs to be brought to your device. 也许在成千上万条记录中,只需要少量记录;也许单个聚合值就可以代表数千条记录;Perhaps only a handful of records from a sea of millions are needed, or a single aggregate value can represent thousands of records. 也许只有第一组记录可以检索,而其余记录是用户在需要更多记录的情况下通过笔势输入的。Or perhaps only the first set of records can be retrieved, and the rest brought in as the user gestures that they want more. 进行重点访问可以大幅缩减应用所需的处理能力、内存和网络带宽,提高用户的响应速度,即使使用的是通过移动电话网络连接的手机。Being focused can dramatically reduce the processing power, memory, and network bandwidth needed by your app, resulting in snappier response times for your users, even on phones connected via a cellular network.

若要通过 PowerApps 公式来尽量减少通过网络传输的数据,则需使用“委派”。Delegation is where the expressiveness of PowerApps formulas meets the need to minimize data moving over the network. 简而言之,这意味着,PowerApps 会将数据的处理委派给数据源,而不是将数据移到应用进行本地处理。In short, it means that PowerApps will delegate the processing of data to the data source, rather than moving the data to the app for processing locally.

但这会很复杂,这也是写作本文的原因,因为并非所有能够在 PowerApps 公式中表示的内容都可以委派给所有数据源。Where this becomes complicated, and the reason this article exists, is because not everything that can be expressed in a PowerApps formula can be delegated to every data source. PowerApps 语言与 Excel 的公式语言类似,旨在让用户能够完整且即时地访问内存中的整个工作簿,并且提供各种数字和文本操作函数。The PowerApps language mimics Excel's formula language, designed with complete and instant access to a full workbook in memory, with a wide variety of numerical and text manipulation functions. 因此,PowerApps 语言要比大多数数据源能够支持的语言(包括强大的数据库引擎,例如 SQL Server)丰富得多。As a result, the PowerApps language is far richer than most data sources can support, including powerful database engines such as SQL Server.

处理大型数据集需要使用数据源和能够委派的公式。Working with large data sets requires using data sources and formulas that can be delegated. 若要让应用始终运行良好,同时要确保用户能够访问所需的全部信息,这是唯一的方式。It is the only way to keep your app performing well and ensure users can access all the information they need. 请注意蓝点建议,这表示不能使用委派的地方。Take heed of blue-dot suggestions that flag places where delegation is not possible. 如果处理的是小型数据集(不到 500 条记录),则可使用任何数据源和公式,因为在公式不能委派的情况下,可以在本地处理。If you're working with small data sets (less than 500 records), you can use any data source and formula as processing can be done locally if the formula cannot be delegated.

可委托的数据源Delegable data sources

请参阅委派列表以获取完整的列表,了解哪些数据源支持委派,以及支持到何种程度。See the delegation list for the full list of which data sources support delegation and to what extent.

我们会持续为现有的数据源增加委派支持,并且会增加更多的数据源。We are continuing to add delegation support to existing data sources, as well as add more data sources.

导入的 Excel 工作簿(使用“向应用添加静态数据”数据源)、集合以及存储在上下文变量中的表不需要委派。Imported Excel workbooks (using the "Add static data to your app" data source), collections, and tables stored in context variables don't require delegation. 所有此类数据都已在内存中,可以应用完整的 PowerApps 语言。All of this data is already in memory, and the full PowerApps language can be applied.

委派函数Delegable functions

下一步是仅使用那些可以委派的公式。The next step is to use only those formulas that can be delegated. 下面提供的是可以委派的公式元素。Included here are the formula elements that could be delegated. 但是,数据源各不相同,并非所有数据源都支持所有这些元素。However, every data source is different, and not all of them support all of these elements. 请查看具体公式中的蓝点建议。Check for blue-dot suggestions in your particular formula.

这些列表会随时间而变化。These lists will change over time. 我们将努力为更多具有委派功能的函数和运算符提供支持。We're working to support more functions and operators with delegation.

Filter 函数Filter functions

FilterSearchLookUp 可以委派。Filter, Search, and LookUp can be delegated.

FilterLookUp 函数中,可以对表的列使用以下项来选择相应的记录:Within the Filter and LookUp functions, the following can be used with columns of the table to select the appropriate records:

如果公式的某些部分对所有记录的求值结果都是一个常量值,则也可使用这些部分。Portions of your formula that evaluate to a constant value for all records can also be used. 例如,Left( Language(), 2 ) 不依赖于记录的任何列,因此所有记录都会返回同一值。For example, Left( Language(), 2 ) does not depend on any columns of the record and therefore returns the same value for all records. 它实际上是一个常量。It is effectively a constant. 使用上下文变量、集合和信号可能不会获得常量,因此会妨碍 FilterLookUp 委派。Use of context variables, collections, and signals may not be constant and therefore will prevent Filter and LookUp from being delegated.

上面的列表缺少的一些重要项目:Some notable items missing from the above list:

排序函数Sorting functions

SortSortByColumns 可以委派。Sort and SortByColumns can be delegated.

Sort 中,公式只能是单个列的名称,不能包括其他运算符或函数。In Sort, the formula can only be the name of a single column and can't include other operators or functions.

聚合函数Aggregate functions

可以委派 SumAverageMinMaxSum, Average, Min, and Max can be delegated. 目前仅有限数量的数据源支持此委派,请查看委派列表了解更多详细信息。Only a limited number of data sources support this delegation at this time, check the delegation list for more details.

无法委派 CountRowsCountACount 等计数函数。Counting functions such as CountRows, CountA, and Count cannot be delegated.

无法委派 StdevPVarP 等其他聚合函数。Other aggregate functions such as StdevP and VarP cannot be delegated.

其他函数Other functions

所有其他函数都不支持委派,包括以下重要函数:All other functions do not support delegation, including these notable functions:

常见模式是使用 AddColumnsLookUp 将一个表中的信息合并到另一个表中,用数据库术语来说,通常就是指“联接”。A common pattern is to use AddColumns and LookUp to merge information from one table into another, commonly referred to as a Join in database parlance. 例如:For example:

  • AddColumns( Products, "Supplier Name", LookUp( Suppliers, Suppliers.ID = Product.SupplierID ).Name )AddColumns( Products, "Supplier Name", LookUp( Suppliers, Suppliers.ID = Product.SupplierID ).Name )

即使 ProductsSuppliers 是可委派的数据源且 LookUp 是可委派的函数,AddColumns 函数也不可委派。Even though Products and Suppliers may be delegable data sources and LookUp is a delegable function, the AddColumns function is not delegable. 整个公式的结果将仅限于 Products 数据源的第一部分。The result of the entire formula will be limited to the first portion of the Products data source.

由于 LookUp 及其数据源可以委派,因此可能会在数据源的任何位置找到 Suppliers 的匹配项,即使该数据源很大。Since the LookUp and its data source are delegable, a match for Suppliers can be found anywhere in the data source, even if it is large. 可能的缺点在于,对于 Products 中第一部分的每个记录,LookUp 都会向数据源发出单独的调用,导致网络上出现大量的零碎信息。A potential downside is that LookUp will made separate calls to the data source for each of those first records in Products, causing a lot of chatter on the network. 如果 Suppliers 够小且不会经常改变,则可在应用启动时通过调用 Collect 将数据源缓存在应用中(在打开的屏幕上使用 OnVisible),然后改为对其执行 LookUpIf Suppliers is small enough and does not change often, you could cache the data source in your app with a Collect call when the app starts (using OnVisible on the opening screen) and do the LookUp to it instead.

不可委派限制Non-delegable limits

将在本地处理不可委派的公式。Formulas that cannot be delegated will be processed locally. 这样就可以使用整套 PowerApps 公式语言。This allows for the full breadth of the PowerApps formula language to be used. 但是也有代价:所有数据都必须先转到设备上,这可能需要通过网络检索大量的数据。But at a price: all the data must be brought to the device first, which could involve retrieving a large amount of data over the network. 这可能需要一段时间,让人以为应用很慢或者可能已挂起。That can take time, giving the impression that your app is slow or possibly hung.

为了避免这种情况,PowerApps 对能够在本地处理的数据量施加了一个限制:500 条记录。To avoid this, PowerApps imposes a limit on the amount of data that can be processed locally: 500 records. 我们选择此数字是为了让你仍然能够对小型数据集进行完整的访问,同时让你虽然只能看到部分结果,也能练习对大型数据集的使用。We chose this number so that you would still have complete access to small data sets and you would be able to refine your use of large data sets by seeing partial results.

显然,使用此工具时必须小心,因为这可能会让用户感到困惑。Obviously care must be taken when using this facility as it can be confusing for users. 例如,你有一个 Filter 函数,其选择公式不能委派,你需要对一个记录数为 1 百万的数据源应用该函数。For example, consider a Filter function with a selection formula that cannot be delegated, over a million record data source. 由于筛选在本地进行,因此只会扫描这 1 百万记录中的前 500 个记录。Since the filtering will be done locally, only the first 500 records of the million records will be scanned. 如果所需记录是第 501 或第 500,001 个记录,则 Filter 不会考虑或返回该记录。If the desired record is record 501, or 500,001, it will not be considered or returned by Filter.

另一可能会造成混淆的情形是使用聚合函数。Another place where this can be confusing is aggregate functions. 对上述包含百万记录的数据源的一个列运行 Average 函数。Take Average over a column of that same million record data source. 由于 Average 尚无法进行委派,因此只会对前 500 个记录进行平均。Since Average cannot yet be delegated, only the first 500 records will be averaged. 必须小心这种情况,否则你会将应用用户基于部分数据得出的答案误认为是基于完整数据得出的答案。Care must be taken or a partial answer could be misconstrued as a complete answer by a user of your app.

蓝点建议Blue dot suggestions

为了方便用户了解什么可以委派,什么不可以委派,特此根据创作体验提出了蓝点建议,这些建议针对的是包含不可委派内容的公式。To make it easier to know what is and is not being delegated, the authoring experience provides blue dot suggestions when a formula contains something that cannot be delegated.

蓝点仅显示在针对可委派数据源进行运算的公式上。Blue dots are only shown on formulas that operate on delegable data sources. 如果没有看到蓝点,但你认为公式未进行适当的委派,请对照上面的可委派数据源列表检查数据源的类型。If you don't see a blue dot and you believe your formula is not being properly delegated, check the type of data source against the list of delegable data sources above.

示例Examples

在此示例中,我们将使用一个 SQL Server 表,其中包含产品,具体说来就是水果,其名称为 [dbo].[Products]In this example, we will use a SQL Server table that contains products, in particular fruits, names [dbo].[Products]. 在“新建”屏幕上,PowerApps 可以创建一个基本的三屏幕应用,该应用连接到以下数据源:On the New screen, PowerApps can create a basic three screen app connected to this data source:

请注意库的 Items 属性的公式。Note the formula for the Gallery's Items property. 该公式使用 SortByColumnsSearch 函数,二者均可委派。It is using SortByColumns and Search functions, both of which can be delegated.

让我们在搜索文本输入控件中键入“Apple”。Let's type "Apple" into the search text-input control. 如果仔细观察,我们就会发现,在新搜索中处理新条目时,屏幕顶部会短暂地出现一串移动的点。If we are very observant, we will momentarily see marching dots on the top of the screen while the new entry in the new search is processed. 这串移动的点表示我们正在与 SQL Server 通信:The marching dots indicate that we are communicating with the SQL Server:

由于这都是可以委派的,因此即使 [dbo].[Products] 表包含数百万记录,我们也仍然可以全部找到它们,当用户滚动浏览结果时在库中对这些记录分页。Because this is all delegable, even if the [dbo].[Products] table contains millions of records, we will still find them all, paging through them in the gallery as the user scrolls through the results.

你会注意到,我们看到的匹配项包含“Apple”和“Pineapple”。You will notice that we are seeing a match for both "Apple" and "Pineapple". Search 函数会在文本列的任何位置查找搜索词。The Search function will find a search term anywhere in a text column. 如果我们只想在水果名称的开头查找搜索词,If instead, let's say we wanted to only find the search term at the beginning of the fruit's name. 则可使用另一委派函数:Filter,该函数的搜索词更复杂(为简便起见,我们将删除 SortByColumns 调用):We can use another delegable function, Filter, with a more complicated search term (for simplicity we'll remove the SortByColumns call):

看起来发挥作用了,仅“Apples”显示,“Pineapple”没有显示。This appears to be working, only "Apples" is correctly showing now and "Pineapple" is not. 但是,在库旁边显示了一个蓝点,在部分公式下标有蓝色的波浪线。However, there is a blue dot showing next to the gallery and there is a blue wavy line under a portion of the formula. 甚至在屏幕缩略图中也显示了一个蓝点。There is even a blue dot showing in the screen thumbnail. 如果我们将鼠标悬停在库旁边的蓝点上方,则会显示以下消息:If we hover over the blue dot next to the gallery, we see the following:

虽然我们使用的 Filter 是可委派的函数,使用的 SQL Server 是可委派的数据源,但在 Filter 中使用的公式是不可委派的。Although we are using Filter which is a delegable function, with SQL Server which is a delegable data source, the formula we used within Filter is not delegable. MidLen 不能委派给任何数据源。Mid and Len cannot be delegated to any data source.

但它确起作用了,不是吗?But it worked, didn't it? 嗯,在某种程度上可以这么说。Well, kind of. 这就是为何只显示蓝点而不显示黄色危险图标和红色波浪线(表示错误)的原因。And that is why this is a blue dot instead of a yellow hazard icon and red wavy error. 如果 [dbo].[Products] 表包含的记录不到 500 条,则没错,此方法完全可以使用。If the [dbo].[Products] table contains less than 500 records, then yes, this worked perfectly. 所有记录都会转到设备,并在本地应用 FilterAll records were brought to the device and the Filter was applied locally.

但如果该表包含的记录超过 500 条,则只会在库中显示表的头 500 条记录中以“Apple”开头的水果。If instead this table contains more than 500 records, then only fruit which begin with "Apple" in the first 500 records of the table will be displayed in the gallery. 如果“Apple, Fuji”作为名称出现在第 501 或 500,001 条记录中,就会找不到它。If "Apple, Fuji" appears as a name in record 501 or 500,001 it will not be found.