Overview
Relationships connect Dataverse tables together, enabling you to reference data across tables without duplication. Understanding relationship types and cascade behaviour is essential for building normalised, maintainable Power Platform solutions.
Prerequisites
- Two or more Dataverse tables already created
- Maker permissions in a non-Default environment
- Understanding of Dataverse table and column basics
- Familiarity with database normalisation concepts (helpful but not essential)
Add Your Heading Text Here
Relationships define how tables connect to each other in Dataverse. Instead of storing all data in one massive table with repeated information, relationships let you split data into logical tables and link them together.
For example, rather than storing location details repeatedly in every Asset record, you create a separate Location table and link Assets to Locations via a relationship. When the location address changes, you update it once in the Location table—all linked Assets automatically reflect the new address.
Why relationships matter:
- Eliminate data duplication – Store information once, reference it everywhere
- Maintain data integrity – Changes to master data cascade to related records
- Enable complex queries – Filter assets by location properties without storing location data in the Asset table
- Support hierarchies – Build parent-child structures like Projects → Tasks → Subtasks
Dataverse supports three relationship types, though only two are created directly:
- One-to-Many (1:N) – One parent record has many child records
- Many-to-One (N:1) – Many child records link to one parent (reverse of One-to-Many)
- Many-to-Many (N:N) – Records from both tables can link to multiple records in the other table
Viewing Existing Relationships
Before creating new relationships, familiarise yourself with what already exists. Every Dataverse table has relationships automatically created by the system, and understanding these helps you design your own.
Accessing the relationships view:
- Navigate to make.powerapps.com and select your environment
- Expand Dataverse → Tables
- Click on any table to open it
- Select Relationships from the top navigation
You'll see a list of all relationships associated with this table. The view shows four key columns:
| Column | What It Shows | Example |
|---|---|---|
| Display Name | Human-readable name of the relationship | Asset → Location |
| Name | Technical schema name used in code | cr6a3_asset_location |
| Related Table | The table on the other end of the relationship | Location |
| Relationship Type | One-to-Many, Many-to-One, or Many-to-Many | Many-to-One |
Understanding the relationship perspective:
If you're viewing the Asset table's relationships and see "Many-to-One → Location", this means:
- Many Assets can link to One Location
- Each Asset has a lookup column pointing to its Location
- From the Location table's perspective, this is a One-to-Many relationship
Understanding One-to-Many Relationships
One-to-Many (1:N) relationships are the most common relationship type in Dataverse. They represent scenarios where one parent record can have multiple child records, but each child belongs to only one parent.
Common One-to-Many examples:
| Parent Table (One) | Child Table (Many) | Business Logic |
|---|---|---|
| Location | Asset | One location houses many assets |
| Customer | Order | One customer places many orders |
| Project | Task | One project contains many tasks |
| Request | Comment | One request has many comments |
| Contract | Invoice | One contract generates many invoices |
How it works technically:
When you create a One-to-Many relationship from Location to Asset, Dataverse automatically:
- Adds a Lookup column to the Asset table pointing to Location
- Creates the reverse Many-to-One relationship from Asset to Location
- Enables you to view all related Assets from a Location record
Users filling in an Asset record will see a Location dropdown showing all available Locations (specifically, their primary column values). When they select a Location, that Asset is now linked to that Location.
Creating a One-to-Many Relationship
Let's create a One-to-Many relationship from Location to Asset, allowing one Location to house many Assets.
Prerequisites:
- You must have already created both the Location table and the Asset table
- Both tables must have Dataverse databases provisioned
- You need maker permissions in the environment
Step-by-step creation:
- Navigate to the Location table (the parent/one side)
- Click Relationships in the top navigation
- Click + New relationship
- Select One-to-Many
- Choose Asset as the Related table
- Configure the lookup column that will be created on the Asset table
- Set cascade behaviour (explained below)
- Click Done to create the relationship
The relationship panel requires you to configure several properties:
| Property | Purpose | Example Value |
|---|---|---|
| Display name | Name shown in forms and relationship lists | Location |
| Name (schema) | Technical name for code and integrations | cr6a3_Location |
| Description | Optional documentation | Physical location where asset is installed |
| Searchable | Include in quick find searches | Yes (recommended) |
After clicking Done, the lookup column appears immediately in the Asset table's column list. You can now use it in forms, views, and apps.
Understanding Cascade Behaviour
Cascade behaviour determines what happens to child records when the parent record is modified or deleted. This is one of the most critical decisions when creating relationships because it cannot be changed later.
Available cascade behaviours:
| Behaviour | Delete Parent | Use When |
|---|---|---|
| Cascade All | Deletes all child records automatically | Children are meaningless without parent (Comments → Request) |
| Remove Link | Clears lookup field in children, keeps records | Children can exist independently (Assets → Location) |
| Restrict | Prevents deletion if children exist | Accidental deletion protection (Customer → Orders) |
Cascade All example:
Request → Comments relationship with Cascade All. When you delete a Request, all associated Comments are automatically deleted. This makes sense because comments without a request serve no purpose.
Remove Link example:
Location → Assets relationship with Remove Link. When you delete a Location, all Assets that referenced it have their Location field cleared, but the Assets themselves remain. This makes sense because assets still exist even if their location record is removed from the system.
Restrict example:
Customer → Orders relationship with Restrict. You cannot delete a Customer if they have any Orders in the system. This prevents accidental data loss—you must first reassign or delete all Orders before the Customer can be removed.
Decision framework:
- Use Cascade All when children are components of the parent (line items in an invoice, attachments on a request)
- Use Remove Link when children can exist independently but reference the parent optionally
- Use Restrict when deleting a parent with children would cause data integrity problems
- When in doubt, use Restrict — it's the safest option and prevents accidental data loss
Understanding Many-to-Many Relationships
Many-to-Many (N:N) relationships allow records from both tables to link to multiple records in the other table. They represent scenarios where the relationship goes both directions without a clear parent-child hierarchy.
Common Many-to-Many examples:
| Table 1 | Table 2 | Business Logic |
|---|---|---|
| User | Skill | Users have many skills; skills are held by many users |
| Product | Category | Products belong to many categories; categories contain many products |
| Project | Team Member | Projects have many team members; members work on many projects |
| Course | Student | Courses have many students; students attend many courses |
How it works technically:
Behind the scenes, Dataverse creates a hidden junction table (also called an intersect table) that stores the relationships. For a User-Skill relationship, the junction table contains two columns:
- UserID (lookup to User table)
- SkillID (lookup to Skill table)
Each row in the junction table represents one User-Skill pairing. If User A has Skills 1, 2, and 3, there are three rows in the junction table linking that user to those skills.
When to use Many-to-Many vs One-to-Many:
Use Many-to-Many when:
- No clear parent-child relationship exists
- Records from both tables can associate with multiple records from the other
- The relationship itself has no additional attributes beyond the link
Use One-to-Many (with an intermediate table) when:
- You need to store additional data about the relationship itself (e.g., "Date assigned", "Proficiency level", "Hours worked")
- You need to track history or audit the relationship
- You need to apply business rules to relationship records
Creating a Many-to-Many Relationship
Let's create a Many-to-Many relationship between Project and Team Member, allowing projects to have multiple team members and team members to work on multiple projects.
Step-by-step creation:
- Navigate to either the Project or Team Member table (order doesn't matter)
- Click Relationships in the top navigation
- Click + New relationship
- Select Many-to-Many
- Choose the related table (Project if you started from Team Member, or Team Member if you started from Project)
- Configure the relationship name and table names
- Click Done
Configuration options:
| Property | Purpose | Recommendation |
|---|---|---|
| Relationship Name | Schema name for the junction table | Use both table names: cr6a3_project_teammember |
| Table 1 Name | How Table 1 refers to Table 2 (plural) | Team Members |
| Table 2 Name | How Table 2 refers to Table 1 (plural) | Projects |
After creation, both tables show the relationship in their Relationships view. You can now associate records from both tables using subgrids in Model-Driven Apps or collection formulas in Canvas Apps.
Relationship Limitations and Constraints
Dataverse relationships have important technical limitations you need to understand before building complex data models.
Lookup column restrictions:
- No cascading lookups in forms — If Asset has Location, and Location has Region, you cannot auto-populate Asset.Region based on Asset.Location in standard forms. You must use business rules, JavaScript, or Power Automate flows.
- Limited to columns in the same table — A lookup column on Asset can only reference columns from the Asset table itself. You cannot directly reference Location.Address in the Asset table without creating a rollup or calculated column.
- Form loading performance — Each lookup on a form triggers a separate query to populate the dropdown. Forms with 10+ lookups can load slowly, especially on mobile connections.
Many-to-Many limitations:
- No view visibility — The hidden junction table cannot be accessed directly. You can't create views, add columns, or apply business rules to it.
- No additional attributes — The junction table stores only the two lookups. If you need to add "Date assigned" or "Role", you must use a One-to-Many pattern with an explicit intermediate table instead.
- Subgrid-only interaction — In Model-Driven Apps, Many-to-Many relationships appear only as subgrids. You can add/remove associations but can't edit relationship metadata.
- Data complexity — More complex to work with in Canvas Apps and Power Automate compared to One-to-Many lookups.
Cascade behaviour constraints:
- Cannot be changed — Once set, cascade behaviour is permanent. Changing it requires deleting and recreating the relationship.
- Performance with Cascade All — Deleting a parent with thousands of children can take minutes and may time out in high-volume scenarios.
Relationship Design Best Practices
Follow these proven patterns to build maintainable, performant relationship structures.
Naming conventions:
- Name lookup columns after the related table:
Location,Customer,Project - Add prefixes only if you have multiple lookups to the same table:
PrimaryContactandSecondaryContactboth pointing to Contact - Use plural names for Many-to-Many: "Team Members" not "Team Member" when viewed from Project
Performance optimisation:
- Limit forms to 5-7 lookup columns for acceptable load times
- Use related data viewers (subgrids) sparingly—each one queries the database
- Index lookup columns if you frequently filter or sort by them
- Consider denormalising data for high-traffic read scenarios (store calculated values instead of always joining)
Data integrity:
- Use Restrict cascade for master data that shouldn't be accidentally deleted (Customers, Products, Locations)
- Use Cascade All for dependent data that's meaningless without its parent (Comments, Attachments, Line Items)
- Make critical lookups required to prevent orphaned records
- Cascade All on everything (accidental data loss)
- Many-to-Many when attributes needed
- Optional lookups to critical master data
- 15+ lookup columns on one table
- Thoughtful cascade behaviour per relationship
- Explicit junction tables when metadata needed
- Required lookups to prevent orphaned records
- Balanced normalisation (not over-engineered)
Troubleshooting Relationship Problems
Issue: Cannot create relationship - "A relationship already exists"
Cause: Dataverse prevents multiple relationships between the same two tables unless you explicitly create them with different names.
Solution: If you need multiple lookups from Asset to Location (e.g., "Installation Location" and "Storage Location"), create the first relationship normally, then create the second with a custom name to differentiate it.
Issue: Lookup dropdown is empty in forms
Cause: The related table has no records, or security roles prevent the current user from viewing them.
Solution:
- Verify records exist in the related table
- Check that the user has Read privilege on the related table
- Confirm the related table's primary column has values (not blank)
Issue: Cascade delete fails with "Object reference not set"
Cause: Circular relationships or complex cascade chains can cause deletion to fail when Dataverse can't determine the correct order of operations.
Solution:
- Simplify your relationship structure to avoid circular dependencies
- Use Remove Link instead of Cascade All where relationships form loops
- Delete child records manually before deleting the parent in complex scenarios
Issue: Many-to-Many subgrid doesn't appear in form
Cause: Many-to-Many relationships require you to manually add the subgrid to the form—they don't appear automatically.
Solution:
- Edit the form in the form designer
- Add a subgrid component
- Select the Many-to-Many relationship from the data source dropdown
- Save and publish the form
Next Steps
You now understand how to create and configure relationships in Dataverse. Build on this knowledge by exploring:
- Business rules – Conditionally require lookup fields based on other values
- Rollup columns – Aggregate child data to display on parent records (e.g., Total Asset Value on Location)
- Model-Driven App forms – Design how users interact with related data via subgrids and quick views
- Canvas App collections – Work with related data programmatically using Filter() and LookUp() functions
- Power Automate flows – Trigger automations when relationships are created or modified
The official Microsoft Dataverse relationships documentation provides advanced guidance on self-referential relationships, hierarchical data, and relationship behaviour configuration.