Retrieve all users in an organization using Office365 connector

[Sidhant_02]

Hi everyone,
I was working on a requirement which is as follows:
We have an application built to track visitors and employees (& their assets). For visitors to grant them temporary access card they need to fill in the information and in case of employees if they forget their ID card they need to fill in the required details, once done then only they are issued a temporary card for that day.
Now this application is deployed at one location (Pune) and we are planning to expand the same to other locations (Germany, Chennai), so for that we were thinking of having two roles: SuperAdmin and Admin. SuperAdmin will have all the access (of the data) and they can assign admins for locations. Whereas admins can only create entries and view entries only for the location for which they have been assigned to (by superadmin)
Example:
There 50 records created with Location: Pune and 78 with Germany location. And Rajesh is been assigned as Admin for Pune location so he can only view Pune location records only not other location.

So to do that I thought of retrieving all the data from Office365 connector and store it in a collection for which I have made use of following expression:

Set(varSkip, 0); // Initialize a variable for pagination
 
// Loop to get all users
Collect(Office365UsersCollection, Office365Users.SearchUser({searchTerm: "", top: 999, skip: varSkip}));
 
// Create a collection for pagination
ClearCollect(Pagination, {Value: 0}, {Value: 999}, {Value: 1998}, {Value: 2997}); // Add more items if you have more than 4000 users
 
// Use ForAll to get all users
ForAll(Pagination,
    Collect(Office365UsersCollection, Office365Users.SearchUser({searchTerm: "", top: 999, skip: Value}))
);

//To get only Active users:
ClearCollect (
    ActiveUsers,
    Filter (
        Office365UsersCollection,
        AccountEnabled = true && !IsBlank(OfficeLocation)
        //Only retreiving ActiveUsers (AccountEnabled = true) && avoiding external users for whom location is blank so have used !IsBlank(OfficeLocation)
    )
)

//To get unique location values:
ClearCollect(uniqueLocations,Distinct(ActiveUsers,OfficeLocation))

Now based on selection (Location dropdown selection made in drop down) I need to show only users with that particular location (OfficeLocation), so superadmin selects : Pune then the Person type choice drop-down should only show employees with Pune location.
For that on the On-Change of thee location drop-down I used the following
ClearCollect(locationsBasedUsers, Filter(ActiveUsers,OfficeLocation=DataCardValue15.Selected.Value))

//DataCardValue15.Selected.Value: Refers to the location drop-down

It gives me the results but I have observed the collection containing few duplicates and when I tried to use the collection for Items property of the drop-down like:
Choices(locationsBasedUsers,DisplayName) or Choices(locationsBasedUsers.DisplayName) 

It gave an error.

Name isn't valid. '{0}' isn't recognized (Error)
So I used: locationsBasedUsers.DisplayName, but here the issue was that I was only able to get few set of records in the dropdown (like 30 odd records that were incomplete the selected location had more number of records).

So my question was is there any better way to do it then what I have done or how can I improve my existing structure.

Regards,
Sidhant.
 

[WarrenBelz]

Hi @Sidhant_02 ,

Please try this for the filtered location

