Filtering Office365Users by Department

(1) Share

Report

Posted on by Hack-7

226 Moderator

Not sure if there is a way, but I am hoping.

I need to filter Office365Users() by department so that we are left with a list of approximately 150 employees in a specific group.

My other problem is I need to have the Filter based on the department containing 'facility'.

If I use this code, I can use a search box to only find employees in the department but we do not want to type a name, I just want to build a collection of all employees in the department.

Filter(
 Office365Users.SearchUser({searchTerm: Trim(TextInput1.Text)}), "facility" in Department
)

The issue I have come across is the Filter() commands want to look for First or Last Name and not just filter by department. Any thoughts or workarounds?

Cheers

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Pro_User_365 546 on at

Like (0)

Report
Copy link

Link copied!
Hi @Hack-7 ,

To filter Office365Users() by department containing 'facility' and build a collection of all employees in that department, you can use the following workaround:

ClearCollect( EmployeesInFacilityDepartment, Filter( Office365Users.SearchUser({ searchTerm: "" }), "facility" in Department ) )

ClearCollect: This function clears any previous data in the collection (if any) and then collects new data into it.
EmployeesInFacilityDepartment: Replace this with the name you want to give to your collection.
Filter: It filters the Office365Users() list by checking if "facility" is in the Department.
Note that we use Office365Users.SearchUser({ searchTerm: "" }) to retrieve all users without a search term, effectively getting all users.
This code will create a collection called EmployeesInFacilityDepartment that contains all employees in the department containing 'facility'. You can then use this collection for further operations or display purposes as needed.
Note: After receiving the answer, if you think it's helpful, please consider accepting it. 😊 Regards! Ali Shahzad

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Pro_User_365 That code produces an empty collection for me.

Was this reply helpful? Yes No
Pro_User_365 546 on at

Like (0)

Report
Copy link

Link copied!
Hi @Hack-7 ,
If the provided code results in an empty collection, it may be due to the SearchUser function not supporting the direct filtering by department. A workaround could be to retrieve all users and then process the collection to filter by department:
ClearCollect( AllUsers, Office365Users.SearchUser({ searchTerm: "" }) ); ClearCollect( EmployeesInFacilityDepartment, Filter( AllUsers, "facility" in Department ) );
This two-step process creates an initial collection of all users and then filters that collection to include only those in the 'facility' department. However, be mindful of potential performance issues when retrieving large numbers of users.
Note: If you find this solution helpful, please consider accepting it. 😊

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Pro_User_365 , Thanks for the help on this so far.

The issue is there are 5000+ employees, so I assume I will get deligation issues

Was this reply helpful? Yes No
Pro_User_365 546 on at

Like (0)

Report
Copy link

Link copied!
@Hack-7 ,
Adjust the Data Source: If possible, modify your SharePoint list or data source to have a view that filters the 'facility' department. PowerApps can delegate filter operations on SharePoint views.
Batch Processing: Retrieve users in batches that your app can handle without hitting delegation limits. Use a loop with incremental filtering based on other non-delegable fields that are indexed, like UserID or CreatedDate.
Azure Functions: Utilize Azure Functions to handle the data retrieval and filtering server-side, then return the result to PowerApps.
Power Automate: Though you want to avoid flows, a Power Automate flow could run on a schedule, filter the users, and update a SharePoint list that PowerApps could read without delegation issues.
Index Columns: Ensure the 'Department' column is indexed in SharePoint to improve performance and potentially avoid delegation issues when filtering.

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Pro_User_365 interesting thoughts.

Question, Not liking the Flow idea, but how would you build a Flow for all users, then filter it by department?

Was this reply helpful? Yes No
Pro_User_365 546 on at

Like (0)

Report
Copy link

Link copied!
Hi @Hack-7 ,
To implement a Power Automate (formerly known as Microsoft Flow) solution that filters users by department and updates a SharePoint list, follow these steps:
Create a New Flow:
Go to the Power Automate website.
Sign in with your Microsoft account.
Click on "Create" to start creating a new flow.
Choose a Trigger:
Select a trigger that suits your needs. In this case, you might use a scheduled trigger to run the flow at specific intervals (e.g., daily or weekly).
Add an Action to Filter Users:
After the trigger, add an action that retrieves all the users you want to filter. This could be from an Office 365 Users connector or another relevant data source.
Apply the Filter:
Use a filtering action or condition to filter the users by the 'Department' attribute. Ensure that you only select users from the 'facility' department.
Update a SharePoint List:
Add an action to update a SharePoint list. Specify which SharePoint site and list you want to update.
Map User Data to SharePoint Columns:
Map the user data you retrieved in step 3 to the appropriate columns in your SharePoint list.
Save and Test the Flow:
Save the flow and test it to ensure that it successfully retrieves users, filters them by department, and updates the SharePoint list.
Set a Schedule:
If you're using a scheduled trigger, configure the schedule according to your requirements (e.g., daily at a specific time).
Turn On the Flow:
Once you've tested and configured the flow, turn it on. It will now run based on the schedule you've defined.
PowerApps Integration:
In your PowerApps app, you can read data from the updated SharePoint list without delegation issues because the data is pre-filtered by the flow.
By following these steps, you can create a Power Automate flow that periodically filters users by department and updates a SharePoint list. Your PowerApps app can then retrieve data from this list without delegation concerns, even with a large number of employees.

Was this reply helpful? Yes No