了解 PowerApps 中的資料表和記錄Understand tables and records in PowerApps

您可以建立一個應用程式,存取 Microsoft Excel、SharePoint、SQL Server,以及數個其他來源 (將資料儲存在記錄和資料表) 中的資訊。You can create an app that accesses information in Microsoft Excel, SharePoint, SQL Server, and several other sources that store data in records and tables. 若要最有效地使用這類資料,請檢閱這些結構的基本概念。To work most effectively with this kind of data, review the concepts that underlie these structures.

  • 記錄包含個人、場所或事物相關資訊的一或多個類別。A record contains one or more categories of information about a person, a place, or a thing. 例如,記錄可能包含單一客戶的名稱、電子郵件地址和電話號碼。For example, a record might contain the name, the email address, and the phone number of a single customer. 其他工具將記錄稱為「資料列」或「項目」。Other tools refer to a record as a "row" or an "item."
  • 資料表會保存一或多筆記錄,其中包含相同類別的資訊。A table holds one or more records that contain the same categories of information. 例如,資料表可能包含 50 個客戶的名稱、電子郵件地址和電話號碼。For example, a table might contain the names, the email addresses, and the phone numbers of 50 customers.

在您的應用程式中,您將使用公式來建立、更新和操作記錄和資料表。In your app, you'll use formulas to create, update, and manipulate records and tables. 您可能會讀取和寫入資料至外部資料來源 (即擴充的資料表)。You'll probably read and write data to an external data source, which is an extended table. 此外,您也可以建立一或多個內部資料表,稱為集合In addition, you might create one or more internal tables, which are called collections.

您可以建置各種公式,採用資料表的名稱做為引數,就像 Excel 中的公式採用一或多個資料格參考做為引數一樣。You can build a variety of formulas that take the name of a table as an argument, just as a formula in Excel takes one or more cell references as arguments. PowerApps 中的某些公式會傳回資料表,反映您指定的其他引數。Some formulas in PowerApps return a table that reflects the other arguments that you specify. 例如,您可能建立一個公式︰For example, you might create a formula:

  • 更新資料表中的記錄,方法為指定該資料表做為 Patch 函式的一或多個引數to update a record in a table by specifying that table as one of multiple arguments for the Patch function
  • 新增、移除和重新命名資料表中的資料行,方法為指定該資料表做為 AddColumnsDropColumnsRenameColumns 函式的引數。to add, remove, and rename columns in a table by specifying that table as an argument for the AddColumns, DropColumns, or RenameColumns function. 那些函式都不會修改原始的資料表。None of those functions modifies the original table. 反而,函式會根據您指定的其他引數傳回另一個資料表。Instead, the function returns another table based on the other arguments that you specify.

資料表的元素Elements of a table

記錄Records

對於個人、場所或事物,每一筆記錄都包含至少一個類別的資訊。Each record contains at least one category of information for a person, a place, or a thing. 上述範例顯示每項產品的記錄 (巧克力麵包) 和每個資訊類別的資料行 (價格庫存量訂購數量)。The example above shows a record for each product (Chocolate, Bread, and Water) and a column for each category of information (Price, Quantity on Hand, and Quantity on Order).

在公式中,您可以使用大括號參照資料表內容外的記錄本身。In a formula, you can refer to a record by itself, outside of a table's context, by using curly braces. 例如,此記錄 { Name: "Strawberries", Price: 7.99 } 與資料表沒有關聯。For example, this record { Name: "Strawberries", Price: 7.99 } isn't associated with a table.

欄位Fields

欄位是記錄中的個別資訊片段。A field is an individual piece of information in a record. 您可以針對特定記錄將這類欄位視覺化為資料行中的值。You can visualize this sort of field as a value in a column for a particular record.

就像控制項,您可以在記錄上使用 .Just as with a control, you refer to a field of a record by using the . 運算子參照記錄的欄位。operator on the record. 例如,First(Products).Name 會傳回 Products 資料表中第一筆記錄的 Name 欄位。For example, First(Products).Name returns the Name field for the first record in the Products table.

欄位可以包含另一筆記錄或資料表,如 GroupBy 函式的範例所示。A field can contain another record or table, as the example for the GroupBy function shows. 您可以依照需求建立多層記錄和資料表的巢狀。You can nest as many levels of records and tables as you want.

資料行Columns

資料行指的是資料表中一或多筆記錄的相同欄位。A column refers to the same field for one or more records in a table. 在上述範例中,每項產品都有一個價格欄位,而且該價格位於所有產品的相同資料行中。In the above example, each product has a price field, and that price is in the same column for all products. 上表有四個資料行,以水平方式顯示在頂端︰The above table has four columns, shown horizontally across the top:

  • 名稱Name
  • 價格Price
  • 庫存量Quantity on Hand
  • 訂購數量Quantity on Order

資料行的名稱反映該資料行中的欄位。The column's name reflects the fields in that column.

資料行內的所有值都屬於相同的資料類型。All values within a column are of the same data type. 在上述範例中,記錄的「庫存量」資料行一律包含數字,而且不能包含字串,例如「12 單位」。In the above example, the "Quantity on Hand" column always contains a number and can't contain a string, such as "12 units," for one record. 任何欄位的值也可能 空白The value of any field may also be blank.

您可能已在其他工具中將資料行稱為「欄位」。You may have referred to columns as "fields" in other tools.

注意

如果 SharePoint 和 Excel 資料來源包含有空格的資料行名稱,PowerApps 會將空格取代為 "_x0020_"For SharePoint and Excel data sources that contain column names with spaces, PowerApps will replace the spaces with "_x0020_". 例如,SharePoint 或 Excel 中的 "Column Name" 在資料版面配置中顯示或是在公式中使用時,會在 PowerApps 中顯示為 "Column_x0020_Name"For example, "Column Name" in SharePoint or Excel will appear as "Column_x0020_Name" in PowerApps when displayed in the data layout or used in a formula.

資料表Table

資料表包含一或多筆記錄,每筆記錄都有多個欄位,它們在各個記錄中都具有一致的名稱。A table comprises one or more records, each with multiple fields that have consistent names across the records.

儲存在資料來源或集合中的任何資料表都有名稱 (用來參照資料表),並將它傳遞至採用資料表做為引數的函式。Any table that's stored in a data source or a collection has a name, which you use to refer to the table and pass it to functions that take tables as arguments. 資料表也可以是函式或公式的結果。Tables can also be the result of a function or a formula.

如下列範例所示,您可以使用公式表示資料表,方法為搭配使用 Table 函式與一組您用大括號表示的記錄︰As in the following example, you can express a table in a formula by using the Table function with a set of records, which you express in curly braces:

Table( { Value: "Strawberry" }, { Value: "Vanilla" } )Table( { Value: "Strawberry" }, { Value: "Vanilla" } )

您也可以定義單一資料行的資料表,以方括弧括住。You can also define a single-column table with square brackets. 另一種撰寫上述公式的方式︰An equivalent way to write the above:

[ "Strawberry", "Vanilla" ][ "Strawberry", "Vanilla" ]

資料表公式Table formulas

在 Excel 和 PowerApps 中,您可以使用公式,以類似方式操作數字和字串︰In Excel and PowerApps, you use formulas to manipulate numbers and strings of text in similar ways:

  • 在 Excel 中,於資料格 A1 中輸入一值 (例如 42),然後在另一個儲存格中輸入公式 (例如A1 + 2),以顯示 44 的值。In Excel, type a value, such as 42, in cell A1, and then type a formula, such as A1+2, in another cell to show the value of 44.
  • 在 PowerApps 中,將 Slider1Default 屬性設為 42,並將標籤的 Text 屬性設為 Slider1.Value + 2,以顯示 44 的值。In PowerApps, set the Default property of Slider1 to 42, and set the Text property of a label to Slider1.Value + 2 to show the value of 44.

在這兩種案例中,如果您變更引數的值 (例如,資料格 A1 中的數字或 Slider1 的值),則算出的值會自動變更。In both cases, the calculated value changes automatically if you change the values of the arguments (for example, the number in cell A1 or the value of Slider1).

