Filter Combo Box in Canvas App for Person or Group column in SharePoint list

(0) Share

Report

Posted on by Hamish Sheild

3,005 Most Valuable Professional

Hi,

I have a Canvas Power App that has an edit form pointing to a SharePoint list as a data source

On the Canvas app, I would like to filter the items shown in the Assigned To Combo Box based on the Person's Department. The Assigned To column in SharePoint is a data type of 'Person or Group'

I have had several attempts at this and looked at similar posts but just can't get the filter to work. I think it has something to do with the data type being 'Person or Group'.

Any help with this would be much appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 153,030 Most Valuable Professional on at

Like (1)

Report
Hi @HSheild ,

Yes, they are tricky to work with. As an example, if you had a drop-down called ddDepartment with a list of all the Departments as Choices, this should do it

Filter( AddColumns( 'Work progress tracker', "Dept", Office365Users.UserProfileV2(AssignedTo.Email).department ), Dept = ddDepartment.Selected.Value )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report

Hi @WarrenBelz

Thanks for your help. This formula is useful.

Unfortunately, this doesn't seem to work. I think there is something particular about a Combo Box control for People or Group data type.

When I unlock the control and use your formula, the choices in the combo box is a list of blank values i.e. is it not showing the name of the people I can set in Assigned To.

If I then return the formula back to the original formula, not values are shown at all in the Assigned To combo box, making me think that unlocking the control does something to it.

Was this reply helpful? Yes No
WarrenBelz 153,030 Most Valuable Professional on at

Like (1)

Report
Hi @HSheild ,

The issue I found with a bit of testing is actually getting the Combo Box to accept a Person field as the displayed fields (the code works fine in a Gallery), however I got this working by adding the required fields

Filter( AddColumns( 'Work progress tracker', "Dept", Office365Users.UserProfileV2(AssignedTo.Email).department, "StaffName", Office365Users.UserProfileV2(AssignedTo.Email).displayName, ), Dept = ddDepartment.Selected.Value )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report
Thanks @WarrenBelz , that kind of works.

I probably haven't explained myself very well. Your solution works for filtering the 'Work progress tracker' list but what I want to filter is the list of available choices of the Assigned To drop down. The Assigned To drop down in SharePoint shows everyone in the organisation - I want to filter this list.

For example, I tried the following formula (which I thought would work)

Filter(Choices('Work progress tracker'.'Assigned to'), Department<>"Student")

Here I am trying to filter the available choices for the Assigned To drop down. I'm not filtering the Work progress tracker list.

I thought I had that formula working OK, but it just not showing any values now.

Was this reply helpful? Yes No
WarrenBelz 153,030 Most Valuable Professional on at

Like (1)

Report

Hi @HSheild ,

Choices refer to a Choice or a Lookup field content - what are the Items of the drop-down ?

Was this reply helpful? Yes No
Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report
Hi @WarrenBelz

The Items are a list of "People or Group" (as defined by the People or Group column type in a SharePoint list). Essentially a list of Office 365 users or groups.

When I generate the app for the list the default Items formula is..

Choices('Work progress tracker'.'Assigned to')

I want to be able to filter the People or Group list based on the Department that the person belongs to. Department is a property of the "People or Group" data type.

Was this reply helpful? Yes No
WarrenBelz 153,030 Most Valuable Professional on at

Like (1)

Report

Hi @HSheild ,

Are you actually getting items in that drop-down with Choices('Work progress tracker'.'Assigned to')

Was this reply helpful? Yes No
Verified answer

Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report
Hi @WarrenBelz

Yes, I am getting item showing in the combo box with the Choices('Work progress tracker'.'Assigned to') formula. This is how it comes when you generate the app from the SharePoint list by default. What I did find is that if you edit the formula and then try set it back to the original, then the items don't show.

My colleague has managed to figure this out and it works with the following formula in the Items property of the Assigned To combo box control (DataCardValue16)

Filter( Office365Users.SearchUser({searchTerm: DataCardValue16.SearchText}), !StartsWith(Department, "Students") )

@WarrenBelz , thank you so much for helping me work through this! I really appreciate it.

Was this reply helpful? Yes No
WarrenBelz 153,030 Most Valuable Professional on at

Like (0)

Report

Hi @HSheild ,

Yes - I use the first bit (second line) on occasions, but I was more focussed on your Choices syntax as I have never been able to get that to display items (and of course you can simply use ListName.FieldName to get the same result) and the Department filter on it (rather than SearchText)

Was this reply helpful? Yes No