Exercise - Create two tables and relate them by using a one-to-many relationship
In this exercise, you'll create the tables needed for the Anchors Away Cruise Line to book charter cruises. You'll create two tables and relate them to one another by using a one-to-many relationship.
Sign in to the Power Apps Portal.
Select the environment that you want to work within by using the drop-down list in the top menu bar.
Select Dataverse from left-side navigation menu.
Select Tables to see the tables list.
Select + New table from the command bar at the top.
In the appearing New table pane, enter Cruises as the Display name. You'll notice that the Plural name field auto-populates, but if not, enter Cruises there, as well.
The Properties has an underline under it, that means you are on the Properties tab for the new table. Let's now define a primary key column for your table. Select Primary column to go to the Primary column tab (you'll see that Primary column now has the underline).
In the Primary column under Display name,** enter Cruise ID.
Press the Save button to create the new table. It may take a few moments for the table to be provisioned.
Now we can begin creating columns for your new Cruises table. Select the Create a new column button from the Cruises columns and data pane.
In the New column pane on the right of your screen, enter Cruise Date in the Display name box, select Date and time > Date only so that it resembles this screenshot:
Select Save at the bottom of the pane. After a few moments, you'll now see that added to your data columns.
To add another column to your data, let's select the + button to the right of the +19 more button in your Cruises columns and data pane.
In the New column popup pane on the right, enter Boat Name as the Display name, under Data type select Choice>Choice, and under Sync with global choice? select No.
Under Choices, in the box under Label enter Mudskipper (don't worry about the Value column; Power Apps automatically assigns a number here that isn't necessary to change).
Select the + New choice button and enter two additional label names: Sunshine and Holiday. Select Save to record this column in your table.
After a few moments, you'll see your Boat Name column has been added to your columns and data. On your own, add the following two Choice (under Data type) columns by selecting the + button on the Cruises columns and data pane:
Display name Choices Destination Catalina, Santa Cruz, Santa Rosa Captain Renny, Blye, Jones Now check your table, it should resemble this:
In the Schema pane, select Keys.
The Tables > Cruises > Keys screen will appear. Look for and select + New Key in the command bar.
Enter CruiseIDKey as the Key Display name.
Select Cruise ID under columns.
Select Save.
Let's take a look at all of the columns in your new table by selecting Columns from the Schema Pane. You should see Cruise ID noted as the Primary name column and the other columns you've created.
Congratulations, you just made the parent table for the cruise booking app. Now you'll make the child table called "Passengers". Each cruise will have many passengers, so the Cruise table is the parent and the Passenger table is the child.
Let's navigate to the Tables list screen. Select the word Tables at the top of the screen where it says Tables > Cruises > Columns, as shown in the previous screenshot. This will take you back to your list of tables.
Select the + New Table button on the menu at the top of the list of tables.
In the New table window under the Properties tab, enter the name Passengers in the Display name field and then select the Primary column tab.
Enter Passenger ID in the Display name field.
Select the Save button at the bottom left of the window and wait for a few moments while the table is provisioned.
In your Tables > Passengers screen, look at the Passengers columns and data pane and select the Passenger ID column, then Edit column.
Change the Data type to AutoNumber. Adjust the format and starting number as shown in the following screenshot.
Select the Save button.
Add the following columns to the Passenger table, again by selecting the + button just to the right of the +18 more button under Passengers columns and data.
Field Name Type Cruise ID Look Up - select “Cruises” in the Related table field, as shown in the following image Passenger Name Single line of Text Type of ID Choice - Select No under Sync with global choice? and enter the following choices: Drivers License, Passport, Student ID, Other (The lookup pane of the Cruise ID column is shown in the following figure.)
After you've created all of the columns, your Passengers table should look like the following screenshot.
Select Keys from the Schema pane and then select + New Key.
Enter PassengerKey in the Display name field, under Columns select Passenger ID, and then Save.
Your new key should now be displayed in your Keys list. Return to the Passengers table by selecting Passengers in the Tables > Passengers > Keys screen title.
In the Schema pane, select Relationships. In the displayed list look for the Cruise ID column where you created a many-to-one relationship that you created in the Passengers table by using the CruiseID lookup.
Now that you have a many-to-one relationship connecting your Passengers list to your Cruises list, you can look at the Cruises table and confirm a one-to-many relationship.
Select the word Tables in the screen title to view the list of all tables.
Select the Cruises table.
From the Schema pane, select Relationships and look for your Cruise ID relationship.
Notice that your Cruise ID relationship also exists in the Cruises table, but it's a one-to-many relationship.
Now you can use the tables and reference the relationship to build a booking app.