Handling SharePoint lookup and people fields in PowerApps
PowerApps gives you the opportunity to extend your SharePoint lists so that it can give just that much better experience for your users. However, there are still some things that need a little bit extra attention, such as lookup and people fields.
In this blog, I will explain how you can use these field types within PowerApps.
Lookup fields
A lookup field uses a basic combination of two attributes to render it correctly:
- Id, which is the item ID of the source item
- Value, which is the text value of the source item
In SharePoint, you can extend the attributes by selecting them when creating the lookup column, but the attributes above are the ones that are necessary.
We can confirm this by referencing a lookup column (which is the DataCardValue3 control) in a PowerApp to a new label:
Because the field expects a combination of both ID and Value, we cannot just only use the Value attribute when doing something with the outcome of that control within PowerApps. For example: setting the Default value of that control.
In this example I want the default Room (which is a lookup column) of my Room planner to be the ‘Bill Gates Room’. By just entering a Default value of ‘Bill Gates Room’, PowerApps will give me an error that an Text value is entered, but it expects a Record value:
So, how can we handle this fieldtype correctly? As stated in the error message above, the control expects a Record value. The correct Record value of a lookup field is as follows:
{
'@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: <ID>,
Value: <VALUE>
}
For this to make it work, we need the Id and the Value of the items. To get the Id, we can look into our source list:
We can now combine both Id and Value into the correct Record syntax and set our Default value:
Patching lookup fields
When using a Form, you will have no issues saving the data back to SharePoint. If you want to use the Patch() function to save the data back to SharePoint, you need to use the same Record syntax as we used in the Default value. For the Id and Value, you can use the properties from the lookup control:
People fields
A people field also consists of a combination of attributes to render into a Record value, as we can see when referencing a people field to a new label:
The only one that is really necessary rendering the field is the Claims attribute, but if you only use this attribute you will see an empty control in your PowerApp, which isn’t quite user friendly. So it’s recommended to also use the DisplayName attribute.
The expected Record value is as follows:
{
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|<EMAIL-ADDRESS>",
DisplayName: <DISPLAYNAME>
}
A common scenario is to pre-fill the control with the current user, which can be achieved by using the User() function. If we use that and put the correct Record syntax into the Default property of our control, the current user will be filled in:
Another way to achieve this is to use the Office365Users connector. Simply add the Office365Users connection as a new data source and put the Office365Users.MyProfile() as Default property of our control:
Patching people fields
Just like the lookup field, you can use this for your Patch() function as well, but you can only use the first Record syntax I showed (and not the Office365Users data source) because the Patch() function expects the following properties:
- Claims
- Department
- DisplayName
- JobTitle
- Picture
All of these properties can be pulled from the control itself:
Final notes
- Always set the Default property on the Card itself, not on the control (e.g. DataCardValue2)! In some cases it might not cause anything weird, but I have seen some cases that the control doesn’t accept the altered Default property where the Card always accepts it.
- Make sure you only change the Default property in your New form. If you set the Default property of your Edit form to something that is different from the value that is actually stored into SharePoint, your value will be overwritten after your edit. The correct Default property of your Edit form is ThisItem.<FIELDNAME>
39 Replies to “Handling SharePoint lookup and people fields in PowerApps”
Thank you for the useful article. I am having some difficulty patching a person field (single select). I am storing the user record as a variable (StudentID) and then using that to construct the record for the patch, but every time, the sharepoint field is blank following the patch.
Patch(‘Residential Register’,
{Student:{‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: StudentID.Claims,
Department: StudentID.Department,
DisplayName: StudentID.DisplayName,
Email: StudentID.Email,
JobTitle: StudentID.JobTitle,
Picture: StudentID.Picture
},
DoB:DateValue(DatePicker1.SelectedDate)});
Is your StudentID variable filled with a record and does it contain the correct values on each attribute?
Hi , I am working on powerapps ,my requirement is ,suppose User “A” like User “B” and User “B” like User “A” then there should me match and flow will triggered and will notify both the user that they have match. Can anyone help me in this?
Not really sure what you’re trying to achieve. You can use a condition in Power Automate to check whether a column matches another column. You can even use Trigger Conditions on this to only start the flow when a certain condition is met (or multiple conditions)
Hello Rik,
Nice article. I have a form with multiple screens and I want to patch a lookup column. I am patching all the forms in the list using the following code:
Patch(‘TestList’,Defaults(‘TestList’),Form1.Updates,Form2.Updates,Form3.Updates)
But if I use the following code to patch a lookup column, it doesn’t fill the column:
LookupCol:{‘@odata.type’:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id:Form1.LastSubmit.ID,
Value:Form1.LastSubmit.’NCR Number’ }
How can I fill the lookup column for multi screen update?
Thanks,
Are you sure you are using the correct Id and Value attribute? These should be the ID and the value of the lookup item. When you are using Form1.LastSubmit.ID, you are picking the ID of the last item you submitted.
Hi couldnt find my original comment to reply to (got your reply via email asking for more details).
I’ll try and explain the scenario i have in more detail.
I have a powerapp that when the user first opens it i want to create a row in a sharepoint list, called AppTracking.
The list has the fields, Title, Person, LastLogin
I want to put the persons fullname in title and the date in Lastlogin (those bits i can get to work via various means).
The bit i’m struggling with is working out in the powerapp how to find out the person’s unique identifier and then to patch that into the “Person” field on sharepoint (that is a people selector field).
Ideally i’d like to check if the user has an entry in the list already, if they do update the last login date via the person field as the unique id, and if not, to create the row of data in that list, with the Person field correctly set to the logged in user.
While i can set fields in the sharepoint list fine via various means, i’m really struggling to be able to set the “Person” field.
Any advice, and code snippets appreciated.
It should work if you use the People syntax as given in the blogpost:“,“,“,“,“,”
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: “
Department: “
DisplayName: “
Email: “
JobTitle: “
Picture: “
}
Not all fields are mandatory, but need to be used in the syntax. I believe that only Claims, DisplayName and Email are mandatory to be filled in.
If you use the Office365Users connector, you can set a variable on the OnStart of your Power App: Set(varUser,Office365Users.MyProfileV2()). You can then use the following syntax:
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: “i:0#.f|membership|” & varUser.mail,
Department: varUser.department,
DisplayName: varUser.displayName,
Email: varUser.mail,
JobTitle: varUser.jobTitle,
Picture: “”
}
Very useful article but i’m struggling with the following issue, i want to log a user entry into a sharepoint list the first time they use the app.
My SP List would be
Title text (persons name)
AppUser Person field in sp
LastLogin date field
I can get the Title and Lastlogin set fine, but cant seem to get it to use the person field correctly in sharepoint so it enters the users id
I’m not sure what you’re trying to achieve. If you are using a form in your Power App, you don’t have to use the People syntax from this blogpost. You just use the SubmitForm option
What would the code be to get the latest document revision number assigned using PowerApps? I need to match the Title on my Sharepoint list and get that document revision number then I need to add 1 to my new document so show what revision number we are on. I was using LookUp it’s not working.
LookUp(‘CRP Request’,Title=DataCardValue45_8,’Document Revision’, Descending).’Document Revision’+1
Any help would be appreciated.
You’re missing the .Text attribute behind the DataCardValue45_8. You also don’t need to sort the outcome of your lookup, because a lookup wil always only return a single value (the first item that is found). Document Revision is being called twice, so that won’t work either. You can just use the .Document Revision+1 after your lookup. It should look something like this:
LookUp(
‘CRP Request’,
Title=DataCardValue45_8.Text
).’Document Revision’+1
Very helpful!
Thank you for the article. I am just giving a try to my first powerapp app and paused because of people fields in my sharepoint. Still wondering if i keep “people” field in sharepoint, later in my app, if i can do count records etc for specific people?
I’m not sure what you’re asking. Can you please explain further?
Howdy,
I’m having difficulties doing a filter/lookup operation on a people field, i have a combobox to pick the user but i get a type error if i pass a odata item. Any suggestions?
Thanks a lot
I’ve sent you an email so that we can troubleshoot a bit further
hi… how can i show both display name and department in a collection?
You should collect both properties using the Office 365 Users connector. Example:
ClearCollect(
colUsers,
{
Displayname: Office365Users.MyProfileV2().displayName,
Department: Office365Users.MyProfileV2().department
}
)
Hi, Thanks so much for this article. I think i’m nearly there with this but I still can’t quite get it to work.
I have a form trying to submit a new entry to sharepoint and want to set a default selection on a lookup column.
I have used your code above entered in the ‘Default’ section on the Data card & the datacardvalue but I can’t get it to work! any ideas:
{
‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Id: Label6_1.Text,
Value: Label5.Text
}
Thanks,
You need to set the Default property of your Datacard to the value as stated in your comment and the Default property of your control should be set to Parent.Default. No need to set them both to the record value.
Also, from the looks of it, the Id attribute of your record does not seem right. Id is always a numeric value. You are using a .Text reference, which is a string value. If your Label6_1 control contains the Id, you should transform its string value to a numeric value using the Value(Label6_1.Text) function.
Hi. Thank you for your article. I have a column called Role which is a drop down and a column called Reviewer. In Powerapps, i have a cascading dropdown so that the user chooses a role it filters the reviewer (people picker column). I can’t seem to send an email. I get an error stating that that the parameter ‘to’ is invalid. Would you have any suggestions?
Make sure you select the Email property from your people picker control. If it’s a multi select control and you only want to use 1 selection, try the .Selected attribute instead of .SelectedItems attribute.
So it would be something like PeoplePicker.Selected.Email
Useful Article. thank you. I have a 2 sharepoint list list A with process and experts (person multiple field) and list B i have process and several other columns and expert. I would like that when i choose process in List B the expert column is pre populated with multiple person field.
Thank you for your response. You should take a look at my conditional lookup blog post, I think that described what you are looking for:
https://www.about365.nl/2018/08/24/creating-conditional-lookup-columns-in-sharepoint-with-powerapps/
Hi Mithali,
Did you get solution to your problem? I am also looking for similar solution to populate my multi person field from a parent list column based on a value selected in dropdown control on powerapps form.
It should work the same as the conditional lookup as I described in the comment above. Please try that
Hi! This is a super helpful article, thank you! I was wondering if you might be able to give me recommendations for the following scenario: I have a PowerApps gallery. The gallery items are filtered based on a SharePoint list’s date column, and each gallery item displays a Person field’s value from the same SP List. Right now, I can display the person field in my PowerApps gallery, but I am having trouble making the following happen: I want hovering over/clicking on the name to prompt the Contact Card, like we see throughout other areas of O365. Any ideas on doing this by utilizing the SP List’s Person column value?
That’s possible, but will take some extra work.
First, you’ll need to assemble the contact card entirely from scratch (or find someone who has built it already and is ready to share it as a component for example). This will take the most of the work.
With that in place, you need to show the contact card when clicking the gallery item. I usually show/hide items by using a variable; so when clicking the gallery item, you can use the following function:
Set(varContactCardVisible,true)Make sure you set the Visible attribute of your contact card (component or grouped controls) to varContactCardVisible.
Also make sure you have a close button on your contact card which where you use the following function:
Set(varContactCardVisible,false).How can update a lookup field filled, to blank?
Use -1 (minus one) as ID and a blank string (“”) as Value to empty the lookup field
how can i return sharepoint list column(including lookups column) from microsoft flow to power apps using response?
Please check my blogpost on viewing SharePoint item history in PowerApps. It describes how you can return data from Flow to PowerApps:
https://www.about365.nl/2019/08/08/view-sharepoint-item-version-history-in-powerapps/
When returning lookup columns, make sure to return both ID and Value
How do I change the data source of a People field to a different SharePoint list with a People field?
Hi Amanda, you just add the new data source to your PowerApp and change your formula so that the new data source will be used.
Basically, it won’t change a thing, since all people fields will lookup users from your tenant. It only differs when you change tenant.
Hello Rik,
Nice blog post.
Can you tell me how you would handle a multi-valued People field from a SharePoint Online List datasource in PowerApps? (if its achievable ofc)
Thanks!
Ben
Hi Ben,
You can trigger a Flow from your PowerApp to update your multi people field using HTTP. See my other post: https://www.about365.nl/2018/08/06/updating-a-multi-people-field-in-sharepoint-with-microsoft-flow/
I create an app with a first page login. I want user to select his mail using a dropdown list, displaying all office 365 users.
Hi, that’s a tricky one. You can use the Office365Users.SearchUser({searchTerm:””}) function, but I think that will have a maximum number of users that it can retract from your tenant. If you have more users in your tenant than this number, you should try getting the users with a Flow on your OnStart event (which would slow down your PowerApp for sure) or building a custom connector for that.