PowerApps 中的 Sort 和 SortByColumns 函数Sort and SortByColumns functions in PowerApps

进行排序。Sorts a table.

说明Description

Sort 函数根据公式对表进行排序。The Sort function sorts a table based on a formula.

系统会针对表的每条记录计算公式,然后根据结果对表进行排序。The formula is evaluated for each record of the table, and the results are used to sort the table. 公式的计算结果必须是数字、字符串或布尔值;不得是表或记录。The formula must result in a number, a string, or a Boolean value; it can't result in a table or a record.

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.

若要先按一列进行排序,然后再按另一列进行排序,请在 Sort 公式中嵌入另一个此公式。To sort first by one column and then by another, you embed a Sort formula within another. 例如,可以使用以下公式先按 LastName 列对 Contacts 表进行排序,然后再按 FirstName 列进行排序:Sort( Sort( Contacts, LastName ), FirstName )For example, you can use this formula to sort a Contacts table first by a LastName column and then by a FirstName column: Sort( Sort( Contacts, LastName ), FirstName )

还可以使用 SortByColumns 函数根据一列或多列对表进行排序。The SortByColumns function can also be used to sort a table based on one or more columns.

SortByColumns 的参数列表提供了作为排序依据的列的名称,以及每个列的排序方向。The parameter list for SortByColumns provides the names of the columns to sort by and the sort direction per column. 系统按参数顺序执行排序(先按第一列排序,然后按第二列排序,依此类推)。Sorting is performed in the order of the parameters (sorted first by the first column, then the second, and so on). 将列名称指定为字符串。如果直接在参数列表中添加列名称,必须用双引号括起来。Column names are specified as strings, requiring double quotes if directly included in the parameter list. 例如,SortByColumns( CustomerTable, "LastName" )For example, SortByColumns( CustomerTable, "LastName" ).

可以将 SortByColumns下拉列表列表框控件结合使用,以便用户能够选择作为排序依据的列。You can combine SortByColumns with a Drop down or List box control to enable users to select which column to sort by.

除了进行升序或降序排序之外,SortByColumns 还可以根据单列表值进行排序。In addition to sorting ascending or descending, SortByColumns can sort based on a single column table of values. 例如,可以提供 [ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ] 作为排序顺序,从而根据星期几的名称对记录进行排序。For example, you can sort record based on the name of a day of the week by supplying [ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ] as the sort order. 所有包含 Monday" 的记录会先显示,随后显示包含 Tuesday 的记录,依此类推。All records which have Monday" will come first, followed by Tuesday, and so on. 与排序顺序表不匹配的记录被置于列表末尾显示。Records found that do not appear in the sort table are put at the end of the list.

是 PowerApps 中的一个值,与字符串或数字类似。Tables are a value in PowerApps, just like a string or number. 可以向函数传递表,也可以从函数返回表。They can be passed to and returned from functions. SortSortByColumn 并不修改表,而是将表视为参数,然后返回排序后的新表。Sort and SortByColumn don't modify a table; instead they take a table as an argument and return a new table that has been sorted. 请参阅使用表,了解更多详情。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 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.

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

Sort( Table, Formula [, SortOrder ] )Sort( Table, Formula [, SortOrder ] )

  • Table - 必需。Table - Required. 要排序的表。Table to sort.
  • Formula - 必需。Formula - Required. 系统会针对表的每条记录计算此公式,然后根据结果对表进行排序。This formula is evaluated for each record of the table, and the results are used to sort the table. 可以引用表中的列。You can reference columns within the table.
  • SortOrder - 可选。SortOrder - Optional. 指定 SortOrder.Descending 可对表进行降序排序。Specify SortOrder.Descending to sort the table in descending order. SortOrder.Ascending 是默认值。SortOrder.Ascending is the default value.

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

  • Table - 必需。Table - Required. 要排序的表。Table to sort.
  • ColumnName(s) - 必需。ColumnName(s) - Required. 要排序的列名称(以字符串形式)。The column names to sort on, as strings.
  • SortOrder(s) - 可选。SortOrder(s) - Optional. SortOrder.AscendingSortOrder.DescendingSortOrder.Ascending or SortOrder.Descending. SortOrder.Ascending 是默认值。SortOrder.Ascending is the default. 如果提供了多个 ColumnNames ,则最后一列必须包括 SortOrderIf multiple ColumnNames are supplied, all but the last column must include a SortOrder.

    注意:对于列名称带空格的 SharePoint 和 Excel 数据源,将每个空格指定为“_x0020_”。Note: 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".

