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

Filter gallery by distinct person entries in SharePoint list

Hi.

 

I have a simple SharePoint list with a few hundred items, each having a standard people picker column 'Employee' containing an Office 365 account indicating the user who owns the item. Any given employee may have one or many items in the SP list.

 

What I would like to achieve is a gallery that presents all the users represented in the SP list's Employee field, but only showing one instance for each employee.

 

SP List

IDPeople Picker FieldTitle
1John SmithSP item title
2John SmithSP item title
3Jane DoeSP item title
4Bob SmithSP item title

 

Desired result

In this case, a gallery (or some other control) showing 'John Smith', 'Jane Doe', 'Bob Smith' is desired but I cannot seem to achieve. 

 

Current method

Currently I use a gallery filter and text box that allows a search of all users and when item is selected, navigate to SP list gallery, filtered by this chosen user.This works but requires a search of thousands of users when only those that have entries in the SP list would be needed.

 

Choose user from all users

If(UserSearchInput.Text="",Office365Users.SearchUser({searchTerm: "NoFind"}),Office365Users.SearchUser({searchTerm: UserSearchInput.Text}))

Set variable with selected user and navigate to SP list gallery

Set(varSelectedUser,ThisItem.DisplayName);Navigate(ChosenEmployeeGoals,Cover)

Show SP list gallery filtered by var value

Filter(Goals, Employee.DisplayName = varSelectedUser)

 

I am fairly new to Powerapps and I am having trouble understanding how this should be approached, even in a general way. The distinct function returns a single column table so that doesn't appear to apply? Perhaps a combo box control would be better suited for this purpose? What I am trying to do does not seem to be an uncommon application, is there a standard way of doing this that I am missing?

 

Hoping someone can steer me in the right direction and thanks so much!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter gallery by distinct person entries in SharePoint list

Hi @PhilD,

 

If the People Picker field is a single value of Person or group field, then you may follow the steps below to generate the distinct value:

1. Under the OnVisible (or OnStart) property of the first screen, add the following formula:

ClearCollect(OwnerData, Distinct(AddColumns(ListName,"OwnerName",Person.DisplayName).OwnerName,OwnerName))

2. Add a Dropdown control, with the items property set to:

OwnerData

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

PhilD
Level 8

Re: Filter gallery by distinct person entries in SharePoint list

Thanks Michael!

 

This worked right away... I think I was making this more complicated then I needed to. I struggled with this for several hours and had acually been almost there but I just couldn't get the expression syntax to work.

 

Thanks again for taking the time to answer my question.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Filter gallery by distinct person entries in SharePoint list

Hi @PhilD,

 

If the People Picker field is a single value of Person or group field, then you may follow the steps below to generate the distinct value:

1. Under the OnVisible (or OnStart) property of the first screen, add the following formula:

ClearCollect(OwnerData, Distinct(AddColumns(ListName,"OwnerName",Person.DisplayName).OwnerName,OwnerName))

2. Add a Dropdown control, with the items property set to:

OwnerData

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

PhilD
Level 8

Re: Filter gallery by distinct person entries in SharePoint list

Thanks Michael!

 

This worked right away... I think I was making this more complicated then I needed to. I struggled with this for several hours and had acually been almost there but I just couldn't get the expression syntax to work.

 

Thanks again for taking the time to answer my question.

View solution in original post

Anonymous
Not applicable

Re: Filter gallery by distinct person entries in SharePoint list

I've tried this about 50 times and cannot get it to work... It won't even work on simple fields, without the same complexity of a people field.

 

Note - I need to use it for a people field for the exact same reason as the OP.

 

Changing only the things to mach my data, I have a list called Projects, and a column called Analyst, and the data collection (as far as I can tell) works, but comes up empty:

 

ClearCollect(OwnerData, Distinct(AddColumns('Projects',"OwnerName",Analyst.DisplayName).OwnerName,OwnerName))

Is there any way to even check if any data is being collected?

 

Furthermore, I now seem to be getting an error on the dropdown control: "This property cannot consume the type of Tables that this rule produces, due to incompatible schemas"

PeterDonohue
Level: Powered On

Re: Filter gallery by distinct person entries in SharePoint list

When I attempt to use this, I receive the yellow triangle and "Warning: The columns produced by this rule / nested tables etc". Any thoughts? This is amajor flaw in PowerApps

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,882)