form on N-N table

[jvanrossum]

I have a Power App that starts with a list of 'Assignments' (from a Azure SQL DB). You can click on them to edit them, but in the edit screen/form I also want to add one or more predefined 'labels' from a table. (so each assignment will have one or more lables added)

What's the best approach for this? I preferably don't want to add a gallery with all the selected labels and then do the edit and add in a separate screen/form.

[KroonOfficeSol]

you're question is a little bit hard to read and understand what you want. But when I am right you want to present a list to the user.

What to do is to

• make a view in the database (minimal fields: id, displayfield - in case you want to show a combination of field then do this concatenation on the sql)
• select the datacard you want to change in the form
• disable the protection (on the Advanced tab. If you don't do this the combobox wil be generated on the screen and is not a part off the datacard)
• add a combobox control to the datacard
• Connect the combobox to the view (Practically the combobox then import a list off objects -> one record in the view is one object)
• Change combobox to select one record
• Change the update parameter off the datacard to the combobox 

  combobox.selected.ID

  This wil give a int to the form on submit.
• Change the combobox default selected item. Here you do a lookup like

  Lookup(view, ID = Parent.Default)

  What this does is that is sets the combobox to the correct selection when you use the form to edit an existing record.

[v-xida-msft]

Hi @jvanrossum,

Could you please share a bit more about your scenario?

Do you want to add one or more predefined labels for each assignment of Assignment table?

You could consider take a try to created a Middle table called AssignmentLabel table, which has two columns: AssignmentId and LabelId. Then add AssignmentLabel table and Label table as two data sources within your PowerApps app. We could use this Middle table to store the corresponding AssignmentId and LabelId.

Please take a try with the following steps:

• Add a Custom Data Card within your EditFrom
• Wihtin the Custom DataCard, add a Label control, Text property set to "Labels". Then add a Combo box control (called "ComboBox1"), set Items property to Label table, the DisplayFields and SearchFields property sre both set to following formula:

["Name"]

The selectMultiple property set to true.

• Set the OnSelect property of the Submit Button control to the following formula:

SubmitForm(Form1);ForAll(RenameColumns(RenameColumns(ComboBox1.SelectedItems,"Name","Name1"),"Id","Id1"),Patch(AssignmentLabel table,Defaults(AssignmentLabel table),{AssignmentId:Gallery1.Selected.ID,LabelId:Id1}))

Note: The Gallery1 represents the Gallery control within your first screen.

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/multi-select-pattern-on-sharepoint-lists/

More details about the RenameColumns, ForAll and Patch function in PowerApps, please check the following article:

RenameColumns, ForAll and Patch function

Best regards,

Kris