Dataverse Create Table [With Examples]

In this Microsoft Dataverse tutorial, we will discuss everything about Tables like what is a table in Dataverse, what its use is, What are the various table types are available in Dataverse, and many more.

Also, we will cover all the below points that are related to the Dataverse table:

  • Types of tables in Microsoft Dataverse or Dataverse table types
    • Activity Table in Dataverse
  • Dataverse table ownership
  • Dataverse tables permissions
  • Dataverse table row limit
  • Dataverse table data types
  • Create a Custom table in Microsoft Dataverse
    • Display Dataverse Custom Table
    • Various ways to create columns in a Dataverse Table
    • Create Different Columns in Microsoft Dataverse Table
  • Dataverse Table create item
  • Dataverse create table from SharePoint list

What is a Dataverse Table?

  • Dataverse helps to store and manage data that is utilized by business applications in a safe manner. Dataverse’s standard and custom tables offer a safe, cloud-based storage alternative for your data.
  • Modeling and managing corporate data is done via tables. You can utilize standard tables, custom tables, or both when creating an app.
  • Standard tables are included by default in Dataverse. These are meant to capture the most typical concepts and circumstances inside a company, in line with the best practices.
Microsoft Dataverse Table
Microsoft Dataverse Table

Types of tables in Microsoft Dataverse or Dataverse table types

Here we will see the various table types in Microsoft Dataverse.

In Dataverse, there is a total of three types of tables. Such as:

  1. Standard:
  • A Dataverse environment comes with several basic tables, sometimes known as out-of-the-box tables. Standard tables in Dataverse include account, business unit, contact, task, and user tables.
  • The majority of Dataverse’s default tables may be changed.

2. Managed:

Tables that have been imported into the environment as part of a managed solution are not editable.

3. Custom:

These are the unmanaged tables that are either imported from another source or developed from scratch in the Dataverse environment. These tables can be totally customized by any user with the necessary permissions.

Activity Table in Dataverse

These are the unique tables where they can only be owned by a single user or team, not by a whole company. You may choose whether to create a standard or activity table when creating table.

These are the below activity tables that are present under the default Dataverse environment.

Table Name Illustration Show in activity menus
Appointment A time period with start/end times and duration is represented by a commitment. Yes
Email Email protocols are used to transmit activity. Yes
Fax Tracks the conclusion of a phone conversation and the number of pages in fax, as well as storing an electronic copy of the document if desired. Yes
Letter An activity that keeps track of a letter’s delivery. The electronic copy of the letter might be included in the activity. Yes
Phone Call Tracking a phone call is happening in this activity. Yes
Recurring Appointment A repeating appointment series’ master appointment. Yes
Task Work to be done is represented by a generic activity. Yes

You may use a new custom activity table to read instant message conversations if you have the ability to do so. Because you don’t provide a primary column when establishing an activity table, it differs from a non-activity table.

The Primary Field in all activity tables is set to Subject, while additional common fields are determined by the Activity table.

Dataverse table ownership

  • There are two forms of table ownership: standard and custom. When you build a custom table, you have the choice of having it owned by a user, a team, or an organization. The ownership type of a table cannot be altered once it has been created.
  • You must remove your custom table and build a new one if you eventually decide that it must be of a different kind.
Ownership Explanation
Organization The organization owns the data. Data access is managed at the organizational level.
User or Team A user or a team owns the data. On a user level, the actions that may be performed on these rows can be regulated.
  • A few Dataverse system tables are comparable to standard tables but have a different form of ownership than an organization, user, or team ownership:
  1. None: Some system tables, such as the Privilege table, do not have an owner.
  2. Business Unit: A few system tables are held by the company. Tables for Business Units, Calendars, Teams, and Security Roles are among them.

Dataverse tables permissions

The below represents the different permissions that are available in a Dataverse table. Such as:

  1. Full Access = This is the permission where the end users can see and change all of the table’s records.
  2. Collaborate = This is a type of permission where the end users can view all records and create new ones. However, they can only make changes to or remove their own records.
  3. Reference = This permission will give you a read-only view of your data.
  4. Private = Users have the ability to generate new records.

Dataverse table row limit