SortByColumns( Table, ColumnName, SortOrderTable )SortByColumns( Table, ColumnName, SortOrderTable )

  • Table - 必需。Table - Required. 要排序的表。Table to sort.
  • ColumnName - 必需。ColumnName - Required. 要排序的列名称(以字符串形式)。The column name to sort on, as strings.
  • SortOrderTable - 必需。SortOrderTable - Required. 作为排序依据的单列表值。Single column table of values to sort by.

    注意:对于列名称带空格的 SharePoint 和 Excel 数据源,将每个空格指定为“_x0020_”。Note: 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".

示例Examples

在下面的示例中,我们将使用 IceCream 数据源,其中包含下表中的数据:For the following examples, we'll use the IceCream data source, which contains the data in this table:

公式Formula 说明Description 结果Result
Sort( IceCream, Flavor )Sort( IceCream, Flavor )

SortByColumns( IceCream, "Flavor" )SortByColumns( IceCream, "Flavor" )
Flavor 列对 IceCream 进行排序。Sorts IceCream by its Flavor column. 由于 Flavor 列包含字符串,因此该表按字母顺序进行排序。The Flavor column contains strings, so the table is sorted alphabetically. 默认情况下,排序顺序为升序。By default, the sort order is ascending.
Sort( IceCream, Quantity )Sort( IceCream, Quantity )

SortByColumns( IceCream, "Quantity" )SortByColumns( IceCream, "Quantity" )
Quantity 列对 IceCream 进行排序。Sorts IceCream by its Quantity column. 由于 Quantity 列包含数字,因此该表按数值大小进行排序。The Quantity column contains numbers, so the table is sorted numerically. 默认情况下,排序顺序为升序。By default, the sort order is ascending.
Sort( IceCream, Quantity, SortOrder.Descending )Sort( IceCream, Quantity, SortOrder.Descending )

SortByColumns( IceCream, "Quantity", SortOrder.Descending )SortByColumns( IceCream, "Quantity", SortOrder.Descending )
Quantity 列对 IceCream 进行排序。Sorts IceCream by its Quantity column. 由于 Quantity 列包含数字,因此按数值大小进行排序。The Quantity column contains numbers, so the sort is done numerically. 排序顺序已指定为降序。The sort order has been specified as descending.
Sort( IceCream, Quantity + OnOrder )Sort( IceCream, Quantity + OnOrder ) 分别对每条记录按 QuantityOnOrder 列的总和对 IceCream 进行排序。Sorts IceCream by the sum of its Quantity and OnOrder columns for each record individually. 由于总和是数字,因此该表按数值大小进行排序。The sum is a number, so the table is sorted numerically. 默认情况下,排序顺序为升序。By default, the sort order is ascending. 由于我们按公式(而不是原始列值)进行排序,因此没有对等的 SortByColumns 排序。Since we are sorting by a formula and not by raw column values, there is no equivalent using SortByColumns.
Sort( Sort( IceCream, OnOrder ), Quantity )Sort( Sort( IceCream, OnOrder ), Quantity )

SortByColumns( IceCream, "OnOrder", Ascending, "Quantity", Ascending )SortByColumns( IceCream, "OnOrder", Ascending, "Quantity", Ascending )
先按 OnOrder 列对 IceCream 进行排序,然后再按 Quantity 列进行排序。Sorts IceCream first by its OnOrder column, and then by its Quantity column. 请注意,在按 OnOrder 进行第一次排序时,“Pistachio”的排序高于“Vanilla”。然后再按 Quantity 进行排序时,它们一同移到相应的位置上。Note that "Pistachio" rose above "Vanilla" in the first sort based on OnOrder, and then together they moved to their appropriate place based on Quantity.
SortByColumns( IceCream, "Flavor", [ "Pistachio", "Strawberry" ] )SortByColumns( IceCream, "Flavor", [ "Pistachio", "Strawberry" ] ) 根据包含“Pistachio”和“Strawberry”的单列表,按 Flavor 列对 IceCream 进行排序。Sorts IceCream by it's Flavor column based on the single column table containing "Pistachio" and "Strawberry". 结果中先显示包含“Pistachio”这种 Flavor 的记录,然后显示包含“Strawberry”的记录。Records which have a Flavor of "Pistachio" will appear first in the result, followed by records that contain "Strawberry". Flavor 列中不匹配的值(如“Vanilla”)将显示在匹配项的后面。For values in the Flavor column that are not matched, such as "Vanilla", they will appear after the items that were matched.