同樣地,您可以使用公式,存取和操作資料表和記錄中的資料。Similarly, you can use formulas to access and manipulate data in tables and records. 您可以使用資料表的名稱做為某些公式 (例如 Min(Catalog, Price)) 中的引數,以顯示 Catalog 資料表的 Price 資料行中的最低值。You can use names of tables as arguments in some formulas, such as Min(Catalog, Price) to show the lowest value in the Price column of the Catalog table. 其他公式提供整個資料表為傳回值,例如 RenameColumns(Catalog, "Price", "Cost"),其會從 Catalog 資料表中傳回所有記錄,但會將 Price 資料行的名稱變更為 CostOther formulas provide whole tables as return values, such as RenameColumns(Catalog, "Price", "Cost"), which returns all the records from the Catalog table but changes the name of the Price column to Cost.

就像數字,當基礎資料表或記錄變更時,涉及資料表和記錄的公式會自動重新計算。Just as with numbers, formulas that involve tables and records are automatically recalculated as the underlying table or record changes. 如果 Catalog 資料表中的產品成本降低而低於前一個最小值,則 Min 公式的傳回值將自動變更以符合它。If the cost of a product in the Catalog table is lowered below the previous minimum, the return value of the Min formula will automatically change to match it.

讓我們逐步解說一些簡單的範例。Let's walk through some simple examples.

  1. 新增文字資源庫控制項,並將其 Items 屬性設為資料表的名稱。Add a Text gallery control, and set its Items property to the name of a table.

    依預設,資源庫會顯示預留位置文字,其來自名為 TextualGallerySample 的資料表。By default, the gallery shows placeholder text from a table named TextualGallerySample. 資源庫的 Items 屬性會自動設為該資料表。The Items property of the gallery is automatically set to that table.

    注意

    基於圖例用途,某些控制項已重新排列並放大。Some controls have been rearranged and enlarged for illustration purposes.

  2. 不是將 Items 屬性設為資料表的名稱,而是將它設為公式,以包括資料表的名稱做為引數,如下列範例所示:Instead of setting the Items property to the name of a table, set it to a formula that includes the name of the table as an argument, as in this example:
    Sort(TextualGallerySample, Heading, Descending)Sort(TextualGallerySample, Heading, Descending)

    此公式包含 Sort 函式,其會採用資料表的名稱做為其第一個引數,並採用該資料表中資料行的名稱做為第二個引數。This formula incorporates the Sort function, which takes the name of a table as its first argument and the name of a column in that table as its second argument. 函式也支援選擇性第三個引數,其中規定您想要以遞減順序排序資料。The function also supports an optional third argument, which stipulates that you want to sort the data in descending order.

  3. Items 屬性設為公式,此公式會採用前一個步驟中的公式做為引數並傳回資料表,如下列範例所示:Set the Items property to a formula that takes the formula from the previous step as an argument and returns a table, as in this example:
    FirstN(Sort(TextualGallerySample, Heading, Descending), 2)FirstN(Sort(TextualGallerySample, Heading, Descending), 2)

    在此公式中,您使用 FirstN 函式,顯示資料表中特定數目的記錄。In this formula, you use the FirstN function to show a particular number of records in a table. 您使用 Sort 函式做為 FirstN 的第一個引數,並使用數字 (在此情況下,2) 做為第二個引數,指定要顯示多少筆記錄。You use the Sort function as the first argument to FirstN and a number (in this case, 2) as the second argument, which specifies how many records to show.

    整個公式會傳回資料表,其中包含 TextualGallerySample資料表的前兩筆記錄,按標題資料行依遞減順序排序。The entire formula returns a table that contains the first two records of the TextualGallerySample table, sorted by the Heading column in descending order.

資料表函式和控制項屬性Table functions and control properties

PowerApps 中的許多函式會採用資料表的名稱做為引數、建立第二個包含相同資料的資料表、操作以其他引數為基礎的新資料表,然後傳回結果。Many functions in PowerApps take the name of a table as an argument, create a second table that contains the same data, manipulate the new table based the other arguments, and then return the result. 下列函式不會修改原始資料表,即使它是資料來源也一樣。These functions don't modify the original table, even if it's a data source.

  • SortFilter - 排序和篩選記錄。Sort, Filter - Sorts and filters records.
  • FirstNLastN - 傳回資料表的前 N 筆或後 N 筆記錄。FirstN, LastN - Returns the first N or last N records of the table.
  • AbsSqrtRoundRoundUpRoundDown - 單一資料行資料表的每筆記錄上的算術運算,產生單一資料行資料表的結果。Abs, Sqrt, Round, RoundUp, RoundDown - Arithmetic operations on each record of a single-column table, resulting in a single-column table of results.
  • LeftMidRightReplaceSubstituteTrimLowerUpperProper - 單一資料行資料表的每筆記錄上的字串操作,產生單一資料行資料表的字串。Left, Mid, Right, Replace, Substitute, Trim, Lower, Upper, Proper - String manipulations on each record of a single-column table, resulting in a single-column table of strings.
  • Len - 若為資料行的文字,傳回單一資料行資料表,其中包含每個字串的長度。Len - For a column of strings, returns a single-column table that contains the length of each string.
  • 串連 -串連多個資料行的字串,產生單一資料行資料表的字串。Concatenate - Concatenates multiple columns of strings, resulting in a single-column table of strings.
  • AddColumnsDropColumnsRenameColumnsShowColumns - 資料表的資料行操作,產生具有不同資料行的資料表。AddColumns, DropColumns, RenameColumns, ShowColumns - Column manipulation of the table, resulting in a new table with different columns.
  • Distinct - 移除重複記錄。Distinct - Removes duplicates records.
  • Shuffle - 隨機重新排列記錄。Shuffle - Shuffles records into a random order.
  • HashTags - 搜尋字串中的雜湊標記。HashTags - Searches for hashtags in a string.
  • Errors - 當您使用資料來源時,提供錯誤資訊。Errors - Provides error information when you work with a data source.

您可以在包含多個資料行的資料表上執行函式,即使函式需要單一資料行做為引數也一樣。You can run a function on a table that contains multiple columns, even if the function requires a single column as an argument. 若要從多重資料行的資料表中擷取單一資料行,請使用 ShowColumns 函式做為您想要使用之函式的引數,如下列範例所示︰To extract a single column from a multi-column table, use the ShowColumns function as an argument for the function that you want to use, as in this example:
Lower( ShowColumns( Products, "Name" ) )Lower( ShowColumns( Products, "Name" ) )

此公式會建立單一資料行的資料表,其中包含 Products 資料表的 Name 資料行中的所有資料,但會將任何大寫字母轉換成小寫字母。This formula creates a single-column table that contains all the data from the Name column of the Products table but converts any uppercase letters to lowercase letters. 如果您指定資料表做為 AddColumnsRenameColumnsDropColumns 函式的引數,則可以將該資料表完全重新塑造成您想要的形狀。If you specify a table as an argument for the AddColumns, RenameColumns, or DropColumns function, you can completely reshape that table however you want.

如果您指定資料來源做為其中一個函式的引數,它將修改該資料來源的記錄,而且通常會傳回資料來源的新值做為資料表。If you specify a data source as an argument for one of these functions, it will modify the records of that data source and, in general, return the data source's new value as a table.

下列控制項具有其為資料表的屬性︰The following controls have properties that are tables:

  • Items - 適用於資源庫及其清單方塊。Items - Applies to galleries and list boxes. 要顯示在資源庫中的資料表。Table to display in the gallery.
  • SelectedItems - 適用於清單方塊。SelectedItems - Applies to list boxes. 使用者已選取之項目的資料表。Table of items that the user has selected.

記錄公式Record formulas

