Relationship Types NN

In the last step, we learned about Relationship Types 1N – Mappings. Now, let’s discuss Relationship Types NN.

Introduction

1:N (one-to-many) entity relationships establish a hierarchy between records. With N:N (many-to-many) relationships there is no explicit hierarchy. There are no lookup fields or behaviours to configure. Records created using N:N relationships can be considered peers and the relationship is reciprocal.

For example, for the companies (Accounts) you may want to record information about the board members (Contacts). One company may have many board members while each contact can be on the boards of multiple companies.

With N:N relationships a special hidden entity is created called a Relationship (or intersect) entity. This entity has a N:1 relationship with each of the related entities and only stores the necessary values to define the relationship. You can’t add custom fields to a relationship entity, and the primary use of the intersect entity is to link records for the reporting purposes.

In the app, the operations of adding or removing records from the relationship are called an associate and disassociate, respectively.

Creating N:N Relationships

Before you create a custom N:N entity relationship, evaluate whether using an existing entity relationship, provided by a Common Data Model, would meet your requirements.

Note: Not all entities have the many-to-many relationship type available, in which case the option to create the N:N relationship will be disabled in the designer user interface.

There are two designers you can use to create and edit N:N relationships:

Designer	Description
PowerApps portal (Web.powerapps.com)	Provides an easy streamlined experience to create the relationship, but some settings may not be available.
Classic Solution Explorer	Classic experience, provides for more flexibility for less common requirements: Navigation pane options for model-driven apps and whether the relationship is hidden in advanced find

PowerApps Portal

When creating N:N relationship in PowerApps portal, you need to select the related entity and specify names for the relationship and relationship entity.

The values for Relationship name and Relationship entity name fields are generated based on the entities chosen. However, it is always good idea to append some additional information about the purpose of the relationship. It will assist report designers and developers who use these values. It also helps to make these fields unique as the system enforces uniqueness on the values.

Once an entity is saved, there is nothing that can be changed for N:N relationship using PowerApps portal. To edit additional properties of the relationship for model-driven apps use the classic solution explorer.

Classic Solution Explorer

Solution explorer provides an alternative to edit N:N entity relationships for Common Data Service for Apps because certain options can only be set using solution explorer.

The following entity relationship properties can be edited after the relationship is created. For both entities, you can edit the display option fields which control how the related entities are displayed for model-driven apps.

Field	Description
Display Option	How the related entity list should be displayed: Do not Display – Hides the related entities for this relationship; Use Custom Label – When this option is chosen, the Custom Label field is enabled so that you can specify the localisable text to be used instead of the plural name; Use Plural Name – Use the plural display name defined for the related entity.
Custom Label	Specify the localisable text to be used instead of the plural name when you select Use Custom Label as the Display Option.
Display Area	Select one of the available groupings to display this list. The available options are: Details (for the Common group), Marketing, Sales, and Service.
Display Order	Controls where the navigation item will be included within the selected display area. The range of allowed numbers begins with 10,000. Navigation pane items with a lower value appear above other relationships with a higher value.
Searchable	Whether this relationship should be visible within Advanced Find in model-driven apps. Select No if you don’t want the relationship to be available in Advanced Find when working with the conditions for either entity.

After you edit these properties you must publish customisations before they will take effect in model-driven apps.

Delete Relationships

While viewing entity relationships in PowerApps portal, select the N:N relationship you want to delete. You can use the Delete relationship command from the command bar or from the row context menu when you click the ellipses (…).

Deleting the relationship will delete the relationship entity created. All data connecting entities using the relationship will be lost.

Note: You will not be able to delete a relationship that has dependencies. For example, if you have added a sub-grid to a form using the relationship, you must remove the sub-grid from the form and publish the form before you delete the relationship.

Manual N:N Relationships

Often there is a need to record additional information about the N:N relationships between the entities. For example, in the example with the board membership you may want to record the start and end date for each membership, the number of votes the member holds, etc. With the native N:N relationships this is not possible because the intersect entity, where information about the relationship is stored, is hidden and not available for customisations. This is where you often will see the references to a manual N:N relationship between two entities.

A manual many-to-many (N:N) relationship is a convenient way to describe how you can use a custom intersect entity that has two N:1 relationships (and two lookup fields, respectively) to the two entities that you want to connect.

This is known as a manual many-to-many (N:N) relationship because you create a custom entity instead of having the system create and maintain a hidden intersect entity. A manual many-to-many (N:N) relationship is not a true type of relationship, and will not appear as the N:N relationship for an entity.

The main benefit of creating a many-to-many (N:N) relationship is that you can add custom fields to the intersect entity. In a native many-to-many (N:N) relationship, the intersect entity is hidden and custom fields cannot be added.

The main disadvantage of the custom intersect entity is a more complicated user interface because the records in the intersect entity must be created explicitly and there is no direct navigation between N:N entities, e.g. it is not possible to display related records in a sub-grid bypassing the intersect entity.

When you decide on the type of many-to-many (N:N) relationship to create, you have to know whether the organisation must record additional information about the intersect entity. If additional information is not required, native N:N relationships have the advantage of much simpler user experience when associating and disassociating records.

Next up, let’s continue with Relationships, as we uncover Relationship Types NN – Connections.