The default item limit when utilizing the List Row action in the Power Automates Dataverse connection is 5000 items. If your table has more than 5,000 entries, you may use the Pagination option in the List rows action’s options. It’s possible to get up to 100,000 records using this method.

Non-delegable filters will only operate up to the Data row limit, which is set at 500 by default but it may be increased to 2,000. The documentation on what is delegable for Dataverse may be found in this.

Dataverse table data types

The below table represents all the data types that support by the Dataverse table.

Power Apps Data types Solution Explorer type API type
Big Integer Time Stamp BigIntType
Choice Option Set PicklistType
Choices MultiSelect Field MultiSelectPicklistType
Currency Currency MoneyType
Customer Customer CustomerType
Date and Time Date and Time
Date and Time Format
DateTimeType
Date Only Date and Time
Date Only Format
DateTimeType
Decimal Number Decimal Number DecimalType
Duration Whole Number
Duration Format
IntegerType
Email Single Line of Text
Email Format
StringType
File File FileType
Floating Point Number Floating Point Number DoubleType
Image Image ImageType
Language Whole Number
Language Format
IntegerType
Lookup Lookup LookupType
Multiline Text Multiple Lines of Text MemoType
Owner Owner OwnerType
Phone Single Line of Text
Phone Format
StringType
Status Status StatusType
Status Reason Status Reason StatusType
Text Single Line of Text
Text Format
StringType
Text Area Single Line of Text
Text Area Format
StringType
Ticker Symbol Single Line of Text
Ticker Symbol Format
StringType
Timezone Whole Number
Time Zone Format
IntegerType
Unique Identifier Unique Identifier or Primary Key UniqueidentifierType
URL Single Line of Text
URL Format
StringType
Whole Number Whole Number
None Format
IntegerType
Yes/No Two Options BooleanType

Create a Custom table in Microsoft Dataverse

A table is a collection of data that contain information such as the firm name, location, goods, email address, and phone number. The data may then be exposed by creating an app that references the table.

There are some standard “out-of-the-box” tables available in Power Apps to handle common scenarios within an organization (such as monitoring Addresses), but you may need to develop custom tables to store data that is unique to your company. For our own individual requirements, we will create a custom table in Microsoft Dataverse.

Now we will see how to create a custom table in Microsoft Dataverse. Though, it’s very easy to make and use in the Dataverse. Let’s follow the below steps.

Step – 1:

  • First, Sign in to Power Apps using your office 365 credentials. On the Power Apps Home page, Expand the Dataverse from the left navigation and click on Tables.
  • Once you will select + New table on the top command bar, then the new table panel will open where you can enter the table information like Display name, Description, etc.
  • In the below screenshot, you can see there are two sections or tabs as Properties and Primary column.
Create a Custom table in Microsoft Dataverse
Create a Custom table in Microsoft Dataverse

Step – 2:

Click on the Properties tab and enter the below table details (those fields have an asterisk mark [*], that should be mandatory fields):

  1. Display name = As this field has an asterisk, it is a required column. This is the name of the table that will be displayed in the app as a single entity. This is something that can be modified afterward.
  2. Plural name = It is also a mandatory field that appears automatically when you are entering the Display name. This is the app’s plural name for the table that will be displayed. This is something that can be modified afterward.
  3. Description = If you want, you may give your table a description. If this table will be used by others, descriptions are useful. This is optional.
  4. Enable attachments (including notes and files) = If you will enable this option, then the attachments control is added to the table. To add or delete files and notes from records, utilize this control. When this option is enabled, users can add files from their PC or existing images from their mobile device. Files can be up to 10 MB in size when attached. This option cannot be altered once the table has been created.

