cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Save Data from Filtered Combobox lookup column values to SharePoint list

Hello,

 

I am having trouble finding solution to save the values from multiselected comboxbox values into a SharePoint lookup multiselect column.

 

I have following 3 lists in SharePoint

Request,

Region,

Location

 

Region and Location are lookup columns in Request list.

Region is lookup column in Locations list as well.

 

While creating an item in Request list, Location multiselect dropdown(combo box) should get filtered according to the Region selection.

 

I am able to get the filtering part working, but not able to save the selected Location values into Request list.

 

Any help/direction on this would be much helpful.

2 ACCEPTED SOLUTIONS

Accepted Solutions

@WarrenBelz  thanks for the tag, another tricky one 🙂

 

@Anonymous 

As @WarrenBelz  has explained you need to build the same table schema to send back to the LookUp column. Try this in the Update property of the DataCard

ForAll( ComboBox2.SelectedItems,
      {
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        Id: LookUp(Choices(Request.Location), Value=Name).Id, 
        Value: LookUp(Choices(Request.Location), Value=Name).Name,
}
)

 

I've assumed 'Name' is what is being used inside the selections of your ComboBox but if it isn't then use whatever it is instead of 'Name'.

 

Let me know if this helps.

 

View solution in original post

Anonymous
Not applicable

@Eelman @WarrenBelz 

 

Thank you very much for all your time and guidance. It all helped me to finally achieve the solution.

 

This is what I have implemented:

  • Inserted delegated ComboBox
  • ComboBox
    • Items is set to:  Filter([@Locations], Region.Value = DataCardValue11.Selected.Value)
    • Default set to: Blank
  • DataCard
    • Update set to: ForAll(
      ComboBox2.SelectedItems,
      {
      '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      Id: LookUp(Choices('Access Request'.Locations), Value = Name).Id,
      Value: LookUp(Choices('Access Request'.Locations), Value = Name).Value
      }
      )
    • Default Set to: LookUp('Access Request', ID = SharePointIntegration.SelectedListItemID, Locations)

 

Thank you

View solution in original post

11 REPLIES 11
WarrenBelz
Super User III
Super User III

Hi @Anonymous ,

Saving to Lookup columns are never much fun when you change the Items property away from the standard Choices syntax.

So you need this basic structure in the Update of the DataCard, but need to ensure these values are available in the control Items structure.

(
   {
      '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      Id:YourControlName.Selected.Id,
      Value:YourControlName.Selected.Value
   }
)

Try this first and if it does not work, we need to look at what is in the data chosen.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Anonymous
Not applicable

@WarrenBelz Thank you very much for your prompt response.

 

I have used this successfully in my other form customization when the lookup is Dropdown without multiselectable.

 

When I used the same code on multiselctable column, it never worked as in this case we are having more than one value to be saved, and may be we need to patch it or loop it? but I am not able to put the pieces together and apply it to the right property.

Thanks @Anonymous ,

The idea of a Lookup column is to have controlled values in the Choices. You cannot Patch anything else back in, especially multi-selected items as it is not a valid value it will accept. What is your reason for a Lookup column if you want to populate it with data other than what can be selected?

Anonymous
Not applicable

@WarrenBelz - I am not sure if I can use any other control other than lookup in-order to read the controlled/filtered values based on the Region column selection. As all the lookup column I am struggling to save is tied to Region column value. 

 

Please advise if there any other better approaches to achieve solution for my requirement.

 

Thank you

Ok @Anonymous ,

Apologies here - since my last post, I have revisited these. I abandoned them some time ago due to their complete redundancy to the PowerApps Lookup function in both standalone and Integrated apps. There is an Allow Multiple Values setting in SharePoint that needs to be checked to allow multiple values, so you will need something like

ForAll(
   YourComboBoxName.SelectedItems,
   {
      '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      Id:YourControlName.Selected.Id,
      Value:YourControlName.Selected.Value
   }
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @Anonymous ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Anonymous
Not applicable

@WarrenBelz

 

I wasn't able to get pass through this. I am still trying to get the solution on this.

 

Will keep you posted soon.

 

Thank you.

Anonymous
Not applicable

@WarrenBelz  I apologize for taking time to update:

 

I have tried several options, and I felt the following way of doing started working little but, but still not complete. Following is what I have done and achieved so far:

 

  • Inserted a Combo Box (ComboBox2),
  • Set the Items property of ComboBox2 to - Filter([@Locations], Region.Value = DataCardValue11.Selected.Value)
  • Set the Default Property of the DataCard to default value - ThisItem.Locations
  • Set the Update Property of the DataCard to - ForAll(
    ComboBox2.SelectedItems,
    {
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    Id: Value(
    LookUp(
    [@Locations],
    Title = ComboBox2.Selected.Name,
    ID
    )
    ),
    Value: ComboBox2.Selected.Name
    }
    )

 

with this, I am able to save the form without any errors but the actual list column is able to hold only last value from multiple selected values, ex. if I have selected Choice1, Choice3, Choice5. Only Choice5 gets saved into the column.

 

Empty ComboBox2  when I open the form in Edit or View mode.

Original Column still holds Choice5 (the actual saved value).

 

I feel that I am missing to set proper Default value on DataCard to display the saved values.

And I am not sure if I have to collect the selected values into the collection and input them to Update property so all the values will get saved.

 

Please let me know your thoughts/inputs.

 

Thank you.

Hi @Anonymous ,

To be honest, I am struggling as I do not use these columns at all. The issue is that a multi-value lookup column is a table (the multiple selections) within a table (the lookup with a Value and ID) within a table ( the list) and somehow you have to drill down into it all to Patch your items "at the bottom".

This post is probably the most detailed solution I could find, but I don't think it exactly addresses your issues. I will tag one of my code guru colleagues (and Ozzie compatriot)  @Eelman who might have a thought for you.

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,800)