Module: Configure Microsoft Dataverse

Section: Manage the Data Model

Lesson Title: Creating Standard and Activity Tables

In the world of the Microsoft Power Platform, data is the foundation of every solution. Whether you are building a simple canvas app to track office supplies or a complex model-driven app to manage global supply chains, the way you structure your data determines the success, scalability, and maintainability of your application. Microsoft Dataverse serves as the intelligent, secure, and scalable backbone for this data.

At the heart of Dataverse are tables. If you come from a traditional database background, you might think of these simply as "tables" in a SQL database. However, Dataverse tables are much more than rows and columns; they are rich objects that include logic, security, and metadata. Understanding the distinction between the different types of tables—specifically Standard tables and Activity tables—is one of the most critical skills for any Dataverse architect or developer. Choosing the wrong type at the start of a project can lead to significant rework later, as some of these settings cannot be changed once the table is created.

In this lesson, we will dive deep into the nuances of creating and managing Standard and Activity tables. We will explore when to use each, how to configure their properties for maximum efficiency, and the best practices that professional developers use to ensure their data models are built to last.

Understanding the Dataverse Table Hierarchy

Before we start clicking buttons in the Power Apps portal, we need to understand what a table actually represents in Dataverse. A table defines the information you want to track in the form of records, which typically include properties such as columns (fields), relationships to other tables, and rules for data validation.

Dataverse categorizes tables into several types, but the two you will interact with most frequently are Standard and Activity tables.

Standard Tables

Standard tables are the workhorses of your application. They represent the "nouns" of your business process. If you are building a system for a university, your standard tables might include "Student," "Course," "Classroom," and "Enrollment." These tables are designed to hold data that has a lifecycle but isn't necessarily a "communication" or a "task" in the traditional sense.

When you create a custom table in Dataverse, it defaults to a Standard table. These tables offer the most flexibility in terms of ownership, security, and how they can be related to other entities. They support complex relationships (One-to-Many, Many-to-One, and Many-to-Many) and can be configured for various specialized behaviors like change tracking and auditing.

Activity Tables

Activity tables are a specialized type of table designed to track "actions" or "interactions." Think of these as the "verbs" or the timeline of events. Examples of built-in activity tables include Phone Calls, Emails, Tasks, and Appointments.

What makes an Activity table unique is that it shares a common set of functionality with all other activity types. Every activity table automatically includes fields like "Subject," "Start Time," "End Time," "Description," and, most importantly, the "Regarding" field. The "Regarding" field is a polymorphic lookup, meaning a single activity (like a phone call) can be linked to almost any other record in the system (like an Account, a Contact, or a custom "Project" table).

Callout: Standard vs. Activity Tables

Use a Standard Table when you need to store master data or transactional data that represents a distinct object (e.g., a "Vehicle" or a "Service Contract").

Use an Activity Table when you need to track a point-in-time interaction that should appear in a record's Timeline view and needs to be scheduled or completed (e.g., a "Site Inspection" or a "Customer Follow-up").

Creating a Standard Table: Step-by-Step

Creating a table is a straightforward process, but the configuration options you choose during creation have long-lasting implications. Follow these steps to create a custom Standard table.

Step 1: Accessing the Table Menu

Log in to the Power Apps maker portal. Ensure you are in the correct environment (check the environment picker in the top right corner). Navigate to Dataverse > Tables on the left-hand navigation pane. Click on + New table and select Table.

Step 2: Defining Basic Properties

A side panel will appear where you must provide the following:

• Display name: This is what users will see in the app (e.g., "Project").
• Plural name: Dataverse usually suggests this, but ensure it is correct (e.g., "Projects").
• Description: Never skip this. A good description explains the purpose of the table for future developers.

Step 3: Setting the Primary Column

Every Dataverse table must have a Primary Column. By default, this is named "Name" and is a Text type. This column serves as the "friendly identifier" for the record. When you see a lookup field in an app, the value displayed is usually the value from the Primary Column.

• Tip: If you are creating a table where a name doesn't make sense (like an "Inventory Adjustment"), you might change the display name of the primary column to "Adjustment ID" or "Batch Number."