Once you expand the Advanced options, you will get all the below fields:

  1. Schema name = This is the logical name used by the system for the table. You can notice a prefix on the name, which is the publisher ID. Whatever the tables you create in Dataverse, the schema name must be present. Also, this is auto-populated and if you want to change it, then you can do it before saving it. This value must be unique and can not be changed after it has been saved.
  2. Type = Here, you have to select the table type that you want. Either you can select Standard, Activity, or Virtual.
  3. Record ownership = Who can do operations in a row is dictated by the type of ownership. The table records can include data about customers, such as accounts or contacts if they are owned by a user or a team. For each user or team, security may be set according to the business unit. Records in the Organization Ownership Table include information about anything that belongs to or can be accessed by the whole organization. Table records that belong to an organization cannot be allocated or shared.
  4. Choose table image = If you want to provide an image to the Dataverse table, then either you can expand the chevron and select the image or else select the image from the + New image web resource option.
  5. Color = If you want to provide some color to the Dataverse table, then click on the color section and then select the color from the color box that you want.

Apart from that, there are certain options for this table to perform some other actions inside the Dataverse table. If you want any particular option to do some specific operation, then you have to enable it here. Such as:

  1. Apply duplicate detection rules = You may define duplication detection rules for this table if you enable this option.
  2. Track changes = It allows for efficient data synchronization by determining what data has changed since the data was first extracted or last synced.
  3. Leverage quick-create form if available = When this option is selected for a custom activity table, it will appear in the group of activity tables when users click the Create button. When the custom table icon is selected, however, the main form will be utilized because activities do not enable rapid build forms.
  4. Creating a new activity = For this table, associate activities with records. This setting cannot be turned off after it has been activated.
  5. Doing a mail merge = This table may now be used with mail merge.
  6. Setting up SharePoint document management = Enabling this feature allows this table to participate in SharePoint integration when other actions to enable document management for your company have been completed.
  7. Can have connections = Use the connections feature to illustrate how this table’s records are linked to records from other tables with connections enabled. This setting cannot be turned off after it has been activated.
  8. Can be linked to feedback = It allows consumers to leave comments on any table row or rate table records within a set of parameters. This setting cannot be turned off after it has been activated.
  9. Have an access team = If you check this option, then you can easily create team templates for this table.
  10. Can be taken offline = When the Dynamics 365 for Outlook program is not connected to the network, this option allows row data for this table to be available.
  11. Can be added to a queue = Make use of the queue table. Queues facilitate task routing and sharing by storing records for this table in a central location where everyone can see them. This setting cannot be turned off after it has been activated.

Refer to the below screenshot.

Microsoft Dataverse create custom table
Microsoft Dataverse create custom table

Step – 3:

Next, go to the Primary column tab and enter the below field values. Such as:

  1. Display name = Every table has a Primary Name Column by default, which lookup columns utilize to form relationships with other tables. Before saving the table for the first time, you can change the name of the primary name column.
  2. Description = Provide some description of what the column is used for. This is optional and it can be editable at any time.

Once you expand the Advanced options, you will get all the below fields:

  1. Schema name = The Primary Column Display name you give is used to pre-populate this column. It includes the Dataverse solution publisher’s customization prefix. After you’ve saved the table, you won’t be able to edit it.
  2. Column requirement = Specify the requirement of the table. There are three types of requirements: Business recommended, Business required, and Optional.
  3. Maximum character count = There is a certain limitation in the column length and the maximum length must be a value between 1 and 4,000.

Once everything is done, just Save the table.

Create custom table in Microsoft Dataverse
Create custom table in Microsoft Dataverse

After a while, you can see the Custom table is ready to use and it will look alike the below image. Inside the table page, you can view all the table information like Table properties, Schema, Data experiences, Customizations, etc.

Dataverse create custom table
Dataverse create custom table

Display Dataverse Custom Table

Suppose you want to view your created custom table in the Dataverse, then follow the below process:

  • First, log in to the Power Apps.
  • Expand the Dataverse from the left navigation and then select Tables.
  • Go to the Custom tab from the top and then search for your own custom table on the top right search box. Also, you can get your table under the All tab section.
Create custom table in Dataverse
Create custom table in Dataverse

This is how to display the custom table in Dataverse.

Various ways to create columns in a Dataverse Table

Once the Custom table is ready, we need to create various data fields on that. So that the user can specify the values in the Dataverse table. Below represents some of the columns that we can create easily.

In the Dataverse Custom table, we can create a column in many different ways. Such as:

  1. On the Dataverse Tables page, tap on the custom table where you want to create the column. On the next page (below image), click on the + New -> Select Column -> Tap on + New column as shown below.
  2. Secondly, we can directly select the Columns option under the Schema section. Next, the New column page will open where we can enter the column details.
