Dataverse Many-to-Many Relationships With Extra Fields That Feel Native

Why can a native many-to-many relationship in Dataverse not carry extra fields?

Because the table that holds the pairs is not yours. When you create a native N:N relationship, Dataverse creates a hidden intersect table with the two identifiers and nothing else. You can read it in FetchXML and associate or disassociate rows through the API, but you cannot add columns, forms, business rules or security of its own to it. So there is nowhere to store "quantity 3", "role: technical contact" or "valid until March".

The moment the link has to say something about itself, the answer is a junction table: a custom table with a required lookup to each side, which Dataverse treats as two ordinary one-to-many relationships. When a native relationship is still enough, when Connections fit better, and how to set cascade and delete behaviour on the junction lookups are covered in detail in our guide to linking multiple products to one case. That guide uses cases and products, but the architecture is the same for contacts and projects, accounts and programmes, or assets and contracts. This page picks up where it stops: making the junction table something users accept.

Why do users reject a junction table even when the data model is right?

Because the default experience of a junction table is worse than the native relationship they are used to, and users judge the model by the clicks, not the schema. With a native N:N subgrid, Add Existing opens a lookup, the user ticks several records and the links appear. With an unimproved junction table, the same subgrid offers New, which opens a full form for one row at a time, and Add Existing, which offers existing junction rows rather than the records the user actually wants to link.

What does the junction table need before any user sees it?

A few small decisions make every later screen easier. Most of them take minutes at design time and a migration to fix later.

• Name it after the business concept, not the mechanics: "Project contact" or "Contract asset", not "Contact project link". Set the plural display name too, because it becomes the subgrid and related tab label.
• Make both lookups required, and set the relationship behaviours deliberately, as described in the multiple products guide.
• Fill the primary name column automatically on create, for example "Finance lead on Project Atlas", with a small pre-operation plugin, so lookups, audit history and search never show a blank name.
• Only add the extra columns someone will actually read. Every column is another thing to fill in inline.
• Build one view per parent: on the project form, show the contact name and role first; on the contact form, show the project name and dates first. Views can include columns from the related record through the lookup, so users see the linked record, not the junction row.
• Decide ownership early. Organization owned is simplest when anyone who can see the parent can see its links; user or team owned with cascading assign keeps link security aligned with the parent.

How do editable subgrids make junction rows feel part of the parent form?

Put the junction table on each parent form as a subgrid, then switch the subgrid to the Power Apps grid control with editing enabled. Users then change the role, quantity or dates of each link directly in the row, in the same way they would edit cells in a spreadsheet, without opening a form per row. For most departments this single change removes the loudest complaint.

Keep the editable columns few and the order deliberate: the linked record name first and read-only, then the attributes people actually change. Lookups, choices, dates and numbers edit inline; some column types and some form events behave differently in an editable grid than on a form, so check current Microsoft documentation for the grid control you use and test the business rules that matter before relying on them. Business logic that protects data integrity belongs on the server, in a plugin or a validation that runs whatever the entry route, not only in grid events. Show the same subgrid on both sides of the relationship where both sides need it, each with its own view.

How should quick create work on the junction table?

Enable quick create on the junction table and build a short quick create form with the other lookup and the handful of attributes needed when a link is first made. When a user selects New from the subgrid on the parent form, the quick create form opens in a side pane with the parent lookup already filled in from the context, so the user picks one record, sets the role or quantity and saves, without leaving the parent.

That handles the one-at-a-time case well. Keep the quick create form to the fields a user knows at the moment of linking; everything else can be edited inline later. Filter the other lookup to active and relevant records, and order its view by what users search for most. Quick create is still one link per save, which is why the next section matters for anyone who links records in bulk.

How do you add several related records at once when the lookup is not multi-select?

A lookup column on a junction row holds one value, so there is no native "tick several" experience for a one-to-many subgrid. You add it with a command, and there are three sound ways to build it. Whichever you choose, the add action should skip records that are already linked and refresh the subgrid when it finishes, so it behaves like the native Add Existing users are comparing it with.

