Most PowerApps apps use external information stored in cloud services called Data Sources. 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. This article describes how the Patch, DataSourceInfo, Validate, and Errors functions provide more control.
Data sources can be connected to a cloud service, or they can be local to an app.
The most common data sources are tables, which you can use to retrieve and store information. 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.
Data sources other than tables include email, calendars, Twitter, and notifications, but this article doesn't discuss these other kinds of data sources.
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.
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 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.
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.
Note: For SharePoint and Excel data sources that contain column names with spaces, PowerApps will replace the spaces with "_x0020_". 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. 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.
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. 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.
The diagram above shows the flow of information when an app reads the information in a data source:
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:
For more fine grained control over the process, you can also use the Patch and Errors function. The Edit form control exposes an Updates property so that you can read the values of the fields within the form. You can also use this property to call a custom connector on a connection, completely bypassing the Patch and SubmitForm functions.
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:
PowerApps offers two tools for validation:
Great, you've validated your record. 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.
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.
If an error occurs when a record is created with Patch or Collect, there is no record to associate any errors with. 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.
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.
When you are creating reports from large data sources (perhaps millions of records), you want to minimize network traffic. 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 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:
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.