Understand data sources for canvas apps

In Power Apps, most canvas apps use external information stored in cloud services called Data Sources. A common example is a table in an Excel file stored in OneDrive. 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's 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.

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. You can use connections to data sources to read and write data in Microsoft Excel workbooks, lists created using Microsoft Lists, SharePoint libraries, SQL tables, and many other formats, which can be stored in cloud services such as OneDrive, DropBox, and SQL Server.

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

With Gallery, Display form, and Edit form controls, you can create an app that reads and writes data from a data source. To get started, see Understand data forms.

When you ask Power Apps 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 type of data source is the Collection, which is local to the app and not backed by a connection to a service in the cloud. Therefore, the information can't be shared across devices for the same user or between users. Collections can be loaded and saved locally.

Kinds of tables

Tables that are internal to an app are fixed values, just as a number or a string is a value. Internal tables aren't stored anywhere, but 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. Power Apps provides "connections" to read and write stored data. Within a connection, you can access multiple tables of information. You can select which tables to use in your app, and each becomes a separate data source.

To learn more, see Working with tables about internal tables, but also external tables residing in a cloud service.

Working with tables

You can use table data sources the same way that you use an internal Power Apps 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.

  Athugasemd

  For SharePoint and Excel data sources that contain column names with spaces, Power Apps replaces the spaces with "_x0020_". For example, "Column Name" in SharePoint or Excel appears as "Column_x0020_Name" in Power Apps 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.

  • Records can be created with the Patch and Collect functions.
  • Records can be modified with the Patch, Update, and UpdateIf functions.
  • Records can be removed with the Remove and RemoveIf functions.
  • Errors when working with a data source are available through the Errors function.

• The DataSourceInfo, Defaults, and Validate functions provide information about the data source that you can use to optimize the user experience.

Creating data sources

Power Apps 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.

Display one or more records

This diagram shows the flow of information when an app reads the information in a data source:

• The information is stored and shared through a storage service (in this case, Microsoft Lists or SharePoint Online).
• A connection makes this information available to the app. The connection takes care of authentication of the user to access the information.
• 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. You 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. You wire controls within the gallery, to the gallery, using the controls' Default property.
• The data source is also a table. So you can use Filter, Sort, AddColumns, and other functions to refine and augment the data source before using it as a whole. You can also use the Lookup, First, Last, and other functions to work with individual records.

Modify a record

The arrows in the previous diagram 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.

To modify an existing record of a data source, the record must have originally come from the data source. The record might display in a gallery, a context variable, and any number of formulas, but its origin should be traceable back to the data source. Extra information travels with the record that uniquely identifies it, ensuring that you modify the correct record.

This diagram 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. The DataSource and Item properties are used to identify the record to edit.
• Each input card has a Default property, which is set to the field of the form's ThisItem record. The controls within the input card take their input values from Default. Normally, you don't need to modify this default.
• 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 don't need to modify this property.
• A button or an image control on the screen enables the user to save changes to the record. The OnSelect formula of the control calls the SubmitForm function to do this work. SubmitForm reads all the Update properties of the cards and writes back to the data source.
• Sometimes there are issues. A network connection might be down, or a validation check is made by the service that the app didn't know about. The Error and ErrorKind properties of the form control makes this information available, so you can display it to the user.

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.

Validation

Before you make a change to a record, the app should do what it can to make sure the change is acceptable. There are two reasons for checking:

• Immediate feedback to the user. The best time to fix a problem is right when it happens, when it's 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 mean 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.

Power Apps 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. You can access this information with the DataSourceInfo function.
• The Validate function uses this same information to check the value of a single column or of an entire record.

Error handling

Now that you validated your record, update that record with Patch.

But, there might still be a problem. The network is down, validation of the service failed, or the user doesn't have the right permissions, just to name a few of the possible errors your app might encounter. Your app 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 are cleared.

If an error occurs when a record is created with Patch or Collect, there's no record to associate any errors with. In this case, blank is 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.

Working with large data sources

When you create 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. Your Customers table contains millions of records.

You don't want to bring those millions of customers into your app, and then choose the ones you want. The choice should happen inside the cloud service where your table is stored, where you 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're run inside the cloud service. Learn more in Delegation.

Collections

Collections are a special type of data source. They're local to the app and not backed by a connection to a service in the cloud. Therefore, the information can't be shared across devices for the same user or between users.

Collections operate like any other data source, with a few exceptions:

• 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.
• 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. Functions such as Remove operate on the first match they find, unless the All argument is supplied.
• 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.