Step 4: Advanced Options

Clicking on Advanced options reveals settings that are critical for your data model:

• Schema name: This is the unique internal name (e.g., crabc_Project). It cannot be changed after the table is saved.
• Table type: Ensure this is set to "Standard."
• Ownership: This is a vital decision.
  • User or Team: Records are owned by a specific person or group. Security can be defined based on who owns the record (e.g., "I can only see projects I own").
  • Organization: Records are owned by the entire organization. There is no individual ownership, and security is usually "all or nothing."
• Enable Attachments: Check this if you want users to be able to upload notes and files directly to the record.

Note: Once you set the Ownership to "Organization," you cannot change it to "User or Team" later. If you are unsure, "User or Team" is the safer, more flexible choice.

The Power of Activity Tables

Activity tables are unique because they are part of a shared family. When you create a custom table and set its type to "Activity," it gains "superpowers" that standard tables do not have.

Why Create a Custom Activity Table?

You might wonder why you would create a custom activity table instead of just using a Standard table. Imagine you are building a system for a real estate agency. They perform "Property Showings." While you could make "Property Showing" a standard table, making it an Activity Table allows for the following:

1. Timeline Integration: The showing will automatically appear in the Timeline wall of the associated "Property" or "Contact" record.
2. Shared Reporting: You can run a single report that shows all "Activities" (Emails, Phone Calls, AND Property Showings) in one view.
3. Scheduling: It automatically includes Start/End dates and can be synced with Outlook if configured.

Creating a Custom Activity Table

The process is similar to creating a Standard table, but with one key change in the Advanced options:

1. In the New table pane, expand Advanced options.
2. Find the Table type dropdown and select Activity table.
3. Notice that the Ownership automatically locks to "User or Team." Activity tables cannot be organization-owned because they are inherently tied to the people performing the actions.
4. Once created, you will notice the table already has dozens of columns pre-created (Subject, Regarding, Priority, etc.). You cannot delete these, but you can add your own custom columns to them.

Warning: You cannot turn a Standard table into an Activity table (or vice versa) after it has been created. This is a "one-way" decision made at the moment of creation.

Deep Dive: Table Properties and Features

When configuring your tables, you will encounter several toggle switches in the advanced settings. Understanding what these do "under the hood" is essential for performance and functionality.

Auditing

Enabling auditing allows the system to track every change made to a record in this table. It records who changed the data, what the old value was, and what the new value is.

• Best Practice: Only enable auditing on tables where it is strictly necessary (like financial records or sensitive HR data). Enabling it everywhere can lead to unnecessary storage consumption.

Change Tracking

Change tracking is a background process that allows external systems (like Power BI or Azure Data Factory) to know which records have been updated since the last time they checked.

• Use Case: This is essential if you plan to export your Dataverse data to a Data Lake or use the "Delta" sync method in integration tools.

Appear in Search

This setting determines if the table's data is indexed by Dataverse Search (the global search bar at the top of your apps).

• Tip: For a table to be searchable, you must not only enable this setting but also configure the "Quick Find View" for that table to include the columns you want to search against.

Developer Perspective: Creating Tables via Code

While most tables are created through the UI, developers often need to automate the creation of the data model using the Dataverse Web API or the SDK. This is common when building "Solution Accelerators" or deployment scripts.

Using the C# SDK

To create a table programmatically, you use the CreateEntityRequest class. Here is a simplified example of how you might define a new Standard table:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;

public void CreateProjectTable(IOrganizationService service)
{
    // Define the table metadata
    EntityMetadata projectTable = new EntityMetadata
    {
        SchemaName = "crabc_Project",
        DisplayName = new Label("Project", 1033),
        DisplayCollectionName = new Label("Projects", 1033),
        Description = new Label("A table to track internal company projects.", 1033),
        OwnershipType = OwnershipTypes.UserOwned,
        IsAuditEnabled = new BooleanManagedProperty(true)
    };

    // Define the Primary Attribute (Primary Column)
    StringAttributeMetadata primaryAttribute = new StringAttributeMetadata
    {
        SchemaName = "crabc_Name",
        RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.ApplicationRequired),
        MaxLength = 100,
        DisplayName = new Label("Project Name", 1033)
    };

    // Create the request
    CreateEntityRequest request = new CreateEntityRequest
    {
        Entity = projectTable,
        PrimaryAttribute = primaryAttribute
    };

    // Execute the request
    service.Execute(request);
}

Explanation of the Code:

1. EntityMetadata: This object holds the high-level configuration of the table. We set the SchemaName, OwnershipType, and whether Auditing is enabled.
2. StringAttributeMetadata: This defines the "Primary Column." We specify that it is a required field (ApplicationRequired) and set a maximum character limit.
3. CreateEntityRequest: This wraps the metadata into a single message sent to the Dataverse server.
4. service.Execute: This line actually sends the command to Dataverse to create the table in the SQL backend and update the metadata cache.

Managing Table Columns and Data Types

Once a table (Standard or Activity) is created, it is essentially an empty shell. You need to add columns to store your data. Dataverse provides a wide array of data types, each with its own specific behavior.

Common Data Types

• Text: For names, descriptions, and short strings. Use "Plain Text" for standard entries and "Text Area" for longer descriptions.
• Choice: A dropdown list of options. These are great for ensuring data consistency (e.g., "Project Status" with options like "Not Started," "In Progress," "Completed").
• Lookup: This creates a relationship to another table. For example, a "Project" table might have a lookup to the "Client" (Account) table.
• Date and Time: Essential for tracking deadlines. You can choose between "User Local" (adjusts for the viewer's timezone) or "Date Only."
• Currency: Used for financial values. Dataverse handles the complexity of exchange rates automatically if you have multiple currencies enabled.

The "Regarding" Column in Activity Tables

As mentioned earlier, Activity tables have a unique column called regardingobjectid. In the UI, this is labeled Regarding. This is not a standard lookup. While a standard lookup can only point to one specific table (e.g., a lookup to "Accounts"), the "Regarding" field can point to many different tables. When you create a custom Standard table, you must check the box "Enable activities" in the table settings to allow Activity records to be linked to it via the "Regarding" field.

Best Practices for Table Design

Designing a data model is as much an art as it is a science. Here are the industry standards you should follow to ensure your Dataverse environment remains healthy.

1. Naming Conventions

Always use a consistent prefix for your custom tables. Microsoft automatically assigns a prefix based on your "Publisher" (e.g., new_, crabc_).

• Do: Use PascalCase for schema names (e.g., crabc_ProjectTask).
• Don't: Use spaces or special characters in schema names.
• Do: Keep display names singular (e.g., "Project") and plural names correct (e.g., "Projects").

2. Minimize Custom Activity Tables

While Activity tables are powerful, they come with overhead. Every activity table is added to the global ActivityPointer table in the database. If you create hundreds of custom activity types, it can eventually impact the performance of the Timeline view. Only create a custom activity table if the record truly represents a time-bound interaction that belongs on a timeline.

3. Use Descriptions Diligently

In a year, you or your successor will not remember why a specific table was created. Use the description field to document the business requirement the table fulfills.

4. Choose the Right Ownership

If the data is sensitive (like Salaries or Performance Reviews), always use User or Team ownership. This allows you to use Security Roles to restrict access so that users can only see their own records. If the data is reference data that everyone in the company needs to see (like a list of "Office Locations"), use Organization ownership to simplify the security model.

Callout: The "Activity Party"

Activity tables support a special data type called "Partylist." This allows you to select multiple records for a single field. For example, in an Email activity, the "To" field can contain three Contacts, two Users, and one Account. Standard tables cannot have Partylist columns. If your business requirement involves "sending" something to multiple stakeholders, an Activity table is likely the right choice.

Comparison: Standard vs. Activity Tables

Common Pitfalls and How to Avoid Them

Even experienced architects make mistakes when configuring Dataverse tables. Here are the most common traps.

Pitfall 1: Forgetting to Enable "Activities"

You create a beautiful "Equipment" table. Later, you realize you want to track "Maintenance Calls" against each piece of equipment. However, when you try to set the "Regarding" field of a Phone Call to an Equipment record, the Equipment table doesn't show up.

• The Fix: You must go into the "Equipment" table settings and check the box "Enable activities." This "registers" the table with the Activity system.

Pitfall 2: Over-using Activity Tables

Some developers make everything an Activity because they like the Timeline view. For example, they might make "Invoices" an Activity. This is a mistake. An Invoice is a financial document, not an interaction.

• The Fix: Use a Standard table for the Invoice. If you want to see it in a timeline, you can use specialized PCF (Power Apps Control Framework) components or simply use the "Related" tab to see associated records.

Pitfall 3: Not Planning for Search

By default, only the Primary Column is searchable in the global search. Users often complain that they "can't find a project by its Project Code."

• The Fix: You must add the "Project Code" column to the Quick Find View of the table. Simply enabling "Appear in Search" in the table settings is not enough; you must also tell the indexer which columns to look at.

Pitfall 4: Neglecting the "Primary Column"

When creating a table for "Internal Requests," you leave the Primary Column as "Name." Users find it confusing because they don't know what to type in the "Name" field for a request.

• The Fix: Rename the Primary Column to "Request Summary" or "Issue Title" during creation. This provides better context for the user.

Step-by-Step: Enabling a Table for Activities

If you have a Standard table and you want to be able to track tasks or emails against it, you must enable this relationship.

1. Navigate to Tables and select your custom table (e.g., "Vehicle").
2. Click on Properties in the command bar.
3. Expand Advanced options.
4. Scroll down to the "Making this table an option for" section.
5. Check the box labeled Creating a new activity.
6. Click Save.
7. Note: This process creates several background relationships between your table and the Activity tables. This cannot be undone, so ensure you truly want activities tracked against this record.

Practical Example: The "Site Visit" Scenario

Let's put this into practice. Imagine you are building an app for an environmental agency. They have Research Sites (Standard Table) and they perform Soil Samples (Activity Table).

1. Research Site (Standard Table):

   • Ownership: Organization (everyone should see all sites).
   • Primary Column: "Site Code" (e.g., SITE-101).
   • Custom Columns: Latitude, Longitude, Ecosystem Type.
   • Settings: "Enable activities" must be checked.

2. Soil Sample (Activity Table):

   • Ownership: User/Team (the scientist who took the sample owns the record).
   • Primary Column: "Sample Subject" (e.g., Nitrogen Test - North Quadrant).
   • Custom Columns: PH Level, Moisture Content, Depth.
   • Integration: Because this is an Activity table, the scientist can go to the "Research Site" record and see every "Soil Sample" ever taken in the Timeline. They can also sync these "Samples" to their Outlook calendar as appointments.

This structure allows for clean reporting. You can easily answer the question: "How many sites do we have?" (Standard Table) and "How much work have we done this month?" (Activity Table).

Summary and Key Takeaways

Configuring the data model is the most important step in building a Power Platform solution. By choosing between Standard and Activity tables correctly, you set the stage for a user-friendly and performant application.

• Standard Tables are for master data and objects (the "nouns"). They offer flexible ownership and are the default choice for most business data.
• Activity Tables are for interactions and events (the "verbs"). They come with built-in columns for timing and a polymorphic "Regarding" field that links to other records.
• Ownership Matters: Choosing "Organization" ownership is permanent. "User or Team" ownership allows for more granular security and is usually the preferred choice for business data.
• The Timeline: Only Activity tables (and tables enabled for activities) participate in the Timeline wall. Use this to provide users with a historical view of interactions.
• Primary Columns: Always rename the default "Name" column to something that makes sense for your specific business case to improve the user experience.
• Metadata is Permanent: Remember that Schema Names and Table Types (Standard vs. Activity) cannot be changed after creation. Plan your model on paper before building in Dataverse.
• Searchability: To make a table truly useful, enable it for search and configure the "Quick Find View" to include all relevant columns.

By mastering these concepts, you ensure that your Dataverse environment is not just a place to store data, but a powerful engine that drives business processes and provides meaningful insights.