Tables, Columns, and Relationships | Guided by A Guide to Cloud

Tables, columns, and rows

Simple explanation

Think of a spreadsheet.

A table is like one sheet in a workbook — “Equipment Inspections”, for example.

Each column is a header — Inspector Name, Date, Pass/Fail, Notes.

Each row is one record — one completed inspection.

The difference from a spreadsheet? Dataverse enforces rules. The Date column only accepts dates. The Pass/Fail column only accepts Yes or No. You cannot accidentally type “banana” in the Date field.

Three types of tables

Table Type	Description	Example
Standard	Pre-built tables included with Dataverse (400+). Follow the Common Data Model. Can be extended but core columns cannot be deleted.	Account, Contact, Activity, User
Custom	Tables you create for your own business needs. Fully under your control — you define columns, relationships, and forms.	Equipment Inspection, Delivery Route, Training Record
Virtual	Tables that display data from an external source in real-time without copying it into Dataverse. Read-only. Data stays in the external system.	SQL Server table, SharePoint list via OData

Aisha builds a custom table

At Coastal Logistics, there is no standard table for “Equipment Inspection”. So Aisha creates a custom table with these columns:

Inspection Name (text) — primary name column
Equipment (lookup to Asset table) — which forklift, truck, or crane
Inspector (lookup to User table) — who performed it
Inspection Date (date only)
Result (choice: Pass, Fail, Needs Repair)
Notes (multiline text)
Photo (image) — snapshot of any damage

What about virtual tables?

Virtual tables are useful when you need to display external data inside Power Apps without importing it. For example, Coastal Logistics has a legacy SQL database with historical shipping data. Instead of migrating millions of rows, Aisha creates a virtual table that connects to the SQL database. Users see the data in their Power App, but it stays in SQL.

The trade-off: virtual tables are read-only and do not support business rules, rollup columns, or calculated columns.

Column data types

Every column in Dataverse has a data type that controls what values it can store:

Data Type	What It Stores	Example
Text (Single line)	Short text up to 4,000 characters	Equipment Name, Email Address
Text (Multiple lines)	Long text up to 1,048,576 characters	Inspection Notes, Description
Whole number	Integers without decimals	Quantity, Count
Decimal number	Numbers with up to 10 decimal places	Weight, Measurement
Currency	Money values with currency symbol	Invoice Amount, Salary
Date and Time	Date with optional time	Inspection Date, Created On
Date Only	Date without time component	Birthday, Start Date
Choice	Single selection from predefined options	Status (Active/Inactive)
Choices	Multiple selections from predefined options	Skills (Driving, Welding, Crane)
Yes/No	Boolean true/false	Is Certified, Is Active
Lookup	Link to a row in another table	Equipment (links to Asset table)
Image	Stores an image file	Employee Photo, Damage Photo
File	Stores a file attachment	Inspection Report PDF

Exam tip: Choice vs Choices

A Choice column allows only one selection (like a dropdown). A Choices column allows multiple selections (like checkboxes). The exam may test whether you know the difference.

Use Choice for things like Status (a record can only be in one status). Use Choices for things like Skills (a person can have many skills).

Relationships

Relationships connect tables so data flows naturally. Dataverse supports two main relationship types.

One-to-many (1:N)

One record in table A relates to many records in table B. This is the most common relationship.

Example: One piece of Equipment can have many Inspections. The Equipment table is the “one” side, and the Inspection table is the “many” side.

In Dataverse, you create this by adding a lookup column on the “many” side. Aisha adds an Equipment lookup column on the Equipment Inspection table — each inspection row points to one piece of equipment.

Many-to-many (N:N)

Many records in table A relate to many records in table B. Dataverse creates a hidden intersection table to manage this.

Example: Employees can be certified for multiple Equipment types, and each Equipment type can have multiple certified Employees. A many-to-many relationship between Employee and Equipment Type handles this.

What about one-to-one relationships?

Dataverse does not have a dedicated one-to-one relationship type. If you need one, you create a one-to-many relationship and enforce the “one” constraint through business rules or application logic.

In practice, one-to-one relationships are rare. Most scenarios are one-to-many or many-to-many.

Business logic in Dataverse

Dataverse is not just storage — it has built-in logic that runs without writing code.

Business logic options in Dataverse
Feature	Business Rules	Calculated Columns	Rollup Columns	Power Automate
What it does	Apply validation and field behaviour on forms	Compute a value from other columns in the same row	Aggregate values from related rows	Automate multi-step processes across systems
Runs where	Client-side (form) or server-side	Server-side, on read	Server-side, on schedule (every hour or on-demand)	Cloud — triggered by events or schedules
Code required	No — visual rule builder	No — formula expression	No — aggregation wizard	No — visual flow designer
Example	If Result is Fail, make Notes required	Full Name = First Name + Last Name	Total inspections count for each piece of equipment	When inspection fails, send email to maintenance team
Best for	Form-level validation and field visibility	Derived values within a single row	Summaries from child records	Cross-system automation and notifications

Business rules

Business rules let you set conditions on forms — show/hide fields, set default values, validate data, or make fields required based on other values. They run without code.

Aisha’s rule: If the Inspection Result is “Fail”, then make the Notes field required and show the Photo field. If the result is “Pass”, hide the Photo field and make Notes optional.

Calculated columns

Calculated columns compute their value automatically from other columns in the same row. They are read-only.

Example: A “Days Since Last Inspection” column that calculates the difference between today and the Inspection Date.

Rollup columns

Rollup columns aggregate data from related records. They update periodically (every hour by default) or can be refreshed manually.

Example: On the Equipment table, a “Total Inspections” rollup column counts all related Inspection rows. A “Last Inspection Date” rollup returns the most recent inspection date.

🎬 Video walkthrough

🎬 Video coming soon

Tables, Columns, and Relationships — PL-900 Domain 2

~12 min