Create Dataverse tables

  • 10 minutes

By default, all full Dataverse environments have a set of standard tables that you can use to build business applications. It's a best practice to use those existing tables to build solutions. Sometimes, your app may require you to create custom tables to store the data. For this module's scenario, none of the existing tables have the metadata to support your app. So, we create some custom tables to hold our data.

Scan these sections for an introduction to the tables that you'll create in this module.

Note

If you haven't completed the previous modules within this learning path, download the packaging files. These files contain the completed work on the Accident Tracking app thus far.

EmployeeTable

You'll use the EmployeeTable to track details about employees of your company. For simplicity in this scenario, you create only the following columns to track the employees:

  • EmployeeId - Autonumber column to identify the employee number. It's common to identify employees by using a number ID because it's easier to find or filter by a number rather than text. In this case, having a number ID also helps identify employees who, in some cases, have the same name (last name and first name).

  • EmployeeName - Single line of text column. In this scenario, you use one column to store the employee's full name. Most databases use separate columns to store the first, middle, and last names. For simplicity, you use only one column.

  • EmployeeDOB - Date-only column to track the date of birth of the employees.

  • EmployeeEmail - Single line of text column in Email format to track the email address of each employee. You can use this column later to send emails to employees when an accident has been logged automatically.

  • EmployeePicture - Image column type, which you use to store employee pictures and display the images in your app.

LocationTable

You'll use the LocationTable to track details about the different work locations for your company. For simplicity purposes, you only track a few details about your work locations:

  • LocationId - Autonumber column to identify the work location. As with the EmployeeId field, it's a good practice to track locations by a number identifier. Some organizations prefer to use location numbers because it's more efficient to identify and is commonly used in accounting systems.

  • LocationName - Single line of text column to track the actual name of the work location.

  • LocationCountry - Choice column. For this scenario, you use a custom choice column to track the country/region locations. Choice columns are excellent for limiting data entry errors, which helps make the task of entering data easier for users, and it's a good practice for data consistency.

  • TypeofAccidentID - Use this table to track information about the types of accidents that usually occur at the workplace. For example, you add a column in this table to rank an accident from one to five to define the severity of the accident. You can use this column later for more reporting based on the number of accidents by severity ranking.

TypeOfAccidentTable

The TypeOfAccidentTable provides quick categorization of the accident. The advantage of using a separate table is that it can be added to without modifying the main AccidentTable. Again, we only have a few fields:

  • TypeofAccidentId - Autonumber column to identify the type of accident. Similar to the other IDs, it's common practice to have numbers identifying data.

  • AccidentName - Single line of text column to describe the type of accident.

  • AccidentSeverity - A simple Choice column allowing the user to pick a number between one to five, one being of minor severity and five being a catastrophic accident.

AccidentTable

The AccidentTable is the main table that stores the details about all logged accidents. Consider this table as the one that holds details about each occurrence. Tables like this are commonly known as transactional tables because they contain details about transactions. Here are the fields we include:

  • AccidentId - Autonumber column to identify the specific accident.

  • LocationId - Lookup column that links the main table back to your LocationTable. Table relationships are discussed later in this module.

  • AccidentTypeId - Lookup column that links to TypeofAccidentTable.

  • AccidentDescription - Single line of text column that allows users to enter details about the accident.

  • AccidentDate - Date and time column that allows users to enter the exact time in which the accident took place.

  • ManagerComments - Single line of text column where managers can enter their comments about the accident.

  • ManagerReviewed - Choice column that is yes/no so that managers can denote which cases have been reviewed by management.

Create a custom table

Follow these steps to create the EmployeeTable:

  1. Go to make.powerapps.com and sign in with your Microsoft credentials, as required.

  2. In the left navigation pane, select Tables, and then select the option to Start with a blank table.

    The Tables screen showing the list of Dataverse tables at the bottom and the Start with a blank table button highlighted.

  3. On your screen, you'll see the Create new tables table designer. This editor lets us design some of our non-complex columns before Dataverse actually saves and adds this custom table to our list of tables.

    The top part of the screen has a box representing the table, and the bottom half of the screen shows an editable grid with the columns of your draft table. Double click on the name field of Table1 and rename it: EmployeeTable.

    Now let's look at some of the properties that Dataverse defines for you. Select the ellipsis in the top right of the new table display these properties.

    • The Plural name field auto-populates, so we leave that one as-is.
    • The Description field is editable, and you can add a description if desired.
    • The Primary column is the column that will show in any related tables that lookup to this one, in this case it's called New column, but we'll modify that shortly.
    • The Schema name is the name that Dataverse assigns this table inside of your environment, you will have a unique prefix for yours (the shaded gray portion).

    Screenshot of the Create new tables designer with the EmployeeTables header and the ellipsis highlighted, and the Edit table properties menu showing the properties mentioned above.

  4. We can modify the table's Primary column by selecting our New column in the editable table grid in the lower half of the screen. Select the column header, and then select Edit column. Change the Display name to EmployeeId and select Update.

    The Edit column properties popup for the New column, highlighting EmployeeId as the Display name and the Update button.

Create custom table columns

Before saving the table and having Dataverse actually create it for you, you can add some of the columns that you will use to store your employee data. You can do so by using the data section in the lower half of your screen.

  1. Select the + New column button and add the following column:

    • Display name - EmployeeName

    • Data type - Single line of text (default)

    • Required - Toggle it to on (you want to prevent users from entering an employee record without a name)

    Screenshot of the Employee Name menu with the Display name, Data type, and Required fields highighted.

  2. Select Save.

    We'll repeat the same process to create the remaining columns that you need for the EmployeeTable, including EmployeeDOB, EmployeeEmail, and EmployeePicture.

  3. Create the EmployeeDOB column:

    • Display name - EmployeeDOB

    • Data type - Date and time

    • Format - Date only

    • Required - toggle on (we want to prevent users from entering an employee record without a date of birth)

    • Save.

  4. Create the EmployeeEmail column:

    • Display name - EmployeeEmail

    • Data type - Single line of text (default)

    • Format - Email (a Dataverse data type that prevents users from entering data that's not formatted in proper email format)

    • Required - toggle on (we want an email address for the employee)

    • Save.

  5. At this point, we've created the basic columns for our table. To add the EmployeePicture column and to adjust our table's EmployeeId column, we first need to Save and Exit this editor. Save and Exit will actually create our table in Dataverse and allow us to continue on.

    After a few moments, you'll return to your list of tables. Find and select your EmployeeTable to continue. Your next step will be to set the EmployeeId field as an autonumber type.

  6. In the Tables viewer for your new EmployeeTable, find and select the EmployeeId field in the columns and data section in the lower part of the screen. Then select Edit column from the dropdown to bring up the Edit column panel on the right of the screen.

    EmployeeTable columns and data with the EmployeeId column header and Edit column options highlighted.

  7. Under Data type select the dropdown and then select Autonumber. Leave the defaults as they are. Your EmployeeId column settings should resemble this image:

    Screenshot of the Edit column panel with Data type as Autonumber.

  8. Select Save.

  9. Create the EmployeePicture column by selecting + New > Column from the command bar, and then enter the following properties.

    • Display name - EmployeePicture

    • Data type - File > Image

    • Primary image - select the checkbox (this image is used as the main employee picture) and then select Save.

    Screenshot of the Employee Picture columns with the Display name and Data type fields highlighted and the Primary image checkbox selected.

The first table is complete, let's continue to the next table.