Sort and filter gallery by Person/Group column

[CathD_]

Hi all!

I have a gallery that is linked to an employee skills matrix list in SP.  The Items is currently set to: 

SortByColumns(Filter([@'ListName'], StartsWith('Employee ID', TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

The problem is that the Employee ID is now a redundant column, and the SP list is based on a Person/Group selector. Is there anyway I can get the gallery to sort and filter/search based on the Person/Group column? 

[BideyYusuf]

Hi @CathD_ 

You can filter with a person column.

Filter([Datasource],PeopleColumn.DisplayName = User().FullName)

It all depends on what you want your filter to do for you. but this should work.

Hope this helps

[CathD_]

Sorry, I should have provided some further info to explain what I'm looking for. The first screenshot attached shows my current gallery. The gallery tiles are in the image, title, subtitle and body layout, and I've customized the fields to the relevant columns from my SP list, where:

• Image = This.Item.ConsultantName.Picture (from Office365 profile)
• Title = ThisItem.ConsultantName.DisplayName (from SP column 'Consultant Name', which is a Person or Group type column)
• Subtitle = ThisItem.PositionTitle (from SP column 'Position Title', which is a Single Line of Text type column)
• Body Layout = ThisItem.JobLocation (from SP column 'Office Location', which is a Single Line of Text type column)

This gallery is supported by two forms (screenshots attached):

• View Record Form
• Edit/New Record Form:
  • The first field (Consultant Name) is the Person or Group picker, where the user can find the relevant consultant from our organisation's user list. 
  • The second and third fields (Position Title and Job Location) are linked to Consultant Name, and they auto-populate once the user selects the consultant from the Person or Group picker. 

Now that gallery display and forms are working exactly how I need them to, and my SP list is automatically updating if a new record is created, or an existing record is edited.

The issue I'm having is the Sort icon, and Search functionality do not work in my gallery. The items setting for my gallery is currently set to:

SortByColumns(Filter([@'ListName'], StartsWith('Employee ID', TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

As I mentioned in my first post, using the Employee ID in this formula does not work with my gallery, as this is a redundant and hidden column in my SP list. 

Is there anyway I can amend the formula to allow both searching/filtering and sorting to be linked to the Consultant Name column (a Person or Group column)? I would like anyone using the gallery to be able to search by First Name and/or Last Name, and the sort the gallery in alphabetical order by First Name and/or Last Name. 

Thank you!!

[BideyYusuf · Accepted solution]

@CathD_ Weldone cath.

The issue with Search function is it only works with text control. What i would do is.... create a text column for the people's picker in sharepoint. eg. if my user column is USERS  i will create another column called USER text.  so whenever i create a record, i get the value i select in the People column and pass it to the User Text datacard control as a text.

Then i can now use the User Text column to do my search function.

SortByColumns(Filter([@'ListName'], StartsWith('User Text', TextSearchBox1.Text)), "User Text", If(SortDescending1, Descending, Ascending))

This is the only work around i always do, if i know I might need to search with the people column.

Try this and everything would fall in place nicely for you, and it would work seamlessly.

I hope this helps.

[CathD_]

Thanks for the quick response, I really appreciate it! It makes sense to add the extra text column, so I'll do that now.

Is there anyway to have the text column (which won't be shown in my gallery or the associated forms), to be automatically filled once the relevant consultant is selected from the people picker? 

[CathD_]

Cancel that, I fixed it! I added the extra text column as you suggested, and then added a new datacard on both forms that auto-populates with the display name of the person selected from the people picker. This has allowed me to use the search and filter functions in the gallery, with them both being linked to the new datacard.

I've then hidden new datacard on the form, so it looks exactly the same as it did before, but the search/filter functionality now works correctly. Thanks so much for your assistance, I really appreciate it!

[ScablandsKing]

@CathD_ can you share what you put in data card for your text? Having a hard time getting it to do it.