了解 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)即可显示值 44In 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 中,将“Slider1”的“Default”属性设置为“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 表的前两条记录,这些记录已按 Heading 列的降序排序。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.

可以针对包含多个列的表运行函数,即使该函数需要使用单个列作为参数。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 Available)。Filter 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"] ).

此外,使用公式 UpdateContext( {Value: "!"} ) 定义名为 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. 生成的表包含名为 Value 的单个列。The 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: