How to Create a Table in Microsoft Dataverse

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.

LeeStott_0-1676882014209.png

 

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 

Untitled (2).png

 

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

 

Ms Power Apps Environment.PNG

 

Step 2:

At the left-hand side of the screen, click on the hamburger icon, then click on Table

 

table.PNG

 

 

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 

 

Contact Table.PNG

 

Edit Table.PNG

 

 

Step 4:

Click on the Edit icon,

Change the display name from Contact to Registration,

Click on Save.

 

Edit icon.PNG

 

 

Dataverse Table.PNG

 

 

Registration table.PNG

 

 

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.

 

existing column.PNG

 

address.PNG

 

country.PNG

 

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 

 

Edit registration.PNG

 

Technology.PNG

 

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

 

speaker id.PNG

 

speakerid.PNG

 

 

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

 

new table.PNG

 

 

new table +.PNG

 

 

Step 9:

Display name is Budget.

Click on Enable attachments (including notes and files) 

Click on Save

 

new table 1.PNG

 

save table.PNG

 

budget table.PNG

 

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.

 

calculated column.PNG

 

number column.PNG

 

minimum and maximum value.PNG

 

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.

 

Hotel budget.PNG

 

column.PNG

 

 

column1.PNG

 

 

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.

 

 

Autonumber column.PNG

 

Autonumber type.PNG

 

 

error message.PNG

 

schema name.PNG

 

 

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.

 

Expenses.PNG

 

 

Calculated column 1.PNG

 

 

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

 

budget 1.PNG

 

 

edit.PNG

 

Calculated field.PNG

 

 

schema.PNG

Set.PNG

 

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. 

 

one to many relationship.PNG

 

New relationship.PNG

 

Related table.PNG

 

table 1.PNG

 

relationship 3.PNG

 

Learn more on Microsoft Dataverse 

Get started using Microsoft Dataverse
Introduction to Dataverse - Training

Table relationships overview

Dataverse: Visualizing with an Entity Diagram

Create a relationship between tables

Autonumber columns

Calculated and rollup columns

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.