Power Apps Many to Many relationships in Canvas Apps

Many to Many relationships can be painful in Canvas Apps . In this post I’m looking at how we can work with many to many relationships in Power Apps. I will cover the creation of the relationships, updating the relationships and querying the relationships.

Power Apps Many to Many relationships

I started by creating two tables in my solution. The first table is called Author and has a field called Full Name and the second table is called Books and has a column called Title.

Now from my books table I’m creating a relationship called Written by

Now I’m renaming the Relationship name to Written by

Once this relationship has been created I can see the relationship between my authors and books as expected.

Adding records to the tables

To add tables to the tables I’m creating a simple app. So that I can add some Authors and books to my tables,.

Relating records with Many to Many relationships

Now that I’ve created a few book and author records I want to relate the records. By selecting the records in both tables, pressing the written by button will need to set the relationship.

Implementing the Written by button is slightly harder than just relating the two records. WE will need to use the relate function twice. So that the relationship will be created in both directions.

Relate(
    LookUp(
        Books,
        Title = galBooks.Selected.Title
    ).Authors,
    LookUp(
        Authors,
        'Full Name' = galAuthors.Selected.'Full Name'
    )
);
Relate(
    LookUp(
        Authors,
        'Full Name' = galAuthors.Selected.'Full Name'
    ).Books,
    LookUp(
        Books,
        Title = galBooks.Selected.Title
    )
)

To display the related records, we can now add a gallery with a child gallery in it. My outer gallery is called galAuthorAndBooks while the inner gallery is called Books By Author.

For the galAuthorsAndBooks, I’m setting the Items property to Authors, so that the Authors are all displayed.

For the inner gallery I can now reference my Books using the following code:

ThisItem.Books

ThisItem refers to the Itemof the parent gallery.

Querying data across Many to Many relationships

So far we managed to get related records displayed. Noticed that there wasn’t an option to select the actual relationship. Therefore if there were multiple many to many relationships between those two table then things would still get messed up.

The next step in the post is that I want to display the books written by the author that is selected. Easy??

I can see two approaches here. First we could use the gallery that we used earlier.

All we have to do is set the Items of the parent gallery to not load data from the Authors table. Adding the following Filter will now only display the records needed.

Filter(Authors, 'Full Name' = galAuthors.Selected.'Full Name')

The other approach could be something like thisL:

Filter(Books As _Book, 
            CountRows(Filter(_Book.Authors As _Author, _Author.'Full Name' = varSelectedAuthor)) > 0
)

However the _Book.Authors property is not available. In various other constructions like creating collections for both tables, which just over complicates the app. The earlier mentioned nested gallery approach is then simpler.

A 3rd approach is to avoid Many to many relationships all together and create a table which links the tables. However that avoids the out of the box relationship data model a bit.

For more posts on Power Apps, please have a look at my Power Apps User Guide.