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|[email protected]"



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

20 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Kim
Kim
1 year 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 year 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
1 year ago

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

Ben
Ben
1 year ago

Hi Matthew, I must say I’m lucky to see this approach from you as I need exactly this method.

One question, my app will be used by ~5000 users on a daily basis. If the first approach (flow) is used, will I encounter any 429 limits here given that the flow connections used is mine? Thanks!

Paul
Paul
1 year ago

Hi Matthew,
 
Thank you so much for this solution! I’ve been searching for a long time to figure out how best to filter/search to avoid delegation. This really seems to be the best compared to others I’ve seen. You really are providing a great public service!
 
There are 2 scenarios that I’m wondering if your solution could be adapted for:

  1. Is there a way to choose multiple selections from a combo box in order to filter the multi select column?
  2. Related to number 1, I have a Projects list/gallery and I want to be able to filter the projects based on whether an ‘Assigned To’ People Picker column has the name of a direct report of the current user. The ‘Assigned To’ column is multi select. I can create a collection of direct reports of a person. Can I then feed this into the Flow somehow? ClearCollect(

   colSubordinates,
   Office365Users.DirectReportsV2(varUser.Email).value
);
 
Any thoughts?
 
Best,
 
Paul

Paul
Paul
1 year ago

Thanks for the response Matthew- still a great technique which will make a big difference on the project I’m working on!

Kirk
Kirk
1 year ago

Hello Matt. This is helpful. I’m having a problem with the “SharePoint Multiple Person Type Column” solution, getting the DisplayNames to appear in my gallery. I’m using this in the Text property of the gallery label… Concat(ThisItem.ProjectTeam,DisplayName, “,”)But the label isn’t pulling the names. Any ideas???

Kirk
Kirk
1 year ago
Reply to  Kirk

So using “Monitor” I found out what was going on. For SOME odd reason, the JSON data returns the “ProjectTeam” info with the tag “ProjectTime”. (Attached a screenshot of the Response tab in Monitor)

I double checked that I didn’t somehow name it initially wrong by going to list settings, and found the actual field name is “ProjectTeam”.

I cannot figure out how that happened. But when I updated the formula for OnSelect of my button with the following – I can access the returned values.
ClearCollect(
  colProjects,
   ForAll(
     Table(ParseJSON(FilterSPMultiplePeople.Run($”i:0#.f|membership|{ProjectTeamCardValue.Selected.UserPrincipalName}”).response)),
     {
       ID: Value(Value.ID),
       Title: Text(Value.Title),
       ProjectTeam: ForAll(
         Table(Value.ProjectTime),
         {
           Claims: Text(Value.Claims),
           DisplayName: Text(Value.DisplayName),
           Email: Text(Value.Email),
           Picture: Text(Value.Picture),
           Department: Text(Value.Department),
           JobTitle: Text(Value.JobTitle)
         }
       )
     }
   )
)

1.png
Kirk
Kirk
1 year ago
Reply to  Kirk

It was my Select action. I must have missed an autocorrect change!!! Geeze.

Dan
Dan
1 year ago

Hey Matthew! I tried this with your first example – multiple people column. I tried this on two separate sites with separate lists. I am getting this error no matter what I try when the Get Items action runs:

Exception from HRESULT: 0x80131904

I can do the filter on single-select people columns, but never multi-select. What would be the issue? I also tried removing the “claims” part and just trying the email directly. Still no luck (but both versions work for single select people columns so the formatting must be correct).

What could be the issue?

Brian
Brian
1 year ago

Super insightful! I am doing your First example with searching a multiperson column. Now I was wondering how I can use the Filtered Collection to then view the actual List rather than the collection we created. Since I am having a hard time recreating it with my list with multiple records and data types Basically I cant save over the Choice Type, and how would I also save one person rather than multiple people for the Instructor Column?

The Course/Category/Action are all Choice Columns.

Ideally would either like to filter based on the ID of the ones with the correct person, or make the collection mirror the list with the correct data types? Having a problem handling record data types.

ClearCollect(
  colProjects,
  ForAll(
    Table(ParseJSON(SPFilterMultiplePeople.Run($”i:0#.f|membership|{cbStudentSearch.Selected.UserPrincipalName}”).response)),
    {
      ID: (Value.ID),
      Subject: Text(Value.Subject),
        Course: (Value.Course),
CourseNumber: (Value.CourseNumber),
Date: (Value.Date),
Instructor: (Value.Instructors),
Category: (Value.Category),
Performance: (Value.Performance),
Action: (Value.Action),
Recommendation: (Value.Recommendation),
ActionChoice: (Value.ActionChoice),
FollowUp: (Value.FollowUp),

      Students: ForAll(
        Table(Value.Students),
        {
          Claims: Text(Value.Claims),
          DisplayName: Text(Value.DisplayName),
          Email: Text(Value.Email),
          Picture: Text(Value.Picture),
          Department: Text(Value.Department),
          JobTitle: Text(Value.JobTitle)
        }
      )
     
    }
  )

)

Last edited 1 year ago by Brian
Kai
Kai
1 year ago

can you help me understand why you write:

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

Department/Id eq ‘triggerBody()[‘text’]’

when right above that you already define that same field as Department/Id eq [the field DepartmentID]?

i’m sure i’m missing something simple. Thanks

James Palumbo
James Palumbo
5 months ago

Where or how did you find out that using ProjectTeam/Name eq ‘triggerBody()[‘text’]’ works? That one small piece of information fixed my problems. Looking at the data I would never have thought that would work. There is DisplayName and Claims stored in there. I am just very curious about using /Name and how that works.

Last edited 5 months ago by James Palumbo
Jim Dunn
2 months ago

I got this working, but how can I use this in onstart and pass the comobox value in automatically?

Yassine
Yassine
27 days ago
Reply to  Jim Dunn

I encountered the same problem. Does anyone have a solution for it? Thank you in advance.