cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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)

})

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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (94,014)