Relate records in Many-to-Many relationships

Canvas app authors have new tools for working with Common Data Service for Apps relationships: Relate and Unrelate functions.

Many-to-Many relationships

Since December, you have been able to read Many-to-Many relationships using the experimental and then preview CDS connector.  But it has been impossible to relate or unrelate two records and the join table is not exposed to do it manually.  These new functions do exactly these operations.

Many-to-Many relationships appear as a One-to-Many table on both entities.  For example, Products has a Contacts field that is a table of Contacts for each Product, and Contacts has a Products field that is a table of Products for that each Contact.  Many-to-Many relationship are symmetric.

The first argument to these functions is the One-to-Many table that you want to add to or remove from.  Since there can be more than one Many-to-Many relationship between two entities we need to know which one you wish to change, hence we need to know which One-to-Many relationship to manipulate.  The second argument is the record in the other entity that you wish to add or remove.

For example, imagine you have a Products entity with a Many-to-Many relationship to the Contacts entity.  You might have a Gallery control with Items = Products, and then a second  Gallery control with Items = Gallery1.Selected.Contacts to show the Contacts for a selected Product:

If you want to add a Contact to a Product, you ultimately want it to appear in Gallery1.Selected.Contacts.  To accomplish this, you write the formula as if you were going to use the Collect function to add a record to a table, but with the Relate function instead:

Relate( Gallery1.Selected.Contacts, First( Contacts ) )

Where First( Contacts ) is the record you’d like to add.

You can do the reverse with the Unrelate function.  This will remove First( Contacts ) with:

Unrelate( Gallery1.Selected.Contacts, First( Contacts ) )

The documentation for Relate and Unrelate have all the details and step-by-step examples.   One of the examples shows relating the same contact to multiple products:

Many-to-Many in reverse

Since Many-to-Many relationships are symmetric, you can also relate a Product with a Contact in the opposite direction, starting from the Contact entity:

Relate( First( Contacts ).Products, Gallery1.Selected )
Unrelate( First( Contacts ).Products, Gallery1.Selected )

The final example is the docs is an app that relates and unrelates records in both directions and shows the results from both directions.

One-to-Many relationships

These new functions can also be applied to One-to-Many relationships.   Since there is a foreign key field in One-to-Many relationships you can already relate and unrelate today by simply manipulating this field.   These functions give you another way to accomplish this.

Imagine you have a Reservations entity that is in a One-to-Many relationship with Products.  Just as you did above, you can use Relate and Unrelate to add and remove a Reservation from the selected Product in a Gallery control:

Relate( Gallery1.Selected.Reservations, First( Reservations ) )

which does the equivalent of:

Patch( Reservations, First( Reservations ), { 'Associated Product': Gallery1.Selected } )

And to unrelate:

Unrelate( Gallery1.Selected.Reservations, First( Reservations ) )

which does the equivalent of:

Patch( Reservations, First( Reservations ), { 'Associated Product': blank() } )

Another of the documentation examples, the app below will perform Relate and Unrelate operations on a One-to-Many relationship, showing how a Reservation can be moved from one product to another and how Reservations that are not related to a product can be managed:

Requirements

This feature only works with the Common Data Service for Apps.  The new Relational features preview switch must also be turned on (it is by default for new apps):

Feedback

Any and all feedback here and in the community forum is most welcome!