Filter SharePoint single or multi-select People Picker based on another List of People

[Amik]

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.

Instructions

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 select People Picker field called "Issue Owner". Note you can alternatively use a multi-select people picker field if required.

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:

Optional steps

The following steps are optional. They have been added here so we can test the solution works:

1. I want my Form to be populated with information based on a selected record. To do this, insert a Gallery into the screen. 

2. Set the Items property to your Data Source. For this demo, I am going set the Items property to:

Sort(
    'Sample Issue Data',
    Modified,
    SortOrder.Descending
)

3. Insert a Label control inside the Gallery and on the Text property of the Label, enter:

ThisItem.'Issue Owner'.DisplayName

4.  On the OnSelect property of the Gallery, enter:

Set(
    gbl_record,
    ThisItem
);
EditForm(Form1)

Note that "gbl_record" Global Variable, but the same logic would apply if you want to use a Context Variable instead.

4. Add a Button control into your App and set the OnSelect property to: 

SubmitForm(Form1)

5. Add another Button control into your app, and set the OnSelect property to:

NewForm(Form1)

6. On the Item property of the Form, enter:

gbl_record

7. On the OnSucess property of the Form, enter:

Set(
    gbl_record,
    Self.LastSubmit
)

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 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"
  
• The Display Name of my single select people picker field from "Sample List" is "Single Choice People Field"
• The Logical Name of my single select people picker field from "Sample List" is "SingleChoicePeopleField". This is important for the next step.
• Further reading on Display Names and Logical Names: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/operators 
  
  

Configure the People Picker Combo Box

1. Unlock the Data Card for the People Field

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

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

With(
    {
        _prefiltered_data: Filter(
            'Sample List',
            'Yes/No Field'
        )
    },
    Sort(
        ForAll(
            Ungroup(
                _prefiltered_data,
                "SingleChoicePeopleField" //Note we are using the Logical Name
            ),
            'Single Choice People Field'
        ),
        DisplayName,
        SortOrder.Ascending
    )
)

If you expect to have duplicate people in your list, you can alternatively use:

With(
    {
        _prefiltered_data: Filter(
            'Sample List',
            'Yes/No Field'
        )
    },
    Sort(
        ForAll(
            Distinct(
                _prefiltered_data,
                'Single Choice People Field'
            ),
            ThisRecord.Value
        ),
        Email,
        SortOrder.Ascending
    )
)

4. In the properties pane for the People Picker Combo Box, select Edit on the Fields property and select "Display Name" for Primary Text.

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

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 ForAll function to return a table of records for each person in Sample List, and then we use the UnGroup function to display the required attributes we need. As a bonus, we also "pre-filter" the our list by only returning records 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.

------------------------------------------------------------------------------------------------------------------------------------------------

Thank you.

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

Imran-Ami Khan