cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SueZeeBee_ganto
Helper I
Helper I

Patch multiple lookup values to a SharePoint List

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.

screenshot app.png

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
SueZeeBee_ganto
Helper I
Helper I

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
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thx, this was great help and did the trick!

 

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (2,214)