您也可以建置公式,計算個別記錄的資料、採用個別記錄做為引數,以及提供個別記錄做為傳回值。You can also build a formula that calculates data for an individual record, takes an individual record as an argument, and provides an individual record as a return value. 回到上述的資源庫範例,讓我們使用 Gallery1.Selected 屬性,顯示使用者在該資源庫中選取之任何記錄的資訊。Returning to our gallery example above, let's use the Gallery1.Selected property to display information from whatever record the user selects in that gallery.

  1. 新增一個按鈕,並將其 OnSelect 屬性設為以下公式:Add a button, and set its OnSelect property to this formula:
    Collect( SelectedRecord, Gallery1.Selected )Collect( SelectedRecord, Gallery1.Selected )

  2. 如果未選取按鈕,請按一下它以選取,然後再按一下,以執行公式。If the button isn't selected, click it to select it, and then click it again to run the formula.

  3. 在 [檔案] 功能表中,選取 [集合]In the File menu, select Collections.

此公式會傳回一筆記錄,其中不只包含目前在資源庫中選取之記錄的資料,還包括該資源庫中的每一個控制項。This formula returns a record that includes not only the data from the record that's currently selected in the gallery but also each control in that gallery. 例如,記錄同時包含 Body 資料行 (符合原始資料表中的 Body 資料行),以及 Body1 資料行 (其代表的標籤會顯示該資料行中的資料)。For example, the record contains both a Body column, which matches the Body column in the original table, and a Body1 column, which represents the label that shows the data from that column. 選取 Body1 資料行中的資料表圖示來鑽研該資料。Select the table icon in the Body1 column to drill into that data.

既然您已選取記錄,就可以使用 . 運算子從中擷取個別欄位Now that you have the selected record, you can extract individual fields from it with the . 運算子來存取控制項屬性。operator.

  1. 按下 Esc 鍵以返回預設工作區,然後在資源庫下方新增標籤。Press Esc to return to the default workspace, and then add a label below the gallery.

  2. 將標籤的 Text 屬性設定為以下公式︰Set the Text property of the label to this formula:
    Gallery.Selected.HeadingGallery.Selected.Heading

您已採用 Selected 屬性 (其為一筆記錄),並從中擷取 Heading 屬性。You've taken the Selected property, which is a record, and extracted the Heading property from it.

您也可以使用記錄做為相關具名值的一般用途容器。You can also use a record as a general-purpose container for related named values.

  • 如果您根據 UpdateContextNavigate 函式建置公式,請使用記錄來蒐集您想要更新的內容變數If you build a formula around the UpdateContext and Navigate functions, use a record to gather the context variables that you want to update.
  • 使用 編輯表單 控制項上的 Updates 屬性,來蒐集使用者已在表單中所做的變更。Use the Updates property on an Edit form control to gather the changes that have been made by the user in a form.
  • 使用 Patch 函式,不僅可以更新資料來源,還可以合併記錄。Use the Patch function to update a data source but also to merge records.

在這些情況下,記錄永遠不會是資料表的一部分。In these cases, the record was never a part of a table.

記錄函式和控制項屬性Record functions and control properties

傳回記錄的函式︰Functions that return records:

  • FirstNLastN - 傳回資料表的第一筆或最後一筆記錄或數筆記錄。FirstN, LastN - Returns the first or last record or records of the table.
  • Lookup - 從符合一或多個條件之資料表中傳回第一筆記錄。Lookup - Returns the first record from a table that matches one or more criteria.
  • Patch - 更新資料來源或合併記錄。Patch - Updates a data source or merges records.
  • Defaults - 傳回資料來源的預設值。Defaults - Returns the default values for a data source.

傳回記錄的屬性︰Properties that return records:

  • Selected - 適用於資源庫及其清單方塊。Selected - Applies to galleries and list boxes. 傳回目前選取的記錄。Returns the currently selected record.
  • Updates - 適用於資源庫。Updates - Applies to galleries. 同時拉出使用者在資料輸入表單中進行的所有變更。Pulls together all the changes that a user makes in a data-entry form.
  • Update - 適用於輸入控制項,例如文字輸入控制項和滑桿。Update - Applies to input controls such as text-input controls and sliders. 針對資源庫設定要同時拉出的個別屬性。Sets up individual properties for the gallery to pull together.

