Issue getting User DisplayNames with a combo box and Filter in use

(0) Share

Report

Posted on by WABGOR_Dave

(Note: this is only my second PowerApp I'm attempting to build and I'm more of a sysadmin/excel data/powershell guy)

I'm trying to get some info out of Office 365 in a dropdown combo box and am unable to get the result I'm looking for.

Relevant info:

"DisplayName" is in the Search and Display Fields.
Office365Users_1 can be treated as Office365Users as I'm building the app as "me" but using another account to connect to Office365 and AzAD.

Using the following works (only returns names ending in the mid "G"s however?):

Filter(Office365Users_1.SearchUser({searchTerm:""}),AccountEnabled=true)

"DisplayName" is in the Search and Display Fields

The following does NOT work:

Filter(Office365Users_1.SearchUser({searchTerm:""}),AccountEnabled=true, Not("#EXT#" in Email), (!IsBlankOrError(mobilePhone) && !IsBlank(First(First(Office365Users_1.SearchUser({searchTerm:""})).BusinessPhones).Value)))

If I use:

Filter(Office365Users_1.SearchUser({searchTerm:""}),AccountEnabled=true, Not("#EXT#" in Email), (!IsBlankOrError(mobilePhone))

This appears to filter out only Enabled Accounts and users who have a mobile phone number which is 75% of what I want (with users up to the mid "G"'s again).

Ultimately I want to return DisplayNames of ALL users whom are: internal, active, and have a Mobile OR OfficeNumber populated. I know I can do this by dumping records into our SQL server and reading from there, but would rather not have to manage two separate databases.

-Dave

******************************************* Edit to insert solution that worked for my case *******************************************
Credit to @v-jefferni

Filter(Office365Users_1.SearchUser({top:500}),AccountEnabled=true && Not("#EXT#" in Email) && (!IsBlankOrError(mobilePhone) || !IsEmpty(ThisRecord.BusinessPhones)))

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

C-Papa 1,836 on at

Like
a
(0)

Report
Copy link

Link copied!

by default SearchUser only returns 100 results, so if it's possible, for example, that you have more than 100 John's in your company, but only 10 of them in the 'Sales' department, this may not return all of them. What you can do is to increase the number of users that is returned by SearchUser, for example, using the expression below, 999 users is the maximum.

Filter(Office365Users_1.SearchUser({searchTerm:"", top: 500}),AccountEnabled=true, Not("#EXT#" in Email), (!IsBlankOrError(mobilePhone)))
WABGOR_Dave 49 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks C-Papa for the limit increase portion. It seems to be only pulling the first 100 records (which I assume are rows and not objects) for processing and 100 results, as our AzAD only contains 331 rows of data. Any inclination as to why my extended filter:

Filter(Office365Users_1.SearchUser({searchTerm:""}),AccountEnabled=true, Not("#EXT#" in Email), (!IsBlankOrError(mobilePhone) && !IsBlank(First(First(Office365Users_1.SearchUser({searchTerm:""})).BusinessPhones).Value)))

is returning no results? I should have mentioned too that if I use this formula it defaults the Search and Display Fields to "Value" for some reason. Even if I change them back to "DisplayName", nothing is returned.
C-Papa 1,836 on at

Like
a
(0)

Report
Copy link

Link copied!
If I was you I would use ClearCollect(col_test,Office365Users_1.SearchUser({searchTerm:"", top: 500})) on a button OnSelect to store the data in a collection, view the collection and make sure you have everything you need within it, I would then add a gallery and apply your filters as above to identify which filter is not returning the expected results. gallery items would be:
Filter(col_test,AccountEnabled=true)
Filter(col_test,!IsBlankOrError(mobilePhone))
Filter(col_test,!IsBlank(BusinessPhones.Value))
once you have found which one is not working you can fix it and then join the formula into one
WABGOR_Dave 49 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks again C-Papa! I'll have some reading to do on collections and galleries it appears. 🙂

I add the ClearCollect(col_test,Office365Users_1.SearchUser({searchTerm:"", top: 500})) to OnStart in the app, so hope that is OK.

I've also thought that instead of using a gallery and collection I may instead use variables, if I can sort out the best way to do it. Alternatively I may use tables.

Time to do some research on gallery and collections.......
WABGOR_Dave 49 on at

Like
a
(0)

Report
Copy link

Link copied!

I believe, after some more research, that the main issue I'm having is that the businessphones is a table inside a table. So, I think I'm either going to have to see if the following is possible:

Create CollectionA (list of users) and CollectionB (list of telephone numbers with blanks or 0's as placeholders) and then do some sort of CollectionC that merges the two together.

I'm also seeing that with collections you can't have a formula? So that means:

Collection(CollectionA (Filter(Office365Users_1.SearchUser({searchTerm:""}),AccountEnabled=true, Not("#EXT#" in Email), (!IsBlankOrError(mobilePhone)))

won't work.

I may just have to go and keep the data in SQL (or MariaDB) to "keep things simple".
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @WABGOR_Dave ,

Please try below in Items of the Combo box:

Filter(Office365Users.SearchUser({top:500}),AccountEnabled=true && Not("#EXT#" in Email) && !IsBlankOrError(mobilePhone) && !IsEmpty(ThisRecord.BusinessPhones))

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.
WABGOR_Dave 49 on at

Like
a
(0)

Report
Copy link

Link copied!
@v-jefferni genius! This worked great, with one small modification, as I needed users who had a mobile OR a desk phone number. I changed your code to the following to use an OR and also to use my data connector "_1":

Filter(Office365Users_1.SearchUser({top:500}),AccountEnabled=true && Not("#EXT#" in Email) && (!IsBlankOrError(mobilePhone) || !IsEmpty(ThisRecord.BusinessPhones)))