Configuring Dataverse Many-to-Many Relationships with Intersect Tables in Model-Driven Apps: Custom Junction Table Attributes, Filtered Subgrids, and Advanced Relationship Behaviors

Introduction

You're building a certification management app. A professional can hold many certifications, and each certification can be held by many professionals. Simple enough — that's a textbook many-to-many relationship. But then your business analyst drops a requirement on your desk: "We also need to track the date they earned it, the score they achieved, the expiration date, and which training provider administered the exam." Suddenly, the relationship itself has data. That's the moment when Dataverse's native many-to-many relationship mechanism runs out of runway, and you need to take control of the intersect table.

Most Dataverse makers know that many-to-many relationships create a hidden junction table behind the scenes. Fewer know that you can — and often should — replace that hidden table with one you design yourself: a custom intersect table with real columns, security behavior, business rules, and form-driven data entry. The difference between letting the platform auto-generate your junction table and deliberately designing it is the difference between a fragile prototype and a production-grade data model. This lesson teaches you the latter.

By the end of this lesson, you'll be able to design and implement custom junction tables for complex M:N scenarios, surface relationship data through filtered subgrids, apply cascade behaviors that respect the associative nature of the intersect row, and avoid the edge cases that catch even experienced makers off guard.

What you'll learn:

• The architectural difference between native Dataverse N:N relationships and custom intersect tables, and when to use each
• How to model a custom junction table with its own columns, views, forms, and security
• How to configure filtered subgrids on parent forms that show only the intersect rows relevant to that record
• How cascade behaviors apply differently to the two one-to-many relationships that make up your intersect table
• Advanced patterns including role-based access to intersect rows, calculated columns on junction tables, and multi-table intersects for polymorphic scenarios

---

Prerequisites

This lesson assumes you're comfortable with Dataverse fundamentals and have hands-on experience with model-driven apps. Specifically, you should already know how to:

• Create tables, columns, and standard relationships in Dataverse (see Designing a Dataverse Data Model: Relationships, Lookups, and Choice Columns if you need a refresher)
• Build and navigate model-driven apps, including forms and views (covered in Building Your First Model-Driven App: Site Map, Tables, Forms, and Views)
• Configure subgrids and quick view forms on model-driven forms (Designing Model-Driven Forms: Sections, Tabs, Subgrids, and Quick View Forms)
• Understand the basics of cascade behaviors and relationship types (Configuring Dataverse Table Relationships in Model-Driven Apps: One-to-Many, Many-to-Many, and Cascade Behaviors Explained)

---

Why Native N:N Relationships Fall Short

When you create a native many-to-many relationship in Dataverse through the relationship designer, the platform automatically generates an intersect entity — a hidden junction table with a naming convention like prefix_table1_table2_rel or a system-generated name you didn't choose. This table contains only two lookup columns pointing to the two parent tables, plus the standard system columns like row ID and created-on timestamp.

For simple association scenarios — "which users are members of which teams," or "which products belong to which categories" — this works well. The relationship is visible in the UI as a subgrid on both sides, records can be associated and disassociated with a couple of clicks, and you don't need to think about the junction table at all.

The problems start when:

1. The relationship itself carries data. A professional/certification relationship needs score, earned date, and expiration. There's no column to put those on the native junction table.
2. You need to query or report on the associations. Native junction tables are not directly accessible through the standard Dataverse API in the same way normal tables are. You can't filter a subgrid by "show only active certifications" because there's no Status column on the junction table.
3. You need business rules or validation on the association. You can't apply a business rule to a native junction table. You can't prevent someone from associating a record if a condition isn't met.
4. The relationship needs its own security. With a native N:N, you either can see and create associations or you can't — there's no row-level control.
5. You need to audit the association. Native junction tables don't support auditing through the standard audit mechanism in the same granular way as proper tables.

The solution is to decompose the many-to-many relationship into two explicit one-to-many relationships connected through a table you design, own, and control.

---

The Architecture: Decomposing M:N into Two 1:N Relationships

Let's use our certification scenario concretely. We have:

• Professional table: one row per person
• Certification table: one row per certification type (e.g., "AWS Solutions Architect – Associate")
• Professional Certification table: the intersect, one row per person-certification combination

The Professional Certification table gets two N:1 lookup relationships pointing up to its parents:

Professional (1) ─────────────────────────── (N) ProfessionalCertification
Certification (1) ─────────────────────────── (N) ProfessionalCertification

The Professional Certification table also gets all the additional columns that describe the relationship:

ProfessionalCertification columns:
├── cr_professional (Lookup → Professional)       [Required]
├── cr_certification (Lookup → Certification)     [Required]
├── cr_earned_date (Date Only)                    [Required]
├── cr_expiration_date (Date Only)                [Optional]
├── cr_score (Decimal, 0–100)                     [Optional]
├── cr_training_provider (Lookup → Organization)  [Optional]
├── cr_status (Choice: Active, Expired, Revoked)  [Required]
├── cr_notes (Multiline Text)                     [Optional]
└── Standard system columns (ID, Created On, etc.)

This is a proper table. It gets its own views, forms, security permissions, and — critically — its own subgrid presence on both the Professional and Certification forms.

Naming Conventions Matter

Before you create a single column, decide on a naming convention for your intersect table. If you let Power Apps default, you'll get something like cr_professional_certification for the table schema name. That's fine. What matters more is the display name being clear about what the table represents. "Professional Certification" is good. "ProfCert" is not.

The lookup column schema names also matter. Many makers name both lookups something generic like cr_parentid1 and cr_parentid2. Resist this. Name them explicitly: cr_professionalid and cr_certificationid. When you're writing FetchXML queries six months from now, you'll be grateful.

---

Creating the Custom Intersect Table Step by Step

Step 1: Create the Intersect Table

In the Power Apps maker portal, navigate to your solution, select New → Table, and configure:

• Display name: Professional Certification
• Plural display name: Professional Certifications
• Schema name: cr_ProfessionalCertification (your prefix will differ)
• Primary column display name: "Name" — but change this to something meaningful. For intersect tables, the primary text column is often used for a calculated concatenation like "John Smith – AWS SAA" or left as a simple reference number. We'll address this later.
• Table type: Standard (not Activity, not Virtual)
• Enable auditing: Yes — you almost always want to audit who created or modified an intersect row

Step 2: Add the Lookup Columns to the Intersect Table

This is where many makers make a mistake: they add the lookups from the intersect table using the column editor. This works, but you lose relationship configuration options. Instead, define the lookups by creating the relationship from the parent table side.

Navigate to the Professional table, select Relationships, then New relationship → Many-to-one. Wait — that's from Professional's perspective, meaning Professional has many rows related to one of something else. We want to go the other direction: one Professional has many Professional Certifications. So you create a one-to-many relationship from Professional to ProfessionalCertification.

Configure the relationship:

• Related table: Professional Certification
• Lookup column display name: Professional
• Lookup column schema name: cr_professionalid (not the auto-generated mess)
• Relationship behavior: We'll configure this carefully in a moment

Repeat from the Certification table to create a second one-to-many relationship pointing to Professional Certification.

Step 3: Add the Data Columns

Now add the substantive columns to Professional Certification: earned date, expiration date, score, status choice, and so on. These are ordinary columns with whatever data types fit your requirements. Nothing special about adding them to an intersect table versus any other table.

The one column worth discussing is Status vs. statecode. Every Dataverse table has a built-in statecode (Active/Inactive) column. For intersect tables that represent time-bounded relationships — like certifications that expire — you have a choice:

• Use the built-in statecode to mark expired rows as Inactive
• Add your own Choice column with more granular values (Active, Expired, Revoked, Pending)

The better pattern is usually to add your own Choice column for granular status and then use the built-in statecode to represent whether the row itself is logically active (i.e., hide inactive rows from most views). This gives you both fine-grained status reporting and easy platform-level filtering.

Step 4: Configure Duplicate Detection

One of the most important things you must do on a custom intersect table is prevent duplicate associations. If a user tries to associate Professional "Jane Smith" with certification "AWS SAA" twice, you need to catch that at the data layer, not just in UI validation.

Create a duplicate detection rule on Professional Certification that fires when the combination of cr_professionalid and cr_certificationid matches an existing active row. You can configure this in Power Apps portal under Settings → Duplicate detection rules, or programmatically.

An even more robust approach: create an alternate key on Professional Certification using both lookup columns (cr_professionalid + cr_certificationid). This creates a unique index at the database level. Any attempt to insert a duplicate row will fail with a clear error. Alternate keys on composite columns are specifically designed for this pattern.

---

Designing the Intersect Table's Form and Views

The Main Form

