Exercise - Create two tables and relate them by using a one-to-many relationship

Completed

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.

  1. Sign in to the Power Apps Portal.

  2. Select the environment that you want to work within by using the drop-down list in the top menu bar.

  3. Select Dataverse from left-side navigation menu.

  4. Select Tables to see the tables list.

  5. Select + New table from the command bar at the top.

  6. 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.

    Screenshot of New Table dialog box with Cruises entered in the Display name box.

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

  8. In the Primary column under Display name,** enter Cruise ID.

  9. Press the Save button to create the new table. It may take a few moments for the table to be provisioned.

  10. 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.

    Screenshot of Cruises Tables screen with the create a new column button selected.

  11. 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:

    Screenshot of New column pane with Cruise date Date and Time and Date only highlighted.

  12. Select Save at the bottom of the pane. After a few moments, you'll now see that added to your data columns.

  13. 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.

    Screenshot of the Columns and data zoom in with the add a column + button selected.

  14. 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.

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

    Screenshot of Synch with global choice and Choices with Mudskipper entered under label and New choice highlighted.

  16. Select the + New choice button and enter two additional label names: Sunshine and Holiday. Select Save to record this column in your table.

  17. 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
  18. Now check your table, it should resemble this:

    Screenshot of Cruises columns and data showing the Name, Cruise date, Boat Name, Destination and Captain columns.

  19. In the Schema pane, select Keys.

    Screenshot of Cruises Table properties and Schema pane with Keys highlighted in the Schema pane.

  20. The Tables > Cruises > Keys screen will appear. Look for and select + New Key in the command bar.

  21. Enter CruiseIDKey as the Key Display name.

  22. Select Cruise ID under columns.

    Screenshot of Key table with cruise ID column selected.

  23. Select Save.

  24. 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.

    Screenshot of Tables > Cruises screen with list of columns in a table.

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.

  1. 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.

  2. Select the + New Table button on the menu at the top of the list of tables.

  3. 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.

  4. Enter Passenger ID in the Display name field.

  5. Select the Save button at the bottom left of the window and wait for a few moments while the table is provisioned.

  6. In your Tables > Passengers screen, look at the Passengers columns and data pane and select the Passenger ID column, then Edit column.

  7. Change the Data type to AutoNumber. Adjust the format and starting number as shown in the following screenshot.

    Screenshot of Passenger ID with Autonumber, Custom, SEQNUM:6 and 1 as values for the different fields.

  8. Select the Save button.

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

    Screenshot of Cruise ID Field dialog.

    After you've created all of the columns, your Passengers table should look like the following screenshot.

    Screenshot of Passenger table after columns entered.

  10. Select Keys from the Schema pane and then select + New Key.

  11. Enter PassengerKey in the Display name field, under Columns select Passenger ID, and then Save.

    Screenshot of Key dialog with Passenger ID column selected.

  12. 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.

    Screenshot of the Keys list with Passengers selected.

  13. 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.

    Screenshot of List of Relationships Many-To-One.

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.

  1. Select the word Tables in the screen title to view the list of all tables.

  2. Select the Cruises table.

  3. From the Schema pane, select Relationships and look for your Cruise ID relationship.

  4. Notice that your Cruise ID relationship also exists in the Cruises table, but it's a one-to-many relationship.

    Screenshot of List of Relationships One To Many.

Now you can use the tables and reference the relationship to build a booking app.