In this post, you’ll learn how you can translate the concept of sub-tables, or repeating tables, or sub-grids, to PowerApps. When you’re creating your PowerApps from within SharePoint, by default the app only uses the data from the one list that you are in. You can add what are called Galleries, to display and interact with data from somewhere else. It can be another SharePoint list, a SQL table, a twitter feed, a list of Salesforce opportunities, etc. You can read more about PowerApps at powerapps.microsoft.com.
In this very simple example, I have a SharePoint list of customers, and a SharePoint list of projects. The projects list has a lookup field to customers, therefore there is a many-to-one relationship. For each customer, there can be multiple projects. This example will be just one screen of a PowerApp. Notice that I haven’t done anything fancy at all with colors or buttons, since the main focus is the functionality of galleries.
When you click to select a customer on the left, it shows the details about that customer in the middle, and that customer’s projects in a list on the right. These lists on the screen are called galleries. Here’s how to insert a gallery:
- On your screen, click the Insert tab, and choose Gallery. For mine, I chose the vertical text gallery.
- Once your gallery is on the page, it will have a default set of test information called TextualGallerySample. Look at the top left where you see the name of the gallery, such as “Gallery2”. Click directly on the name to change it. Rename it to something more meaningful. I named mine CustomerGallery and ProjectGallery. Notice here that the drop-down box(Property) says Items, and the fx (function) says TextualGallerySample.
- Instead of TextualGallerySample, type the name of your data source, like mine is “Projects” since it is a SharePoint list called Projects. Just type the name in the function box. Notice that the data in the gallery changes immediately.
- Notice that when you have the gallery selected, the Gallery tab shows several configuration options for the gallery. For example, change the transition to Pop, and that affects the way the gallery items behave when you hover over each of them.
- Select the first item in the gallery. When you do that, it allows you to edit the way the gallery items appear. This is completely free-form, so you can change colors, rearrange, add shapes and buttons, etc. Also, look at the panel on the right. Notice that one of your column names may be listed in there multiple times. Select each field in the gallery, and then from the drop-down box, choose which field from your data source to map it to. Also, they may not show in the same order in the panel as they show on the form. Notice that my Title field is in there twice and then Hours Planned, a different order than on the form. If I want the third box to show some other field in the gallery, I can either change the drop-down box, or completely delete the field from the gallery. You can also select that whole first item in your gallery, and that will let you change the size of it, which will automatically change all of the gallery items at once.
- The next major concept is filtering the projects gallery to only show the projects that are related to the selected customer. Here’s a chart containing a list of some ways you can filter and sort a gallery. This is still using my concept of customers and projects as an example, specifically the gallery of projects. This command is what you enter into the “Items” property seen in the screenshot at step 2.
|Projects||Items from my data source called Projects|
|Filter(Projects, CustomerLookup.Id=CustomerGallery.Selected.ID)||Projects filtered to show only the ones related to the customer that I clicked on the gallery of customers.|
|Search(Projects, TextSearchBox.Text, “Title“)||Shows projects filtered by what I typed in the TextSearchBox on the screen. (This is a default box that exists in PowerApps that are created directly from your SharePoint list.)|
|SortByColumns(Projects, “Title”, If(SortDescending, SortOrder.Descending, SortOrder.Ascending))||Sort the list of projects by title, as the SortDescending button is toggled, ascending to descending. (This is a default button that exists in PowerApps that are created directly from your SharePoint list.)|
|SortByColumns(Search(Filter(Projects,CustomerLookup.Id=CustomerGallery.Selected.ID), TextSearchBox1.Text, “Title”), “Title”, If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))||Put these all together!|
This is an example screenshot of the items that show at the top of the default gallery in a PowerApp created by clicking the PowerApps –> Create an App button at the top of a list in SharePoint””:
On my SharePoint Power Hour on 12/21/2016, I will do a demonstration of this whole concept, and will go in depth as far as some nuances here and there as you’re working in PowerApps, specifically galleries.
Would you like to learn PowerApps from the beginning? Check out my PowerApps training classes at iwmentor.com