The Professional Certification table needs a well-designed main form because users will sometimes navigate directly to an intersect record — especially when editing it. At minimum, the form should show:

• Both lookup columns (Professional and Certification) in a Header section — they're the identity of the record
• The earned date, expiration date, and score in a prominent section
• The status choice column
• A notes or comments multiline field
• A quick view of the related Professional's key details (name, department, manager)
• A quick view of the related Certification's details (level, issuing body, validity period)

The quick view forms give users context without forcing them to navigate away. Build them on both the Professional and Certification tables, then embed them in the Professional Certification form. This transforms the intersect record form from a sterile data-entry screen into an informative record that tells the full story.

Views for the Intersect Table

You need several views on Professional Certification:

Active Professional Certifications (default view):

• Filter: statecode = Active, cr_status = Active
• Columns: Professional, Certification, Earned Date, Expiration Date, Score, Status
• Sort: Expiration Date ascending (soonest to expire first)

Expiring Soon (for dashboard use):

• Filter: statecode = Active, Expiration Date within next 60 days
• Columns: Professional, Certification, Expiration Date, Training Provider
• Sort: Expiration Date ascending

All by Professional (for reporting):

• No status filter
• Columns: Professional, Certification, Earned Date, Status
• Sort: Professional, then Earned Date

Revoked Certifications (for compliance):

• Filter: cr_status = Revoked
• Columns: Professional, Certification, Earned Date, Revoked Date, Notes

Creating these views now pays dividends when you configure subgrids on parent forms — you'll reference these exact views to control what each subgrid shows. For a full walkthrough on building sophisticated views, see Creating and Customizing Views in Model-Driven Apps: Filters, Sorting, and Editable Grids.

---

Configuring Filtered Subgrids on Parent Forms

This is where the custom intersect pattern really pays off. You'll add subgrids to both the Professional form and the Certification form, each showing the intersection rows relevant to that record.

Adding the Subgrid to the Professional Form

Open the Professional table's main form in the form designer. Add a new Tab called "Certifications." Inside it, add a Subgrid component.

Configure the subgrid:

• Table: Professional Certification
• Default view: Active Professional Certifications (the view you built)
• Show related records: Yes — this is the critical toggle. When enabled, the subgrid automatically filters to show only Professional Certification rows where cr_professionalid equals the current Professional record's ID. You don't need to write any FetchXML filter manually.

The "Show related records" behavior works because the subgrid knows about the one-to-many relationship from Professional to Professional Certification, and it traverses that relationship to filter the rows. This is Dataverse's native subgrid filtering, and it's powerful.

But here's the nuance: the view you select for the subgrid still has its own filters (statecode = Active, cr_status = Active). The platform combines the relationship filter (this professional's rows only) with the view filter (only active ones). The result: you see exactly the active certifications for this specific professional.

You can add a second subgrid in a separate tab called "Certification History" using the "All by Professional" view — same relationship filter, different view filter, showing all records regardless of status.

Configuring the Subgrid's Buttons

By default, subgrids on related tables show Add Existing and New buttons. For an intersect table, you usually want:

• New — creates a new Professional Certification row pre-populated with this Professional's lookup (because of the relationship context)
• Remove Add Existing — this doesn't apply to intersect rows the same way it does to simple lookup relationships. You don't "add an existing" Professional Certification record; you create a new association.

You control which buttons appear using the subgrid's command bar settings. In the modern form designer, select the subgrid, open its properties, and look for the commands configuration. You can also hide specific buttons using command bar customization with Power Fx if you need conditional visibility based on the current user's role or the parent record's state.

The Certification Side Subgrid

Repeat the pattern on the Certification table's form, adding a subgrid showing Professional Certification rows where cr_certificationid equals this certification. A useful view here is sorted by Professional name, showing who holds this certification and when it expires — essentially a roster view.

---

Cascade Behaviors: The Critical Configuration

Here's where many developers get burned. When you decompose a many-to-many into two one-to-many relationships through a custom intersect table, you have two sets of cascade behaviors to configure: one for the Professional → Professional Certification relationship, and one for the Certification → Professional Certification relationship.

Each relationship has cascade settings for:

• Assign — when the parent is reassigned to a different owner
• Share / Unshare — when the parent is shared or unshared
• Delete — when the parent is deleted
• Reparent — when the parent lookup is changed

The settings that matter most for intersect tables are Delete and Assign.

Delete Cascade