記錄範圍Record scope

有些函式的運作方式為跨資料表的所有記錄個別評估公式。Some functions operate by evaluating a formula across all the records of a table individually. 公式的結果有各種使用方式︰The formula's result is used in various ways:

  • FilterLookup - 公式會判定輸出中是否應該包括記錄。Filter, Lookup - Formula determines if the record should be included in the output.
  • Sort - 公式會提供排序記錄所依據的值。Sort - Formula provides the value to sort the records on.
  • Concat - 公式會決定要一起串連的字串。Concat - Formula determines the strings to concatenate together.
  • ForAll - 公式可以傳回任何值,可能有副作用。ForAll - Formula can return any value, potentially with a side effect.
  • Distinct - 公式會傳回一值,用來識別重複記錄。Distinct - Formula returns a value, used to identify duplicate records.
  • AddColumns - 公式會提供已新增之欄位的值。AddColumns - Formula provides the value of the added field.
  • AverageMaxMinSumStdevPVarP - 公式會提供要加總的值。Average, Max, Min, Sum, StdevP, VarP - Formula provides the value to aggregate.

在這些公式內,您可以參考正在處理之記錄的欄位。Inside these formulas, you can reference the fields of the record being processed. 其中每一個函式都會建立一個用以評估公式的「記錄範圍」,其中記錄的欄位可以做為最上層識別碼。Each of these functions creates a "record scope" in which the formula is evaluated, where the fields of the record are available as top-level identifiers. 您也可以參考整個應用程式中的控制項屬性和其他值。You can also reference control properties and other values from throughout your app.

例如,採用 Products 的資料表:For example, take a table of Products:

若要判斷其中是否有任一項產品要求的超過可用的︰To determine if any of any of these products had more requested than is available:

Filter( Products, 'Quantity Requested' > 'Quantity Available' )Filter( Products, 'Quantity Requested' > 'Quantity Available' )

Filter 的第一個引數為操作所在之記錄的資料表,而第二個引數為公式。The first argument to Filter is the table of records to operate on, and the second argument is a formula. Filter 會建立一個記錄範圍,用於評估此公式,其中每筆記錄的欄位均可用,在此情況下,指的是 ProductQuantity RequestedQuantity AvailableFilter creates a record scope for evaluating this formula in which the fields of each record are available, in this case Product, Quantity Requested, and Quantity Available. 比較的結果會判定函式的結果中是否應該包含每一筆記錄︰The result of the comparison determines if each record should be included in the result of the function:

加入下列範例,我們可以計算每項產品的訂購數量︰Adding to this example, we can calculate how much of each product to order:

AddColumns( Filter( Products, 'Quantity Requested' > 'Quantity Available' ), "Quantity To Order", 'Quantity Requested' - 'Quantity Available' )AddColumns( Filter( Products, 'Quantity Requested' > 'Quantity Available' ), "Quantity To Order", 'Quantity Requested' - 'Quantity Available' )

在這裡我們會將算出的資料行新增至結果。Here we are adding a calculated column to the result. AddColumns 具有自己的記錄範圍,用來計算要求的數量與可用的數量之間的差異。AddColumns has its own record scope that it uses to calculate the difference between what has been requested and what is available.

最後,我們可以將結果資料表減少至只有我們想要的資料行︰Finally, we can reduce the result table to just the columns that we desire:

ShowColumns( AddColumns( Filter( Products, 'Quantity Requested' > 'Quantity Available' ), "Quantity To Order", 'Quantity Requested' - 'Quantity Available' ), "Product", "Quantity To Order" )ShowColumns( AddColumns( Filter( Products, 'Quantity Requested' > 'Quantity Available' ), "Quantity To Order", 'Quantity Requested' - 'Quantity Available' ), "Product", "Quantity To Order" )

