Dataverse, Power Apps and people picker

The People picker is a feature that is often needed in applications. It is used for selecting the approver, owner, next handler, etc. from a list. When a SharePoint list is used as the data source, using People picker is very straightforward. You can easily select the right user from the tenant users by utilizing a Person-type column. Even multiple users can be selected in the field this way.

Everything works in Canvas Power Apps as expected.

But what about when we use Dataverse as the data source? Is everything as straightforward?

Well, yes and no.

Utilization of Dataverse users

As a simple example, let’s use meetings whose information is stored in Dataverse. One of the saved data attributes is the Chairman of the meeting (chairman).

It is natural to add this attribute as a LookUp column that points to the Users table.

This allows the chairman to be conveniently picked from the users in model-driven apps.

The same works also in Canvas Apps.

But what if we wanted to list the meeting participants as well? In SharePoint, this is accomplished by utilizing the Person column, as it supports multiple selections. In Dataverse, N:N relation is made between the meeting and the users for this purpose. In practice, a participants-table is created (participant).

But let’s not go there yet. We have other problems to solve as well.

First and foremost, the users in Dataverse.

The user of a tenant becomes a user of Dataverse when:

• The user belongs to an Azure AD group connected to the environment
• The user has a license to use Dataverse

This means that typically not all tenant users are Dataverse users, too. Getting there requires a license, which is a bit awkward when we would just like to record the participants of a meeting.

The participants do not use the system itself in any way (directly or indirectly). This means that they do not need a license.

Let’s come up with something else.

Utilization of Dataverse’s contact information

What if we used the Contact table in Dataverse? Let’s create a chairman column for the meeting, which is a LookUp to the contact table.

As in the previous example, this works in both types of applications.

Nice. But now we need to copy all tenant users as contacts for our environment. And make sure that users are created and updated as tenant users change.

That does not sound like a feasible option.

Virtual table

What if we created a virtual table, that would somehow be mapped to the Azure AD users?

This also seems like a too complicated solution to a simple problem.

Update: Today there is a virtual table called “AAD Users” available in Dataverse by default! You can use that.

Saving people in a text field

Let’s do something really simple, and save the chairman information into a text field. This approach provides the users the traditional way to pick a person from the tenant users.

Let’s create a text field for the chairman.

The meeting form looks like this in Canvas Apps.

Let’s add the Office365Users connector as a data source.

After this, a combobox with a search to Office 365 users as its items is added to the chairman data card.

Office365Users.SearchUserV2({searchTerm:Self.SearchText}).value

This allows us to choose the chairman from the tenant users.

The e-mail address(es) of the chairman/chairmen are stored in the chairman column, separated by semicolons.

Concat(cmbPeoplePicker.SelectedItems,Mail, ";")

This way, the information about the chairman/chairmen is stored in a way that is understandable and editable in model-driven apps.

What about editing an existing meeting?

In order for the People picker control to be filled by the stored chairmen, a table containing both the person’s name (DisplayName) and the e-mail (Mail) must be created for them.

Let’s create a table from the stored strings using Split, and complete it with the person’s name utilizing Office365Users connector and AddColumns. Finally, the columns are renamed using RenameColumns so that the table is valid for the combobox.

Like this.

RenameColumns(
    AddColumns(
       Split(DataCardValue3.Text,";"),
       "DisplayName",
       Office365Users.UserProfileV2(Result).displayName
    ),
    "Result",
    "Mail"
)

The table is set as the default value for the combobox using DefaultSelectedItems. Below is an example highlighted in red of what the table suitable for our combobox looks like.

Now the form also works nicely in edit mode.

The text field is useless for the final UI, so it can be deleted.

Finding out the names of people using DefaultSelectedItems feature of the control easily generates unnecessary errors (which do not affect the operation of the application). If you want to get rid of them, you can create the table when selecting a row to edit (or during an OnVisible event in the edit screen).

Like this.

Set(varPeoplePickerValue,
  RenameColumns(
   AddColumns(
     Split(ThisItem.'Chairman (Custom)',";"),
     "DisplayName",
     Office365Users.UserProfileV2(Result).displayName
     ),
  "Result",
  "Mail"
  )
)

Optimizing

The solution can be optimized by storing the chairman’s name in addition to the email in the text field (separated by a semicolon). This eliminates the need to retrieve the chairman’s name in edit screen of the form.

This is quite easy if only one person is always selected as the chairman.

With this modification, the update formula in the chairman field in Canvas Apps now looks like this.

cmbPeoplePicker.Selected.Mail & ";" & cmbPeoplePicker.Selected.DisplayName

The value looks like this in model-driven apps. Readable, but no longer editable.

The chairman stored in Dataverse is selected for the control (DefaultSelectedItem) by the following formula.

Table({Mail:First(Split(Parent.Default,";")).Result, DisplayName:Last(Split(Parent.Default,";")).Result})

The formula again creates a table with the columns Mail and DisplayName.

Selection of more than one chairman

If several chairmen can be selected, the information of a chairman (name and email) is separated by a semicolon, and the information between the different people is separated by two semicolons.

In this case, the update formula looks like this.

Concat(cmbPeoplePicker.SelectedItems,Concatenate(Mail,";" ,DisplayName), ";;")

With this modification, the value looks even more confusing in model-driven apps.

When editing an existing record, filling the combobox is a bit more challenging. A table with two columns ( DisplayName and Mail ) is parsed from the string stored in Dataverse.

DropColumns(
   AddColumns(
     Split(Parent.Default,";;"),
     "Mail", 
     First(Split(ThisRecord.Result,";")).Result,
     "DisplayName", 
     Last(Split(ThisRecord.Result,";")).Result
   ),
   "Result"
)

Now the app only uses the Office365User connector when searching for new people.

However, at the same time, the field’s easy readability and maintainability in model-driven apps are lost.

Life is a compromise.