cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Digital
Level 8

Office365Users.SearchUser() in gallery duplicating users

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.

 

 

9 REPLIES 9
Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

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?

 

Eelman
Level 10

Re: Office365Users.SearchUser() in gallery duplicating users

@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??

Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

@Eelman , 

 

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.

Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

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.

 

 

Eelman
Level 10

Re: Office365Users.SearchUser() in gallery duplicating users

@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.

Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

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

Community Support Team
Community Support Team

Re: Office365Users.SearchUser() in gallery duplicating users

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

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.

 

Highlighted
Digital
Level 8

Re: Office365Users.SearchUser() in gallery duplicating users

@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.

 

 

 

 

 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,271)