請注意,在上述中,我們在某些位置使用雙引號 ("),而在其他位置使用單引號 (')。Note that in the above, we used double quotes (") in some places and single quotes (') in other places. 參考物件 (例如欄位或資料表) 的值時需要單引號,因為物件的名稱包含空格。Single quotes are required when referencing the value of an object, such as a field or table, in which the name of the object contains a space. 不是參考物件的值,而是談論它,尤其是在物件尚未存在的情況時,會使用雙引號,如 AddColumns 的案例所示。Double quotes are used when we are not referencing the value of an object but instead talking about it, especially in situations in which the object does not yet exist, as in the case of AddColumns.

去除混淆Disambiguation

隨附記錄範圍新增的欄位名稱會覆寫應用程式中其他位置的相同名稱。Field names added with the record scope override the same names from elsewhere in the app. 發生這種情況時,您仍然可以使用 @ 去除混淆 運算子,從記錄範圍外存取值︰When this happens, you can still access values from outside the record scope with the @ disambiguation operator:

  • 若要從巢狀記錄範圍中存取值,請使用 @ 運算子,搭配在使用模式 Table[@FieldName*] 時進行操作的資料表名稱。To access values from nested record scopes, use the @ operator with the name of the table being operated upon using the pattern Table[@FieldName].
  • 若要存取全域值 (例如資料來源、集合和內容變數),請使用模式 [@ObjectName]** (不含資料表指定)。To access global values, such as data sources, collections, and context variables, use the pattern [@ObjectName] (without a table designation).

如果操作所在的資料表是運算式,例如 Filter( table, ... ),則無法使用去除混淆運算子。If the table being operated upon is an expression, such as Filter( table, ... ), then the disambiguation operator cannot be used. 只有最內層的記錄範圍可以不使用去除混淆運算子,從這個資料表運算式中存取欄位。Only the innermost record scope can access fields from this table expression, by not using the disambiguation operator.

例如,假設有集合 XFor example, imagine having a collection X:

您可以利用 ClearCollect( X, [1, 2] ) 建立這個集合。You can create this collection with ClearCollect( X, [1, 2] ).

還有另一個集合 YAnd another collection Y:

您可以利用 ClearCollect( Y, ["A", "B"] ) 建立這個集合。You can create this collection with ClearCollect( Y, ["A", "B"] ).

此外,使用下列公式,定義名為 Value 的內容變數:UpdateContext( {Value: "!"} )In addition, define a context variable named Value with this formula: UpdateContext( {Value: "!"} )

總而言之,Let's put it all together. 在此內容中,下列公式︰In this context, the following formula:

  • Ungroup( ForAll( X, ForAll( Y, Y[@Value] & Text( X[@Value] ) & [@Value] ) ), "Value" )Ungroup( ForAll( X, ForAll( Y, Y[@Value] & Text( X[@Value] ) & [@Value] ) ), "Value" )

會產生此資料表︰produces this table:

這是怎麼一回事?What is going on here? 最外層的 ForAll 函式定義 X 的記錄範圍,允許在處理每一筆記錄時存取其 Value 欄位。The outermost ForAll function defines a record scope for X, allowing access to the Value field of each record as it is processed. 只需使用 Value 一字或使用 X[@Value] 即可存取。It can be accessed by simply using the word Value or by using X[@Value].

最內層的 ForAll 函式為 Y 定義另一個記錄範圍。因為此資料表已定義 Value 欄位,所以在這裡使用 Value 表示 Y 記錄中的欄位,不再是來自 X 的欄位。在這裡,若要存取 XValue 欄位,我們必須搭配使用較長的版本與去除混淆運算子。The innermost ForAll function defines another record scope for Y. Since this table also has a Value field defined, using Value here refers to the field in Y's record and no longer the one from X. Here, to access X's Value field, we must use the longer version with the disambiguation operator.

由於 Y 是最內部的記錄範圍,存取此資料表的欄位不需要去除混淆,因而允許我們搭配使用此公式與相同結果︰Since Y is the innermost record scope, accessing fields of this table do not require disambiguation, allowing us to use this formula with the same result:

  • Ungroup( ForAll( X, ForAll( Y, Value & Text( X[@Value] ) & [@Value] ) ), "Value" )Ungroup( ForAll( X, ForAll( Y, Value & Text( X[@Value] ) & [@Value] ) ), "Value" )

所有 ForAll 記錄範圍均會覆寫全域範圍。All the ForAll record scopes override the global scope. 若沒有去除混淆運算子,無法透過名稱使用我們定義的 Value 內容變數。The Value context variable we defined is not available by name without the disambiguation operator. 若要存取此值,我們必須使用 [@Value]To access this value we must use [@Value].

Ungroup 會壓平合併結果,因為巢狀 ForAll 函式將產生巢狀結果資料表。Ungroup flattens the result, since nested ForAll functions will result in a nested result table.

內嵌語法Inline syntax

記錄Records

您可以使用包含具名欄位值的大括號表示記錄。You express records by using curly braces that contain named field values. 例如,您可以使用下列公式,表示本主題開頭的資料表中的第一筆記錄︰For example, you can express the first record in the table at the start of this topic by using this formula:

{ Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 }{ Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 }

您也可以搭配使用內嵌公式與其他公式,如下列範例所示︰You can also embed formulas within other formulas, as this example shows:

{ Name: First(Products).Name, Price: First(Products).Price * 1.095 }{ Name: First(Products).Name, Price: First(Products).Price * 1.095 }

您可以使用巢狀大括號建立巢狀記錄,如下列範例所示︰You can nest records by nesting curly braces, as this example shows:

{ 'Quantity': { 'OnHand': ThisItem.QuantOnHand, 'OnOrder': ThisItem.QuantOnOrder } }{ 'Quantity': { 'OnHand': ThisItem.QuantOnHand, 'OnOrder': ThisItem.QuantOnOrder } }

以單引號括住每個包含特殊字元 (例如空格或冒號) 的資料行名稱。Enclose each column name that contains a special character, such as a space or a colon, in single quotes. 若要在資料行名稱內使用單引號,請按兩下它。To use a single quote within a column name, double it.

請注意,Price 資料行中的值不包括貨幣符號,例如錢幣符號。Note that the value in the Price column doesn't include a currency symbol, such as a dollar sign. 顯示值時,將套用該格式。That formatting will be applied when the value is displayed.

資料表Tables

您可以使用 Table 函式和一組記錄建立資料表。You can create a table by using the Table function and a set of records. 您可以使用下列公式,表示本主題開頭的資料表︰You can express the table at the start of this topic by using this formula:

Table( { Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 },
            { Name: "Bread", Price: 4.95, 'Quantity on Hand': 34, 'Quantity on Order': 0 },
            { Name: "Water", Price: 4.95, 'Quantity on Hand': 10, 'Quantity on Order': 0 } )
Table( { Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 },
            { Name: "Bread", Price: 4.95, 'Quantity on Hand': 34, 'Quantity on Order': 0 },
            { Name: "Water", Price: 4.95, 'Quantity on Hand': 10, 'Quantity on Order': 0 } )

您也可以建立巢狀資料表︰You can also nest tables:

Table( { Name: "Chocolate",
            'Quantity History': Table( { Quarter: "Q1", OnHand: 10, OnOrder: 10 },
                        { Quarter: "Q2", OnHand: 18, OnOrder: 0 } ) } )
Table( { Name: "Chocolate",
            'Quantity History': Table( { Quarter: "Q1", OnHand: 10, OnOrder: 10 },
                        { Quarter: "Q2", OnHand: 18, OnOrder: 0 } ) } )

值資料表Value tables

您可以在方括號中指定值,建立單一資料行的資料表。You can create single-column tables by specifying values in square brackets. 產生的資料表具有單一資料行,名為 ValueThe resulting table has a single column, named Value.

例如,[ 1, 2, 3, 4 ] 等同於 Table( { Value: 1 }, { Value: 2 }, { Value: 3 }, { Value: 4 } ),並傳回下列資料表:For example, [ 1, 2, 3, 4 ] is equivalent to Table( { Value: 1 }, { Value: 2 }, { Value: 3 }, { Value: 4 } ) and returns this table: