How to retrieve user ID from Dataverse table based on email address field and update 'Owner' field?

[pawelpomaranski]

Hello,

I'm struggling with finding a way to retrieve Dataverse user ID that's needed to update 'Owner' field.

I have a Dataverse table, which consists of around 40K records - I have a 'Supervisor email' field that I would like to use and update 'Owner' column. Owner field will allow to apply filters so that one Dataverse view will be used by approx 500 supervisors to see their own records.

Records are imported periodically from Excel file, so I can't really use system fields, like 'Created by' because I'm the creator of all these records.

I created Power Automate flow that lists Dataverse table records and then gets user details with 'Get user profile (V2)' connector from 'Supervisor email' field. I thought that User ID can be used to update 'Owner' field in Dataverse, however it looks like it needs a Dataverse user ID (systemuserid), because it's getting me errors.

Is there a way to retrieve Dataverse user ID from User ID field (Get user profile V2 connector)? Or maybe there's another way of retrieving Dataverse user ID from Supervisor email field?

[Ethan_R]

Hi @pawelpomaranski ,

Use Dataverse 'List Rows' action where you can use FetchXML in query.

1. How to get FetchXML -> go to Dataverse -> Open 'Advanced Find'

2. Select 'Users' table from Look into dropdown, -> Next add a filter of Email equals __

3. DownLoad FetchXML.

=> Now, put this fetchXML in your 'List Rows' action and in filter condition, email equals (dynamic email of Supervisor) 

Now, you may have multiple records, but on safer side you can add condition to check if count of recods fetched is 1.

If yes, use 'Update a Row' and add that user ID (systemuserid) -> Update the Owner lookup

Hope this helps

[dpoggemann]

Hi @pawelpomaranski ,

Few things here...  

1. The systemuser table or User table if looking at it from the user interface in Dataverse is the table that you would utilize for the createdby, owner, modifiedby, etc. fields.  This is different than the AAD User.  
   
2. Most of the time all of your AAD Users will not exist in Dataverse as User table records because this is usually only for individuals with the appropriate Power Apps or Dynamics 365 licensing.
3. If the users are in Dataverse and you are trying to find the manager's UserID to set the owner, the Users table in Dataverse has a Manager field that should be populated, field name behind the scenes is (parentsystemuserid). 

Are you trying to specifically allow the Supervisor to see their direct report's records in Dataverse and the current owners of the records are the direct reports?  If this is the case, you can use hierarchical security in Dataverse to provide the ability for the supervisors to see and even edit the records without changing the owner of the records.

See the following article for details:  https://learn.microsoft.com/en-us/power-platform/admin/hierarchy-security 

[pawelpomaranski]

Hi @dpoggemann , big thanks for your expertise. 

Regarding point #2 - all users in the company have premium licenses. Does that mean they have a unique Dataverse user ID assigned, just the way it works in AAD?

I'm the current owner of all the records in Dataverse table, not the users reporting to supervisors. I load the data from Excel report, that's why Dataverse sets myself as the owner. The requirement is to have one view in model-driven app, where supervisors will see their subordinates' records (initially I was thinking about using Owner field for that). Hierarchical security will not work in this case, neither personal views in model-driven app (approx. 500 supervisors). 

Is there any other way to update Owner field in Dataverse with 'Supervisor email' data? I thought there's a way to do it with Power Automate, but now I'm not really sure. 

[dpoggemann]

Hi @pawelpomaranski ,

What do you mean they have "Premium" licensing?  The users (supervisors) would require Power Apps Per App or Power Apps Per User licensing to utilize the model driven application and views on top of Dataverse.  No Microsoft 365 licensing (i.e. Business Premium or other) includes this.

If you go to https://admin.powerplatform.com  you will be able to then select the Environment and on the right hand side of the page you will see the ability to view the users that exist in the environment.

From here you will be able to see what users exist in the environment.

You can setup the environment to be related to an Azure Active Directory Group (i.e. something like All Employees) that may help you manage the situation you have here:  https://youtu.be/b8tSOl8eolw 

This should synchronize the users to the environment based on the Azure Active Directory Group.  Verify this works successfully and then you can verify that the Users table is populated with all the users and the Manager is set correctly, if it is then you can use the flow to lookup the user (employee) based on their email address and take the assigned manager and assign them as owner to the appropriate records.  These can all be done with Dataverse connector in Power Automate Flow.

[LinnZawWin · Accepted solution]

@pawelpomaranski 

You can trigger your flow when the row is created or modified. (just specify the Select columns property is set so that the flow does not trigger an infinite loop when the owner column is updated)

Use "List rows" to get the User which has the same email address as the Supervisor Email column and assigns as the owner in the loop (as in the attached screenshot)

Alternatively, you can use the Condition branch to check if the length of the List rows outcome is more than 0 and update the owner using the first() value but that is a bit more complicated and you will need to write expressions.

[pawelpomaranski]

@LinnZawWin awesome! This is what I've looking for but couldn't figure out how to do it. 

I've tested the Flow on my Dataverse table and it works as expected. 😀

Cheers,

Pawel

[pawelpomaranski]

@dpoggemann Many thanks for your help. You provided me a great understanding how the the users are set up in Dataverse. The video regarding mapping AAD users will help me with another use case I'm working on.

Hopefully, I won't have similar issues in the future. 😃

Thanks,

Pawel