• A Power Automate flow can also create the rows from a selection, but a cloud flow runs asynchronously, so the user sees an empty subgrid for a moment and must refresh. It suits background bulk linking better than an interactive add button.
• Combine the patterns where useful: a multi-select lookup in the browser that calls the custom API gives the familiar dialog with server-side rules.
• Test the button against the security roles of real users, not only as an administrator, including users who can read the parent but not create links.

How does an alternate key on the pair prevent duplicate links?

Define an alternate key across the two lookup columns of the junction table. Dataverse then refuses a second row with the same pair, whether it comes from quick create, the grid, the add button, an import or an integration, and integrations get upsert by key, so a retried message updates the existing link rather than duplicating it.

Two practical notes. First, the key is backed by an index that is built by a system job, and it fails to activate if duplicates already exist, so clean existing duplicates before creating it and check its status afterwards. Second, a duplicate refused by the key surfaces as an error, which is correct but not friendly. Make the add button filter out already linked records in its lookup, or skip them silently in the custom API, so users rarely meet the error at all. If the business genuinely allows the same pair twice, for example the same contact on a project in two different roles, include the role in the key rather than dropping the key.

When does a PCF control close the remaining gap?

When the department's work is linking, not occasionally adding a link. Configuration, the grid control and a good add button get most tables close to native. A PCF control is worth it where users link many records many times a day, or where the right interface is not a grid at all.

• A dataset control on the junction subgrid that shows available records as a searchable checklist or tag picker, creating and removing junction rows as users tick and untick, with the attributes editable inline.
• A matrix view, such as contacts down the side and roles across the top, where each ticked cell is a junction row.
• Custom cell renderers or editors for the Power Apps grid control, where only one or two columns need a better editing experience; check current Microsoft documentation for the supported customisation points.
• Keep the rules on the server anyway: the alternate key and any validation plugin still apply when the control writes through the Web API, so the control is an interface, never the only guard.

How do you pilot the junction table with a sceptical department?

Let the department that objects most test it first, on their own work, before anyone commits to a rollout. Scepticism about a junction table is usually about clicks, and clicks are easiest to settle by watching real tasks rather than arguing about the model.

• Agree acceptance criteria with the department before the pilot starts: the tasks that must be as easy as today, and the details they have been asking to record.
• Build the pilot in a sandbox with a copy of their real records, with the editable subgrid, quick create, add button and alternate key already in place.
• Script their three or four most common linking tasks and sit with several users while they do them, noting where they hesitate.
• Show what the model gives them that the old relationship could not: the role, dates or quantities they previously kept in notes or spreadsheets.
• Fix the top objections within the pilot period, then run the tasks again with the same users.
• Roll out department by department. Copy existing native pairs from the intersect table into the junction table by upserting on the alternate key, and keep the old relationship readable until the last department has moved; the safeguards for changing a live model are in our Dataverse data model remediation guide.

Is a Dataverse junction table the right platform choice for this data?

For organisations already on Dynamics 365 or Power Apps, yes: the junction table sits in the same security model, audit, views and reporting as the rest of the data, and the user experience gap is closed with configuration, a small script or custom API and, where it pays off, a PCF control. What is worth checking first is whether the relationship really needs attributes, because a native N:N relationship is cheaper to build and keep when it does not.

We recommend the right solution - whether that's Microsoft Dynamics 365, Power Platform, or a custom-built CRM. Some businesses need the Microsoft ecosystem. Others need full control without licensing. We deliver both. If the data model is really a product in its own right, with many attributed relationships and interfaces built entirely around linking, a custom-built CRM on React, Node.js, PostgreSQL or .NET gives full control over the interface without Microsoft licensing.

How does Solzet help build junction tables users accept?

We start by watching how the department links records today, then design the junction table and its keys, build the editable subgrids, quick create and multi-select add pattern, and run the pilot with the users most likely to object. Where the remaining gap matters, our PCF controls development team builds the dataset control or grid customisation that closes it, and existing native relationships are migrated across without a freeze.

The work is done by senior consultants and full-stack developers delivering remotely from Yerevan, Armenia, with 8+ years of Dynamics 365 Customer Engagement and Power Platform work, directly for your team or white-label for Microsoft partners. The modelling choice between a native relationship, Connections and a junction table is covered in our multiple products per case guide.