PowerApps gallery two lists

Power Apps & SharePoint List Relationships

• Posted by - Matthew Devaney
• on - February 7, 2021
• 14 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 havent yet built an app this way yet you must give it a try. Ill 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.

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.

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]
  
  

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 vehicles YearMakeModel information once clicked.

Put this code in the Text property of the Title label.

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.

These fields are for display purposes only. We dont want the employee to edit them. To prevent this set the DisplayMode property of the text inputs to:

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.

Then set the Default property of the Start Date and End Date date pickers to 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.

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.

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.

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.

We will need to change some code in the date pickers to show the selected reservations values.

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

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

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.