Power Apps & SharePoint List Relationships

Power Apps & SharePoint List Relationships



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.

IDYearMakeModelAssetCodeLicensePlateOffice
12020 Dodge Ram10-023Y2K 9D9Albany
22016 Ford F15010-034Q9T 9T5Albany
32019 GMC Sierra10-122A7I 0Z5Fargo
42020 Honda Ridgeline10-021J9B 1P8Schaumberg
52021 Nissan Pathfinder10-301Q7K 7L7Schaumberg



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)

IDVehicleIDEmployeeStartDateEndDate
13Mark Clark1/25/20211/29/20201
22Anna Sinclair1/25/20211/27/2021
34Laura Andrews1/27/20211/29/2021
41Sarah Green1/25/20211/25/2021
51John Freeman1/26/20211/27/2021
63Laura Andrews2/1/20212/5/2021
73Mark Clark2/10/20212/10/2021
85Anna Sinclair2/13/20212/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



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



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


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)



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



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,
         
       // other fields
       Employee: txt_Employee.Text,
       StartDate: dte_StartDate.SelectedDate,
       EndDate: dte_EndDate.SelectedDate
    }
);
// reset controls
Reset(txt_Employee);
Reset(dte_StartDate);
Reset(dte_EndDate);


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')



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



Use this code in the Default property of the Employee text field…

gal_CurrentBookings.Selected.Employee



…and put this code in the DefaultDate property of the Start Time and End Time date pickers respectively.

Coalesce(gal_CurrentBookings.Selected.StartDate, Today())
Coalesce(gal_CurrentBookings.Selected.EndDate, Today())



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,
 
       // other fields
       Employee: txt_Employee.Text,
       StartDate: dte_StartDate.SelectedDate,
       EndDate: dte_EndDate.SelectedDate
    }
);
// reset controls
Reset(txt_Employee);
Reset(dte_StartDate);
Reset(dte_EndDate);
Reset(gal_CurrentBookings); //<-- new code




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)
)



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



it will be removed from the app and from SharePoint.





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.

Matthew Devaney

Subscribe
Notify of
guest

31 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Carl Williams
3 years ago

Haha, that’s really slick Matthew! Thanks for sharing.

Harjit Samra
3 years ago

Well explained Sir!

Teresa Agustin
Teresa Agustin
3 years ago

Very nice! Thank you for posting this article Matthew. The parent/child relationship is a very helpful concept to learn.

Harjit Samra
3 years ago

When I create new lists on my SharePoint I don’t get an ID column, I get a Title column. Is this a SharePoint site/list setting?

Charls Rosillo
Charls Rosillo
3 years ago
Reply to  Harjit Samra

The ID column is hide. Follow this in sharepoint… Select title, after columns configurations, then you can choose change name or show/ hide columns

Harjit Samra
3 years ago
Reply to  Harjit Samra

I had to follow instructions from here (https://www.c-sharpcorner.com/article/how-to-remove-title-as-mandatory-field-from-sharepoint-list/)

Looks like a weird “feature” of SharePoint.

Harjit Samra
3 years ago

I think there’s a missing section here – to add the below code to the Default of txt_Employee
Coalesce(gal_CurrentBookings.Selected.Employee, Blank())

Vivek
Vivek
3 years ago

Hi Matt,

Nice Article.

Can you please help me with the point “Reservation Form” ?

I’m unable to get it to work , would appreciate if you could elaborate that point a little more

Vivek
Vivek
3 years ago

Hi Matt,

Thank you for replying.

I got it to work finally , the issue was that I was not pasting the formula to the correct location i.e. the text property in our scenario 🙂

Jennifer Fry
Jennifer Fry
2 years ago

Thank you so much for creating this exercise. Is there a way to add multiple reservations at once? Maybe it would work as a collection? For example, on the Dodge Ram, Sarah wants it this week, John wants it next week, and Ann wants it next month. Can I do these all in one transaction without having to Submit between each one?

Sibusiso Cebisa
Sibusiso Cebisa
2 years ago

Hi there, Thank you for sharing this useful information, As you have create this so my issue now is that how to get the form for approval maybe by manager after you submitted with the notification, once approved how to create another form for doing the pre-check inspection and post check inspection like damage, miles and ect of the vehicle, secondly, is it possible to also add the status of each vehicle like if is available or inuse, or went for service. finally when the process is done meaning the driver has return the car how to submit and get all the record stored in one place. thank you

Sibusiso Cebisa
Sibusiso Cebisa
2 years ago

Yes i understand but for me seems to be more interesting and i real wish to use it .
Can you help here , where the gallery gal_CurrentBookings is created because
i just saw it from this code Coalesce(gal_CurrentBookings.Selected.StartDate, Today())

Ciro
Ciro
1 year ago

As a column with UniqueKey, is it possible to use other SharePoint column than ID ?

Ciro
Ciro
1 year ago

Matthew, didn’t work for me.

I have two lists:

Master (columns: ID, RecId*, Title) * my unique id column
Detail (columns: ID, Master, Title).

I manually created records in MASTER and DETAIL and deleted them to test if the relationship was working. Everything went as planned. Records in DETAIL were automatically excluded after deletions in MASTER.

But this code didn’t work in my test (notified as: error when use Patch function):

Patch(Master; Defaults(Master); {RecId:150; Title:”Jonas”});;

Patch(Detail; Defaults(Detail); 
  {  Title: “bla bla bla”; 
    Master: {Id:150; Value:”Jonas”}
  }
);;

Any clue to fix this ?

Happy new year !!!

Ciro
Ciro
1 year ago

Network error when using Patch function.
Field ‘Master_x003a_x0020_Title’ is mandatory.

Ciro
Ciro
1 year ago

Matthew, Master_x003a_x0020_Title was provided:

Patch(Detail; Defaults(Detail); 
  {  Title: “bla bla bla”; 
    Master: {Id:150; Value:”Jonas”}
  }
);;

Ciro
Ciro
1 year ago

Matthew,
The error message was due to an extra column from column Master in the Detail list.
I removed this column and now the following error message is appearing:
Network error when using patch function. The requested operation is invalid.

Any clue ?

Last edited 1 year ago by Ciro
CMolina
CMolina
1 year ago

Hello Mateo, excelent blog!!!

I have a question. If you want sort vehicle reservations by name of employee, what would you do?

I have a similar app and I’m trying use Sort + LookUp Formula but I have a warning delegation, I don’t understand why because both are delegable.

Any idea?

TechSavvy
TechSavvy
1 year ago

I would like to pull from one SP list which stores information that will pre-populate a SP form(read only) and then user adds status/comments/ratings.. read only data and new data is to be published to a new sp list. Any ideas would be appreciated.

Robert Orsino
Robert Orsino
1 year ago

Hey Matt,
Ok, In the Showing Related Reservations part, the 2 different Items are showing on one scrolling line. (screenshot attached). How do I get each record to display on another line?

RegIssue.png
Matthias Hufnagel
Matthias Hufnagel
24 days ago

Great work as always. Thanks so much for sharing your findings so brilliantly.
In addition, I would be interested how to filter the parent list by properies of the child list or even by the fact if a child for a given parent exists or not.
So, for example to filter the model gallery to the models that haven’t been reserved yet.