This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Microsoft Dataverse provides a scalable and secure environment for storing data and is integrated with other Microsoft products and services, such as Azure Active Directory, Microsoft Power BI, and Microsoft Teams. With Dataverse, users can create and manage custom entities (tables) and fields to store their data, define relationships between entities, and create business rules and workflows to automate their business processes.
Dataverse also provides robust data integration capabilities, allowing users to connect to external data sources and perform ETL (Extract, Transform, Load) operations to import and export data from other systems. This makes it easy to combine data from multiple sources and build powerful applications that leverage that data.
Before creating a Table in Microsoft Dataverse ask yourself these questions.
Do I understand the user story or case study I'm working on and what columns and data type to use?
In this blog post, I will be discussing how to create a table in Microsoft Dataverse, what to understand before working on a case study that uses Microsoft Dataverse, where we will be seeing a lot of words like columns, rows, data types, relationship, Entity Relationship Diagram and more.
Let's work together on a case study to understand better.
The Call for Speakers Annual Tech Conference Event Overview
In this case study, we will be working on a Call for Speakers Annual Tech Conference Event.
To create a Table, I need to understand the following:
- What the case study is about
- The data that is needed for the event
- The data type for each column added to the table
- Am I working with one table or more than one table (where I will need to create a relationship between the tables)
- Create an Entity Relationship Diagram to make it easier for us to understand.
- With the table and data I'm using, is there already an existing table I can use? Please check for this. If no current table works, you can create a new table.
Let's follow the steps listed above.
What the case study is about
This is a call for speakers' annual tech event hosted this year. Speakers from different parts of the world for the event are to attend to discuss and teach about new technologies and various tools. The speakers have already been accepted and this is a database for the speakers.
The data collected for the table
The first table here is the
Speakers Table
Full Name
Gender
Email Address
Country
City
Address
Postal code
Company name
Technology
Topic
Speaker ID (This column is to differentiate the speakers to avoid mix-ups of names, email addresses, etc.)
Note: I made a last-minute correction; where you see Registration, that is, the Speakers Table, and I missed a column in the Speakers table, that is Topic. The data type for the Topic is a Single line column.
If there are plans for flight and hotel expenses for the speakers, that is another table.
Budget Table
Expenses budget
Hotel budget (maximum is 400 and minimum is 200: which means that they can not spend more than 400 dollars for hotel expenses for one speaker)
Flight ticket (maximum is 800 and minimum is 200)
Budget ID
Creating of Tables
The relationship between the Speaker table and the budget table is a one-to-many relationship making the Speaker table the parent table.
The Entity Relationship Diagram for this case study is
To create the ERD, I used Dbddiagram
The step-by-step process of creating the tables.
Registration Table
Step 1:
Sign into your Microsoft Power Apps Account
Step 2:
At the left-hand side of the screen, click on the hamburger icon, then click on Table
Step 3:
From the existing tables, I noticed that the Contact Table works with what I need for the Registration Table.
Click on the Contact Table
Click on Edit
Step 4:
Click on the Edit icon,
Change the display name from Contact to Registration,
Click on Save.
Step 5:
Check for the existing columns in the table that relate to the needed ones. From the existing columns, when we relate it to the columns we need.
The existing columns here that we can use are:
Full Name
Gender
Address 2: Country/Region
Address1: City
Address 1
Address 1: Zip/Postal Code
Company name
Here we will change the Display name of Address 2: Country/Region to Country
Changing Address 1: City to City
Changing Address 1 to Address
Changing Address 1: Zip/Postal Code to Postal code.
Let's start with changing the Display name of Address 2: Country/Region to Country.
- Click on Address 2: Country/Region,
- Click on the Edit column,
- Change the Display name from Address 2: Country/Region to Country.
- Click on Save.
Repeat the step for the other existing columns.
Step 6:
Next, we will be adding the remaining columns missing in step 6 and step 7.
Add the Technology column to the table
- Click on +
- Add the display name to Technology
- Click on Save
Step 7:
Add the Speaker ID column to the table
-
To create the Speaker Id column will be using the data type, Autonumber.
- Click on +
- Display name is Speaker ID
- Change the Data type from a Single line of text to Autonumber
- Scroll down and change it to your preferred Prefix autonumber type. Here, we will be using the String prefix number
- The Prefix to Speaker
- It is optional to change the minimum number of digits. For this, we will be using 4
- Change the Seed value from 1000 to 0000 (this is optional)
- Click on Save
Create the Budget Table
There is no already existing table we can relate to for the Budget table so we will create a new one.
Step 8:
Click on Tables on the left-hand side
Click on + New Table
Step 9:
Display name is Budget.
Click on Enable attachments (including notes and files)
Click on Save
Step 10:
Add the Flight budget column to the table.
- Scroll down and click on +,
- Display name is Flight budget,
- Change the Data type from a Single line of text to a Number,
- Scroll down and click on Advanced options,
- Change the Minimum value to 200,
- Change the Maximum value to 800,
- Click on Save.
Step 11:
Add the Hotel Budget column to the table.
- Click on +,
- Display name is Hotel budget,
- Change the Data type from a Single line of text to a Number,
- Scroll down and click on Advanced options,
- Change the Minimum value to 200,
- Change the Maximum value to 400,
- Click on Save.
Step 12:
The Budget table has a primary key column available, but it is related to the GUID Field. To create the Budget Id column will be using the data type, Autonumber.
- Click on +,
- Display name is Budget ID,
- Change the Data type from Single line of text to Autonumber,
- Scroll down and change it to your prefered Prefix autonumber type. Here, we will be using the String prefix number,
- The Prefix to Event,
- It is optional to change the minimum number of digits. For this, we will be using 4,
- Change the Seed value from 1000 to 5052 (this is optional),
- Click on Save.
After clicking on Save, I had an error message because the table's GUID column uses the same schema name. To solve this, click on the Advanced options and change the Schema name from budgetid to eventbudgetid.
Click on Save.
Step 13:
Add the expenses budgeted column to the table.
- Add the display name to Expenses Budgeted,
- Change the data type from a Single line column to a Number,
- Change the behavior from Simple to Calculated,
- Click on Save and edit.
Step 14:
In this column, we will be working on the calculated column, the expenses budgeted column.
- Click on the Edit column,
- Click on Edit,
- Click on Add action.
Here we will be adding the Flight budget and Hotel budget to give the sum of what was spent for each Speaker.
Search for Flight budget and Hotel budget, and use the addition sign. This will show you the schema name of flight budget and hotel budget, which is:
cr8fc_flightbudget+ cr8fc_hotelbudget
Next, click on SAVE AND CLOSE
Create the relationship between the table.
They are two ways in creating a one to many relationships in a table
- creating a lookup column
- creating a one-to-many relationship in the parent table
Step 15:
Here we will be creating the one-to-many relationship from the Parent table.
- Click on Table on the left-hand side,
- Click on the Registration table,
- Click on Relationship,
- Click on New Relationship,
- Next, click on One-to-many,
- On Table, search for the table, Budget,
- Change the Lookup column name to Speaker's name,
- Click on Done.
To confirm if the relationship was created, scroll down the page. The display name is Speaker's name.
Learn more on Microsoft Dataverse
Get started using Microsoft Dataverse
Introduction to Dataverse - Training
Dataverse: Visualizing with an Entity Diagram
Create a relationship between tables