了解 PowerApps 中的数据源Understand data sources in PowerApps

大多数 PowerApps 应用使用所谓数据源的云服务中存储的外部信息。Most PowerApps apps use external information stored in cloud services called Data Sources. OneDrive for Business 中存储的 Excel 文件包含的表就一个常见的例子。A common example is a table in an Excel file stored in OneDrive for Business. 应用使用连接访问这些数据源。Apps access these data sources by using Connections.

本文介绍不同类型的数据源,以及如何使用表数据源。This article discusses the different kinds of data sources, and how to work with table data sources.

我们可以轻松创建一个向数据源执行基本读取和写入的应用。It is easy to create an app that does basic reading and writing to a data source. 但有时,我们希望能够以更大的力度控制数据流入和流出应用的方式。But sometimes you want more control over how data flows in and out of your app. 本文介绍如何使用 PatchDataSourceInfoValidateErrors 函数来提供更高的控制度。This article describes how the Patch, DataSourceInfo, Validate, and Errors functions provide more control.

数据源的类型Kinds of data sources

数据源可连接到云服务,或者,可以位于应用本地。Data sources can be connected to a cloud service, or they can be local to an app.

连接的数据源Connected data sources

最常见的数据源是可用于检索和存储信息的表。The most common data sources are tables, which you can use to retrieve and store information. 可以使用与数据源建立的连接,通过 Microsoft Excel 工作簿、SharePoint 列表、SQL 表和许多其他格式读取和写入数据,并将这些数据存储在 OneDrive for Business、DropBox、SQL Server 等云服务中。You can use connections to data sources to read and write data in Microsoft Excel workbooks, SharePoint lists, SQL tables, and many other formats, which can be stored in cloud services like OneDrive for Business, DropBox, SQL Server, etc.

除表以外的其他数据源包括电子邮件、日历、Twitter 和通知,但本文并不介绍其他这些类型的数据源。Data sources other than tables include email, calendars, Twitter, and notifications, but this article doesn't discuss these other kinds of data sources.

本地数据源Local data sources

使用显示窗体编辑窗体控件可以轻松创建一个可在数据源中读取和写入数据的应用。Using the Gallery, Display form, and Edit form controls, it is easy to create an app that reads and writes data from a data source. 若要开始,请阅读了解数据窗体一文。To get started, read the article Understand data forms.

如果想要让 PowerApps 基于数据创建应用,需使用这些控件。When you ask PowerApps to create an app from data, these controls are used. 在幕后,应用使用内部表来存储和处理来自数据源的数据。Behind the scenes, the app uses an internal table to store and manipulate the data that comes from the data source.

一种特殊的数据源是集合,它位于应用本地,而不是基于与云中服务建立的连接,因此,无法在同一用户的设备之间或者在不同的用户之间共享信息。A special kind of data source is the Collection, which is local to the app and not backed by a connection to a service in the cloud, so the information can not be shared across devices for the same user or between users. 可在本地加载和保存集合。Collections can be loaded and saved locally.

表的类型Kinds of tables

PowerApps 应用内部的表为固定值,就如同数字或字符串是值一样。Tables that are internal to a PowerApps app are fixed values, just as a number or a string is a value. 内部表不会存储在任何位置,而只存在于应用的内存中。Internal tables aren't stored anywhere, they just exist in your app's memory. 无法直接修改表的结构和数据。You can't directly modify the structure and data of a table. 替代做法是通过公式创建一个新表:使用该公式创建原始表的修改副本。What you can do instead is to create a new table through a formula: you use that formula to make a modified copy of the original table.

外部表存储在数据源中,供日后检索和共享。External tables are stored in a data source for later retrieval and sharing. PowerApps 提供用于读取和写入存储数据的“连接”。PowerApps provides "connections" to read and write stored data. 在一个连接中可以访问多个信息表。Within a connection, you can access multiple tables of information. 可以选择要在应用中使用哪些表,每个表将成为独立的 数据源You'll select which tables to use in your app, and each will become a separate data source.

若要了解详细信息,请参阅使用表,其中更详细介绍了内部表,但该文章同样适用于驻留在云服务中的外部表。To learn more, Working with tables goes into more detail about internal tables, but it is also applicable to external tables residing in a cloud service.

使用表Working with tables

