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.

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.

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

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:

Set(gblManyToManyAItem, Defaults('Many To Many A Items'));
Set(gblMode, "Add");

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:

Set(
    gblManyToManyAItem,
    galManyToManyAItems.Selected
);
Set(
    gblMode,
    "Edit"
);
ClearCollect(
    colManyToManyCItems,
    Filter(
        'Many To Many C Items',
        'Many To Many A Item'.'Many To Many A Item' = galManyToManyAItems.Selected.'Many To Many A Item'
    )
);
ClearCollect(
    colManyToManyBItems,
    Blank()
);
ForAll(
    colManyToManyCItems,
    Collect(
        colManyToManyBItems,
        ThisRecord.'Many To Many B Item'
    )
);
// Force the control to display the correct items
Reset(cbxManyToManyBItem);

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:

If(
    gblMode = "Edit",
    Patch(
        'Many To Many A Items',
        galManyToManyAItems.Selected,
        {Name: txtName.Text}
    ),
    Set(
        gblManyToManyAItem,
        Patch(
            'Many To Many A Items',
            Defaults('Many To Many A Items'),
            {Name: txtName.Text}
        )
    )
);

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.

// Gather all the selected items
ClearCollect(
    colManyToManyBItemsSelected,
    Blank()
);
ForAll(
    cbxManyToManyBItem.SelectedItems,
    Collect(
        colManyToManyBItemsSelected,
        {Id: ThisRecord.'Many To Many B Item'}
    )
);
// Gather all the original items
ClearCollect(
    colManyToManyBItemsOriginal,
    Blank()
);
ForAll(
    colManyToManyBItems,
    Collect(
        colManyToManyBItemsOriginal,
        {Id: ThisRecord.firefly_manytomanybitemid}
    )
);

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.

// Add Records
ForAll(
    Filter(
        colManyToManyBItemsSelected,
        Not(Id in colManyToManyBItemsOriginal.Id)
    ),
    Patch(
        'Many To Many C Items',
        Defaults('Many To Many C Items'),
        {
            'Many To Many A Item': LookUp(
                'Many To Many A Items',
                'Many To Many A Item' = gblManyToManyAItem.'Many To Many A Item'
            ),
            'Many To Many B Item': LookUp(
                'Many To Many B Items',
                'Many To Many B Item' = Id
            )
        }
    )
);
// Remove Records
ForAll(
    Filter(
        colManyToManyBItemsOriginal,
        Not(Id in colManyToManyBItemsSelected.Id)
    ),
    Remove(
        'Many To Many C Items',
        LookUp(
            'Many To Many C Items',
            'Many To Many A Item'.'Many To Many A Item' = galManyToManyAItems.Selected.'Many To Many A Item' And 'Many To Many B Item'.'Many To Many B Item' = Id
        )
    )
);

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

Select(btnCancelChanges);

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.

Enjoy!