cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

Combobox: Lookup to other source & save to SP

Hey there
I created a people picker field in SharePoint. In Powerapps, I'd like to limit this field to a set of people who are in another list. I managed to do that with Distinct, but whenever I changed Combobox.Items to any other value than the default "Choices(Table,Field)", it will not save my changes onto SharePoint. 

Any idea on what's wrong or how I can achieve this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Combobox: Lookup to other source & save to SP

@FabianAckeret 

So based on how you are getting that information from another source, you will need to go to that source to get the additional information you need.  Your Distinct formula is going to pull out everything except the actual email, you need more than that to complete your submit.

 

So, this formula on your Items property will give you all of the information you need tied to the combobox:

 

ForAll(Distinct(Personen, Person.Email).Result, LookUp(Personen, Person.Email=Result).Person)

Now all the items of the ComboBox have full records with them.  (by the way...I find the ComboBox control to be a little more robust for these type of actions, but DropDown will work too - regardless, looking at your message, I believe you are in fact using a ComboBox)

 

 

Now, for your Submit, it is the following:

 

{ 
        '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", 
        Claims:CBKursleitendeTest.Selected.Claims, 
        Department: CBKursleitendeTest.Selected.Department, 
        DisplayName:CBKursleitendeTest.Selected.DisplayName, 
        Email:CBKursleitendeTest.Selected.Email, 
        JobTitle: CBKursleitendeTest.Selected.JobTitle, 
        Picture: CBKusleitendeTest.Selected.Email
}

In fact, since the selected Item of the ComboBox is in fact a full record for a person, you can shorten it more to just simply make your update:  CBKusleitendeTest.Selected 

Now, that last statement is going to depend on how you are putting all this together...Is this in an EditForm?  Are you doing this in a Patch/Update? 

But, the formulas above should get you to the next step.

 

I hope that helps some more.

 

EDIT: By the way, that first formula (the ForAll) can be used directly on the Items property of the Control.  However, I have found the Controls to now be finicky lately and seem to work better when given a collection (although, try to avoid over use of collections if possible).  But in this case, if you find the control to be wacky during design time, you might want to put that formula in an action where you can do a ClearCollect on it to create a collection, then use that collection name for your control Items property.  Just an FYI.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: Combobox: Lookup to other source & save to SP

@FabianAckeret 

I will assume you have a People Column in SharePoint that you are trying to change.  

I will also assume that your "other list" has a People Column in it as well.

If that is the case, there are a couple steps you need to take to be successful on your update.

 

First, you can go back to your ComboBox (personally, unless you need a ComboBox, I'd go with a DropDown, but either way...) and set its Items to what you want - Distinct is fine, but be mindful of the "Result" column (i.e. Distinct returns a single column table of Results).  

 

Now for your actual field you want to update (again I will assume you are dealing with a DataCard on an EditForm - If not, let me know there is another process for a Patch function).  Change the Update property to the following:

 

{ 
  '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", 
  Claims:"i:0#.f|membership|" & Lower(yourComboBoxName.Selected.Email), 
  Department:"", 
DisplayName:yourComboBoxName.Selected.DisplayName,   Email:yourComboBoxName.Selected.Email,   JobTitle:".",   Picture:"." }

This should get you the update you need.

There may be some variations based on the Results you get from your Distinct, but this should provide you enough to move to the next obstacle.

 

Hope this is helpful.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Responsive Resident
Responsive Resident

Re: Combobox: Lookup to other source & save to SP

Hi @RandyHayes 

 

Thanks for your answer.
It definitely does something, but I'm not quite there.

Issues:
#1 - It saves only the last entry of my combobox to SharePoint
#2 - Once I saved and then reopened the form, the combobox was empty again (did not save WITHIN the form)

I adjusted your formula to this as its based on distinct (my Result = Email):

{ 
        '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", 
        Claims:"i:0#.f|membership|" & Lower(CBKursleitendeTest.Selected.Result), 
        Department:"", 
        DisplayName:CBKursleitendeTest.Selected.Result, 
        Email:CBKursleitendeTest.Selected.Result, 
        JobTitle:".", 
        Picture:"."
}

Do you have any ideas?
Cheers!

Highlighted
Super User III
Super User III

Re: Combobox: Lookup to other source & save to SP

@FabianAckeret 

Can you provide the formula for your Items property for your dropdown/combobox?  I am interested to know what the information is that you are gathering for that.

If you are pulling from another list, then there has to be some discoverable connection to that list and the people properties.  You will need to have the email and the display name in order to update your people field.  If the other list does not have people fields in it, then what are they - just email addresses?  If so, then you're going to need to lookup the actual information before submitting.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Responsive Resident
Responsive Resident

Re: Combobox: Lookup to other source & save to SP

@RandyHayes 

 

Thanks for your reply.

Sure - this is what I'm using for the ComboBox:

Distinct(Personen,Person.Email)

It is referring to the list "Personen" which has a people field called "Person".

Highlighted
Super User III
Super User III

Re: Combobox: Lookup to other source & save to SP

@FabianAckeret 

So based on how you are getting that information from another source, you will need to go to that source to get the additional information you need.  Your Distinct formula is going to pull out everything except the actual email, you need more than that to complete your submit.

 

So, this formula on your Items property will give you all of the information you need tied to the combobox:

 

ForAll(Distinct(Personen, Person.Email).Result, LookUp(Personen, Person.Email=Result).Person)

Now all the items of the ComboBox have full records with them.  (by the way...I find the ComboBox control to be a little more robust for these type of actions, but DropDown will work too - regardless, looking at your message, I believe you are in fact using a ComboBox)

 

 

Now, for your Submit, it is the following:

 

{ 
        '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", 
        Claims:CBKursleitendeTest.Selected.Claims, 
        Department: CBKursleitendeTest.Selected.Department, 
        DisplayName:CBKursleitendeTest.Selected.DisplayName, 
        Email:CBKursleitendeTest.Selected.Email, 
        JobTitle: CBKursleitendeTest.Selected.JobTitle, 
        Picture: CBKusleitendeTest.Selected.Email
}

In fact, since the selected Item of the ComboBox is in fact a full record for a person, you can shorten it more to just simply make your update:  CBKusleitendeTest.Selected 

Now, that last statement is going to depend on how you are putting all this together...Is this in an EditForm?  Are you doing this in a Patch/Update? 

But, the formulas above should get you to the next step.

 

I hope that helps some more.

 

EDIT: By the way, that first formula (the ForAll) can be used directly on the Items property of the Control.  However, I have found the Controls to now be finicky lately and seem to work better when given a collection (although, try to avoid over use of collections if possible).  But in this case, if you find the control to be wacky during design time, you might want to put that formula in an action where you can do a ClearCollect on it to create a collection, then use that collection name for your control Items property.  Just an FYI.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Responsive Resident
Responsive Resident

Re: Combobox: Lookup to other source & save to SP

@RandyHayes 

That works like a charm. Yes, I'm using an EditForm. Since I actually need to display those records visually more attractive (pulling in some more data from that list...), I need to put them in a collection anyway - so, good idea! 🙂 

 

Thank you so much! One thing I still don't understand is why I'd need to maintain the "update" on the datacard for this combobox, but for other fields not? 

Thanks again and cheers!

Highlighted
Super User III
Super User III

Re: Combobox: Lookup to other source & save to SP

@FabianAckeret 

This is a complex data type for your data source.  Any changes or alterations you make, you will have to maintain the Update property yourself.  Otherwise you will get "default" results...which probably works fine for all the other datacards.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Responsive Resident
Responsive Resident

Re: Combobox: Lookup to other source & save to SP

Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,290)