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.

View solution in original post

Thx, this was great help and did the trick!

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,837)