cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HSheild
Super User
Super User

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

Hi,

 

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

HSheild_0-1658445775963.png

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!

 

HSheild_0-1658464756086.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
HSheild
Super User
Super User

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.

View solution in original post

9 REPLIES 9
WarrenBelz
Super User
Super User

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
Super User
Super User

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.

 

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
Super User
Super User

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.

 

 

Hi @HSheild ,

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

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.

Hi @HSheild ,

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

HSheild
Super User
Super User

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.

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)

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,312)