Create a column in a Dataverse Table
Create a column in a Dataverse Table

3. Thirdly, we can create a new column by using the Edit option on the specific table page. Expand the Edit option and select either Edit or Edit in new tab.

Create columns in a Dataverse Table
Create columns in a Dataverse Table
  • Then on the next page, we can click on the + New column option (from the top) to create the new column in the Dataverse custom table.
Dataverse Table create column
Dataverse Table create column

4. At last, there is much easy to create a column by clicking one icon i.e. + as it is present under the Patient Registration columns and data section like the image below.

Create column in Dataverse Table
Create column in Dataverse Table

These are the various ways to create a column in Microsoft Dataverse Custom Table.

Create Different Columns in Microsoft Dataverse Table

Now we will see how we can create various columns or fields in the Dataverse Custom Table. As we already discussed there are different types of data types in a Dataverse table such as Text, Number, Date and time, Lookup, Choice, Currency, File, etc.

Below represents some of them:

Dataverse Create Number Column

Do you want to create a Number column inside the Dataverse Custom Table? Let’s follow the below process.

  • Inside the Custom data table, click on the + New column. On the New column pane, specify the below fields:
  1. Display name = This is a required column. Enter the name of the integer column (suppose Age) that you want to create in the Dataverse table.
  2. Description = This is an optional field. Provide the field description for the purpose to create it.
  3. Data type = Expand the chevron and select the data type as a Number and then select the Whole number. Not only the whole number, but also you can select the options like Decimal, Float, Language code, Duration, and Time zone as per your need.
  4. Format = There are four types of field formats. Such as Duration, Language code, None, and Time zone. Select as per your need.
  5. Behavior = Select the field behavior either Simple, Calculated, or Rollup.
  6. Required = Choose it either for your Business recommended, Business Required, or Optional.
  7. Searchable = If you want to make this field searchable, then put a checkmark on this option.
  • If you want to enable more options for this number column (like Enable column security, Appear’s in dashboard’s global filter, etc.), then expand the Advanced options and set the values.
  • Also, you can specify the Size of number, Minimum value, Maximum value, etc.

Once all the fields are filled up, just Save the column as shown below. After a while, the new number column is ready to use in the Dataverse table.

Dataverse create Number Column
Dataverse create Number Column

This is how we can create a Number field inside the Dataverse Custom Table.

Dataverse Create Date Column

Suppose you want to create a Date column inside the Dataverse Custom table, then refer to the below things to do.

  • Go to your specific Custom data table, and click on the + New column. On the New column pane, specify the below fields:
  1. Display name = This is a required column. Enter the name of the Date column (suppose DOB) that you want to create in the Dataverse table.
  2. Description = This is an optional field. Provide the specific field description for the purpose to create it.
  3. Data type = Expand the chevron and select the data type as a Date and time and then select either the Date and time or Date only as per the need.
  4. Format = Whatever you will select the Data type, it will auto-populate in this Format field.
  5. Behavior = Select the field behavior either Simple, Calculated, or Rollup.
  6. Required = Choose it either for your Business recommended, Business Required, or Optional.
  7. Searchable = If you want to make this field searchable, then put a checkmark on this option. By default, it is enabled.

Then, once you expand the Advanced options, you will get all these below fields. Such as:

  1. Schema name = As we already discussed, it is a pre-populate column that is coming from the Display name. It includes the Dataverse solution publisher’s customization prefix. After you’ve saved the table, you won’t be able to edit it.
  2. Time zone adjustment = There are two types of time zones. Such as User local and Time zone independent. By default, it will be User local only.
  3. Input method editor (IME) mode = There are four IME modes like Active, Auto, Disabled, and Inactive. By default, it will be Auto mode only.
  • Also, if you want to enable more options for this Date and time column (like Enable column security, Appear’s in dashboard’s global filter, etc.), then set the values as per the need.

Once all the fields are filled up, just Save the column as shown below. After a while, the new date column is ready to use in the Dataverse table.

Dataverse create Date Column
Dataverse create Date Column

This is how we can create a Date and time field inside the Dataverse Custom Table.

