Person column where the results are filtered

[jmiller87]

I have a SharePoint list and an associated PowerApp. One of the columns in this SharePoint list is a person or group column. The PowerApp was made from the SharePoint list and so it's essentially a form. Currently when someone searches using the person or group column to find a staff member they're able to see every user in the address book. It appears the person or group column grabs users based off one of two selections. Either "all users" or a SharePoint group. Creating a SharePoint group and then adding the users manually would work but it has flaws. The main problem with the SharePoint group is that it won't be dynamic. Once a user leaves or transfers buildings then I'll be forced to manually go in and remove/add users to keep the various SharePoint groups updated. The other issue is there's several similar PowerApps of this variety so it would involve creating dozens of SharePoint groups and keeping them all updated.

So my question is...is there a way in PowerApps to filter/narrow the results of this person or group column when searched? Ideally I would like to have the search results display only the users who share the same office location as the currently signed in user to the PowerApp. That way when the user goes to search for a user the only people that will display in the results would be co-workers in the same building. We have security groups that are being synced from AD that we could leverage as well in place of office location.

I've played around with drop-down boxes and galleries and have read up on collections but I can't seem to find a solution that will display results pointed at a certain security/AAD group or a shared user attribute.

Any help would be greatly appreciated.

[v-xida-msft]

Hi @jmiller87 ,

Could you please share a bit more about your scenario?

Do you only want the Person Picker ComboBox to display users with same Office Location as current login user?

Based on the needs that you mentioned, I afraid that there is no direct way to achieve your needs. As an alternative solution, I think the Office 365 Users connector could achieve your needs. I have made a test on my side, please take a try with the following workaround:

1. Add Office 365 Users connection in your app.

2. Unlock the Person field data card in your Edit form, then set the Items property of the Person Picker ComboBox to following:

Filter(
      Office365Users.SearchUser({searchTerm:DataCardValue13.SearchText, top:999}),
      Office365Users.UserProfile(Id).OfficeLocation = Office365Users.MyProfile().OfficeLocation
)

On your side, you should type:

Filter(
      Office365Users.SearchUser({searchTerm: PersonPickerComboBox.SearchText, top:999}),
      Office365Users.UserProfile(Id).OfficeLocation = Office365Users.MyProfile().OfficeLocation
)

After that, when you click the Person Picker ComboBox, it would display as below:

you could also type searck key within the search box to find the specific person.

Note: Currently, there is a limit with the Office365Users.SearchUser() function -- It could only retrieve 999 records at most once time from your Office 365 Tenant.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[jmiller87]

Thank you for your response.

Yes, that's exactly what I'm aiming for. Where they click the person field and then a list populates of those who share the same office location. And then if they want to search instead of scroll then they have that option.

I tried following your steps but it's not liking the Items property that I've entered for the Combo Box. My person field has a DataCardValue of 11 so the Items property I used was...

Filter(Office365Users.SearchUser({searchTerm:DataCardValue11.SearchText, top:999}), Office365Users.UserProfile(Id).OfficeLocation = Office365Users.MyProfile().OfficeLocation)

I'm getting this error when using that Items property for DataCardValue11...

Office365Users.SearchUser failed. Status code 429. "message". Rate limit is exceeded. Try again in 48 seconds.

I'm unable to access the "configure it as a person layout" window that's in your first screenshot. I do have allow searching turned on for the combobox. I'm wondering if maybe that's part of the issue.

[jmiller87]

I changed the 999 value to 300 and the error went away and it's pulling in only those that have a matching office location. Which is great!

Thank you so much!

[jmiller87]

The remaining issue that I've found is while the correct users are now displaying, it's not adding that value to the associated Person/Group column of the SharePoint list. I can select a user and submit a form but when I check the SharePoint list the Teacher column is blank for the submitted item on the SharePoint list even though I searched for and selected a user from within the PowerApp.

[v-xida-msft · Accepted solution]

Hi @jmiller87 ,

Please consider take a try with the following workaround:

Set the Update property of the Teacher Data card in your Edit form to following:

{
  Claims: "i:0#.f|membership|" & Lower(DataCardValue11.Selected.Mail),
  DisplayName: DataCardValue11.Selected.DisplayName,
  Email: DataCardValue11.Selected.Mail,
  Department: "", 
  JobTitle: "", 
  Picture: ""
}

Note: I assume that the Teacher Person column is a Single value column.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[jmiller87]

That did it. Yes, the Teacher Person/Group column is setup to only allow a single value. Changing the Update property now fills out the Teacher column in the SharePoint list when submitting the form.

Thank you so much for the help!

[ashokpershad]

@v-xida-msft 

I faced same issue and tried to implement same solution and it seems working as now I am able to see all employees in the drop down list. Only, I am not aware how to add the "Search box" as you mentioned in your reply above ("you could also type search key within the search box to find the specific person").

Could you help me out because now the drop down (attached pic) is showing all the employees names and i cannot search specific names.