Filter gallery by multiselect people picker secondary attribute

[PhilD]

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.

Title	Person Name
Item 1	Bill, Joe	Bill Marketing Joe Purchasing
Item 2	Bob, Sally	Bob HR Sally IT
Item 3	Frank	Frank Marketing

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

Is this possible? Thank you in advance!

[ThatAPIGuy]

@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

[PhilD]

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.

[ThatAPIGuy]

@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

[PhilD]

@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.

[BitLord69]

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]

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]

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 · Accepted solution]

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