Practical example – developing the data model

In this section, we will walk through a practical example of developing the data model for an incident management feature. By examining this real-life scenario, you’ll gain insights into the process of designing and structuring your data tables effectively. Sarah did consider preparing an entity-relationship (ER) diagram, but her customers didn’t want to pay for this as part of the design; they said they would rather see the pages and that she could show them tables of data represented in a view. Sarah did some background reading on ER diagrams before this.

Further reading on ER diagrams

Go to https://learn.microsoft.com/en-us/power-apps/developer/data-platform/use-metadata-generate-entity-diagrams to learn more about ER diagrams.

Creating tables in Dataverse

To start implementing the data model, Sarah must create three tables: Incident, Incident Type, and Injury Type. These three tables must be implemented to capture and organize the relevant data in our incident management feature. Each table serves a specific purpose: the Incident table stores the incident details, the Incident Type table categorizes the types of incidents, and the Injury Type table captures specific injury classifications. By implementing these tables, Sarah can create a structured data model that facilitates incident management and reporting.

It’s recommended to begin with the Incident Type and Injury Type tables as they serve as reference tables or lists. Since these tables have a simpler structure and no additional fields, ownership can be set to the organization, making the table more efficient without ownership fields.

When creating a table in Dataverse, the system automatically generates a Name field, which serves as the primary field and is displayed when interacting with the list. This primary field is sufficient for a basic list view, eliminating the need for additional fields.

Let’s dive into the step-by-step process of creating these tables, defining their fields, and establishing the necessary views within Dataverse. This foundation will lay the groundwork for efficient data management and information organization in Sarah’s incident management feature.

Type tables

To help you understand how to create a table, we will illustrate creating the Incident Type table:

1. Open Power Pages Design Studio and edit the site.
2. Select Home, then the Solution tab.
3. Open your solution – for example, Buildapp, which Sarah created in the previous chapter.
4. Select + New | Table | Table, as shown in Figure 4.2:

Figure 4.2 – Creating a new table in a solution

Figure 4.2 illustrates how Sarah would create a new table within a solution. We have opened a solution from within Power Pages Design Studio and added a new table. Sarah had to create two new tables for Incident Type and Injury Type reference data.

Tip

Working within a solution helps you organize your work better; you can have a solution for each project or feature. You can also add new or existing tables and other objects to work within your solution.

For efficient use of data storage, it is good practice to configure a table that stores reference data records. You can do this by setting Record ownership to Organization with no additional options enabled, as shown here:

Figure 4.3 – Configuring a new reference data table for Incident Type

Having created a reference data table called Incident Type, repeat this process to create the Injury Type table. You can do this by browsing to the Buildapp solution, selecting a new table, and entering Injury Type as the table name while providing a similar configuration that you did for Incident Type.

The Incident table

Having created the two reference tables, Sarah can now create the Incident table. For the Incident table, Sarah wants to add the configuration options shown in the following screenshot. Here, Sarah enables attachments by checking the Enable attachments (including notes and files) box and selects User or team under Record ownership. Enabling attachments will allow notes and attachments such as images and files to be added to the incident record. Implementing user ownership will future-proof this table in case Sarah wants to assign user ownership to records at the Dataverse level:

Figure 4.4 – Creating the Incident table

Sarah can also enable activities, as shown in the following screenshot, to enable timeline features such as portal comments, emails, and tasks. This means that emails and tasks can be related to an incident record:

Figure 4.5 – Enabling attachments

