Did you know that you can include Multiple Data Sources into the same form in your Power App? This scenario takes an existing Power App and adds additional data sources to it, specifically to use with dropdowns inside of a Form control to replace simple text input fields. You can use a technique like this if you want some consistent and/or sortable input data saved to your data source.
But remember, you can have many different data sources in a single app, the nuance here is that we’re going to use those data sources inside of our Edit Form.
In the video, which originally aired Feb 23, 2018, Shane Young demonstrates how to use multiple data sources with a Power App form. The goal is to create a Power App that connects to both an Excel file and a SharePoint list, providing users with drop-down menus and allowing them to add new records and edit existing ones.
In the app, we will have drop-down menus for the "Department" and "Favorite Color" fields. The "Department" drop-down is populated from an Excel file, while the "Favorite Color" drop-down is fed from a SharePoint list.
From our existing App, we need to add data sources. As an update to what is in the video, you can now select the Add data option from the command bar (or the Data tab from the side menu), then you can enter “excel” in the search field, and then select the Excel Online (Business) connector. Next you would “Choose a Location” where your Excel spreadsheet resides. Then find the document itself, select the table from the Excel spreadsheet, and finally connect it to your app. Doing this adds a new data connection for the Excel file.
In a similar manner, we can add another data connection for the SharePoint list. Then the app has access to both data sources.
Next, Shane focuses on the "Department" drop-down. As it is, it displays data from the existing data source, which is not what we want. So, we unlock the card, delete the existing control, and add a drop-down control in its place. Shane configures the drop-down to use the "Departments" table from the Excel data source. After setting it up, he runs the app to check the drop-down functionality, which works as expected, showing the different departments from his spreadsheet table.
Inserting a new control into the form card causes some warnings on the screen, caused by some data card properties. Any properties of the form card referring to the deleted control will need to be updated to refer to the new control. The card’s Update property has to be changed to reflect the dropdown control such as:
controlName.Selected.Value
This ensures that a text value is passed back to the data source for the form. Once the card is wired to the dropdown control, the last fix is to update the Default value of the new control which needs to be based on whether it's a new form or an edit form. He does this in the Power fx entry field by using this formula:
If(EditForm1.Mode = FormMode.Edit, Parent.Default, “ “)
Next, Shane proceeds to add the "Favorite Color" drop-down. He unlocks the card, removes the default value, and inserts a new drop-down control. Shane sets the "Items" property to the "Colors" table from the SharePoint list data source. He also fixes a warning by specifying the correct column in his SharePoint list and uses a SortByColumns function in the Items formula to sort the colors alphabetically.
Additionally, Shane addresses another issue by refreshing the data source, since the dropdown was showing some duplicates from the previous SharePoint list version. After refreshing the data source, the duplicates disappeared. It’s a reminder that when you make any changes to your data while editing your app, you need to refresh your data source to see your data source changes! The modern experience differs from the video, in that now you select the Data tab from the left-side rail and then the … (more actions) to the right of the SharePoint list and Refresh. That’ll do the trick.
Finally, Shane ensures the default value for the "Favorite Color" drop-down is set correctly based on whether it's a new form or an edit form, with a similar formula, however, he uses an underscore “_” which is an item in his “Favorite Color” column.
To test the app, Shane verifies that the "Department" drop-down is working correctly and displays the appropriate value for each record. He also tests the "Favorite Color" drop-down by creating a new record and verifying that it allows users to choose from a list of colors.
In conclusion, Shane successfully demonstrates how to use multiple data sources with a Power App form. By connecting to an Excel file and a SharePoint list, users can access data from different sources and interact with the app seamlessly. The app provides drop-down menus for data entry, allowing users to select from various options, and enables them to add new records and edit existing ones with ease. The bonus tip provided additional insights and troubleshooting techniques for a more efficient app-building experience.