可以像使用内部 PowerApps 表一样使用表数据源。You can use table data sources the same way that you use an internal PowerApps table. 与内部表一样,每个数据源具有可在公式中使用的记录和属性。Just like an internal table, each data source has records, columns, and properties that you can use in formulas. 此外:In addition:

  • 数据源包含的列名称和数据类型与连接中的基础表相同。The data source has the same column names and data types as the underlying table in the connection.

    注意:对于列名称带空格的 SharePoint 和 Excel 数据源,PowerApps 会将空格替换为“_x0020_”。Note: 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.

  • 加载应用时,将自动从服务中加载数据源。The data source is loaded from the service automatically when the app is loaded. 可以 Refresh 函数强制刷新数据。You can force the data to refresh by using the Refresh function.
  • 用户运行某个应用时,可以创建、修改和删除记录,然后将这些更改推回到服务中的基础表。As users run an app, they can create, modify, and delete records and push those changes back to the underlying table in the service.
    • 可以使用 PatchCollect 函数创建记录。Records can be created with the Patch and Collect functions.
    • 可以使用 PatchUpdateUpdateIf 函数修改记录。Records can be modified with the Patch, Update, and UpdateIf functions.
    • 可以使用 RemoveRemoveIf 函数删除记录。Records can be removed with the Remove and RemoveIf functions.
    • 可通过 Errors 函数显示使用数据源时出现的错误。Errors when working with a data source are available through the Errors function.
  • DataSourceInfoDefaultsValidate 函数提供有关数据源的、可用于优化用户体验的信息。The DataSourceInfo, Defaults, and Validate functions provide information about the data source that you can use to optimize the user experience.

创建数据源Creating data sources

无法使用 PowerApps 创建连接的数据源或修改此类数据源的结构;数据源必须已在服务的某个位置存在。PowerApps can't be used to create a connected data source, or modify its structure; the data source must already exist in a service somewhere. 例如,若要在 OneDrive 上存储的 Excel 工作簿中创建表,首先应使用 OneDrive 上的 Excel Online 创建一个工作簿。For example, to create a table in an Excel workbook stored on OneDrive, you first use Excel Online on OneDrive to create a workbook. 接下来,可通过应用与该工作簿建立连接。Next you create a connection to it from your app.

但是, 可以 在应用内部创建和修改集合数据源,不过,这种数据源只是临时性的。However, collection data sources can be created and modified inside an app, but are only temporary.

显示一个或多个记录Display one or more records

上图显示了应用读取数据源中的信息时的信息流 The diagram above shows the flow of information when an app reads the information in a data source:

  • 信息通过存储服务(在本例中为 Office 365 站点的 SharePoint 列表)存储和共享。The information is stored and shared through a storage service (in this case, a SharePoint list of an Office 365 site).
  • 可以通过连接来向应用提供此信息。A connection makes this information available to the app. 该连接负责对访问信息的用户进行身份验证。The connection takes care of authentication of the user to access the information.
  • 启动应用或者调用 Refresh 函数时,信息将从连接抽取到应用中的数据源供本地使用。When the app is started or the Refresh function is pressed, information is drawn from the connection into a data source in the app for local use.
  • 可以使用公式读取信息,并在用户可见的控件中公开这些信息。Formulas are used to read the information and expose it in controls that the user can see. 通过在屏幕上使用库并将 Items 属性连接到数据源来显示该数据源的记录:Gallery.Items = DataSourceYou can display the records of a data source by using a gallery on a screen and wiring the Items property to the data source: Gallery.Items = DataSource. 使用控件的 Default 属性将库中的控件连接到该库。You wire controls within the gallery, to the gallery, using the controls' Default property.
  • 数据源也是一个表。The data source is also a table. 因此,在使用整个数据源之前,可以使用 FilterSortAddColumns 和其他函数来对它进行优化和补充。So you can use Filter, Sort, AddColumns, and other functions to refine and augment the data source before using it as a whole. 还可以使用 LookupFirstLast 和其他函数来处理单条记录。You can also use the Lookup, First, Last, and other functions to work with individual records.

修改记录Modify a record

前一部分已介绍如何读取数据源。In the prededing section, you saw how to read a data source. 请注意,上图中的箭头是单向的。Note that the arrows in the diagram above are one way. 对数据源所做的更改不是通过用于检索数据的同一个公式推回的,Changes to a data source aren't pushed back through the same formulas in which the data was retrieved. 而是使用新的公式。Instead, new formulas are used. 通常,用于编辑记录与浏览记录的屏幕是不同的,尤其是在移动设备上。Often a different screen is used for editing a record than for browsing records, especially on a mobile device.

请注意,若要修改某个数据源的某个现有记录,该记录最初必须来自该数据源。Note that, to modify an existing record of a data source, the record must have originally come from the data source. 记录可能已遍历某个库、某个上下文变量和任意数量的公式,但其来源应可追溯到该数据源。The record may have traveled through a gallery, a context variable, and any number of formulas, but its origin should be traceable back to the data source. 这一点非常重要,因为其他信息会连同唯一标识它们的记录一起遍历,确保修改正确的记录。This is important because additional information travels with the record that uniquely identifies it, ensuring that you modify the correct record.

