Setting Up Table Relationship in Dynamics 365 CE/CRM

One of the most important parts of configuring your Dynamics 365 CRM system is to set up proper relationships between your tables. What happens when you get it wrong? Unexpected things start to happen. You will hear feedback from your users like:

“When I deleted an Account, it cleared the Account lookup on our custom table.”

“When I reassigned an Account to a new sales rep, all of the related Activities were changed to that rep.”

“After I shared an Account with my coworker, they were also able to see and make changes to that Account’s Leads and Opportunities.”

Relationships 101

Dataverse supports 1:N and N:N relationships between tables. You will also see the N:1 in the designer, which is the related-table view of a 1:N relationship. This post focuses on 1:N relationships, because N:N relationships don’t have cascading behaviors to configure.

Consider the Account:Opportunity relationship. One Account can have many Opportunities. The inverse of this is the N:1 relationship of many Opportunities can be on one Account.

Cascading Relationship Behavior

There are several relationship behaviors you can define between two tables (and some of the out-of-the-box tables are restricted on what you can configure and change). Essentially, when you choose one of these, you are telling the system what should happen to the child row(s) when actions are performed on the parent.

In a 1:N relationship, cascading occurs when specific actions happen to the parent row. Those actions include Assign, Share, Unshare, Reparent (applies only to parental relationships), Delete, and Merge (dependent on the referenced table and may not be configurable the way other actions are). Additionally, some relationships also expose Rollup View behavior.

For each action, Dataverse lets you choose a behavior (what the system does to the related rows). Common options include:

• Cascade All: apply the action to all related rows
• Cascade Active: apply only to active related rows
• Cascade User Owned: apply only to related rows owned by the same user as the parent
• Cascade None: don’t cascade

For Delete specifically, you can also choose:

• Remove Link: clear the lookup on the child rows, leaving them orphaned
• Restrict: block deleting the parent while related rows exist

Types of Relationship Behaviors

To help illustrate each, assume the relationship we are looking at is the Account:Opportunity. Each Opportunity is related to one parent Account. When specific actions occur on the Account, the relationship behavior dictates what will happen to the Opportunity.

• Parental: Account is the boss; Opportunities follow it. If someone deletes the Account, the system deletes all of its Opportunities. If the Account is reassigned to a new sales rep, all Opportunities automatically reassign.
• Configurable Cascading: This one follows the Account but is dependent on how the Admin sets it up. For example, deleting an Account may delete Opportunities, leave them or block deletion until related Opportunities are assigned to a new Account; it all depends on which settings are selected.
• Referential: Opportunities are linked to the Account, but they do not automatically follow it. The deal is related to the Account, but it’s treated independently. If someone deletes an Account, the Opportunity remains with no related Account.
• Referential, restrict delete: You cannot delete the Account while it still has Opportunities. Additionally, reassigning the Account will not automatically change the Opportunities.

When Should I Use Each Behavior?

Relationships can certainly get confusing. Here is a practical guide to help you decide which option to use for a scenario:

• Parental: Use when child rows are meaningless without the parent (strict integrity).
• Referential: Use when the child must survive independently (history/reporting).
• Restrict Delete: Use when deletion would be dangerous and should be blocked.
• Configurable: Use when you want some cascades (like Assign) but not others (like Share).

Troubleshooting

Returning to the beginning of this article, let’s review some common troubleshooting you can do when you see specific scenarios in your CRM system.

• Owners changed unexpectedly: check Assign cascade (Cascade All/Active/User Owned).
• Coworker can edit related rows after sharing parent: check Share/Reparent cascades and inherited access implications.
• Lookup got cleared after delete: Delete Remove Link (Referential).
• Can’t delete parent: Delete Restrict (Referential, Restrict Delete or configurable).
• A tip on Share/Reparent: If you change Share or Reparent from cascading to No Cascade, inherited access can remain and Dataverse may run (or need you to rerun) a cleanup job to revoke it.

Final Thoughts

Relationship behavior isn’t a “set it and forget it” option. It’s a decision about data integrity, ownership, and security inheritance. Before deploying changes, test Assign/Share/Delete/Reparent in a sandbox with realistic security roles, and document the intended outcomes so user feedback can be traced back to a specific cascading action and setting.

---