How to create a Parent / Child PowerApp with related SharePoint Lists
Dealing with Parent / Child data in PowerApps can be a little challenging. In this post, I will show you exactly how to render data from a master SharePoint List and display the related (child) records in PowerApps.
- Related lists Concepts, what they are and some examples.
- SharePoint Lookup Fields.
- SharePoint Data in PowerApps and how PowerApps sees it.
- Parent-Child relationship in PowerApps.
- DEMO: Expense Reports PowerApp
Related Lists Concepts
A lot of custom applications have been built over the years. Using Microsoft Access we could easily create relational data with a related form, then Infopath came along and we had repeating tables (but this made data hard to get to), and now PowerApps allow us to have relational data and a nice Parent-child UI to go with it, although it can be a little complicated to achieve.
Here are a few examples of what you might use relational data for.
Each Project has:
- Multiple Project Tasks
- Multiple Project Files
- Multiple Project Goals
Each Expense report has:
- Multiple Expense Costs
- Multiple Expense Approvers
- Multiple Expense Receipts
Each Company Location has:
- Multiple Location Employees
- Multiple Location Audits
- Multiple Location Sales
SharePoint Lookup Fields
Here is a visual example of an expense report showing the Parent-Child relationship. The example shows that each Expense report has multiple child items. This is achieved in the data source by adding a Lookup column to the child SharePoint list.
So let’s have a look at how we add lookup columns to provide this relational view of the data.
We can add a Lookup column to our child list. In the example above, the Expense Items list has the Parent ID field to link it to the Parent list of data, the Expense Report.
When you create a lookup field you can pick another SharePoint list that is on the same site to do the looking up from, and then pick which column of data from that Parent list you want to display in the child list. In this case we are bringing over the ‘Title’ which shows the title of the Expense report, however, you could bring other fields across as well if there is other useful information to see.
For End-users this field in the child list is going to appear as a drop-down box when they are entering data, so they can select which Expense Report this Expense item is to be allocated to.
That covers some real basics of how you might have data setup in SharePoint lists to provide related lists. Let’s now take a look at how that might be used in PowerApps.
Case Management PowerApp Example
The main PowerApp screen shows a list of cases (Parent data) :
Then each case you click on shows the data associated with that case. In this instance showing that the case has 2 sets of child data, it has multiple tasks and multiple files associated with the case (the child data):
Branch Offices PowerApp Example
The main PowerApp screen shows a list of Branch Offices (the Parent data):
Then each Branch you click on shows the data associated with that Branch. In this instance showing that the Branch has multiple audits (the child data):
Expense Report Example
The main PowerApp screen shows a list of Expense Reports (the Parent data):
Then each Expense Report you click on shows the data associated with that Expense Report, i.e. the related Expense items for the Report (the child data):
These real-world examples hopefully help you understand how relational data and relational SharePoint Lists can be used within a PowerApp. We will be looking into the Expense Report PowerApp in a little more detail later on in this post.
SharePoint Data in PowerApps
Complex Column types
There are a few types of fields that are considered more complex fields. Complex column types are:
- Managed Metadata.
Within PowerApps complex data is structured like a small table with one row in it.
Today we are mostly going to be covering Lookup fields, the way data is stored for a looked up value is:
- ID = The SharePoint ID of the item in the list.
- Value = The title of the item.
To automatically populate a complex field, a more advanced syntax is used.
Tip: If at all possible avoid using these types of fields in your App, it’s not always possible but it is much easier if you can.
SharePoint Lists in PowerApps
Here is an example of how PowerApps views a SharePoint list. Simple fields have visible data, complex fields have a clickable icon.
When you then click on one of the complex icons, a screen is presented with the structure of the Complex field. This example is a lookup field called ‘Parent_Expense’, showing the ID and Value:
So, if we refer back to where we added a lookup column in our child SharePoint list, this is the ‘Title’ field we added, and in PowerApps you can get to this data using the formula as shown below:
Getting the Parent ID within your PowerApp
The SharePoint item ID does not exist until the new item has been saved, therefore working with Parent and child in your PowerApp is easier on existing items, here are some examples:
We will look now at a solution that works with new items and existing items by using the power of having the parent record as a variable.
Parent Record as a Variable
You can set a whole record to be a Variable in PowerApps. This would mean the variable is set to be a whole row from a SharePoint list.
This is useful in many cases in an App e.g:
- OnClick in a gallery.
- Filtering child items in a “sub-list”.
- Having data about the last submitted form.
Parent-Child Relationships in PowerApps
Let’s now look into a simplified view of the PowerApp Parent-Child concepts. The example below shows a Parent Expense report with ID = 42.
The Child items each have that Parent ID lookup associated with them to show they all go together with that expense report.
This sub-list of items would be a Gallery in PowerApps and would be filtered so that the ID is equal to the Parent ID of the Parent Variable, meaning only related child items are shown.
Then if we are creating new items using a form, to enter new expense items, the Parent ID needs to have a default value set to the ID of the parent you have already selected or created in the PowerApp. This would be achieved by setting the control default as shown in the screenshot.
Before we look at building this solution in a very simplistic way in PowerApps, let’s review some of the Syntax we will need to use.
Creating a new Parent item
We will do this with the ‘OnSelect’ for the “New Expense Report” or ‘+’ Button in the App:
- This will create a new form to fill in.
- Clear out the Parent variable, in case it has any data already in it.
- Then trigger navigation, this is an optional step depending on how you have built your App and will not be needed if you created it all on one screen.
This creates a new form but does not help with filling out the form or saving the data, it just takes the user to the screen where the new form is.
Saving the New Item
Once we have filled in the details and click the ‘Next’ or ‘Submit’ button, whatever we have called it, we will need the following syntax:
- The ‘OnSelect’ will submit the form, trying to create the expense report.
- If it is successful, then the ‘OnSuccess’ property will trigger, which will set the variable to what we have just submitted, and also create a new child form after the Parent has been created. This means the child is only created after the Parent exists, otherwise, there would be no Parent ID to link to the Child.
- Then the ‘Item’ property for the Parent form control is set to the value of the variable we have just set, i.e. the details we just entered on the form.
Editing an Existing Parent Item
This syntax is for when we are selecting an existing Expense report in the App.
- Set the Parent Variable to be equal to this Parent record.
- Navigate to the expense report parent screen if it is on a separate screen.
That about covers all the different fundamentals of creating this kind of application with Parent-child relationship, so now we can take a look at building a sample PowerApp to get this working.
DEMO: Expense PowerApp
You will need two SharePoint lists for ‘Expense Report’ and ‘Expense items’ containing some data to be used in the PowerApp. For demonstration purposes, we are going to add all the controls onto one screen.
Step 1 – Firstly create a Canvas app from blank, using the tablet format, and give it a name.
Step 2 – Select the ‘View’ menu and ‘Data Sources’. Select ‘Add data source’ and add your SharePoint lists (‘Expense Report’ and Expense items’) as data sources.
Step 3 – Insert a Vertical Gallery control to hold all the expense reports.
Set the ‘Layout’ in the right-hand panel to be ‘Title and Subtitle’.
Set the ‘Items’ property of the Gallery to be the name of our Expense Report SharePoint list:
Name the Gallery – ‘galExpenseParentList’.
Add a label control to the top with the text ‘Expenses’ and then size and position both controls to look something like this:
Step 4 – Next from the ‘Insert’ menu select ‘Icons’ and add a ‘+’ above the Gallery control, which will be used to add new expense reports.
Step 5 – From the ‘Insert’ menu select ‘Forms’ and add an ‘Edit’ from. This is to add a form for new expense reports.
Position it to the right of the Gallery and set the ‘Data source’ also equal to ‘Expense Report’.
Then choose which fields from the data source to add to the form. For the purpose of demonstration we just have Title and Department fields.
Set the ‘Columns’ property from the right-hand panel to ‘2’ and position the form next to the Gallery.
Set the ‘Item’ Property of the form to equal ‘varParentItem’ variable. This will be the variable that is set to the Parent record we are creating or looking at.
Give the form a good name e.g. ‘frmExpenseParent’.
Step 6 – Add a button below the fields on the Form and set the text on the button to ‘Next’. This will be the save button for a new expense report. We should now have something looking like this:
Step 7 – Now we need to make the form show when the ‘+’ icon is clicked.
Select the ‘+’ icon and in the ‘OnSelect’ property we are going to add the following:
This is to create a new form and set the Parent variable to be blank.
NB: We will set the default visible statuses of our controls as the last step in building the PowerApp.
Step 8 – We will now update the ‘Next’ button to save a new Parent Expense Report, and allow the user to then enter the first child item for this parent.
Select the ‘Next’ button and set the ‘OnSelect’ property to:
Step 9 – We now need to update the ‘OnSuccess’ property of the Form to carry out the actions we want to happen after the Parent record has successfully been submitted.
To do this, select your form and set the ‘OnSuccess’ property to:
This is going to set our variable to be equal to the last record submitted, and also set the form to still be in edit mode so that we can add further items or edit our entry.
NB: This code will also add a new child item, but we need to set the child form up first so will come back to it later in the post.
Step 10 – To set up the child items we will need a Gallery to show all the Expense sub-items for each Expense Report, as well as a form to add each Expense into the child list.
Firstly add a ‘Blank vertical’ Gallery control, and set the ‘Items’ property (the data source) to be our ‘Expense Items’ list, or whatever the name of your equivalent child SharePoint list is.
Set the Layout in the right-hand panel to be ‘Title and Subtitle’.
Set the Gallery name to ‘galExpenseChildren’.
NB: At the moment this is not filtered and will show all items in the list, we will filter it to only show items related to the Parent in later steps.
Next, add another Edit Form, and set the ‘DataSource’ property of this form to also be the ‘Expense Items’ list.
As we did with our other form, you will also need to click ‘Edit fields’ in the right-hand panel and choose which fields you want to show on your form, for this example we are adding ‘Amount’, ‘Category’ and ‘Date’.
The other field we will need to add is the Parent lookup, the field that links the child to the Parent, so add the ‘Parent ID’ into the form too. We will need to set this fields’ default value to be that of the Parent ID that is in the Parent form. We will do this in later steps.
Set the Form name to ‘frmExpenseChild’.
Shuffle and size the items so it looks something like this:
NB: This is just for demonstrations purposes, you would likely want it to look a little smarter in a real PowerApp, and there is every chance you might want a different screen for each aspect.
Step 11 – Now we need to go back to our ‘OnSuccess’ property of the first form ‘frmExpenseParent’ to add the New form command so that a new child form is created when an Expense report is submitted successfully on the Parent form.
Add the New command as per the screenshot below:
Step 12 – Next we are going to set the default value of the ‘ParentID’ field in the child form, to be that of the ParentID that has just been submitted in the Parent form. This will create the Parent to Child link when a new record is created.
To do this, select the ‘Parent ID’ field on the child form, and on the right-hand panel click ‘Advanced’ and then click the padlock icon to unlock it. This will allow us to select the data card in the left-hand controls panel.
In the left-hand controls panel, select the ‘Parent ID_DataCard1’, choose the ‘Default’ property and update it as per the screenshot below:
This will now set the ID and Value of that field based on the data in our Parent variable ‘varParentItem’.
Step 13 – The second form now also needs a submit button.
From the ‘Insert’ menu add a button and place it underneath our child form. Set the text on the button to be ‘Save’.
Select the ‘OnSelect’ property as shown below:
This will submit the record to the Child list, again we want to do other things when that is done, but we don’t add them here, instead we add them to the ‘OnSuccess’ property of the Form.
Step 14 – Select the child form, and choose the ‘OnSuccess’ property, and set it as shown below:
This will mean that once the record is submitted successfully the form is reset and ready for any new records you want to add.
Step 15 – We now need to filter the Child item gallery so that it only shows the child items related to the current Parent Expense report record.
To do this select the Child gallery and select the ‘items’ property and update it as shown below:
This filters it to only show Child items that have the same Parent ID as the Parent ID in our variable ‘varParentItem’. Meaning that only the expense items associated with this Expense Report are shown.
Step 16 – When a user selects an existing Parent item from the Gallery, we need that to populate the variable ‘varParentItem‘.
To do this select the Parent gallery and go to the ‘OnSelect’ property, and set it as shown below:
Now is a good time to run your PowerApp, you should have a working App that looks something like this:
Try each of the main bits of functionality e.g.:
- Add a new Expense report and add child entries (expenses) to that report.
- Select existing Expense Reports and the associated expenses should show in the child gallery.
- The child form should have the Parent title defaulted in the Parent ID field, so when you do add a new record it is associated with the Parent Expense report that is selected or has just been added.
Step 17 – Now the only thing left to do is to ensure that controls are only shown when they are needed and not visible when they are not needed.
Let’s start with when we add a new Expense Report, i.e. when we click the ‘+’ icon. When this happens, we do not want the Child Gallery or Child Form to show yet, as we want to create the Parent record first.
We are going to use a variable to control this. To do this select the ‘+’ icon and choose the ‘OnSelect’ property. We already have some code in here, we are going to add the setting of the variable ‘varShowChild’ to false to the bottom of the formula we already have, as shown:
Then on the same child Gallery, select the ‘Visible’ property and set it to equal the variable ‘varShowChild’.
Similarly, for the Child form, and the Save button on the form, select the ‘Visible’ property and set them to equal the variable ‘varShowChild’.
This now means that on clicking the ‘+’ to add a new parent record, the child elements will NOT be visible.
Step 18 – Now we want those items to become visible whenever the ‘Next’ button is clicked and the Form submission is successful in the Parent form, meaning we have just created an Expense Report, and we are now ready to enter the Expense Items associated with it.
Select the Parent Form, choose the ‘OnSuccess’ property, and add the setting of the variable to the formula we already have in there, like below:
Step 19 – Set the Parent ID field on the Child Form to not be Visible.
You don’t have to do this, but there is no need for the user to see that Parent ID against every child record, so all you need to do is select the Parent ID field or the data card from the left panel. Select the ‘Visible’ property and set it to ‘False’.
The Value will still be passed to all the child forms, but it is just not showing to the user.
Run the App and give it a go!!
You should now have a working App, give it a test through and see if there are any improvements you might want to make. You could spend more time setting the visible and not visible elements to cater for every scenario, you could maybe add a Clear selection button to reset the screen, and you could make the application look more attractive, or even have Parent and Child elements show on different screens.
As you can see there are so many possibilities for layout and functionality within your PowerApp, this allows you to create some fantastic and functionally rich applications.
A great way to understand how your App is working in more detail, to help you either debug issues or just to help with understanding, is to run the application and at each step go back into the designer window and check the status of any variables being set or data being created.
To do this with this App, run the application and click the ‘+’ icon to add a new Parent. Then go back into the designer window and click on the ‘View’ menu and click variables:
You can see the status of the variables.
For the ‘varParentItem’ variable you can click the table icon and see more details of the variable and that at this time it is empty, this is because we have not entered details on our form and submitted the record yet.
You can also see that our ‘varShowChild’ variable is currently set to false, meaning the child gallery and child form are currently not showing, again correct for this stage.
This approach can be used for every step of the application, giving you a real in-depth view of how it all hangs together, and even help you understand how to improve or modify your PowerApp.