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:

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.

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.