Ever since Microsoft announced that InfoPath would be going the way of the dinosaur, developers have been looking for alternatives to the program. With the introduction of PowerApps, users have found most of what they loved about InfoPath, plus some improvements. One of the most-used features in InfoPath was the ability to repeat tables, and happily, you can still do that in PowerApps.
What is a repeating table?
A repeating table, or sub-table, is a way to display data from two or more lists. Developers used them to display a many-to-one relationship, such as using a look-up column in a SharePoint list. The idea being you could select an item in a list A and use it to filter items in list B.
In PowerApps, by default, you’ll only be able to see data from the list the app is associated with when you create a new PowerApp. A Gallery is PowerApps’ word for connections to external sources, whether it be SQL, SharePoint, Twitter, etc. Like everything else in PowerApps, the screen is completely customizable via drag-and-drop or Excel-like formulas.
In this scenario, we’ll be using two lists: one for projects and one for expenses. Our project list is a master list of all large projects occurring in the business, and our expenses list is where we add each expense and income. To make life easier and to view the health of the business, we’ve opted to track all our expenses in a master list, and tag each expense with the project it belongs to. Using our PowerApps, we’ll be able to filter on each project and sub-project.
Once you’ve got your blank PowerApp, you can add a new Gallery to it. For the purposes of this example, we’ve used a Blank Screen, then added a Blank Vertical template to it.
From here, we can set our data value. For this exercise, we’re going to use our master project list first.
Once we have our project in place, we can select the type of data we want to view. For the purposes of this exercise, we’ve chosen to view simply the title of the items.
Next, we want to repeat the same action to add a second gallery, this one will show our ledger and items that we want to filter.
This is a good point to rename both Galleries from the standard Gallery1 and Gallery2 to something more meaningful. For the purposes of this exercise, we’ll rename them to ProjectsView and LedgerView.
From here, we want to set the PowerApp to filter on a specific item when it’s selected. On our SharePoint list, the column called Subproject is a lookup column on the Ledger list that displays the Title column in the Project list. In layman’s terms, we’re managing our projects in the projects list, and using it as a drop-down menu.
But because of the way SharePoint reads look-up columns, we won’t be able to use the value. Instead, we’ll have to pull the ID from the project list and add the .ID modifier. Let’s look at it in practice:
First, select the Ledger View gallery to bring up the Item function in the top header:
We’ll want to use the following syntax:
Filter([List name to be filtered],[Column Name].Id=[Gallery Name].Selected.ID)
[List to be filtered] = This is the actual list of items to be filtered (in our case, Ledger), NOT the Gallery
[Column name] = The column we’re applying the filter on (in our case, Subprojects)
[Gallery name] = The gallery where the selected filter will come from, so in this case, Projects View (note: not the gallery the filter is on)
Since 2006, our specialized Microsoft Consulting teams have been delivering incredible solutions for clients all over North America and the world. Get in touchwith us today and let's talk about what it's like to have ESW on your team.