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. 例如,您可以使用此公式來排序 Contacts 資料表,方式為先依 LastName 資料行排序,再依 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" ).

您可以將 SortByColumnsDrop downList box 控制項結合,讓使用者選取排序依據的資料行。You can combine SortByColumns with a Drop down or List box control to enable users to select which column to sort by.

除了遞增或遞減排序外,亦可根據包含值的單資料行資料表來排序 SortByColumnsIn 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" )
IceCream 依其 Flavor 資料行排序。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" )
IceCream 依其 Quantity 資料行排序。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 )
IceCream 依其 Quantity 資料行排序。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 ) IceCream 分別依據其每筆記錄的 QuantityOnOrder 資料行總和排序。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. 由於我們是依公式排序,而不是原始資料行值,因此不等同使用 SortByColumnsSince 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 )
IceCream 先依其 OnOrder 資料行,再依其 Quantity 資料行排序。Sorts IceCream first by its OnOrder column, and then by its Quantity column. 請注意,"Pistachio" 在第一次排序時會根據 OnOrder 排在 "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" ] ) IceCream 根據包含 "Pistachio" 與 "Strawberry" 的單一資料行資料表來依 Flavor 資料行排序。Sorts IceCream by it's Flavor column based on the single column table containing "Pistachio" and "Strawberry". Flavor 為 "Pistachio" 的記錄會在結果中第一個出現,接下來是包含 "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. 不過,新集合中的資料會依據 Quanity 的數字大小排序,再依據 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.