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
  • Email
  • 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>

26 Replies to “Handling SharePoint lookup and people fields in PowerApps”

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

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

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

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

  3. 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?

    1. 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).

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

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

  5. 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?

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

  6. 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,

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

    1. You should collect both properties using the Office 365 Users connector. Example:
      ClearCollect(
      colUsers,
      {
      Displayname: Office365Users.MyProfileV2().displayName,
      Department: Office365Users.MyProfileV2().department
      }
      )

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

  8. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *