Adventures with Power Apps: Dataverse, Many-to-Many Relationships and Combo Box Controls

Wow! That title was a mouthful! 😊

Let me set the stage.

I have the following Dataverse tables:

  • Many To Many A Item
  • Many To Many B Item
  • Many To Many C Item, has a Lookup column to Many To Many A Item and Many To Many B Item.
Figure 1 – Entity relationship diagram

I want to create a Canvas App for managing records in the Many To Many A Item table, that included managing the Many To Many C Item records.

Figure 2 – Gallery populated with Many To Many A Items

I set the Items property of the Gallery to the Many To Many A Item table.

Figure 3 – Edit form for Many To Many A Items

For the edit a Many To Many A Item record, I added a Text Input to capture the Name.

I then added a Combo Box to select multiple Many To Many B Item records, which would end up being captured in the Many To Many C Item table.

I set the Items property of the Combo Box to Many To Many B Item table.

Now to start wiring things up!

The OnSelect of the New Parent A Item button contained the following code:

This code sets the gblManyToManyAItem variable to the Defaults for the Dataverse table and sets the edit mode to Add.

The OnSelect of the Edit Parent A Item button for an existing Many To Many A Item contained the following code:

This code sets the gblManyToManyAItem variable to the currently selected item in the Gallery and sets the edit mode to Edit.

I then collected all the child records in the Many To Many C Item table for the selected Many To Many A Item record.

I then collected all the Many To Many B Item records based on the previous collection into a collection called colManyToManyBItems.

I set the Combo Box property DefaultSelectedItems to the colManyToManyBItems.

Lastly, I call the Reset, which forces the Combo Box to display the DefaultSelectedItems.

Now on to saving the changes to Dataverse!

In the OnSelect of my Save Changes button I have the following code:

The value of gblMode will determine whether we Edit or Add the Many To Many A Item.

Now I populate two collections, one that includes the selected Many To Many B Item records and one that includes the original values.

I now make my two passes at the data to determine whether I need to add a record to the Many To Many CItem table or remove a record.

Lastly, I select the Cancel Changes button to reset the form.

Not the prettiest code, but it does the job!

Check out it for yourself by importing https://mattruma.com/wp-content/uploads/2024/03/ManyToManySolution_1_0_0_0.zip into your own environment.

A Model-Driven App is included to help pre-populate the data for testing.

Figure 4- Model-Driven App

Enjoy!


Discover more from Matt Ruma

Subscribe to get the latest posts sent to your email.

Leave a Reply

Your email address will not be published. Required fields are marked *