When a Professional record is deleted, what should happen to their Professional Certification rows?

• Cascade (Delete related): All their intersect rows are deleted. This is clean and prevents orphaned records. It's usually the right choice for the "stronger" parent — the Professional, in this case. If Jane Smith's record is deleted, her certification history goes with it.
• Restrict (Block delete if related rows exist): The Professional record cannot be deleted while Professional Certification rows reference it. This is appropriate when you want to force explicit cleanup before deletion — useful in compliance scenarios where the certification history must be archived first.
• Remove Link (Set lookup to null): The Professional Certification rows remain but the cr_professionalid lookup is set to null. This creates orphaned intersect rows — almost never what you want for a required lookup.

For the Certification side, think carefully. If someone deletes the "AWS SAA" certification type, should all the Professional Certification records documenting who earned it also be deleted? Often the answer is Restrict — you shouldn't be able to delete a certification type that professionals currently hold. Require the administrator to first revoke or expire those records before the certification type can be removed.

This asymmetric cascade behavior — Cascade on the Professional side, Restrict on the Certification side — is a natural fit for most intersect scenarios where one parent represents a person/entity (whose records might be deactivated or purged) and the other represents a reference type (which should be protected).

Assign Cascade

When a Professional record is reassigned to a different owner (say, when an HR admin transfers the record from one business unit to another), should the Professional Certification rows also be reassigned?

For intersect tables, the typical answer is Cascade — the intersect rows should follow the parent. A professional's certification records should be owned by the same user/team that owns the professional record. This ensures that security roles applied at the record-owner level behave consistently.

But if your intersect rows are owned by a separate team — say, a Certifications Administration team that manages all certification records regardless of which business unit the professional belongs to — then you'd set this to No Cascade.

---

Security Roles for Intersect Tables

Custom intersect tables require explicit security role configuration. A mistake many makers make is granting permissions on the parent tables without thinking about the intersect table, then wondering why users can see the Professional and Certification records but can't create associations.

For each security role that interacts with certifications, you need to configure Professional Certification privileges independently:

Role	Create	Read	Write	Delete	Append	Append To
Professional (self)	Business Unit	Business Unit	Business Unit	None	Business Unit	Business Unit
HR Admin	Organization	Organization	Organization	Organization	Organization	Organization
Certifications Manager	Business Unit	Organization	Business Unit	Business Unit	Business Unit	Business Unit
Read-Only	None	Organization	None	None	None	None

The Append and Append To privileges are specifically relevant to intersect tables. "Append" controls whether a user can associate a Professional Certification row with a Certification. "Append To" controls whether a user can associate a Professional with a Professional Certification row. Both must be granted for the association to work. Many security configuration bugs on intersect tables trace back to missing Append privileges.

For detailed guidance on mapping out security roles against table privileges, see Model-Driven App Security: Configuring Security Roles, Field Permissions, and Team-Based Access for Table Data.

---

Adding Business Rules to Intersect Rows

This is where the custom intersect pattern completely outclasses native N:N. Native junction tables can't carry business rules. Custom intersect tables can, and this is enormously valuable.

Consider these rules for Professional Certification:

Rule 1: Expiration date must be after earned date If cr_expiration_date is set and is less than or equal to cr_earned_date, show an error and block the save.

Rule 2: Score is required when certification has a pass threshold If the related certification's cr_requires_score column is true, then cr_score is required on the Professional Certification row.

Rule 3: Training provider is required for third-party certifications If cr_certification.cr_cert_type equals "Third Party", then cr_training_provider is required.

Rules 1 and 3 can be implemented as straightforward Dataverse business rules on the Professional Certification table. Rule 2 is trickier — it requires referencing a column on the related Certification table. Business rules in Dataverse can't natively reference a parent table's column directly. You have two options:

• Denormalize the value: Add a calculated column on Professional Certification that mirrors the parent's cr_requires_score value. Then write the business rule against that local column. This works, but adds maintenance overhead if the parent column ever changes.
• Use a Power Automate flow or plugin: A cloud flow triggered on Professional Certification create/update can perform the cross-table validation. More robust, but adds latency and complexity.

For the business rule implementation itself — combining visibility, requirement, and lock behaviors across conditions — the patterns in Configuring Dataverse Column-Level Business Rules and Multi-Condition Logic: Combining Visibility, Lock, and Requirement Rules Across Form Scopes will serve you well.