Dataverse Create Choice Column

Next, we will discuss one of the most important data types in Dataverse i.e. Choice column.

In the same way, Go to your specific Custom data table, and click on the + New column. On the New column pane, specify the below fields:

  • Display name = This is a required column. Enter the name of the Choice column (suppose Reason) that you want to create in the Dataverse table.
  • Description = Provide the specific field description for the purpose to create it. This is an optional column.
  • Data type = Once you will expand the chevron, you can able to view two types of options i.e. Choice and Yes/no. Select the data type as a Choice to add a choice field.
  • Behavior = Select the field behavior either Simple, Calculated, or Rollup.
  • Required = You can choose it either for your Business recommended, Business Required, or Optional.
  • Searchable = If you want to make this field searchable, then put a checkmark on this option. By default, it is enabled.
  • Selecting multiple choices is allowed = If you will enable this option, then the user can able to select multiple-choice values.
Dataverse create Choice Column
Dataverse create Choice Column
  • Sync with global choice? = This is a recommended field that contains two values:
    • Yes (recommended) = If the user enables this option, then it can be used in multiple tables, and will stay updated everywhere.
    • No = If the user enables this option, then it creates a local choice that can only be used in one table. People using it can add new choices.
  • Sync this choice with = This is a required field where you can create your own choices. To create it, click on the + New choice option.
Create a Choice Column in Dataverse
Create a Choice Column in Dataverse

In the New choice pane, enter the below field values:

  1. Display name = Provide a display name of the choice values (Suppose Patient Disease Reason). That means, under this name, all the choices will be stored.
  2. Choices = In the field, there are two options. Such as:
    1. Label = Specify one or multiple numbers of choice values (like High Blood Pressure, Diabetes, etc.). You can add more choices by using the + New choice option.
    2. Value = Also, if you want, then you can provide their values too. The value must be between 0 and 2147483646.

Moreover, you can provide individual colors to the choice values and also you can sort or delete any of the choice values that you want as shown below.

Next, expand the Advanced options and then you can see these below fields:

  1. Name = it is a pre-populate column that is coming from the Choice Display name.
  2. External type name = This is an optional field. Specify the external name to the choice field.
  3. Description = Provide the description of the choice field. It is optional.

Once everything is done, Save the new choice pane.

Dataverse create a choice field
Dataverse create a choice field

Now, expand the chevron of Sync this choice with field and select the Patient Disease Reason that you have created recently.

If you want to modify some choice values, then click on the Edit choice option and do the modifications as per your need.

Similarly, if you want to create another category of the choice column, then click on the + New choice option and add choice values within that.

Once all the fields are filled up, just Save the choice column as shown above. After a while, the new choice column is ready to use in the Dataverse table.

Dataverse Choice column
Dataverse Choice column

This is how to create a Choice field inside the Dataverse Custom Table.

Dataverse Create Multiline/TextArea Column

Now comes how we can add a Multiline or Text Area field into the Dataverse Custom table.

There is a total of three types of text columns in Dataverse and the below table represents the details:

Column Type Default Value Maximum Value Illustration
Text 100 4000 Allows for a single line of text to be entered.
Text Area 100 4000 Allows for several lines of text to be entered. The number of rows that appear in the column can be customized. Use for short bursts of text.
Multiline text 150 1048576 Text can be entered in numerous lines. The number of rows shown in the column may be customized. When a lot of text is needed, this is the option to use.

To create it, go to your specific Custom data table, and click on the + New column. On the New column pane, specify the below fields:

  • Display name = This is a required column. Enter the name of the multi-field column (suppose Address) that you want to create in the Dataverse table.
  • Description = Provide the specific field description for the purpose to create it. This is an optional column.
  • Data type = Once you will expand the chevron, you can able to view two types of sections i.e. Single line of text and Multiple lines of text.
    • In the Single line of text sections, there are some options such as Plain text, Text area, Rich text, Email, Phone number, etc.
    • In the Multiple lines of text sections, there are two options i.e. Plain text and Rich text. You can choose either Text area or Plain text to enter the address value.
  • Behavior = Select the field behavior either Simple, Calculated, or Rollup.
  • Required = You can choose it either for your Business recommended, Business Required, or Optional.
  • Searchable = If you want to make this field searchable, then put a checkmark on this option. By default, it is enabled.

