Defining data structures

To support our help desk solution, we are going to use related tables as our data source. We will design a base column template to keep track of all the information needed. You can customize it to your own needs.

Explanation and overview

The following tables will make up our solution's data structure with the main focus on the Ticket table; this will be the one from which all other tables will have their relationship. Let's start with this one and then move on to the rest.

Ticket

This table will hold the base information of the help desk ticket, and it's going to be the primary object of our data model. The column structure is as follows:

• Title [Text]
• Description [Text Area]
• Ticket Status [Choice]
• Priority [Choice]
• Customer validation [Choice]
• Resolution [Text Area]

Ticket operation

Related to the Ticket table, this will hold the specific operations executed to solve the ticket. The structure is as follows:

• Title [Text]
• Description [Text Area]
• Operation Status [Choice]
• Duration [Duration]

Project

All tickets are going to be associated with a customer's project. The structure is as follows:

• Title [Text]
• Description [Text Area]
• Start [Date Only]
• End [Date Only]

Account

We will take advantage of one of the default business tables that comes with Dataverse when a new database gets created. This table holds our customers' information.

How to do it…

1. In the Tables section, select New Table from the top toolbar. On the panel that opens, fill in the required information to create the structure.

   For the table section, set Display name and the plural form, such as Ticket and Tickets. For the Name field, you can put the same as the display name; the system will automatically generate a prefix to help make your name unique.

2. Primary Name Column is the main identifier in this table. For the Ticket table, input Title. Choose something that your users can also use to select the rows when the system is listing them.
3. Depending on the needs of your table, you might want to Enable attachments. For example, it might be useful in the Ticket table to include extra information about the incident that started the ticket, such as screenshots or PDF files.
4. Once we have entered all the required information, you can click on Done. The system will then start creating the table with the specified primary name column and the rest of the business-oriented columns.

   The following is an example of the Ticket table:

   

   Figure 2.5 – Ticket table base structure

5. Now we can start shaping the table by adding the rest of the required columns. Click on Add column, and in the Display name field, input Description, and for the Name field, change it back to lowercase as description. Set Data type to Text Area and click on Done.
6. Repeat step 5 to add the Resolution column.
7. Click on Add column again to include the Ticket Status column. For Data type, select Choice. Selecting this will display a new dropdown to choose from the existing choice columns. Choose + New choice from this dropdown, and in the Items section, remove the existing one and add these: New, Pending, Resolved, and Closed. Finally, click on Save and then Done.
8. Repeat step 7 for the Ticket Priority column, adding the following items: Low, Medium, and High.
9. Again, repeat step 7 for the Ticket Validation column, adding Phone and Email for the items.

   Here's an example of the Ticket Status column settings:

   

   Figure 2.6 – Status column sample

10. When you complete the creation of the columns, click on Save Table.
11. Click on Tables on the left pane and then on New Table to add the Ticket operation table. For Primary Name Column, use Title. Remember to change the Name fields to lowercase, and then click Done.
12. When the table completes the provisioning, click on Add column to add the Description column using Text Area as its Data type value, and then click Done.
13. Click on Add column again to add the Operation Status column. For Data type, select Choice, and from the dropdown, choose the one we created in step 7, Ticket Status, and then click Done.
14. Again, click on Add column to create the last column on this table, Duration. For Data type, select Duration and click Done, and then click on Save Table.
15. Go back to the tables list by click on Tables on the left pane, and then click on New Table to add the Project table. Use Title for Primary Name Column and set the Name fields to lowercase. Click Done.
16. Once the provisioning completes, click on Add column to add the Description column using Text Area as its Data type value, and then click Done.
17. Click on Add column to create the Start column. Set Data type to Date Only and then click Done. Repeat this step to make the End column.
18. Click Save Table to complete the configuration of the Project table.
19. Let's configure the relationship between tables to maintain data integrity in our solution. From the Tables list, select to open the Ticket table, and then click on the Relationships tab. In the toolbar, click on Add relationship and then choose One-to-many. This kind of relationship roughly means that one element from one side (Ticket) can have many child items on the other (Ticket Operations).
20. When the panel opens, select Ticket Operation from the Related (Many) list. This action will create a lookup column to connect both tables. You can configure more settings in the advanced section regarding which action gets taken when a record gets deleted. To keep data integrity, you might want to avoid a ticket deletion without removing the ticket operations first. In this case, we are going to restrict deletions. When finished, click on Done and then Save Table. For more information on relationships, please refer to https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-entity-lookup#add-advanced-relationship-behavior
21. Repeat steps 8 and 9 to configure the One-to-many relationship between Project (one) and Ticket (many), and finally between Account (one) and Project (many):

Figure 2.7 – Relationship example

How it works…

We now have the data structure and relationships in place. To start entering data in our tables, we need to complete the required modifications in the next recipe's data forms, Building the model-driven app. However, the Account table comes by default in the system, so everything is already configured to enter data.

From the Tables list, select to open the Account table, and then select the Data tab. In the toolbar, click on Add record. This action will open up a new browser tab with a form to load data. Fill in all the desired columns and then click Save & Close from the toolbar if you want to add only one account, or click Save and then New to add more records.