---

Calculated and Rollup Columns on Intersect Tables

Calculated columns on intersect tables open up interesting possibilities. You can derive values that are meaningful precisely because they sit at the intersection of two entities.

Days Until Expiration: A calculated column using DIFFINDAYS(TODAY(), cr_expiration_date) gives you an integer representing how many days until the certification expires. This becomes a powerful view filter: show all certifications expiring in the next 30 days. Surface this on the Professional form's subgrid to give HR managers immediate visibility into renewals needed.

Certification Age: DIFFINDAYS(cr_earned_date, TODAY()) tells you how long someone has held a certification. Use this in views to identify long-tenured certified professionals for recognition programs.

Validity Status (Calculated Choice): You can build a calculated column that derives status from dates — if today is past the expiration date and the status is still "Active," flag it as "Overdue Renewal." This kind of derived status is valuable for dashboards and alerts.

Rollup columns on parent tables become possible and meaningful with a custom intersect. On the Professional table, a rollup column can count active certifications: COUNT(ProfessionalCertifications WHERE cr_status = "Active"). On the Certification table, count how many professionals hold it. These rollup columns make the Professional record itself more informative without requiring a separate dashboard query. For implementation details, see Configuring Dataverse Calculated Columns and Multi-Table Rollup Columns in Model-Driven Apps.

---

Advanced Pattern: Polymorphic Intersects

Sometimes your intersect table needs to associate with one of several possible parent tables, not just one. Consider a "Skill Endorsement" scenario: an employee can endorse a colleague's skill, but the colleague might be stored in the Employee table, the Contractor table, or the Partner table.

The standard approach here is a polymorphic lookup — a Customer-style lookup that can point to multiple tables. On the intersect table, instead of a fixed lookup to Employee, you'd have a polymorphic lookup that can point to any of the three tables.

This is powerful but complex, and the model-driven app UI has limitations with polymorphic lookups in subgrids. The filtering behavior — "show only rows where the polymorphic lookup points to the current record" — requires additional configuration compared to a simple lookup subgrid. Explore this pattern in depth in Configuring Dataverse Polymorphic Lookups and Customer Columns: Modeling Multi-Table Relationships in Model-Driven Apps.

---

Advanced Pattern: Self-Referential Intersect Tables

Another sophisticated use case is when both sides of the M:N relationship point to the same table. Think of a mentorship program: a Professional can mentor many other Professionals, and a Professional can be mentored by many Professionals.

