cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

@WarrenBelz  thanks for the tag, another tricky one 🙂

 

@Kalyan-Polasi 

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

Highlighted
Frequent Visitor

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

@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
Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

Hi @Kalyan-Polasi ,

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.

 

Highlighted
Frequent Visitor

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

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

Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

Thanks @Kalyan-Polasi ,

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?

Highlighted
Frequent Visitor

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

@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

Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

Ok @Kalyan-Polasi ,

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.

Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

Hi @Kalyan-Polasi ,

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.

Highlighted
Frequent Visitor

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

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

Highlighted
Frequent Visitor

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

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

Highlighted
Super User
Super User

Re: Save Data from Filtered Combobox lookup column values to SharePoint list

Hi @Kalyan-Polasi ,

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,740)