The AAD Users virtual table that was added to Dataverse has been a lifesaver for a lot of applications, especially when using Power Apps licensing as they provide the full subset of Active Directory users. When using Power Apps licensing, not all users are automatically synched to the Users table, so that is a great addition.
Saying that, when building apps for the Power Platform, there are some limitations to these. In a current project, we have a table that has a lookup to the AAD Users table and need to display information from that table. When the lookup control is implemented, you can display the related AAD Users lookup value and additional corresponding values associated with it.
Let’s take a look at the scenario that we built. In the screenshot below we have a subgrid that shows records from the Account table. The AAD User Lookup column shows the UPN of the AAD User record by using the ThisItem.’AAD User Lookup’.’User Principal Name’ formula.
The following shows the different sections from the image above
- Filter option that allows showing all records, showing records by Filtering by Lookup column and showing records by Filtering over a Text/Json column
- This text control shows the function that is used to filter the subgrid
- Toggle to show or hide the AAD User Json column. You will be able to see the AAD User Json column in the next image
- A badge containing the number of records in the subgrid after being filtered
- The gallery containing all records visible to the users.
Let’s start by enabling the toggle and showing also the Json column. Next, when we select the Show All option in the Filter, all records are being displayed. The two other options that are available are Filter by Lookup and Filter by Json. When we select the Filter by Lookup option from the Filter Options drop down, this filters the subgrid by User Principal Name ending with onmicrosoft.com:
Filter(Accounts, EndsWith(‘AAD User Lookup’.’User Principal Name’, “onmicrosoft.com”))
Running the above function does not return any data, which is the source of the problem, and a known issue that can be seen in many blog and community sites. I am not sure when a resolution for this will exist. The issue also exists when we try to count the number of records based on a filter in that table, and the same limitations also existing using Power Automate.
There is no easy solution, but in our case, we still needed to implement something like that and we determined that there are a few possibilities. We could replicate the AAD Users table and get it synched, add additional fields to the related table, or replicate parts of the functionality of the AAD Users table. We decided to go with the last approach and add a Json string to the related table which would contain the AAD User Id, Display Name, UPN/Email and Employee Number. These are fields that are very unlikely to change on a regular basis, and since for us the records are more like point-in-time, it was not an issue if someone email or display name changed in the future. The AAD User Id, allows us also to go to AD via Office 365 Users connector and retrieve additional information about the users.
Having a Json field allowed us to search for a value in the AAD User Json column and count the number of records which we needed as part of the application, as shown in the screenshot below.
We added an additional screen to allow us to edit existing records and see the Json string that gets generated and then update the account record with the correct Json string. We decided to keep both the lookup and the Json string, for future functionality in case Microsoft will resolve the filtering issues with the AAD Users table in the future and we can replace the existing logic with OOB logic. Screenshot shown below.
I hope that this implementation has been helpful for somebody, and although not optimal, it is a temporary workaround for what we needed to do.