Power Apps Filter Multiple Person Column (No Delegation Warning)

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 WarningSetup The SharePoint ListBuild The Power Automate Cloud FlowReturn The Flow Response To Power AppsFilter A SharePoint Multiple Choices Type Column With No Delegation WarningSetup The SharePoint ListBuild The Power Automate Cloud FlowReturn The Flow Response To Power AppsFilter A SharePoint Multiple LookUps Type Column With No Delegation WarningSetup The SharePoint ListBuild The Power Automate Cloud FlowReturn 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

IDTitleProject Team
1Time Off Request AppMatthew Devaney, Mary Baker
2Safety Incidents ReportingMary Baker, David Johnson, Alice Lemon
3Job Site Inspection AppAlice Lemon, Sarah Green
4Expense Report AppMary Baker, David Johnson, Matthew Devaney
5Accounting System AutomationMatthew Devaney


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.

IDTitleProjectTeam
1Time Off Request AppMatthew Devaney, Mary Baker
4Expense Report AppMary Baker, David Johnson, Matthew Devaney
5Accounting System AutomationMatthew Devaney



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

IDTitleSkillsRequired
1Time Off Request AppPower Apps
2Safety Incidents ReportingPower Apps, Power Automate, Power BI
3Job Site Inspection AppPower Apps, Power Automate
4Expense Report AppPower Apps, Power BI, Power Virtual Agent
5Accounting System AutomationPower Automate


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.

IDTitleSkillsRequired
2Safety Incidents ReportingPower Apps, Power Automate, Power BI
4Expense Report AppPower Apps, Power BI, Power Virtual Agent



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

IDTitleDepartment
1Time Off Request AppHuman Resources, Accounting, Operations
2Safety Incidents ReportingOperations, Project Management
3Job Site Inspection AppOperations, Project Management
4Expense Report AppHuman Resources, Accounting
5Accounting System AutomationAccounting



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

  • ID – autonumber column
  • Title – text column

IDTitle
1Operations
2Procurement
3Project Management
4Legal
5Accounting
6Human Resources


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.

IDTitleDepartment
1Time Off Request AppHuman Resources, Accounting, Operations
4Expense Report AppHuman Resources, Accounting
5Accounting System AutomationAccounting



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.

Matthew Devaney

Subscribe
Notify of
guest

6 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Kim
Kim
1 month ago

This is a great solution to the delegation issue. However, your third example using the multiple lookups has some errors where you describe how to build the SP List. Is the field named to be Skills Required or Department? Also, errors in how you build the flow. You mention the trigger is ‘Claims’, but shouldn’t it be department? I think maybe you cut and pasted from the previous example, lol?

Otherwise, it’s great. Thanks

Jeroen
Jeroen
1 month ago

Great asset, Matthew, thanks for this ultimate description!

One question came up over here. Taking your first example, how to look at it in terms of performance? The Power Automate will run asynchronously I presume? How quick will the results be available in the build app?

Bradley
Bradley
24 days ago

Thank you Matthew this is life saving function for people who use SharePoint as their database!