cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Query Active Directory

Hello Folks.

 

We have got a requirement to show some default properties and custom properties as well from the AD and filter accordingly - the aim is to sort the data by User, Department, Location, etc...

 

When it comes to Search by User, I am aware of the Office365Users connector which works seamlessly, however the complete requirement is not fulfilled.

 

I saw that powerapps have got Azure AD  which return only a single user and not the entire list of users, also the connector does not return custom attributes of Azure AD entities.

https://docs.microsoft.com/en-us/connectors/azuread/ 

 

The other alternative would be to use Graph API and build a custom collection, on the other hand the app could be penalized in performance. It seems that Graph API not returning all the user properties: https://graph.microsoft.com/v1.0/users

 

I heard of this however it is a paid one: https://www.cdata.com/kb/tech/activedirectory-odata-powerapps.rst

 

Is there another way ? or connector available ?

 

@EricRegnier @v-jefferni this is related to the previous post - your inputs would be appreciated on this approach.

Telephone Directory 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @fmourtaza :

Q:As we are querying the 200 top users, so the Collect ……

A:Yes.You can also adjust the parameter 200 to other numbers to get more records.

Q: Is there any way to get these collection OnLoad, I ……

A:I sugget you put this code into the apps' OnStart Property.This command will only be executed once during use, and will not affect the user experience too much.

  In addition,This formula is indeed flawed in terms of performance, but there does not seem to be a simpler and more straightforward way.

Q:The MyOrgUsers collection do not have all the columns from the UserList collection (for example: Department), any clue why is it so ?

A:The point is AzureAD.GetUser() function does not return Department.If you need to combine the return values of AzureAD.GetUser() and Office365Users.SearchUserV2(), you also need to use the AddColumns function to merge the two tables.For Example:

 

 

ClearCollect(UserList,Office365Users.SearchUserV2({top:80}).value);
ClearCollect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-10),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-20),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-30),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-40),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-50),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-60),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-70),10),AzureAD.GetUser(Mail)));
ClearCollect(FinalResult,AddColumns(MyOrgUsers,"Department",LookUp(UserList,Mail=mail).Department))

 

 

Best Regards,

Bof

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Hi @fmourtaza :

Do you want to get all users in the organization and their detailed information?

Using Garph can get all user in Org.

But by default, only a limited set of properties are returned (businessPhonesdisplayNamegivenNameidjobTitlemailmobilePhoneofficeLocationpreferredLanguagesurname, and userPrincipalName).Certain properties cannot be returned within a user collection. The following properties are only supported when retrieving an single user: aboutMe, birthday, hireDate, interests, mySite, pastProjects, preferredName, responsibilities, schools, skills, mailboxSettings.

In addition,you can also use 'Office365Users.SearchUserV2({top:1000}).value' to get the list of users in your Org.

If you think its return value contains too few fields, you can also use this formula:

AddColumns(Office365Users.SearchUserV2({top:20}).value,"skills",Office365Users.UserProfileV2(Mail).skills)

However, doing so may cause performance problems. A safer method is to save the user list to a collection first, and then use 'Office365Users.UserProfileV2 (Mail)' to update the collection in batches.

Best Regards,

Bof

 

Highlighted

@v-bofeng-msft I have already tried your suggested solutions and mentioned them in my initial post.

 

  • The Graph Api is not returning all properties hence wont be able to use it.
  • Office365Users.SearchUser returns only list of users, however I need to filter the data using other custom properties.

 

What I Am thinking is get the data from AD through a PS script and build a collection out of it.

 

Regards,

Highlighted

Hi @fmourtaza :

 You can use this formula to extract the user list from the user group.

AzureAD.GetGroupMembers("GroupID").value

Or add a button and set it's OnSlelect property to:

ClearCollect(UserList,Office365Users.SearchUserV2({top:200}).value);
ClearCollect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-10),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-20),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-30),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-40),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-50),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-60),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-70),10),AzureAD.GetUser(Mail)));

 Best Regards,

Bof

Highlighted

Hello @v-bofeng-msft 

 

Your solution looks promising however I have few queries on the same. 

 

  • If I understand your code, first it stores the 200 top users in UserList collection,
  • then you are building a new collection MyOrgUsers where AzureAD.GetUser(Mail) is the identifier.

1. As we are querying the 200 top users, so the Collect has to subsequently add the record to the MyOrgUsers collection - right ? 

2. Is there any way to get these collection OnLoad, I read that the form OnLoad events are synchronous which means the app could be penalised in term of performance.

https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/events/fo...

3. The MyOrgUsers collection do not have all the columns from the UserList collection (for example: Department), any clue why is it so ?

 

Thanks a ton 

 

Highlighted

Hi @fmourtaza :

Q:As we are querying the 200 top users, so the Collect ……

A:Yes.You can also adjust the parameter 200 to other numbers to get more records.

Q: Is there any way to get these collection OnLoad, I ……

A:I sugget you put this code into the apps' OnStart Property.This command will only be executed once during use, and will not affect the user experience too much.

  In addition,This formula is indeed flawed in terms of performance, but there does not seem to be a simpler and more straightforward way.

Q:The MyOrgUsers collection do not have all the columns from the UserList collection (for example: Department), any clue why is it so ?

A:The point is AzureAD.GetUser() function does not return Department.If you need to combine the return values of AzureAD.GetUser() and Office365Users.SearchUserV2(), you also need to use the AddColumns function to merge the two tables.For Example:

 

 

ClearCollect(UserList,Office365Users.SearchUserV2({top:80}).value);
ClearCollect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-10),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-20),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-30),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-40),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-50),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-60),10),AzureAD.GetUser(Mail)));
Collect(MyOrgUsers,ForAll(LastN(FirstN(UserList,CountRows(UserList)-70),10),AzureAD.GetUser(Mail)));
ClearCollect(FinalResult,AddColumns(MyOrgUsers,"Department",LookUp(UserList,Mail=mail).Department))

 

 

Best Regards,

Bof

View solution in original post

Highlighted

This is great, a big bravo @v-bofeng-msft 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,610)