cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fmourtaza
Helper II
Helper II

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

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
v-bofeng-msft
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

 

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

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

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 

 

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

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

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,655)