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

[HSheild]

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!

[WarrenBelz]

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

[HSheild]

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.

[WarrenBelz]

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

[HSheild]

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.

[WarrenBelz]

Hi @HSheild ,

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

[HSheild]

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.

[WarrenBelz]

Hi @HSheild ,

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

[HSheild · Accepted solution]

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.

[WarrenBelz]

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)