Power Apps Filter Multiple Person Column (No Delegation Warning)

A SharePoint multiple person column can be filtered with no delegation warnings in Power Apps. So can a multiple choices column type and a multiple lookups type column. No, it cannot be done with the Power Apps filter function. But it can be done by using a Power Automate flow. Pass in the filter criteria, let a flow do the heavy lifting and return the results. No premium licensing needed. In this article, I will show how to filter complex SharePoint columns containing multiple values using a flow.

Table of Contents:

• Filter A SharePoint Multiple Person Type Column With No Delegation Warning
  ◦  Setup The SharePoint List
  ◦  Build The Power Automate Cloud Flow
  ◦  Return The Flow Response To Power Apps

• Filter A SharePoint Multiple Choices Type Column With No Delegation Warning
  ◦  Setup The SharePoint List
  ◦  Build The Power Automate Cloud Flow
  ◦  Return The Flow Response To Power Apps

• Filter A SharePoint Multiple LookUps Type Column With No Delegation Warning 
  ◦  Setup The SharePoint List
  ◦  Build The Power Automate Cloud Flow
  ◦  Return The Flow Response To Power Apps

Filter A SharePoint Multiple Person Type Column With No Delegation Warning

Setup The SharePoint List

Create a SharePoint list named Projects Backlog with the following columns:

• ID – autonumber column
• Title – text column
• ProjectTeam – person column with allow multiple selections set to true
  
  

Build The Power Automate Cloud Flow

Open Power Automate and create a new cloud flow named FilterSPMultiplePeople. Add the Power Apps V2 trigger with a required field called Claims. Then add a SharePoint – Get Items action that targets the Projects Backlog list.

Write this code in the Filter Query field to filter the multiple people type column.

ProjectTeam/Name eq 'triggerBody()['text']'

Complete the flow using the following actions.

Return The Flow Response To Power Apps

Make a People Picker combobox and use it to select a person’s name. Learn how to make a people picker by reading this article.

Connect the FilterSPMultiplePeople flow to Power Apps. Then write this code in the OnSelect property of a button. It will pass the claims token of the selected person into the flow and return a JSON with the matching results. Then we use the ParseJSON function to convert the JSON to a collection.

ClearCollect(
    colProjects,
    ForAll(
        Table(ParseJSON(FilterSPMultiplePeople.Run($"i:0#.f|membership|{cmb_Person.Selected.UserPrincipalName}").response)),
        {
            ID: Value(Value.ID),
            Title: Text(Value.Title),
            ProjectTeam: ForAll(
                Table(Value.ProjectTeam),
                {
                    Claims: Text(Value.Claims),
                    DisplayName: Text(Value.DisplayName),
                    Email: Text(Value.Email),
                    Picture: Text(Value.Picture),
                    Department: Text(Value.Department),
                    JobTitle: Text(Value.JobTitle)
                }
            )
        }
    )
)

For reference, the claims token passed into the flow for Matthew Devaney looks like this.

"i:0#.f|membership|md@matthewdevaney.com"

The collection colProjects shows the matching results with Matthew Devaney in the ProjectTeam field.

When the colProjects collection is displayed in a Power Apps gallery it looks like this:

Filter A Sharepoint Multiple Choices Type Column With No Delegation Warning

Setup The SharePoint List

Create a SharePoint list named Projects Backlog with the following columns:

• ID – autonumber column
• Title – text column
• SkillsRequired- choice column with allow multiple selections set to true
  
  

Build The Power Automate Flow

Open Power Automate and create a new cloud flow named FilterSPMultipleChoices. Add the Power Apps V2 trigger with a required field called Claims. Then add a SharePoint – Get Items action that targets the Projects Backlog list.

Write this code in the Filter Query field to filter the multiple choices column.

SkillsRequired eq 'triggerBody()['text']'

Complete the flow using the following actions.

Return The Flow Response To Power Apps

Create a combobox to allow the user to select a value from the SkillsRequired choices field.

Connect the FilterSPMultipleChoices flow to Power Apps. Then write this code in the OnSelect property of a button. It will pass the claims token of the selected person into the flow and return a JSON with the matching results. Then we use the ParseJSON function to convert the JSON to a collection.

ClearCollect(
    colProjects,
    ForAll(
        Table(ParseJSON(FilterSPMultipleChoices.Run(cmb_Choice.Selected.Value).response)),
        {
            ID: Value(Value.ID),
            Title: Text(Value.Title),
            SkillsRequired: ForAll(
                Table(Value.SkillsRequired),
                {
                    ID: Value(Value.ID),
                    Value: Text(Value.Value)
                }
            )
        }
    )
)

The collection colProjects shows the matching results with Power BI in the SkillsRequired field.

When the colProjects collection is displayed in a Power Apps gallery it looks like this:

Filter A SharePoint Multiple LookUps Type Column With No Delegation Warning

Create The SharePoint Lists

Create a SharePoint list named Projects Backlog with the following columns:

• ID – autonumber column
• Title – text column
• Department- lookup column with allow multiple selections set to true
  
  

Then create another SharePoint list called Departments with the following columns:

• ID – autonumber column
• Title – text column
  
  

Build The Power Automate Flow

Open Power Automate and create a new cloud flow named FilterSPMultipleChoices. Add the Power Apps V2 trigger with a required field called departmentID. Then add a SharePoint – Get Items action that targets the Projects Backlog list.

Write this code in the Filter Query field to filter the multiple lookups column.

Department/Id eq 'triggerBody()['text']'

Complete the flow using the following actions.

Return The Flow Response To Power Apps

Create a combobox to allow the user to select a value from the Department lookups field.

Connect the FilterSPMultipleLookUps flow to Power Apps. Then write this code in the OnSelect property of a button. It will pass the claims token of the selected person into the flow and return a JSON with the matching results. Then we use the ParseJSON function to convert the JSON to a collection.

ClearCollect(
    colProjects,
    ForAll(
        Table(ParseJSON(FilterSPMultipleLookUps.Run(cmb_LookUp.Selected.Id).response)),
        {
            ID: Value(Value.ID),
            Title: Text(Value.Title),
            Department: ForAll(
                Table(Value.Department),
                {
                    ID: Value(Value.ID),
                    Value: Text(Value.Value)
                }
            )
        }
    )
)

The collection colProjects shows the matching results with Accounting in the Department field.

When the colProjects collection is displayed in a Power Apps gallery it looks like this:

Questions?

If you have any questions or feedback about Power Apps Filter Multiple Person Column (No Delegation Warning) please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.