Many to many in form SQL source

[PhilD]

I am struggling to understand the correct design pattern to use in PowerApps to approach forms where a many to many relationship exists.

In my attached image I have depicted the classic many to many relationship between SQL tables. I understand how to build the relationships and associated views to render galleries in PowerApps. The part I am having trouble with is how to provision a form/control so a user can perform the following basic tasks:

•Show current values associated with Goal ID 1.
•Provision choices to add/remove associated items.
•Update the bridge table with the user's changes.

Because this would be a relatively common scenario, I want to be sure my approach makes sense before repeating it.

Some basic questions I am having:

• Should I use the built-in edit forms or abandon these altogether? I like the ease with which the regular table fields are provisioned but for this situation the edit form fields will obviously not work.
• Should I somehow use a list box to show the current choices associated with the current item, allowing the user to add or remove from the list box and then try to commit the list box back to the bridge table as a way to update?
• Is there maybe another way of looking at this entirely that is simpler and more straight-forward?
• Is a normalized table structure simply not supported/recommended?

I really appreciate the expertise of those on this board who have been farther down this road... At the moment I just can't seem to get my mind around how this should work yet it is such a common scenario if using SQL tables as a source.

[Meneghino]

hi @PhilD

please see here:

https://baizini-it.com/blog/index.php/2017/10/10/powerapps-101-many-to-many-relationships-between-ta...

[PhilD]

Thanks @Meneghino, I was actually reading your posts here just now when the notification popped up that you replied.

I had read the article you referenced but this seems to speak more to building views of the linked data (unless I'm not reading right which is entirely possible).

What I am specifically stuck on is provisioning controls to allow a user to update the bridge table.

This started small and mushroomed into something more complicated and I'm hoping I didn't overplay my hand by promising something I can't deliver...

I really appreciate your taking time to try and help...

[PhilD]

I actually got this working pretty well using a combobox except for an issue with combobox not recognizing its DefaultSelctedItems as selected.

Here are the basic steps I followed.

• Set combobox items property to '[dbo].[MacroMetrics]'
• Set combobox DefaultSelectedItems property to

RenameColumns(
   ShowColumns( 
       Filter('[dbo].[vMacroMetricsOrganizationalGoals]', OrganizationalGoalID = varEditOrganizationalGoal.ID),
    "MacroMetric" ),
    "MacroMetric", "Title"
)

• Set combobox DisplayFields and SearchFields property to [Title]
• Remove current associated entries in bridge table using something like:Collect the combobox SelectedItems values in a table then patch the table as new records into the bridge table using something like:

RemoveIf('[dbo].[MacroMetricsOrganizationalGoals]',
OrganizationalGoalID=varEditOrganizationalGoal.ID
)

• Collect the combobox SelectedItems values in a table then patch the table as new records into the bridge table using something like:

ClearCollect(colSelectedMacroMetrics,ComboBoxMacroMetrics.SelectedItems);

ForAll(colSelectedMacroMetrics,
	Patch('[dbo].[MacroMetricsOrganizationalGoals]', Defaults('[dbo].[MacroMetricsOrganizationalGoals]'),
		{OrganizationalGoalID: varEditOrganizationalGoal.ID , 
		 MacroMetricID: ID
		}
	)
)

This actually works surprising well with ONE MAJOR PROBLEM...

The combobox "DefaultSelectedItems" are not picked up as "selected" until actually removed and reselected by a human. In their initial state, even though they display, there are no IDs pulled through. 

Tearing my hair out... time to call it a night.

[PhilD]

Using a gallery with a checkbox control works much better actually.

I used a gallery with a checkbox control then I used the RemoveIf and Patch functions to update the bridge table with the table of current values selected by the user using the checkboxs. When an item is checked or unchecked it is added to a collection and then that collection is used to patch the bridge table. Essentially, I remove all the matching items first then patch in the new values (which are the ones currently checked in the gallery control). Because the tables are relatively small, this should be okay performance wise.

The only problem I'm having with this method is that if the gallery is placed in a datacard on a form, it returns no items... not sure if that can be overcome or if simply a technical limitation.

Overall though this is pretty straightforward and repeatable so I think it will work for most cases. Always glad to hear any other thoughts though.

[PhilD]

@Meneghino thanks for the reference to this article.

Once I got past the forms difficulty, I ran up against learning how to filter a gallery using the values from a bridge table in a many to many relationship. About half way down the article are some examples that are extremely well laid out and easy to understand (even for a lunkhead like me) and I highly recommend anyone working on getting many to many SQL relationships to work in PowerApps read this. I'm glad you pointed this out to me, thanks again.

Slowly piecing together the core design patterns needed. Really wish there were better options to comment code.