Power Apps & SharePoint List Relationships

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-sharepoint-list-relationships/. Author: Matthew Devaney.

Power Apps & SharePoint List Relationships Posted by - Matthew Devaney on - February 7, 2021 31 Comments

Every app I build with SharePoint uses data from multiple related lists. There are just too many benefits to ignore: smaller storage size, improved data accuracy, and excellent flexibility when building new features and doing reporting. If you haven’t yet built an app this way yet you must give it a try. I’ll make a believer out of you!

In this article I will show you how to make Power Apps that take advantage of SharePoint list relationships. Table of Contents Introduction: Reserve A Vehicle App

SharePoint Lists & One-To-Many Relationships Select A Vehicle Reservation Form Showing Related Reservations Adding A New Reservation Edit An Existing Reservation Resetting The Form Deleting A Reservation Introduction: Reserve A Vehicle App The Reserve A Vehicle

App is used by employees of a company to book a truck for travel purposes. An employee opens the app, selects a vehicle from the list, chooses the reservation date range and submits the request. Existing reservations can be edited or deleted.

SharePoint Lists & One-To-Many Relationships

Create a SharePoint list called Company Vehicles with the columns shown below. Every column should have the type single line of text other than ID. The ID column is automatically included in every SharePoint list and the number is automatically assigned. You can find the list item ID numbers by clicking on the column options and choosing Show/Hide . ID YearMakeModel AssetCode LicensePlate Office 1 2020 Dodge Ram 10-023 Y2K 9D9 Albany 2 2016 Ford F150 10-034 Q9T 9T5 Albany 3 2019 GMC Sierra 10-122 A7I 0Z5 Fargo 4 2020 Honda Ridgeline 10-021 J9B 1P8 Schaumberg 5 2021 Nissan Pathfinder 10-301 Q7K 7L7 Schaumberg

Then create a 2nd SharePoint list called Vehicle Reservations with the following column types: VehicleID (Number) Employee (Single line of text) StartDate (Date) EndDate (Date) ID VehicleID Employee StartDate EndDate 1 3 Mark Clark 1/25/2021 1/29/20201 2 2 Anna Sinclair 1/25/2021 1/27/2021 3 4 Laura Andrews 1/27/2021 1/29/2021 4 1 Sarah Green 1/25/2021 1/25/2021 5 1 John Freeman 1/26/2021 1/27/2021 6 3 Laura Andrews 2/1/2021 2/5/2021 7 3 Mark Clark 2/10/2021 2/10/2021 8 5 Anna Sinclair 2/13/2021 2/14/2021

The Company Vehicles and the Vehicle Reservations lists have a one-to-many relationship. Every company vehicle has one or more reservations. Notice that the VehicleID column in the Vehicle Reservations list corresponds to an ID in the Company Vehicles list. This is how we will define the relationship between both tables. Select A Vehicle

An employee selects a vehicle from the list they would like to reserve.

Open Power Apps Studio and create a new tablet app from blank. Insert a vertical gallery on the screen and choose company vehicles as the datasource.

Change the gallery layout to Title and set the Title field to YearMakeModel .

Then reposition the right-chevron icon to the left-side of the label showing YearMakeModel and change the Icon property to Icon.Cars.

To complete the menu make a new label and place it above the gallery with the title “Choose A Vehicle.” Now the employee can select a truck. Reservation Form

After choosing a vehicle the employee fills-in their name and picks the reservation date range.

We will start by creating a label that will display the chosen vehicle’s YearMakeModel information once clicked.

Put this code in the Text property of the Title label. gal_ChooseAVehicle .Selected .YearMakeModel Code language: CSS ( css )

Next, add 3 sets of labels & text input fields: Asset Code, License Plate, Office .

Use this code in the Default property of the respective text inputs to show values for the selected vehicle. // Asset Code gal_ChooseAVehicle.Selected.AssetCode // License Plate

gal_ChooseAVehicle.Selected.LicensePlate // Office gal_ChooseAVehicle.Selected.Office Code language: JavaScript ( javascript )

These fields are for display purposes only. We don’t want the employee to edit them. To prevent this set the DisplayMode property of the text inputs to: DisplayMode .Disabled Code language: CSS ( css )

The next 3 fields will be fillable by the employee: Employee, Start Date, End Date.

Employee is a text input and Start Date/End Date is a date picker.

Set the Default property of the Employee text input to blank. Blank()

Then set the Default property of the Start Date and End Date date pickers to today. Today() Showing Related Reservations

With the vehicle reservation form now created we can move onto our first part of the app that makes use of the SharePoint list relationships: a gallery showing all related reservations for a vehicle.

Insert a blank gallery at the bottom of main canvas area with the datasource Vehicle Reservations .

Write this code in the Items property of the gallery to filter it by the currently selected vehicle.

Filter( 'Vehicle Reservations' , VehicleID = gal_ChooseAVehicle.Selected.ID) Code language: JavaScript ( javascript )

Put three labels in the gallery to show Employee, Start Date and End Date.

Use this code in the Text property of the respective labels to display values for the reservation. // Employee ThisItem.Employee // Start Date ThisItem.StartDate // End Date ThisItem.EndDate Code language: JavaScript ( javascript )

To complete the design place a label above the gallery with column names to act as the header.

The Book A Vehicle App is now showing related data from two different tables at once. Adding A New Reservation

An employee completes the reservation form then clicks submit to record the booking.

Insert a Send icon and a label with the text “Submit” onto the screen.

Use this code in the OnSelect property of both the icon and label to save the reservation data to SharePoint.

// create a new record Patch( 'Vehicle Reservations', Defaults('Vehicle Reservations'), { // relationship field VehicleID: gal_ChooseAVehicle.Selected.ID, < code > </ code > < code > // other fields </ code >

< code > Employee: txt_Employee.Text, </ code >

< code > StartDate: dte_StartDate.SelectedDate, </ code >

< code > EndDate: dte_EndDate.SelectedDate </ code > < code > } </ code >

); // reset controls Reset(txt_Employee); Reset(dte_StartDate); Reset(dte_EndDate); Code language: HTML, XML ( xml )

Test the form. When we click submit the reservation now shows in the current bookings gallery…

…and also appears in the SharePoint list. Notice how the is recorded as a number. Edit An Existing Reservation

Employee can edit an existing reservation to change the date.

Place a new Edit icon in the gallery. When the employee clicks the icon the row will become selected.

When the gallery is reset we do not want it to not have a row selected. Use this code in the Default property of the gallery. Defaults( 'Vehicle Reservations' ) Code language: JavaScript ( javascript )

We will need to change some code in the date pickers to show the selected reservation’s values.

Use this code in the Defaul t property of the Employee text field… gal_CurrentBookings .Selected .Employee Code language: CSS ( css )

…and put this code in the DefaultDate property of the Start Time and End Time date pickers respectively. Coalesce ( gal_CurrentBookings .Selected .StartDate , Today ()) Code language: CSS ( css ) Coalesce ( gal_CurrentBookings .Selected .EndDate , Today ()) Code language: CSS ( css )

We will also need to update the code in the OnSelect property of the submit icon and label to accommodate both new and edited records. // create a new record Patch( 'Vehicle Reservations' , /* new code block */ Coalesce( LookUp(

'Vehicle Reservations' , ID=gal_CurrentBookings.Selected.ID ), Defaults( 'Vehicle Reservations' ) ), /* end block */ { // relationship field

VehicleID : gal_ChooseAVehicle.Selected.ID, <code> // other fields</code>

< code > Employee: txt_Employee.Text, </ code >

< code > StartDate: dte_StartDate.SelectedDate, </ code >

< code > EndDate: dte_EndDate.SelectedDate </ code > < code > } </ code > ); // reset controls

Reset(txt_Employee); Reset(dte_StartDate); Reset(dte_EndDate); Reset(gal_CurrentBookings); //<-- new code Code language: JavaScript ( javascript )

Test the editing feature by clicking on the edit icon for a record, changing the date and clicking Submit.

The edited record will appear changed in both the app and SharePoint. Resetting The Form

If the employee has selected a record but decided they want to create a new record instead we need to give them the ability to do it. Fortunately, this is quite easy. Insert a new Add icon and label beside the submit icon.

Simply put this code in the OnSelect property of the New icon… Reset(gal_CurrentBookings)

…and when you click it the Employee and Start/End Date fields will revert to their default values. Deleting A Reservation

Finally, an employee can delete reservation by clicking the delete icon beside it. Put a Trash icon into the gallery…

…and write the follwong code in the OnSelect property . Remove(

'Vehicle Reservations' , LookUp( 'Vehicle Reservations' , ID=ThisItem.ID) ) Code language: JavaScript ( javascript )

Click delete on the record you created for the earlier testing.

it will be removed from the app and from SharePoint. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about Power Apps & SharePoint List Relationships please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. SharePoint Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps