Data sources are extensions of tables that can retrieve and store information. Connected data sources read and write data in Microsoft Excel workbooks, SharePoint lists, SQL Server tables, and many other services. Static data sources and collections are alternative data sources that can be loaded and saved locally.
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. When you ask PowerApps to create an app from data, these controls are used.
As we learned in Working with tables, tables in PowerApps are values, just as a number or a string is a value. Tables aren't stored anywhere. You can't directly modify the structure and data of a table, only derivative tables that you create through a formula.
Some of the most interesting tables are stored 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.
A data source is an extension of a table, and you can use it in any context that you use a table. Just like a table, each data source has records, columns, and properties that you can use in formulas. In addition:
PowerApps can't be used to create or modify a data source; the table must already exist in a service elsewhere. To create a table (for example, in an Excel workbook stored on OneDrive), you would use Excel Online on OneDrive first to create a workbook and then create a connection to it from your app. Collections can be created and modified in 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 last section, we showed you 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 API 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.
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.