Wow! That title was a mouthful! 😊
Let me set the stage.
I have the following Dataverse tables:
Many To Many A ItemMany To Many B ItemMany To Many C Item, has a Lookup column toMany To Many A ItemandMany 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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
// 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
// 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.
|
1 |
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!