上图显示了更新数据源时的信息流: The diagram above shows the flow of information to update a data source:

  • 编辑窗体”控件提供了输入卡片的容器。输入卡片由文本输入控件或滑块等用户输入控件构成。An Edit form control provides a container for input cards, which are made up of user input controls such as a text-input control or a slider. DataSourceItem 属性用于标识要编辑的记录。The DataSource and Item properties are used to identify the record to edit.
  • 每个输入卡片具有一个 Default 属性,该属性通常设置为窗体的 ThisItem 记录字段。Each input card has a Default property, which is usually set to the field of the form's ThisItem record. 然后,输入卡片中的控件将从 Default 取输入值。The controls within the input card will then take their input values from Default. 通常不需要修改此属性。Normally you do not need to modify this.
  • 每个输入卡片公开一个 Update 属性。Each input card exposes an Update property. 此属性将用户的输入映射到记录的特定字段,以便写回到数据源。This property maps the user's input to a specific field of the record for writing back to the data source. 通常不需要修改此属性。Normally you do not need to modify this.
  • 用户可以使用屏幕上的按钮或图像控件来保存对记录所做的更改。A button or an image control on the screen enables the user to save changes to the record. 控件的 OnSelect 公式调用 SubmitForm 函数来执行此操作。The OnSelect formula of the control calls the SubmitForm function to do this work. SubmitForm 读取卡片的所有 Update 属性,并使用此信息写回到数据源。SubmitForm reads all the Update properties of the cards and uses this to write back to the data source.
  • 有时会出现问题。Sometimes there will be issues. 网络连接可能会出现故障,或者服务会执行应用所不知道的验证检查。A network connection may be down, or a validation check is made by the service that the app didn't know about. 窗体控件的 ErrorErrorKind 属性可提供此信息,让你向用户显示。The Error and ErrorKind properties of the form control makes this information available, so you can display it to the user.

若要对整个过程进行更精细的控制,还可以使用 PatchErrors 函数。For more fine grained control over the process, you can also use the Patch and Errors function. 编辑窗体控件公开一个 Updates 属性,用于读取窗体中字段的值。The Edit form control exposes an Updates property so that you can read the values of the fields within the form. 还可以使用此属性对连接调用自定义连接器,从而完全绕过 PatchSubmitForm 函数。You can also use this property to call a custom connector on a connection, completely bypassing the Patch and SubmitForm functions.

验证Validation

在对记录进行更改之前,应用应该尽力确保该项更改被接受。Before making a change to a record, the app should do what it can to make sure the change will be acceptable. 原因有两点:There are two reasons for this:

  • 可立即向用户反馈Immediate feedback to the user. 解决问题的最佳时机就是在发生问题的那一刻,用户还没有忘记前因时。The best time to fix a problem is right when it happens, when it is fresh in the user's mind. 例如,当用户每次触屏或击键时,出现一段红色文本来提醒用户输入有问题。Literally with each touch or keystroke, red text can appear that identifies an issue with their entry.
  • 降低网络流量和用户延迟Less network traffic and less user latency. 在应用中检测到的问题越多,就意味着可用于通过网络检测和解决问题的对话更少。More issues detected in the app means fewer conversations over the network to detect and resolve issues. 每次对话都需要占用一定的时间,而用户必须等待这段时间才能继续操作。Each conversation takes time during which the user must wait before they can move on.

PowerApps 提供两个验证工具:PowerApps offers two tools for validation:

  • 数据源可以提供有关哪些内容有效、哪些内容无效的信息。The data source can provide information about what is and isn't valid. 例如,数字可能包括最小值和最大值,并且需要提供一个或多个输入项。For example, numbers can have minimum and maximum values, and one or more entries can be required. 可以使用 DataSourceInfo 函数访问此信息。You can access this information with the DataSourceInfo function.
  • Validate 函数使用相同的信息来检查单个列或整条记录的值。The Validate function uses this same information to check the value of a single column or of an entire record.

错误处理Error handling

很好,我们已验证了记录。Great, you've validated your record. 接下来介绍如何使用 Patch 更新该记录!Time to update that record with Patch!

