CDS Many to Many in canvas app - trying to replicate model driven form/sub grid experience

[MorkandMindy]

Hi,

I'm using a many to many relationship in CDS tables where Accounts can have multiple Proposals and Proposals can have multiple  Accounts.  I've been using this example here  for relate and unrelating functions which is great but has some limitations.

I'm trying to replicate the experience you get in a model driven form in CDS - on a main form you can go to a subgrid connected to the current selected record and add or remove many-to-many record instantly just by clicking a button.  It seems much, much more complicated in a canvas app!

I'd like to make sure that the user is only working on the proposal record they have just created from the previous screen (I can't trust them to pick the right one themselves in a gallery) and relate accounts only to this proposal.    Then clear the galleries when they move to the next screen. 

So I have two galleries:  Gallery1 (Items: Proposals) and Gallery2 (Items: Gallery1.Selected.Accounts).  I have a button that relates account records to Gallery2 from a combobox using Relate( Gallery1.Selected.Accounts, ComboBox1.Selected ) .

If I try to filter Gallery1 proposals by using a variable created in the last screen (varProposalID) Gallery2 will not show any data when it's in edit mode.

If I sort Gallery1 proposals by last created Sort('Proposals','Created On',Descending) it will show Gallery2 in edit mode, but I'm having problems clearing these galleries when users exit the screen.  I'm also nervous the user could end up in the last record created- but it may not be necessarily theirs.  I've tried a collection with a filter in OnVisible for the screen against Gallery1 but it stops Gallery2 appearing in edit again.  

I've also got a revenue total from Gallery2 Text(Sum(Gallery2.AllItems,'Revenue'),"[$-en-GB]£#,##0.00") for all the accounts I've related which I sum and patch back into the newly created proposal record when the user navigates to next screen.

I don't know if I'm just taking a poor approach here and I should be using a form instead of Gallery1 for my newly created Proposal (as I only want to see and link all Gallery2 accounts to one proposal record at a time) but I can't see how I reference that form in  Gallery2 items to make the many-to-many connection and still use the relate function.

Can anyone help please?

Thank you.

[PowerAddict]

Hi @MorkandMindy 

I believe I had someone reach out to me with a similar question - of allowing creation, deletion etc. of many-to-many relationships. 

Here is my use case: a user can belong to more than one team, a team can have more than one user, an account can be related to many users. Here is what my canvas app does: 

1. Users can be added/related to a team by selecting them from a combo box
2. Once added, they don't show up in the combo box for that team
3. Users can be added/related to an account by checking the check box
4. Once added/related, all the instances of that user (across all teams) are checked
5. Users can be removed/unrelated from an account by clicking on the delete icon
6. Users can be moved from one team to another by clicking on the down or up arrow
7. The up arrow is disabled for the 1st team and the down arrow is disabled for the last team
8. Making any change doesn't require the form to be refreshed

Attaching a small clip here, let me know if this is what you are looking for.

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email