Now that Sarah has created the necessary tables (Incident, Incident Type, and Injury Type, in the next section, Sarah will work to establish relationships between them to enhance the data model’s functionality.

Creating table relationships in Dataverse

Table relationships allow us to link related data across different tables, providing a more comprehensive view of the incident management feature. In the following steps, Sarah will explore how to create these table relationships and leverage the lookup fields to establish seamless connections between the Incident table and the reference data tables.

By adding lookup fields in the Incident table that go to the Incident Type and Injury Type reference tables, we can create associations between records. This “many-to-one” relationship enables us to categorize incidents based on their types and link specific injuries to each incident. These relationships enhance data integrity, querying capabilities, and reporting functionalities within the application.

Creating lookups to reference data tables

In this section, Sarah will learn how to create lookup columns on a table, such as Incident, which, in turn, will create relationships between the Incident Type and Injury Type tables in Dataverse. On the Incident table, Sarah will create a lookup field to the Incident Type table, which creates a many-to-one relationship from the Incident table to the Incident Type table. A lookup column provides a reference to the Incident Type table.

How to create a lookup column

In the Buildapp solution, browse to the Incident table and add a new column by clicking on + New column. Call this new column Incident Type:

Figure 4.6 – Adding a new column

To complete the new column configuration, follow these steps:

1. In Power Pages Design Studio, select the solution you wish to edit.
2. Browse to the Tables tab and select Incident.
3. Select + New column, as shown in the preceding screenshot.
4. Set Display name to Incident Type.
5. Set Data type to Lookup.
6. Set Related table to Incident Type:

Figure 4.7 – Configuring the Incident Type lookup

The new column configuration window for the Incident Type lookup is shown in the preceding screenshot. It’s important to follow a consistent naming pattern that makes sense. Sarah will use these names later when selecting relationships while setting table permissions and setting related data in basic forms. In the original Microsoft Dynamics editor, the lookup names would default with a suffix of id. It is recommended to follow the widely held practice of renaming your schema name so that it’s in lowercase – for example, incidenttypeid.

It is also good practice to rename the relationship name so that it’s simpler and easier to read – for example, incident_incidentypeid. Lastly, adding a description to the column by using the Description field can be useful when you want tooltips to appear. Therefore, it is recommended to add meaningful, informative descriptions.

Due to Sarah creating the lookup field, Dataverse will automatically create a many-to-one relationship because it’s a lookup column, as shown in Figure 4.8:

Figure 4.8 – Many-to-one relationship created automatically for Incident Type

Having created a lookup column for Incident Type, Sarah wants to repeat this process for the Injury Type lookup. Sarah needs to create these lookup-type columns on the Incident table so that she has the necessary table relationships for classification, filtering, and reporting.

Creating contact lookups on the Incident table

Sarah also wants to create two lookups on the Incident table from the Contact table: an Employee column and an Originator column. These create a many-to-one relationship. Both contact fields will identify people based on an incident.

Employee lookup

In our app, employees are recorded as contact records, so Sarah can add a lookup column based on the incident to the existing Contact table and call the Employee lookup column. This is useful for employee write-up incidents. Set this to Business recommended so that it can be configured as required on a web page:

Figure 4.9 – Employee lookup configuration

Originator lookup

Sarah also wants to record the originator – that is, the person who raised and created the incident record on the web page. The originator is the logged-in contact record, so Sarah wants to add a lookup field from the Incident table to the existing Contact table and call the Originator lookup column. This is used by table permissions to give the creator delete permission and also to record who authored the incident. In the next chapter, Sarah will configure this field so that it’s automatically filled in when the insert record is saved:

Figure 4.10 – Originator lookup configuration

In the next section, Sarah will show you how to implement many-to-many relationships and why they are used.

Many-to-many (N:N) relationships for people contacts

Sarah needs subgrid lists of contacts in the incidents form. These subgrids are designed with many-to-many relationships between the Contact table and the Incident table. Sarah uses many-to-many relationships so that a contact can appear on many different incident records. This enables users to add and remove contacts to/from any incident. Sarah will create three many-to-many relationships for contact subgrids on an incident for witnesses, people notified, and people involved. To create a many-to-many relationship, browse to the Incident table’s Relationships tab and choose a new Many-to-many relationship, as shown here:

Figure 4.11 – Creating a new many-to-many relationship on the Incident table

Configure the many-to-many relationship for witnesses on the Incident table:

1. In Power Pages Design Studio, select the solution you wish to edit.
2. Browse to the Tables tab and select Incident.
3. Select the Relationships tab, as shown in the preceding screenshot.
4. Select + New relationship, then + Many-to-many.
5. Select the Contact table for the Related table.
6. Set Relationship name to incident_witness_contact.
7. Set Relationship table name to incident_witness_contact.

When Sarah selects a new many-to-many relationship, the following configuration form opens, showing the current table – that is, Incident. To create a relationship with the existing Contact table, set the related table to Contact and enter a relationship name. Sarah has set this to incident_witness_contact:

Figure 4.12 – Witnesses many-to-many relationships

Repeat this for the other two relationships to create three many-to-many relationships to enable subgrids on the incident forms.

Create the following three many-to-many relationships. These will be used later in this chapter when we configure the different forms:

1. Witnesses
2. People Involved
3. People Notified

This will result in the following many-to-many relationships being available on the Incident table:

Figure 4.13 – Many-to-many table relationships on the Incident table

Having created the related lookup columns for the many-to-one table relationships and completed the many-to-many table relationships, Sarah will create the remaining columns to be used by our incident management feature.

Creating table columns

Now, Sarah will create the other columns that will used in the feature. She will specify types from the planned data model as she won’t be able to change the type once the column is created. These columns will be placed on the forms on the web pages.

Tip

To further understand column types, read the following documentation from Microsoft Learning: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-edit-field-portal#column-data-types.

Sarah should have a meaningful data collection process in place for the incident on the form. Let’s look at some typical columns that should be used on the incident form. She will use these to capture and report on the different incidents.

Columns can be created by following the process described in the How to create a lookup column section. Browse to the solution and add a new column under the Incident table. The schema name is automatically set when the field name is entered. These schema names can be overwritten, just like Sarah did with the imc_managingaccountid column, as shown here:

Table 4.2 – Table of incident columns to add

Setting the date of the incident column

The Date type defaults to the user locale. This is usually preferred as that way, it’s always displayed in the user-preferred time format and according to the user’s locale. Sarah has selected the date and time format as she needs to know the exact time of an incident, especially if this could help form notes for legal action.

Tip

Further reading on the DateTime data type can be found at https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-edit-field-portal#date-time.

Global choices

We need lists as dropdowns on web pages. In the preceding table, we have two columns that must be created as choice columns: Occurrence and Type. In Dataverse, these are called choice types; Sarah has the option of creating a global or local table choice. When creating choice fields, it is often best to select global choice so that the choice can be used in other tables as it could well be used in other tables.

Create a new column for Occurrence and select a data type of your choice. Once you’ve done this, the choice configuration shown in the following screenshot will appear. Sarah hasn’t added a default choice for Occurrence, which means that the field will be blank on page load and the user will need to select a value from the drop-down choices field. In the choice configuration form, Sarah can create a global choice by selecting Yes for Sync with global choice?, as shown in Figure 4.15:

Figure 4.14 – Global choice configuration for the Occurrence column

To modify or add new choice values, Sarah must click Edit choice, which opens the following configuration form. In these choice fields, she sets Display name to Incident Occurrence:

Figure 4.15 – Occurrence choice values

Note that Sarah leaves the Choices values as the defaults that Dataverse generates, as illustrated in the preceding screenshot. This is fine to do.

The Form type choice

The Form type choice here is for an Incident type of Employee write up, which is a common incident type within the construction industry. Sarah wants this choice of form type so that she can redirect the user to the correct incident form. Sarah plans to create two different forms, one for Incident and one for Employee write up, which we will see her implement later in this book. These two form types will be used to filter records, organize our data, and filter reports.

Follow these steps to create the Form type choice field:

1. In Power Pages Design Studio, select the solution you wish to edit.
2. Browse to the Tables tab and select Incident.
3. Select the + New column.
4. Set Display name to Form Type.
5. Set Data type to Choice.
6. Set Required to Business required.
7. Create a global choice named Incident Construction choice.
8. Select Yes for Sync with Global choice? regarding Incident Construction choice.
9. For global choice, select Edit choice to manage the actual choices. Add the Incident and Employee Write-Up options, as shown in Figure 4.18.
10. Select Incident as the default value of the Form Type choice:

Figure 4.16 – The Form Type column’s configuration with Incident as the default value

Steps 8 and 9 create a global choice as Sarah creates the Form Type choice column in the Incident table. Once she’s created the global choice, Sarah can add the choice options shown in the following screenshot. Once these choice values have been added and saved, Sarah can select Incident as the default value:

Figure 4.17 – Form Type column choice values

Multi-line text fields

Many of the text fields to be created on the Incident table have been designed to be in multi-line text format, which means that they aren’t single lines but blocks of text in paragraphs on a web page. The Description column is an example of this. When selecting multi-line text fields, Sarah must specify the length in characters. In this case, Sarah has selected 2,000 characters, which is the default setting. Note that the larger the value, the greater the amount of database storage that’s allocated to the table. This affects the cost of the systems as Microsoft charges storage by allocated space, regardless of whether the rows contain data or not. Please note that you can adjust the size of these fields by either reducing or increasing them.

The Status Reason field

When creating a new table, the system creates some system fields, including a Status Reason field. This special field can be used to organize and group records in a view. It’s common to use the Status Reason field to trigger automations. Sarah will need to view separate views for open incidents, completed incidents, and canceled incidents.

Note

Note that on Power Pages, the Status Reason field cannot be displayed on a form, and therefore users can’t set it on a web page. However, you can set the values in a process or Power Automate flow.

Later in this book, we will see Sarah implement a cancellation process that will set Status Reason to Cancelled. So, Sarah needs a Cancelled option. To do this, Sarah can browse to the Status Reason field, edit it, and add a new option called Cancelled.

A system-generated value is provided under Inactive for Status Reason. Sarah will relabel this inactive option Completed, as shown here:

Figure 4.18 – Inactive values for Completed and Cancelled

With that, Sarah has set both Completed and Cancelled to Inactive. She’s done this because the Dataverse system will lock records and prevent them from being writeable once Status Reason is set to an inactive state. Sarah must do this to ensure changes aren’t made to a completed record.

Sarah will also relabel Active to Open as it’s an open incident. She’s done that so that she has a view of open incidents:

Figure 4.19 – Active values

So far, we’ve learned how to plan the development of a feature, as well as how to create tables and create relationships between these tables. Then, we learned how to implement fields, as well as how to decide on what data types and formats to use when creating fields. Having created and implemented the configuration for the various incident columns, in the next section, we will see Sarah configure existing views and create new views as needed in her app.

Views

Views are defined lists of ordered columns that are configured via sorting and filtering. Earlier, Sarah modified Status Reason to enable filtered views of incidents. Now, we will see how Sarah configures these views. When creating tables, the system generates views. It is important to modify these existing views and create new ones.

Further reading

You can read more about views in the Microsoft Learning documentation: https://learn.microsoft.com/en-us/power-pages/configure/data-workspace-views#view-designer.

Active views are the default views and are created automatically when creating a new table. The Active Incidents view is the default view for incidents and is created alongside the Name and Created on columns. The Active Incidents view is created with a filter of status eq active. This filter will show any open incidents – that is, a list of records that have Status Reason set to Open. The idea here is to add more meaningful columns so that we can use this view in Power Pages. Let’s see how Sarah does this.

How to edit an existing view

To modify the view, Sarah must work on the Buildapp solution, which she will access from Power Pages Design Studio:

1. In Power Pages Design Studio, select the Buildapp solution.
2. Browse to the Tables tab and select Incident.
3. Select the Views tab of the Incident table.
4. Select the Active Incidents view, as shown in Figure 4.21.
5. Click on the ellipsis to edit the view in a new tab.
6. Opens the view designer, as shown in Figure 4.22. This is where Sarah will edit the view:

Figure 4.20 – Editing the Active Incidents view

For the most part, Sarah will always want to configure the default view so that it has more columns and sometimes change the configured sorting. Having gone to the Active Incidents view, she modifies this default view in the designer, where she can manage columns, add and remove columns, and modify sorting and column widths.

Modifying the Active Incidents view

To make this default view more useful and present an informative view relevant to incidents, Sarah will add four columns to the custom fields we created earlier in this chapter.

Sarah adds the following columns, which are highlighted in the rectangular box in Figure 4.22, by dragging and dropping the columns into the view:

1. Date of Incident
2. Form Type
3. Incident Type
4. Description:

Figure 4.21 – The Active Incidents view’s columns in the view designer

The system-generated view of active incidents will be sorted by name, but that’s not very useful in a default view. It’s common to have the default open incidents view sorted by Date of Incident, with the newest at the top. Sarah wants this view to only show incidents and not show employee write-ups, so she adds filter-by-form-type criteria, as shown in Figure 4.23. After making these changes, she saves and publishes her work:

Figure 4.22 – Active Incidents sorting and filtering configuration

Having modified the Active Incidents view so that it shows open records of form-type incidents, Sarah now wants a similar view for active employee write-up incidents.

The Active Employee write ups view

The best way to create a similar view is to open an existing view and save it as a new view. To do this, Sarah edits the Active Incidents view and saves it as Active Employee write ups. Then, she modifies Filter by … so that it includes Type is ‘Employee Write-Up’:

Figure 4.23 – The Active Employee write ups filter

Having completed the two open records for each form type of incident, in the next section, Sarah will create views for the Completed and Cancelled views.

The Completed and Cancelled views

Earlier in this chapter, we saw how Sarah modified Status Reason for the two inactive states of Completed and Cancelled. Here, we will see how Sarah creates and configures these views. As mentioned previously, it’s quicker to open an existing view and save it, though Sarah can also create a new view and configure its columns and filters.

The Completed views

As before, we will edit the Active Incidents view, save it as Completed Incidents, and apply the following configurations:

Figure 4.24 – Configuring the Completed Incidents view

Note that we set Filter by … to Status Reason is ‘Completed’ and Form Type is ‘Incident’. Sarah saves and publishes her work. Then, she saves this as Completed Employee write ups and configures the rest of the fields, just like she did for the Completed Incidents view:

Figure 4.25 – The Completed Employee write-ups view

The Cancelled views

To create the Cancelled Incidents view, Sarah opens the existing Completed Incidents view and saves it as Cancelled Incidents. After, she modifies the Status Reason filter and sets it to Cancelled. Then, she saves and publishes her work.

To create the Cancelled Employee write ups view, Sarah opens the existing Completed Employee write ups view, saves it as Cancelled Employee write ups, and then modifies the Status Reason filter and sets it to Cancelled. Then, she saves and publishes her work.

Having completed the incident views, Sarah needs to create views in the Contact table. These contact views will be used in the Incident table lookups on the web page forms to help users filter and find their contacts, thereby enhancing the user experience.

Contact table views

Sarah wants to create some custom views to organize her contacts into filtered lists, which help users select contacts from employees or customers or My Crew or vendors lists.

Creating Boolean columns to filter views

To have a new view filtered by a contact type, Sarah will need to create new columns to enable these views – columns consisting of a Boolean type of Yes/no field for each new view. Sarah uses a Boolean field to identify the type of contact as this allows us to have a contact with multiple types – for example, a contact could be My Crew as well as Employee. So, an employee who is a member of My Crew will have Is Employee set to Yes and Is My Crew set to Yes.

Sarah creates the following Yes/no columns:

1. Is Contractor
2. Is Customer
3. Is Employee
4. Is My Crew
5. Is Vendor:

Figure 4.26 – Yes/no columns to add to the Contact table

Next, Sarah must create corresponding views for each of these options to allow filtering to occur when contacts are being selected. Later in this book, Sarah will use these views on forms and pages.

She creates a new view, one for each new Boolean column. Then, she configures the filter for each view with the corresponding column equal to yes. So, the Customers view has a filter of Is customer equal yes.

A quick way of doing this is to open the Active Incidents view, save it as Customers, and then modify the filter. She repeats this process for each of the new views:

Figure 4.27 – These views have been configured with the Yes/no column

Now that Sarah has her contact views, she’s ready to dive into the Report view.

The Report view

Earlier, in our planning stage, we discussed list pages, which list incident records. Users use the list page to organize, filter, search, and access incident records. A useful pattern is a view with most or all columns available. This is called a Report view.

Sarah will create a Report view with nearly all the columns on the forms set in a meaningful order. Then, on a list page, she’ll implement the Download function, something she’ll implement in the next chapter. This pattern enables users to download incident data from the list page.

Now that Sarah has created a data model consisting of tables, columns, and views, she needs to design the forms that will be used on the Power Pages website.