Retrieve Team Members from Dynamics 365 Dataverse with Power Automate

Managing teams and their members is a common requirement for organizations using Dynamics 365. If you’ve ever needed to retrieve the list of members in a specific team from the Dataverse, you’ve likely found that it isn’t as straightforward as you might expect. After a bit of research and trial-and-error, I’ve put together a simple solution using Power Automate, and I’m excited to share it with you. This blog post will walk you through the process step by step.

The Challenge

The Dataverse has a many-to-many relationship between teams and system users, managed through the hidden teammemberships table. This table links users (systemusers) to teams, but since it isn’t visible by default, you need to use Power Automate to query it effectively.

The Solution

By leveraging Power Automate’s Dataverse connector, we can build a flow to retrieve team members. Here’s how it works:

Step 1: Get the Team

The first step is to identify the team you’re working with. To do this:

1. Add a Get Row action.
2. Set the Table name to Teams.
3. Specify the Row ID. If you’re dynamically retrieving this (e.g., based on the owner of a record), you can use the Owner (Value) field.

Step 2: Retrieve Team Memberships

Next, we’ll query the teammemberships table to find all users linked to the team:

1. Add a List Rows action.
2. Set the Table name to teammemberships.
3. Use a Filter Query to specify the team ID:

   teamid eq '<TeamID>'

   Replace <TeamID> with the dynamic value retrieved from the previous step.

This will return all system users associated with the team.

Step 3: Loop Through Team Members

The teammemberships table gives you the systemuserid values for all team members. To retrieve detailed information about each member:

1. Add an Apply to Each loop.
2. Set the input to the value array from the previous step.
3. Inside the loop, add a Get Row by ID action:
   • Set the Table name to Users.
   • Set the Row ID to the systemuserid from the current item in the loop.

Step 4: Process the Results

Once you have the user details, you can process them further. For example:

• Aggregate the results into an array.
• Format the data into an HTML table for an email notification.
• Save the data to a SharePoint list or Excel file.

Practical Use Case

Let’s say you want to send an email to a manager listing all the members of a specific team. Here’s how your flow might look:

1. Retrieve the team and its members as described above.
2. Collect each member’s name and email into an array.
3. Use the Create HTML Table action to format the data.
4. Add a Send an Email (V2) action and include the table in the email body.

Why This Solution is Helpful

Finding a way to retrieve team members in Dataverse is a common challenge, and there’s limited documentation on how to achieve it effectively. By using the teammemberships table in Power Automate, you can overcome this limitation and build powerful workflows tailored to your organization’s needs.

Final Thoughts

I hope this guide helps you streamline your Dataverse workflows. If you’re managing Dynamics 365 teams and need to automate team-related tasks, this approach can save you time and effort. Feel free to share this with your team or let me know if you have questions—I’d be happy to help!