啊,但还有一个问题。But, alas, there may still be a problem. 应用可能会遇到网络故障、服务验证失败、用户没有适当的权限,或者其他一些可能的错误。The network is down, validation at the service failed, or the user doesn't have the right permissions, just to name a few of the possible errors your app may encounter. 它需要对错误状况做出适当的响应,向用户提供反馈,并让他们通过某种方式纠正问题。It needs to respond appropriately to error situations, providing feedback to the user and a means for them to make it right.

如果数据源出错,应用会自动记录错误信息,并通过 Errors 函数显示这些信息。When errors occur with a data source, your app automatically records the error information and makes it available through the Errors function. 错误与出现问题的记录相关联。Errors are associated with the records that had the problems. 如果该问题可由用户解决(例如验证问题),则用户可以重新提交记录,这样即可清除错误。If the problem is something the user can fix, such as a validation problem, they can resubmit the record, and the errors will be cleared.

如果使用 PatchCollect 创建记录时出错,则没有任何错误与任何记录相关联。If an error occurs when a record is created with Patch or Collect, there is no record to associate any errors with. 在此情况下,Patch 将返回 blank ,可将此值用作 Errors 的记录参数。In this case, blank will be returned by Patch and can be used as the record argument to Errors. 使用以下操作可清除创建错误。Creation errors are cleared with the next operation.

Errors 函数返回错误信息表。The Errors function returns a table of error information. 如果错误归因于特定的列,则此信息可能包括列信息。This information can include the column information, if the error can be attributed to a particular column. 可以在编辑屏幕上与列所在位置靠近的标签控件中使用列级错误消息。Use column-level error messages in label controls that are close to where the column is located on the edit screen. 如果错误表中的 空白 ,则可以在靠近整个记录的“保存”按钮的位置使用记录级错误消息。Use record-level error messages where the Column in the error table is blank, in a location close to the Save button for the entire record.

使用大型数据源Working with large data sources

从大型数据源(可能包括数百万条记录)创建报告时,最好是尽量减少网络流量。When you are creating reports from large data sources (perhaps millions of records), you want to minimize network traffic. 假设你要报告纽约市 StatusCode 为“Platinum”的所有客户,Let's say you want to report on all Customers having a StatusCode of "Platinum" in New York City. 而“客户”表包含数百万条记录。And that your Customers table contains millions of records.

想要将数百万条客户记录导入应用,然后选择所需的记录。You do not want to bring those millions of Customers into your app, and then choose the ones you want. 你希望在表所存储到的云服务中完成这种选择,然后通过网络发送选择的记录。What you want is to have that choosing happen inside the cloud service where your table is stored, and only send the chosen records over the network.

可用于选择记录的许多(但并非全部)函数可以 委派 ,这意味着,它们将在云服务中运行。Many, but not all, functions that you can use to choose records can be delegated, which means that they are run inside the cloud service. 若要了解如何执行此操作,请参阅委派You can learn how to do this by reading about Delegation.

集合Collections

集合是一种特殊的数据源。Collections are a special kind of data source. 它们位于应用本地,而不是基于与云中服务建立的连接,因此,无法在同一用户的设备之间或者在不同的用户之间共享信息。They're local to the app and not backed by a connection to a service in the cloud, so the information can not be shared across devices for the same user or between users. 它们的工作方式与其他任何数据源类似,但有几项差别:They operate like any other data source, with a few exceptions:

  • 可以使用 Collect 函数动态创建集合。Collections can be created dynamically with the Collect function. 它们不需要提前建立,而基于连接的数据源则需要提前建立。They don't need to be established ahead of time, as connection-based data sources do.
  • 随时可以使用 Collect 函数修改集合的列。The columns of a collection can be modified at any time using the Collect function.
  • 集合允许重复记录。Collections allow duplicate records. 一个集合中可以存在同一记录的多个副本。More than one copy of the same record can exist in a collection. 除非提供了 All 参数,否则 Remove 等函数将针对它们找到的第一个匹配项运行。Functions such as Remove will operate on the first match they find, unless the All argument is supplied.
  • 可以使用 SaveDataLoadData 函数来保存和重新加载集合的副本。You can use the SaveData and LoadData functions to save and reload a copy of the collection. 信息存储在其他用户、应用或设备无法访问的专用位置。The information is stored in a private location that other users, apps, or devices can't access.
  • 可以使用导出导入控件将集合的副本保存和重新加载到用户可交互的文件中。You can use the Export and Import controls to save and reload a copy of the collection to a file that the user can interact with.

有关使用集合作为数据源的详细信息,请参阅创建和更新集合For more information on working with a collection as a data source, see create and update a collection.

集合通常用于保存应用的全局状态。Collections are commonly used to hold global state for the app. 请参阅使用变量,了解可用于管理状态的选项。See working with variables for the options available for managing state.