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>

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

Leave a Reply

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