Combobox: Lookup to other source & save to SP

[FabianAckeret]

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?

[RandyHayes]

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

[FabianAckeret]

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!

[RandyHayes]

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

[FabianAckeret]

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

[RandyHayes · Accepted solution]

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

[FabianAckeret]

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

[RandyHayes]

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

[FabianAckeret]

Thank you!