Patch multiple lookup values to a SharePoint List

[SueZeeBee_ganto]

Hi,

I have a challenge with a combobox that I hope someone can help me with.

To simplify my issue I've built a simple example of what I want to achieve.

I have two SharePoint lists. 

tst_participants with the Participant name as a single line column and Active with a boolean column

tst_event with the Event name column (single line) and a column Multiple_participants as a lookup column with multiple values enable to the tst_participant list column Participant name.

I've added some data into SharePoint and made a simple canvas app to show the idea.

The top part is a gallery to display the content of the tst_events list. In it I've added a subgallery to display the table Multiple participants with the id and Active column content.

Under the gallery I've added a Textinput for the Event name I'd like to add and a Combobox for the selection of the (multiple) participants. Because I only want to offer active participants in the combobox the item property is set to:  Filter(tst_Participants,Active=1).tst_dln_name) . This works correctly.

When I patch the selecteditems from the dropdown to the Event list at the on select property of the button, it gives an error that it expects a table with a different schema. I've used the formula:

Patch(tst_event,Defaults(tst_event),{Participants_multiple:ComboBox1.SelectedItems})

I understand I need to get the Id column of the participant into the SelectedItems table, probably with a Addcolumn function, but how do I combine that with the filter function I want to use for the itemsetting of the combobox? 

[SueZeeBee_ganto]

I've tried a couple of things today.

• Introduced a collection with the active Participants on the On visible property of the screen (ClearCollect(C_active_participants,Filter(tst_Participants,Active=1);)
• The items property of the Combobox is now set to:  AddColumns(C_active_participants,"_sel_dln_naam",tst_dln_name,"_sel_dln_id",'Id (ID)')
• The On Change property of the combobox now makes the collection with the selected records with the participant id included: ClearCollect(C_selected_participants,ComboBox1.SelectedItems)

But I'm still stuck on the last step in the Patch formula. I've tried it with something like this because the Choice and Lookup combo works on a single record Combobox, but this unfortunately does not work for multiple value Look Up fields.

Patch(tst_event,Defaults(tst_event),{tst_naam_event:TextInput1.Text,

Participants_multiple:Filter(Choices(tst_event.Participants_multiple),Id=ComboBox1.SelectedItems._sel_dln_id)

})

[v-yutliu-msft · Accepted solution]

Hi @SueZeeBee_ganto ,

Please notice that:
multiple lookup field is actually a table with two columns: Id and Value.

However, since you set the combo box's Items to "Filter(tst_Participants,Active=1).tst_dln_name", so the "ComboBox1.SelectedItems" will only represnets a table with one column named tst_dln_name.

I suggest you set like this:
the combo box's Items:

Filter(tst_Participants,Active=1)

the update formula:

Patch(tst_event,Defaults(tst_event),
{'Event name':textinputname.Text,
Participants_multiple:RenameColumns(
                                    ShowColumns(ComboBox1.SelectedItems,
                                                "tst_dln_name",
                                                "ID"
                                                ),
                                    "tst_dln_name","Value",
                                    "ID","Id"
                                     )
})

Best regards,

[SueZeeBee_ganto]

Thx, this was great help and did the trick!