ForAll(
   Sequence(4, 1, 999),
   Collect(
      Office365UsersCollection,
      Filter(
         Office365Users.SearchUser(
            {
               searchTerm: "",
               top: 999,
               skip: Value
            }
         ),
         AccountEnabled = true && OfficeLocation = DataCardValue15.Selected.Value
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Sidhant_02]

Hi @WarrenBelz ,
Thanks for your quick response, I tried the expression on my test screen 
(In the drop-down I had selected location as 'Chennai' and I got all the employees with OfficeLocation as Chennai)

I have one query as we are using Collect all the new records will be appended into the same collection.
Like if a user selects Chennai and gets all the Chennai individuals but then changes the location to USA (as I will be using the expression on On-Change of the location drop-down it will add all the new records in the same collection)
So for now I have added: Clear(Office365UsersCollection) on Visible property of the screen but is there any other way to avoid the appending.

And the other issue was in the Administrator column (which is a person type column in my AdminList_VM sharepoint list).
In my test screen I added another drop-down and tried to extract the DisplayName from the Office365UsersCollection using 'Choices':

I even tried: Choices(Office365UsersCollection.DisplayName)

Have I missed something?

Regards,
Sidhant.

[WarrenBelz]

Hi @Sidhant_02 ,

Easy enough to avoid the appending

Clear(Office365UsersCollection);
ForAll(
   Sequence(4, 1, 999),
   Collect(
      Office365UsersCollection,
      Filter(
         Office365Users.SearchUser(
            {
               searchTerm: "",
               top: 999,
               skip: Value
            }
         ),
         AccountEnabled = true && OfficeLocation = DataCardValue15.Selected.Value
      )
   )
)

As for the drop-down Items - you can use (you do not need Choices)

Office365UsersCollection.DisplayName

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Sidhant_02]

Thanks @WarrenBelz ,
Missed the part of adding the Clear option before 😉. But in the collection I can see few duplicates so to avoid that I thought of modifying the current expression by using Distinct()

ClearCollect(
   Office365UsersCollection,
   Distinct(
      ForAll(
         Sequence(0, 999),
         Filter(
            Office365Users.SearchUser(
               {
                  searchTerm: "",
                  top: 1000,
                  skip: Value * 1000
               }
            ),
            AccountEnabled = true &&
            OfficeLocation = Dropdown2.Selected.Title
         )
      ),
      Email
   )
)

Did I miss something?

Regards,
Sidhant.

[WarrenBelz]

@Sidhant_02 ,

You need Mail, not Email.

[Sidhant_02]

Hi @WarrenBelz ,
I tried replacing email with mail but it was not recognized:

Original exression:
Clear(Office365UsersCollection);
ForAll(
   Sequence(4, 1, 999),
   Collect(
      Office365UsersCollection,
      Filter(
         Office365Users.SearchUser(
            {
               searchTerm: "",
               top: 999,
               skip: Value
            }
         ),
         AccountEnabled = true && OfficeLocation = Dropdown2_1.Selected.Title
      )
   )
)


Added Distinct:

(Same issue and it was expecting 2 arguments)

Regards,
Sidhant

[WarrenBelz · Accepted solution]

@Sidhant_02 ,

You need Distinct() on the FIlter as below.

Clear(Office365UsersCollection);
ForAll(
   Sequence(4, 1, 999),
   Collect(
      Office365UsersCollection,
      Distinct(
         Filter(
            Office365Users.SearchUser(
               {
                  searchTerm: "",
                  top: 999,
                  skip: Value
               }
            ),
            AccountEnabled && OfficeLocation = DataCardValue15.Selected.Value
         ),
         Mail
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Sidhant_02]

Thanks @WarrenBelz ,
I am using the following list (for locations)

(not retrieving locations from office 365 which was done previously)

On-Change of the locations drop-down I have used the same expression that you mentioned in your latest reply

Clear(Office365UsersCollection);
ForAll(
   Sequence(4, 1, 999),
   Collect(
      Office365UsersCollection,
      Distinct(
         Filter(
            Office365Users.SearchUser(
               {
                  searchTerm: "",
                  top: 999,
                  skip: Value
               }
            ),
            AccountEnabled && OfficeLocation = Dropdown2.Selected.Title
         ),
         DisplayName  //Used DisplayName inplace of Mail
      )
   )
)

Now the expression is fine, but still I observed there are duplications even after applying Distinct keyword like:

After scrolling down (in the collection) I saw the same set of values again

(For security reasons have masked other values but as you can Ajit record was visible again) even after applying Distinct, so in this case is the Distinct has not worked as expected.
What are your thoughts on this?

Regards,
Sidhant.

[WarrenBelz]

@Sidhant_02 ,

It did work as expected on each iteration of ForAll, however there is still an opportunity for duplicates to exist in two different "bundles". You cannot add a further Distinct into this process due to Data Row Limit issues, however if you then used

Distinct(
   Office365UsersCollection,
   Mail
)

you should receive the correct values.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps