How to Filter Inactive Users from a Dataverse AADUSER Lookup
Question details
The user needs to filter out inactive users from a Dataverse lookup based on the AADUSER table to prevent duplicate names from appearing in the results.
- Product
- Microsoft Dataverse
- Device & OS
- not provided
- Scenario
- Configuring a lookup column in Dataverse that references the AADUSER table.
- Observed behavior
- The lookup displays duplicate user names because it retrieves both active and inactive user records by default.
Ensure you have the necessary Maker or System Administrator permissions in your Power Platform environment to modify Dataverse tables and lookup views.
Modify the Lookup View and Seek Expert Power Apps Guidance
Configure a specific Dataverse lookup view to filter by user status, or consult the Power Apps Community for complex formula solutions.
Because the AADUSER table synchronizes all directory users, lookups can return both active and disabled accounts. To resolve this, you need to apply a condition that restricts the lookup to active user records only. As this often requires specific Power Apps and Dataverse configurations depending on your exact setup, engaging with the official community is the most effective approach.
Navigate to the Power Apps maker portal, open your specific table, and locate the lookup column referencing the AADUSER table.
Check if your table and column support applying a custom lookup view that filters out users based on their active status field.
If you cannot modify the view directly, go to the official Microsoft Power Apps Community website.
Click 'Start a Topic' and provide your exact Dataverse lookup view details, status field name, and formula requirements to receive customized expert guidance.
Looking for a simpler way to manage and analyze data? Try WPS Office
While complex relational databases require specialized platforms like Power Apps, managing your daily datasets, spreadsheets, and reports doesn't have to be complicated. WPS Office provides a lightweight, highly compatible, and completely free alternative to Microsoft Office for all your everyday data processing needs.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the lightweight setup in minutes.
- 2. Open Your Existing Data Files: Launch WPS Spreadsheets and seamlessly open your exported Excel (.xlsx) or CSV data files with zero formatting loss.
- 3. Filter Your Data Quickly: Use the built-in Data tab to easily apply filters, sort records, and remove duplicates without needing advanced formulas.
Frequently Asked Questions
Why does the AADUSER table show duplicate names in Dataverse?
The AADUSER table syncs user accounts from Azure Active Directory. If an employee leaves and their account is deactivated rather than deleted, the table retains both the old inactive record and any new active records with similar names, leading to duplicates in unfiltered lookups.
Can I filter inactive users using a Canvas app formula?
Yes, in a Power Apps Canvas app, you can wrap your lookup column in a Filter() function. For example, filtering the choices to only show items where the user status or account enabled field equals true.
Where is the best place to ask questions about Dataverse relationships?
The Microsoft Power Apps Community is the dedicated forum for questions regarding Dataverse tables, relationships, Canvas app formulas, and Model-driven app views.
