Create many-to-many table relationships overview
One-to-many (1:N) table relationships establish a hierarchy between rows. With Many-to-many (N:N) relationships there is no explicit hierarchy. There are no lookup columns or behaviors to configure. Rows created using Many-to-many relationships can be considered peers and the relationship is reciprocal.
One example of a many-to-many relationship is defined between two standard tables included with the Dynamics 365 Sales app. The opportunity table has an N:N relationship with the competitor table. This allows for multiple competitors to be added to the opportunity and multiple opportunities associated with the same competitor.
With Many-to-many relationships a Relationship (or Intersect) table stores the data that associates the tables. This table has a One-to-many table relationship with both of the related tables and only stores the necessary values to define the relationship. You can’t add custom columns to a relationship table and it is never visible in the user interface.
Creating a Many-to-many relationship requires choosing the two tables that you want to participate in the relationship. For model-driven apps you can decide how you want the respective lists to be available within the navigation for each table. These are the same options used for the primary table in 1:N table relationships. More information: Navigation Pane Item for Primary table
Not all tables can be used with Many-to-many relationships. If the table isn't available to be chosen in the designer, you can’t create a new Many-to-many relationship with this table. More information: Developer documentation: table relationship eligibility
There are two designers you can use to create and edit 1:N (one-to-many) or N:1 (many-to-one) relationships:
| Designer | Description |
|---|---|
| Power Apps portal | Provides an easy streamlined experience, but some special settings are not available. More information: Create Many-to-many table relationships in Microsoft Dataverse using Power Apps portal |
| Solution explorer | Not as easy, but provides for more flexibility for less common requirements. More information: Create N:N (many-to-many) table relationships in Dataverse using solution explorer |
Note
You can also create new Many-to-many (N:N) table relationship in your environment using the following:
- Import a solution that contains the definition of the relationship. More information: Import, update, and export solutions
- A developer can use Metadata services to write a program to create and update table relationships. More information: Developer documentation: Customize table relationship metadata
Information in this topic will help you choose which designer you can use.
You should use the Power Apps portal to create and edit Many-to-many (N:N) table relationships unless you need to address any of the following requirements:
- Configure navigation pane options for model-driven apps.
- Hide the relationship from Advanced find in model-driven apps.
See also
Create and edit relationships between tables
Create Many-to-many table relationships in Dataverse using Power Apps portal
Create N:N (many-to-many) table relationships in Dataverse using solution explorer