Unanswered

Office365Users.SearchUser() in gallery duplicating users

(0) Share

Report

Posted on by Digital

1,207

I have a gallery of users. which is filtered using the A-Z buttons under the search. I want the user to press a letter and employees whose first name begins with that letter to be displayed. The OnSelect of the A-Z buttons set a variable with the corresponding letter.

Employees are being displayed correctly when varSelectedFilter = "All", but when a letter button is pressed the gallery contains duplicate entries (see screen shot example).

The searchTerm will populated with the letter of the button pressed

Office365Users.SearchUser({searchTerm: varSelectedFilter})

The searchTerm property in Office365Users applies to: display name, given name, surname, mail, mail nickname, and user principal name. I want to display just those employees where first name starts with the selected letter, so I use StartsWith(DisplayName, varSelectedFilter) to filter.

Items property of Gallery1

If(
varSelectedFilter="All",
Sort(Sort(Filter(Office365Users.SearchUser({searchTerm: txt_SearchBox.Text, top:999}),!IsBlank(Mail)),Surname, Ascending),GivenName,Ascending),
Sort(Sort(Filter(Office365Users.SearchUser({searchTerm:varSelectedFilter,top:500}),!IsBlank(Mail),StartsWith(DisplayName,varSelectedFilter)),Surname, Ascending),GivenName,Ascending)
)

The interface

Screen Shot 2019-11-06 at 17.12.13 copy.png

Maybe this isn't the best strategy. If so I'd appreciate any help pointing me in the right direction.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Digital 1,207 on at

Like (0)

Report
Copy link

Link copied!
I think I've found what is causing the duplicate gallery items.

If I want only those employees whose GivenName begins with 'A' i.e. user has pressed button A (varSelectedFilter= A)

Office365Users.SearchUser({searchTerm: varSelectedFilter})

SearchTerm applies to: display name, given name, surname, mail, mail nickname, and user principal name.

I was wondering if more than one of these fields starts with A, could that be the reason for the duplicate entries? There are only ever two duplicates though. If for example: display name, given name, surname and user principal name , all began with A wouldn't there be 4 duplicates?

Is there a way to filter out the duplicates without using Distinct as this only returns a single column? Or a better way to return only those employees with name beginning with the selected letter without the duplicates?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Digital do users in your Office365 list have multiple user accounts? I know that in my org users can have up to 4 email accounts, most have 2 and I have one (I'm new 🙂 ).

If this is the case maybe try filtering the items of your gallery using some part of the email addresses that you want to display?
Eg. digital@BIGSoftwareCo.com & digital@LITTLESoftwareCo.com
Filter out the LITTLESoftware email addresses, maybe??

Was this reply helpful? Yes No
Digital 1,207 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous ,

Thanks for the suggestion. Unfortunately the duplicate users are identical records - same email addresses, id etc. They aren't duplicated in the source - if my search text is a first name then no duplicates are returned. Only when the search text passed is a single letter do I get duplicates e.g. Office365Users.SearchUser({searchTerm: "A"}).

Bizarre thing is the first record returned is never duplicated and it seems result sets less than 50 are also not duplicated...weird.

Was this reply helpful? Yes No
Digital 1,207 on at

Like (0)

Report
Copy link

Link copied!

Microsoft's own Org Browser example PowerApp does exactly the same thing, so think I'll have to give up on this one and remove the A-Z navigation or just accept the duplicates.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Digital It seems to be working for me using:

If(varLetter="All",Office365Users.SearchUser({top:999}),Filter(Office365Users.SearchUser({searchTerm: varLetter,top:999}),StartsWith(DisplayName,varLetter)))

Replace 'varLetter' with your varSelectedFilter variable. Also, set the OnSelect of your "All" to update the same variable to "All".
You can just wrap your Sorts around this as well.

Was this reply helpful? Yes No
Digital 1,207 on at

Like (0)

Report
Copy link

Link copied!

Thanks again for the suggestion, much appreciated.

That is the formulae I've been trying that produces duplicates.

When you tried it was it for result sets bigger than 100? Seems that is when i get the duplicates. I will try it for a different organisation and report back.

Cheers

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Digital ,

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. According to the formula you provided, even though, you typed varSelectedFilter as search term within the Office365Users.SearchUser, it would only return single one record from your Org 365 tenant.

Please check if there are Multiple records existd for same person in your Office 365 Orgs. You could consider display the User Id of the duplicated users in your Gallery (using ThisItem.Id formula), then check if they are the same. If not, it means that there are multiple records for same person in your O365 Org.

Please also consider modify your formula as below:

Sort( Sort( Filter( Office365Users.SearchUser({searchTerm: txt_SearchBox.Text, top:999}), !IsBlank(Mail), If( varSelectedFilter = "All", true, StartsWith(DisplayName, varSelectedFilter) ) ), Surname, Ascending ), GivenName,Ascending )

or

SortByColumns( Filter( Office365Users.SearchUser({searchTerm: txt_SearchBox.Text, top:999}), !IsBlank(Mail), If( varSelectedFilter = "All", true, StartsWith(DisplayName, varSelectedFilter) ) ), "Surname", Ascending, "GivenName", Ascending )

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

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

Was this reply helpful? Yes No
Digital 1,207 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your help. Unfortunately that solution doesn't work for me. I think it may be due to the size of my dataset.

@v-xida-msft ,

Using your formulae employees with names towards the end of the alphabet don't display in the gallery e.g. when pressing on Z, the gallery is empty.

That is the reason I specified varSelectedFilter as the searchTerm. The idea was to retrieve all the employees where GivenName begins with the letter you press. If those beginning with XYZ are number 1000 onwards they are not returned.

I added a label to the gallery with ThisItem.Id and the duplicates have the same ID.

Thanks again for your suggestion.

Was this reply helpful? Yes No
Digital 1,207 on at

Like (1)

Report
Copy link

Link copied!

@v-xida-msft ,

Using the proposed formulae, if I enter a single letter in the search box e.g. 'J' , I get duplicates returned. If I enter another letter a single user record is displayed in the gallery. This doesn't matter as I want to use the A-Z buttons but just wanted to point out the duplicate record problem seems to be related to entering a single letter in the searchTerm parameter. Duplicates occur when more than 50 records are returned.

Filtering on the single letter in variable would be a good strategy, however this doesn't work for users with first name beginning with Z. Seems that employee record isn't retrieved in the set filter is applied to. If I enter Z in search box the employee is retrieved.

Was this reply helpful? Yes No