分步操作Step by step

若要自行运行这些示例,请将 IceCream 数据源创建为集合To run these examples yourself, create the IceCream data source as a collection:

  1. 添加一个按钮,然后将其 OnSelect 属性设置为以下公式:Add a button, and set its OnSelect property to this formula:
    ClearCollect( IceCream, { Flavor: "Chocolate", Quantity: 100, OnOrder: 150 }, { Flavor: "Vanilla", Quantity: 200, OnOrder: 20 }, { Flavor: "Strawberry", Quantity: 300, OnOrder: 0 }, { Flavor: "Mint Chocolate", Quantity: 60, OnOrder: 100 }, { Flavor: "Pistachio", Quantity: 200, OnOrder: 10 } )ClearCollect( IceCream, { Flavor: "Chocolate", Quantity: 100, OnOrder: 150 }, { Flavor: "Vanilla", Quantity: 200, OnOrder: 20 }, { Flavor: "Strawberry", Quantity: 300, OnOrder: 0 }, { Flavor: "Mint Chocolate", Quantity: 60, OnOrder: 100 }, { Flavor: "Pistachio", Quantity: 200, OnOrder: 10 } )
  2. 预览应用,选择按钮,然后按 Esc 返回到默认工作区。Preview the app, select the button, and then press Esc to return to the default workspace.
  3. 选择“文件”菜单上的“集合”,调出刚刚创建的集合,然后按 Esc 返回到默认工作区。Select Collections on the File menu to display the collection that you just created, and then press Esc to return to the default workspace.

排序Sort

  1. 添加另一个按钮,然后将其 OnSelect 属性设置为以下公式:Add another button, and set its OnSelect property to this formula:
    ClearCollect( SortByFlavor, Sort( IceCream, Flavor ) ) ClearCollect( SortByFlavor, Sort( IceCream, Flavor ) )

    上一公式创建第二个集合(名为 SortByFlavor),其中包含与 Ice Cream 相同的数据。The previous formula creates a second collection, named SortByFlavor, that contains the same data as Ice Cream. 不过,新集合中包含的是按 Flavor 列进行字母顺序升序排序的数据。However, the new collection contains the data sorted alphabetically by the Flavor column in ascending order.

  2. 按 F5,选择两个新按钮,然后按 Esc。Press F5, select the new button, and then press Esc.
  3. 选择“文件”菜单上的“集合”,调出这两个集合,然后按 Esc 返回到默认工作区。Select Collections on the File menu to display both collections, and then press Esc to return to the default workspace.
  4. 重复最后三步,不同之处在于更改要创建的集合的名称,然后将 Sort 公式替换为本部分前面示例表中的另一 Sort 公式。Repeat the last three steps, but change the name of the collection that you want to create, and replace the Sort formula with a different formula from the table of examples earlier in this section that uses Sort.

SortByColumnsSortByColumns

  1. 添加另一个按钮,然后将其 OnSelect 属性设置为以下公式:Add another button, and set its OnSelect property to this formula:
    ClearCollect( SortByQuantity, SortByColumns( IceCream, "Quantity", Ascending, "Flavor", Descending ) ) ClearCollect( SortByQuantity, SortByColumns( IceCream, "Quantity", Ascending, "Flavor", Descending ) )

    上一公式创建第三个集合(名为 SortByQuantity),其中包含与 Ice Cream 相同的数据。The previous formula creates a third collection, named SortByQuantity, that contains the same data as Ice Cream. 不过,新集合包含的是按 Quantity 列进行数值大小升序排序,然后按 Flavor 列进行降序排序的数据。However, the new collection contains the data sorted numerically by the Quanity column in ascending order, and then by the Flavor column in descending order.

  2. 按 F5,选择两个新按钮,然后按 Esc。Press F5, select the new button, and then press Esc.
  3. 选择“文件”菜单上的“集合”,调出所有三个集合,然后按 Esc 返回到默认工作区。Select Collections on the File menu to display all three collections, and then press Esc to return to the default workspace.
  4. 重复最后三步,不同之处在于更改要创建的集合的名称,然后将 SortByColumns 公式替换为本部分前面示例表中的另一 SortByColumns 公式。Repeat the last three steps, but change the name of the collection that you want to create, and replace the SortByColumns formula with a different formula from the table of examples earlier in this section that uses SortByColumns.