cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilD
Level 8

Filter gallery by multiselect people picker secondary attribute

This may not be possible, not sure.

 

I have a SharePoint list containing a people field called PersonName set to accept multi selections.

 

If this list contained an item that had two people entered in this people field, I would want to return gallery items that matched the department associated with one or more of the entries.

 

For example, in the table below the 'Person Name' is the people picker which contains a table of values that contains Bill & Joe's department. I can see this structure if I collect the list and examine it, just not sure how to access the department.

 

In my example, I would want to return "Item 1" & "Item 3" when "Marketing" was used as the filter term.

 

TitlePerson Name  
Item 1Bill, Joe
BillMarketing
JoePurchasing
 
Item 2Bob, Sally
BobHR
SallyIT
 
Item 3Frank 
FrankMarketing
 

 

Something like Filter(ListName,PersonName.Department) errors for "invalid argument type table, expected boolean"

 

Is this possible? Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
whatthej
Level: Power Up

Re: Filter gallery by multiselect people picker secondary attribute

Sharing my solution in case anyone else is looking for one (it took me a long while of searching!)

 

Filter(SharePointList, ... && // use the filter function 
If(IsEmpty(DataCardValue1.SelectedItems), // if the datacard/combobox is empty, i want to return all values
true, // empty- return all values
// not empty - 
true in ( // this converts the table into a single answer
   ForAll(DataCardValue1.SelectedItems, // for each item in the datacard/combobox,
   Text(Value) in // check if the selected item is in
   Concat(SharePointListColumn,Text(Value) & " ")))) // our gallery item

May not be the best way, but it works for me. 

 

Tags: gallery allow multiple selections combobox combo box filter advanced search datacards selecteditems

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Filter gallery by multiselect people picker secondary attribute

@PhilD

 

Try this -

 

Filter(ListName,PersonName.Department="Marketing") or 

 

Filter(ListName,PersonName.Department.Value="Marketing")

 

You need to specify a condition for the filter. PersonName.Department is just a table containing all the values


Vivek Bavishi aka That API Guy
PowerApps and Flow MVP
Blog | Twitter | YouTube | Community Profile | GitHub



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PhilD
Level 8

Re: Filter gallery by multiselect people picker secondary attribute

Thanks. I did use the condition (I inadvertently left off the condition when I pasted, sorry). That works when people picker is set to single selection.

 

Here is what I did and the results:

 

  1. Filter(ListName,PersonName.Department="Marketing") returns empty result when people picker is set to "Allow multiple selections".
  2. When people picker is changed in SharePoint to allow only one choice (removing all but the first choice in the process), this filter works as expected.
  3. When people picker is changed back to "Allow multiple selections" (but before any multi selections are actually added) the filter fails (returns no results - invalid argument type).

Number 3 seems to indicate that the structure of the people picker changes when the multi-select is enabled. For this to work in a multi-select environment, wouldn't the filter have to iterate through each of the multiple Department values, sort of a "sub-table" of values for each item?

 

When multi-select is enabled on the people picker and I set a label on the gallery to ThisItem.PersonName.Department  the formula displays the error: " this property expects Text values, but this rule produces incompatible Table values", presumably because it is attempting to display the "sub-table" of departments for one item in a label.

 

PhilD
Level 8

Re: Filter gallery by multiselect people picker secondary attribute

 
Super User
Super User

Re: Filter gallery by multiselect people picker secondary attribute

@PhilD

 

Yes, your observations are completely correct.

 

Generally, for a choice column with multiple sections allowed, Selecteditems is the property that we could use, However, for Person column , that property is not available.

 

I am trying to play around to see if we can still achieve it somehow.

 

Can you share how you showed the multiple slected people in your gallery, nested galleries? can you take a screenshot of the code for that.

 

Thanks


Vivek Bavishi aka That API Guy
PowerApps and Flow MVP
Blog | Twitter | YouTube | Community Profile | GitHub



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PhilD
Level 8

Re: Filter gallery by multiselect people picker secondary attribute

@ThatAPIGuy

 

I didn't successfully show the multiple departments in my gallery. My main objective was to be able to use a filter on the gallery to choose a department and return gallery items having the selected department as one of their multiple department entries. Both the gallery filter, and the label to show the selected values, failed when the people picker field was set to multiple selection.

Super User
Super User

Re: Filter gallery by multiselect people picker secondary attribute

Just a wild guess, but can you use the in-operator?

 

Filter(ListName,PersonName.Department IN "Marketing") or maybe the other way around 

Filter(ListName,"Marketing" IN PersonName.Department)

 

 

PhilD
Level 8

Re: Filter gallery by multiselect people picker secondary attribute

Pretty sure I tried this but I'm going to try it again just to be certain. I'll report back whether that works.
Garima
Level: Powered On

Re: Filter gallery by multiselect people picker secondary attribute

No "in" or "exactin" does not work either. I am trying to do the same thing that if current user is present in multi-select people picker field but it does not return anything, even though the list has values

whatthej
Level: Power Up

Re: Filter gallery by multiselect people picker secondary attribute

Sharing my solution in case anyone else is looking for one (it took me a long while of searching!)

 

Filter(SharePointList, ... && // use the filter function 
If(IsEmpty(DataCardValue1.SelectedItems), // if the datacard/combobox is empty, i want to return all values
true, // empty- return all values
// not empty - 
true in ( // this converts the table into a single answer
   ForAll(DataCardValue1.SelectedItems, // for each item in the datacard/combobox,
   Text(Value) in // check if the selected item is in
   Concat(SharePointListColumn,Text(Value) & " ")))) // our gallery item

May not be the best way, but it works for me. 

 

Tags: gallery allow multiple selections combobox combo box filter advanced search datacards selecteditems

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,026)