The intersect table (let's call it "Mentorship") has two lookups to Professional:

• cr_mentor → Professional
• cr_mentee → Professional

Additional columns: start date, end date, mentorship focus area, status.

The design challenge: on the Professional form, you want two separate subgrids — one showing records where this Professional is the mentor, another showing where they're the mentee. Both subgrids use the Mentorship table, but filter on different lookup columns.

The platform's "Show related records" behavior on a subgrid will filter by the relationship name, not just the table name. Since you have two distinct relationships from Professional to Mentorship (one via cr_mentor, one via cr_mentee), you can configure each subgrid to use the appropriate relationship for its filter. This is one of the elegant capabilities that custom intersect tables enable — native N:N has no equivalent.

---

FetchXML and API Access to Intersect Rows

One of the most practical differences between native N:N and custom intersect tables is how you query them.

With a native N:N, querying the intersection through the Dataverse API requires joining through the hidden intersect entity using a link-entity in FetchXML. The intersect entity name is semi-opaque, and you can only return columns from the two parent tables — not from the junction itself (because there are no extra columns to return).

With a custom intersect table, querying is straightforward:

<fetch version="1.0" output-format="xml-platform" mapping="logical">
  <entity name="cr_professionalcertification">
    <attribute name="cr_professionalcertificationid" />
    <attribute name="cr_earned_date" />
    <attribute name="cr_expiration_date" />
    <attribute name="cr_score" />
    <attribute name="cr_status" />
    <link-entity name="cr_professional" from="cr_professionalid" 
                 to="cr_professionalid" alias="prof">
      <attribute name="cr_fullname" />
      <attribute name="cr_department" />
    </link-entity>
    <link-entity name="cr_certification" from="cr_certificationid" 
                 to="cr_certificationid" alias="cert">
      <attribute name="cr_name" />
      <attribute name="cr_level" />
    </link-entity>
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="cr_expiration_date" operator="next-x-days" value="60" />
    </filter>
    <order attribute="cr_expiration_date" descending="false" />
  </entity>
</fetch>

This query returns all active Professional Certification records expiring in the next 60 days, joined to both parent tables for display columns. You can use this FetchXML in advanced find, as the basis for a custom view, or in a Power Automate cloud flow that generates a weekly "Expiring Certifications" email report.

The API call using OData is equally clean:

GET /api/data/v9.2/cr_professionalcertifications
    ?$select=cr_earned_date,cr_expiration_date,cr_score,cr_status
    &$expand=cr_professionalid($select=cr_fullname),cr_certificationid($select=cr_name)
    &$filter=statecode eq 0 and cr_expiration_date le 2024-04-01
    &$orderby=cr_expiration_date asc

This is the direct OData equivalent — explicit, readable, and returning only what you need. Try doing this kind of targeted query with a native N:N junction table and you'll quickly appreciate why custom intersects are architecturally superior for data-rich scenarios.

---

Hands-On Exercise

This exercise builds the complete Professional Certification model from this lesson. Budget approximately 90–120 minutes for a thorough implementation.

Setup

• Open your development environment
• Create or open a solution in the Power Apps maker portal
• You'll need an existing Professional table (or create a basic one with Full Name and Email columns) and a Certification table (with Name, Level choice column, and Issuing Body text column)

Part 1: Create the Intersect Table (20 minutes)

1. In your solution, create a new table named "Professional Certification" with schema name following your prefix convention.
2. Rename the primary column display name from "Name" to "Reference" — you'll make this a formula column shortly.
3. Enable auditing on the table.
4. Add the following columns:
   • Earned Date (Date Only, Required)
   • Expiration Date (Date Only, Optional)
   • Score (Decimal Number, Min 0, Max 100, Optional)
   • Certification Status (Choice: Active, Expired, Revoked, Pending — Required, Default: Active)
   • Notes (Multiline Text, Optional)

Part 2: Create the Relationships (15 minutes)

5. From the Professional table, create a one-to-many relationship to Professional Certification:

   • Lookup column name: "Professional"
   • Lookup schema name: cr_professionalid
   • Delete behavior: Cascade
   • Assign behavior: Cascade

6. From the Certification table, create a one-to-many relationship to Professional Certification:

   • Lookup column name: "Certification"
   • Lookup schema name: cr_certificationid
   • Delete behavior: Restrict
   • Assign behavior: No Cascade

7. Add an alternate key on Professional Certification using both cr_professionalid and cr_certificationid to enforce uniqueness.

Part 3: Build Views (20 minutes)

8. Create an "Active Certifications" view for Professional Certification:

   • Filter: statecode = 0 (Active) AND Certification Status = Active
   • Columns: Professional, Certification, Earned Date, Expiration Date, Score, Certification Status
   • Sort: Expiration Date ascending

9. Create an "Expiring in 60 Days" view:

   • Filter: statecode = 0 AND Expiration Date within next 60 days
   • Sort: Expiration Date ascending

Part 4: Build the Form (20 minutes)

10. Create a main form for Professional Certification with:
    • Header: Professional lookup, Certification lookup
    • Main section: Earned Date, Expiration Date, Score, Certification Status
    • Second section: Notes
    • A tab for Related Details with quick view forms showing Professional and Certification details

Part 5: Add Subgrids to Parent Forms (15 minutes)

11. Open the Professional table's main form. Add a "Certifications" tab containing a subgrid:

    • Table: Professional Certification
    • Show related records: Yes
    • Default view: Active Certifications

12. Open the Certification table's main form. Add a "Certified Professionals" tab containing a subgrid:

    • Table: Professional Certification
    • Show related records: Yes
    • Default view: Active Certifications (or create a "Professionals with this Certification" view sorted by Professional name)

Part 6: Configure a Business Rule (10 minutes)

13. On the Professional Certification table, create a business rule:
    • Condition: Expiration Date is not null AND Expiration Date <= Earned Date
    • Action: Show error message "Expiration date must be after the earned date." Block save.
    • Scope: All Forms

Validation

Test your implementation by:

• Creating a Professional record and adding certifications via the subgrid
• Verifying that the subgrid on the Professional form shows only that professional's certifications
• Verifying that the subgrid on the Certification form shows only holders of that certification
• Attempting to enter an expiration date before the earned date and confirming the business rule fires
• Attempting to create a duplicate association and confirming the alternate key blocks it

---

Common Mistakes & Troubleshooting

"The subgrid shows all Professional Certification records, not just the related ones"

Cause: The "Show related records" toggle on the subgrid is not enabled, or the subgrid is configured against the table directly rather than through the relationship.

Fix: Edit the subgrid properties in the form designer. Ensure "Show related records" is set to Yes. If the option isn't appearing, verify that the lookup relationship from Professional to Professional Certification actually exists — the subgrid can only filter by relationship if the relationship is defined.

"Users can see Professional records but can't create associations in the subgrid"

Cause: Missing Append or Append To privileges on the Professional Certification security role configuration.

Fix: Open the security role, navigate to the Professional Certification table, and verify that both Append (on Professional Certification) and Append To (on Professional and Certification tables) are set appropriately for the user's access level. This is one of the most common intersect table security issues.

"The alternate key is not blocking duplicate creation"

Cause: Alternate keys enforce uniqueness at the database level, but there's a lag after creation before the index is fully built. Also, if either lookup value is null, the uniqueness constraint may not apply as expected (database null behavior: null ≠ null).

Fix: After creating the alternate key, wait for the "Active" status in the alternate key configuration panel — this confirms the index is built. Ensure both lookup columns are marked as Required so neither can be null on a valid row.

"Cascade delete is taking a very long time or timing out"

Cause: A Professional record has hundreds or thousands of associated Professional Certification rows. The cascade delete is processing each one individually.

Fix: For large-scale cleanup, switch the Professional → Professional Certification relationship delete behavior to Restrict temporarily, perform a bulk delete of the intersect rows using Advanced Find or a Power Automate flow before deleting the parent, then proceed with the parent delete. For future prevention, consider asynchronous cascade processing settings in your environment.

"Business rules referencing the parent Certification's columns don't work"

Cause: Dataverse business rules cannot cross table boundaries — they can only reference columns on the same table as the rule, plus columns from related tables that are exposed via quick view forms for display only.

Fix: Either denormalize the needed value to the intersect table via a calculated column, or move the validation to a Power Automate cloud flow running in synchronous mode (before-save trigger using Dataverse connector with direct API call), or implement a server-side plugin for the strictest enforcement.

"Quick view forms on the intersect record are not showing data"

Cause: Quick view forms require the lookup to be populated before the form saves. On a new record that hasn't been saved yet, the quick view form will be blank because the row doesn't exist yet in Dataverse.

Fix: This is expected behavior. Quick view forms display data from existing related records. Save the intersect record first, then reopen it to see the quick view panels populated. You can mitigate user confusion by placing a note on the form: "Related details will appear after saving."

---

Summary & Next Steps

You've now moved well beyond the surface-level understanding of Dataverse many-to-many relationships. The custom intersect table pattern — two explicit one-to-many relationships connected through a table you design — is the right choice whenever your M:N relationship carries data, requires validation, needs auditing, or demands security controls beyond simple association.

The core principles to carry forward:

• Own your junction table. Don't let the platform generate and hide the intersect from you when your relationship is data-rich.
• Configure cascade behaviors asymmetrically. The two sides of your intersect often have different delete and assign requirements — model them correctly from the start.
• Filtered subgrids are your UI. The platform's "Show related records" behavior makes intersect subgrids look and behave like native relationship panels.
• Alternate keys are your data integrity layer. Don't rely on UI validation alone for uniqueness — enforce it at the database level.
• Security requires both parent and intersect permissions. Always audit Append and Append To privileges when users report association failures.

Where to go from here:

If you're building complex reporting against these intersect tables, explore how to surface aggregated data using rollup columns on the parent tables (covered in Configuring Dataverse Calculated Columns and Multi-Table Rollup Columns in Model-Driven Apps) and how to build dashboards that visualize intersect data across populations.

If your intersect table needs to feed into a business process — for example, a multi-stage certification approval workflow — you can attach a Business Process Flow directly to the Professional Certification table and guide users through the verification and approval stages.

And if you need to import historical certification data from an existing HR system into your new model, the import and upsert patterns in Importing and Migrating Data into Dataverse: Excel Import, Dataflows, and Upserts will help you load intersect rows correctly while respecting the referential integrity of both parent relationships.

The custom intersect table is one of the highest-leverage design patterns in the Dataverse toolkit. Use it deliberately, configure it carefully, and it will serve you reliably at scale.