Power Apps: Set SharePoint Lookup Field
In many solutions, we need to be able to set the values of fields via logic in our form, and we don’t always need the end user to pick from a drop-down box. The most common example is the relational database and parent-child relationships. When we build this type of thing in SharePoint, you would want the end user to have the master record on a form on the screen, and they should be able to quickly add child items to a sub-list (like repeating table style) on the same page. A common example is an expense report with expense items in a sub-list.
I’ll be writing out the whole cascading drop-down thing in a separate post, but in this example, the end user has selected the name of a project from a gallery of projects, and when they submit their timesheet form, that has a lookup field to the associated project, we need to set the value of that selected project, to the “Project” lookup field. Seems pretty simple, and you may be surprised, but currently it’s not that simple.
I’ve named my gallery ProjectGallery, and it shows items from a data source that is my SharePoint list called “Projects”.
In my timesheet form (using the form control in PowerApps), its data source is a SharePoint list.
Unlock this card in the advanced pane on the right.
Update 12/5/2017: Click to select the card.
Click to select the actual drop-down control as seen in the photo above. (don’t select the card, select the drop-down itself)
After we’re done configuring it, we can just hide the whole card.
What you’ll need, is the Title and ID of the project that you need to set in the drop-down or combo box control in this card.
In the Default property of the card, type this, using the name of your own gallery:
(do not copy/paste this text from my blog here, it doesn’t translate correctly into the PowerApp. Just type it manually in your PowerApp)
{
Id : Value(ProjectGallery.Selected.ID),
Value: ProjectGallery.Selected.Title
}
Yes, it’s ugly. Go ahead and set the Visible property to false for that Project card.
Now, when you submit a timesheet, the project you selected in a gallery will post in the timesheet form’s Project lookup field.
Here’s my full hour-long demo of how to build this, and how to build it with drop-down boxes instead of galleries:
Good stuff, but I have a question. If you’re creating a child item based on the parent using PowerApps, is there a need for the lookup column? You can look the two lists together for reporting simply by having a text column that holds the value/id of the parent item in it couldn’t you?
The real issue I found in PowerApps was when creating a parent item and then a child item in the same form (e.g. you create a new order and then enter order details as part of that app in a different form). Because the parent hasn’t yet been created in SharePoint there is no ID assigned to it in the list, so therefore I had to generate a GUID that could be automatically assigned to both the parent and child on creation of each.
The parent/child relationship is such a common requirement it would be great if Microsoft could make this a really easy function to implement.
You’re right. But, there are a couple of reasons you’d need to know how to do this. You may have existing lists that already have all of their relationships built upon lookup fields, and you’re creating a new PowerApp using those lists. Another reason would be if you ever need to interact with the data from within SharePoint, and have the lookup actually be a lookup. So, it would be more useful in both PowerApps and SharePoint. So, with both of those in mind, it was a show stopper for me, not being able to set those types of field values.
Pingback: TFS/SharePoint Integration; Co-authoring SharePoint Online Documents; Enterprise Cloud Computing Security
Pingback: Office 365 August 2017 Edition | Alpesh Nakar
Thanks Laura, by the way, how do you do this with a choice field?
With a choice field its much simpler. You just type in the actual text value in that “default” box.
This doesn’t work for me. When I set the lookup default to a value like this:
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id:BrowseGallery1.Selected.ID,
Value:BrowseGallery1.Selected.Title
}
then when I look at the app preview, the lookup is filled in with a default value. But when I open the app through Dynamics365, the combo box remains empty.
Thanks! Had this challenge in my own app … worked like a charm! 🙂
This has been great but has suddenly stopped working; it works the first time but then won’t repeat the action moments later. Do you have ideas why this could be? Can’t see any update that would make it do this?
Same with me. It works the first time you open the app and create an item, then the lookup never gets populated again.
I worked out where I went wrong. If you place the following code in the Default section of the ENTIRE Datacard, rather then on the dropdown field of the datacard, it works.
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id:BrowseGallery1.Selected.ID,
Value:BrowseGallery1.Selected.Title
}
This method also removes the #MIcrosoft.ASSURE nonsense from the field, thus allowing users can change from the default dropdown option to any other option if they need to.
Thanks, have tried that but am still having the same issue unfortunately. Still working ok for you?
Hi Chris. Yes. It is still working fine. Perhaps try deleting the custom card and adding it on again. And then only apply the code to the Datacard rather then the drop down box.
Thanks Hayden but that still won’t work. Its brought our project to a stop as the relations are very important within our app.
I just smacked my hand on the ceiling fan jumping for joy after the first record was saved to my list using this solution. I’ve spent the last tree days looking for this. THANK YOU !! Mine is unique where I have a Gallery that opens a parent form. From that parent form you open the child form (Apartment > Inspection Items). This solution allowed me to save the cascading drop down id/value pairs AND the Apartment id/value pair using variables. Thank you so much for taking the time to share your discoveries!
This was great! But now I’m on trying to solve the next riddle and that is how do I do a GroupBy with a LookUp column as the column to Group By? LookUp columns aren’t listed as one of the columns to choose from, so does anyone know how to do it?
Hi,
I’ve thought this was the answer finally! But no, I have followed the procedure and get the #Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference showing up in the dropdown text box but when I submit the form is accepted but the entry in the SharePoint list shows the new record but with a Field Required error message in the lookup field.
I have tried with Hayden’s suggestion but that didn’t work either.
So near yet so far. Would be feeling like Rob if someone could sort this for me.
Thanks
Phil
how do you reset the gallery? what is the code on the back bottom
ho boy … phiip I know how you feel … so near but yet so far. I’ve been trying to get this working for hours and hours and hours now, with no luck. When I submit the form, after a few seconds I get the error message “The requested operation is invalid. Server Response: A value must be provided for the item.” followed by a clientRequestId value with a long string of numbers.
This is the code I inserted into the dropdown list inside the datacard: {‘@odata.type’: “#Microsoft.Azure.Connectors.Sharepoint.SPListExpandedReference” , Id : glyCustomerStaff.Selected.ID, Value: glyCustomerStaff.Selected.FullName}
Any more ideas would be much appreciated!
Hi, I just made this correction, the ID needs to be a value… Id : Value(ProjectGallery.Selected.ID),
I have tried your solution and can get the detail record to submit – but ONLY if I click on the pick list, get a dropdown and pick one, and then submit. It does correctly submit a Lookup record, but the list isn’t restricted to only the item selected in the gallery, and won’t submit until I pick one. Can you help? These reference a master – detail list, with the detail list having a look-up column to the Title field in the master list.
Here is my code inserted into the default value of the dropdown list:
{ ‘@odata.type’ : “#microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”, ID: Value(Gallery1.Selected.ID), Value: Gallery1.Selected.Title}
Thanks for your help!!
Does anyone know if something changed in PowerApps as far as setting Lookup Value’s go? This functionality was working previously but as of a few days ago it doesn’t. It looks like it is setting the default value correctly on the app, however, the item isn’t getting added to the list and I get an error in the editor that “A value must be provided”.
I am having the same issue. When trying to customize a form, the lookup field (defined within SharePoint) does not appear to be a LookUp control within Powerapps (it was a few weeks ago though). Instead it appears to be a Choice control. Has anyone figured out a work around or solution to this?
Pingback: PowerApps: Setting a SharePoint List Lookup Column | Marc D Anderson's Blog
I am also having the same issue. They appear to be set up as Choice controls now and it says it can’t find a value to submit. the default info isn’t even show up in my columns.
Hi Laura (thanks for the good stuff you have here), hello Everyone!
Have you tried and were you able to make those Lookups work with data/columns from other sub-sites (within the same Site Collection)? Or possibly Site Columns? I’m talking about true lookups (ID + Display) where data changed in one place will be referenced in another, not just grabbing a value of a field. Thanks!
Hi Laura, this is great information, thanks!
Not sure if something changed with powerapps, but I did have to change the lookup field from Default to DefaultSelectedItems.
Yes, you are correct. Something did change in the past couple of weeks.
Can you clarify? the datacard has default value but the dropdown item has defaultselecteditems value…
Put this in the card’s default property, not the drop-down:
{
Id : Value(ProjectGallery.Selected.ID),
Value: ProjectGallery.Selected.Title
}
Hei Somebody figured out or not having same issue like about ?? when i try to submit it didnt save.
I am currently having this issue as well. The lookup field value displays correctly, and a test label with text=label.selected.value shows the correct value but it won;t save unless I actually select a value from the dropdown list. I did notice the datacard actual value is showing as a combo box but I was expecting it to be a dropdown box. Is this correct? Has anyone else solved this issue?
I have tried several lists, sites and even tenants and get the same result. JSON code matches most current updates (Value(gallery.selected.id)).
Hi, did anyone find an answer to this? Currently it shows the correct item in the app, but as soon as I submit it the field comes up blank in SharePoint
I love you right now!
Pingback: PowerApps – default values for SharePoint Lookup columns – Alex's SharePoint & PowerApps Blog
Can you have a lookup from a list that was a lookup from another list??
It doesn’t seem like that question is related to PowerApps at all, but in SharePoint when it comes to lookup fields, the answer is no. Maybe you’re asking about a cascading drop-down concept?
Hello All,
Firstly thanks Laura for the great blog share and video. Not only we benefit from the skillsets but also it was a great pleasure to follow your blog and the small talks in the video:-)
I spent a couple of hours on this and finally made it work. Below are some key points I want to share:
a. The default value defined are for the card, but not for the data card value. When I define it in the datacard value -> Default, it didn’t work.
b. The expression below worked for me:
{
‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id: Value(BrowseGallery1.Selected.ID),
Value: Text(BrowseGallery1.Selected.ID)
}
(My lookup column is an ID hence I converted it to text.)
Hope this helps,
Best Regards,
Evan Zhang
I am having the same issue mentioned by lots of people here where the related item is not added to the list unless something is actually chose. The data card looks fine — fills in exactly the information I want. Any more thoughts on the resolution to this?
Hello!
In PowerApps 3.19031.13 version solutions do not work, type of control «Edit Lookup» is not available anymore.
Hello Laura !
I’m trying to populate the DefaultSelectedItems in a ComboBox with the people selected in a previous gallery. I’m stuck 🙁
It’s working only with 2 records (it’s hardcoded), I wonder WonderLaura, What do I need to do with n records?
This is the current value in DefaultSelectedItems:
Table(
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: First(Gallery1.Selected.’Crew Onboard’).Claims,
Value: First(Gallery1.Selected.’Crew Onboard’).DisplayName
},
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: Last(Gallery1.Selected.’Crew Onboard’).Claims,
Value: Last(Gallery1.Selected.’Crew Onboard’).DisplayName
}
)
The syntax for a people-column is:
{
Claims:”i:0#.f|membership|” & User().Email,
DisplayName: User().Email,
Email: User().Email
}
Hey Laura,
Thanks for your informative blogs. Can you please make an updated blog about lookup columns in Powerapps? I noticed that they normally just pull like choice columns, but I am having an issue with one of mine not pulling any choices…
I have followed you for years. I feel you have done some great work and I love your power hours. I am recently struggling with a Timesheet app issue. The timesheet has a gallery and in the gallery is a project field that is a lookup field. I can create a new sheet with no issues. I can edit an existing sheet with no issue. But when I copy an existing sheet, it looks ok. all the data populates, but when I save it and reopen my project field is blank. I read through this post and the comments. I updated the Default and DefaultSelectedItems with the recommended script. The first script I got to post but did not work:
‘ {
‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id: ThisItem.BillTo.Id,
Value: ThisItem.BillTo.Value
}’
BillTo is my project list and the lookup field in my timesheet list
When I changed the script to the point to the gallery it did not like it, in fact, it did not like Title:
‘{
‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id: Value(galleryTimeCard1_1.Selected.ID),
Value: galleryTimeCard1_1.Selected.Title
}’
As I stated previously, the new timesheet works and editing existing works. it is only when I copy existing that it appears to work, but upon refresh project is blank.
any help would be greatly appreciated.
You don’t need that odata.type stuff. That whole line can go away.