When every field is filled-up, Save the column as shown below. After a while, the address field is ready to use in the Dataverse table.

Dataverse create Multiline column
Dataverse create Multiline column

This is how to create a Multiline field inside the Dataverse Custom Table.

Dataverse Create Phone Number Column

Do you want to create a Phone Number or Email field in the Dataverse table? It’s very simple as the Dataverse text column.

  • On the New column pane, enter the below fields to make a Phone Number field:
  1. Display name = This is a required column. Enter the name of the Phone Number column (suppose Patient Phone Number) that you want to create in the Dataverse table.
  2. Description = Provide the specific field description for the purpose to create it. This is an optional column.
  3. Data type = Once you will expand the chevron, you can able to view two types of sections i.e. Single line of text and Multiple lines of text. Select the Phone number under the Single line of text section. Similarly, if you want to create an Email field, then you can click on the Email option under this section.
  4. Behavior = Select the field behavior either Simple, Calculated, or Rollup.
  5. Required = You can choose it either for your Business recommended, Business Required, or Optional.
  6. Searchable = If you want to make this field searchable, then put a checkmark on this option. By default, it is enabled.

Now Save the column as shown below. After a second of time, the Phone number field is ready to use in the Dataverse table.

Dataverse Create Phone Number Column
Dataverse Create Phone Number Column

This is how to create a Phone number field inside the Dataverse Custom Table.

Create Item in Dataverse Table

Once everything is set up, we need to create items or records in the Dataverse Custom Table. As we know that a table is nothing without the records or data. For that, we should know the process that how we can insert data into the table.

  • Go to the Tables under the Dataverse section (from the left navigation). Click on the specific table (Patient Registration) from the table list and then expand the Edit option from the top bar.
  • As you can see in the below screenshot, there are three different types of options where you can enter and edit the data inside the Dataverse custom table. Such as:
    • Edit = When you will choose this option, a user can enter or edit the records in the same tab itself.
    • Edit in new tab = When you will choose this option, a new tab will open where a user can enter or edit the records.
    • Edit data in Excel = When you will choose this option, an Excel spreadsheet will open where a user can enter or edit the records. Once you will save and publish the excel, then the data will save or store in the Dataverse custom table.
  • Also, you can tap on the Edit button directly i.e. present under the table. But here if you will expand this Edit button, then you can view only two options i.e. Edit and Edit in new tab.
Create Item in Dataverse Table
Create Item in Dataverse Table
  • On the next page, first, select or choose the columns that you want to add to the custom table. As in the below screenshot, I have chosen all the columns that I have created recently. Also, you can select the (Select All) option if you want to add all the columns in the Dataverse custom table. Next, Save it.
Dataverse Table create item
Dataverse Table create item
  • Now enter or insert the record into individual fields. If you want to add any extra new row or new column, then you can do it by using the + New row and + New column option as shown below. Here, the data will save automatically in the custom table. Refer to the below screenshot.
Create an item in Dataverse Table
Create an item in Dataverse Table

This is the way to create Item(s) in a Custom Dataverse Table.

Dataverse create table from SharePoint list

Do you know how to create a Dataverse table from the SharePoint list? Check out this post to know more details: Dataverse create table from SharePoint list

Also, you may like the below Power Apps and Dataverse Tutorials:

This Microsoft Dataverse tutorial explains everything about Tables like what is a table in Dataverse, what its use is, What are the various table types are available in Dataverse, and many more.

Also, we covered all the below points that are related to the Dataverse table:

  • Types of tables in Microsoft Dataverse or Dataverse table types
    • Activity Table in Dataverse
  • Dataverse table ownership
  • Dataverse tables permissions
  • Dataverse table row limit
  • Dataverse table data types
  • Create a Custom table in Microsoft Dataverse
    • Display Dataverse Custom Table
    • Various ways to create columns in a Dataverse Table
    • Create Different Columns in Microsoft Dataverse Table
  • Dataverse Table create item
  • Dataverse create table from SharePoint list

>