Filter SharePoint People Picker based on another List of People

When using Forms connected to SharePoint, there are different workarounds to Filter or limit the selectable Choices on a People Picker control. One of the workarounds involves building a custom People Picker with a Combo Box control and setting the Items property with the Office365 user connector. Another workaround involves limiting the selectable people in the Combo Box control using a SharePoint security group.

Another workaround, which I have not seen documented online, has been detailed below for reference.

Scenario

For the purposes of this tutorial, I have created a basic SharePoint list which displays 4 columns.

1. The system generated SharePoint ID field
2. A Single Line Text field called “Title”
3. A Single Choice People field called “Single Choice People Field”
4. A Yes/No field which returns a Boolean TRUE or FALSE

I also have a 2nd SharePoint list called “Sample Issue Data”. This list displays a list of Issue IDs together with a single choice People Picker field called “Issue Owner”.

In our Power App, I want to create a Form for “Sample Issue Data”, but I want to limit the possible selections in the Issue Owner Combo Box to display only the names which exist in “Sample List”, and where the Yes/No field in that list equals true.

Initial App Set Up

1. Add both SharePoint lists as Data Sources in your App
2. Add an Edit Form control (called “Form1” in this example) and set the DataSource property to the SharePoint list you want to update. In this example, the SharePoint list is “Sample Issue Data”. I have also ensured the People Picker field is included in the Form:

   

3. Add a Button control into your App and set the OnSelect property to: SubmitForm(Form1)
4. I also want my Form to be populated with information based on a selected record. Typically, we would achieve this by inserting a Gallery control into our App and then entering “BrowseGallery1.Selected” into the Form’s Item property. To keep this tutorial simple, I am going to insert a Combo Box control (called “ComboBox1“) into the App and set the Item property of the Combo Box control to my Data Source (“Sample Issue Data”).  To do this, select the Form and set the Item property to “ComboBox1.Selected” :

   

5. I will also set the Issue ID field from my SharePoint list as the unique field to display in the Combo Box. 

To recap for the purposes of this tutorial, the objects in our App have the following names:

• The name of the SharePoint List which holds the list of selectable People is “Sample List“
• The name of the SharePoint List which contains the People field I want to update (based on the list above) is “Sample Issue Data“
• The name of my Form is “Form1“
• The name of my single select people picker field from “Sample List” is “Single Choice People Field“
• The name of my single select people picker field from “Sample Issues Data” is “Issue Owner“
• The name of the People Picker Combo Box in my Form is “DataCardValue2“
• The name of the Combo Box control which displays a list of Issue IDs is “ComboBox1“

Configure the People Picker Combo Box

1. Unlock the Data Card for the People Field

2. Select the Item property of the People Picker Combo Box (DataCardValue2)

2. Remove the existing Choices expression and replace it with the following expression:

Sort(
    Filter(
        AddColumns(
            'Sample List',
            "_person_display_name",
            'Single Choice People Field'.DisplayName,
            "_person_email",
            'Single Choice People Field'.Email
        ),
        'Yes/No Field'
    ),
    _person_display_name,
    SortOrder.Ascending
)​

3. In the properties pane for the People Picker Combo Box, select Edit on the Fields property and select “_person_display_name” for Primary Text. You can also set the SearchField to “_person_display_name”.

Note it is ok to set the “Allow searching” toggle to On for the People Picker Combo Box control – only the names in “Sample List” will be displayed in the search results.

Select the People Picker Combo Box in the Form. You will notice only names where the “Yes/No” field equals true in “Sample List” are displayed.

What did we just do?

We used the above expression to perform a few tasks. 

A complex field type requires a record, and different complex field types will require different attributes in the record. A People Picker field is also a complex field type, and it requires a record value with the following attributes in the schema:

• Claims
• Department
• DisplayName
• Email
• Job Title
• Picker

Simply displaying the Display Name in the People Picker Combo Box will not save the selected person into SharePoint, because as mentioned, a Combo Box is supposed to pass a record, and the SharePoint People field requires a record containing the 6 attributes noted above.

To explain in a different way – to successfully Patch a SharePoint People field, we must make explicit reference to these required attributes:

Patch(
    'Data source',
    LookUp('Data source', ID=1),
    {
        'People Picker Field': {
           Claims: "i:0#.f|membership|johnsmith@companyname.com",
           Department: "",
           DisplayName: "",
           Email: "",
           JobTitle: "",
           Picture: ""
        }
    }
)
​

Notice above that we can get away with passing empty for every attribute except for the Claims token, which must have a value.

We use the Add Columns function to create two virtual columns in the Table: “_person_display_name” and “_person_email”. Because this function evaluates each record of the table, we now have the DisplayName and Email attribute for each Person in “Sample List”.

We already used the “_person_display_name” column to ensure a Display Name is visible in the People Picker Combo Box. Later, we will also leverage the “_person_email” column to update the Claims token.

We then take the output of the above and wrap it with a Filter where the Yes/No field equals true. Finally, we optionally wrap the entire expression with a Sort function to ensure the names are displayed in alphabetical order.

Loading the Form with an existing selection

1. Next, we need to ensure that the Data Card continues to display the selected Display name if we leave the Form or if we select a different record from ComboBox1. Select the Default property of the Data Card (not the Data Card Value)

2. Remove the existing expression and replace with:

{Value:ThisItem.'Issue Owner'.DisplayName}​

Writing the selected Person back to our SharePoint List

Lastly, replace the existing expression in the Update property of the Data Card with:

{
    Claims: "i:0#.f|membership|" & Lower(DataCardValue2.Selected._person_email),
    Department: "",
    DisplayName: "",
    Email: "",
    JobTitle: "",
    Picture: ""
}

————————————————————————————————————————————————

Thank you.

If you like this blog, please give it a Thumbs Up.

Imran-Ami Khan

This post was originally published on this site