Many To Many Relationships in Power Automate - Easiest Way to Retrieve Related Records!

In this Pragmatic Works training video, Nate Halliwell demonstrates how to work with many-to-many (M2M) relationships inside Power Automate. Building on his earlier tutorials about using M2M relationships in Canvas Apps, Nate shifts the focus to Power Automate and shows how to retrieve related records efficiently when changes occur in Dataverse tables.

Scenario Overview

Nate begins with a simple scenario: when the start date for a class changes, the system should automatically find all students enrolled in that class and send them an email notification. He uses a Dataverse solution that contains both students and classes, linked together with a many-to-many relationship.

Setting Up the Flow

To automate this, Nate creates an Automated Cloud Flow in Power Automate:

1. Trigger: When a row is added, modified, or deleted in the Class table.
2. Scope: Set to the entire organization to capture changes made by anyone.

The challenge comes immediately: Power Automate’s Dataverse connector allows relating or unrelating rows but doesn’t directly return all related records. Nate explains that although there are more complex approaches like fetchXML queries, he wants to demonstrate the easiest method.

Finding the Virtual M2M Table

Behind the scenes, Dataverse generates a hidden relationship table when a many-to-many relationship is created. While users cannot edit or add columns to this table, it can still be queried. Nate shows how to locate the relationship table name:

• Navigate to the Relationships section of the Student or Class table.
• Filter by “Many-to-Many” and select the relationship (in this case, Class-Student).
• Copy the Relationship Table Name, such as M2M_student_M2M_class.

In Power Automate, a List Rows action is added. Since the table is not searchable in the UI, Nate enters the relationship table’s logical name manually. The key trick is appending Set to the name, resulting in something like M2M_student_M2M_classSet.

Filtering the Related Records

Once the rows are listed, Nate filters them to only return records related to the class that was changed:

1. Identify the logical name of the Start Date field in the Class table.
2. Use this field to ensure the flow only runs when the Start Date column changes.
3. Add an OData filter query on the relationship table, such as: M2M_classId eq [Class GUID].

Testing this filter confirms that only the relevant student records are returned. For example, if three students are enrolled in Math, the query returns exactly three rows.

Retrieving Student Details

At this point, the flow has the GUIDs for each related Student. Nate adds another step:

• Get Row by ID – Retrieve the full student record using the Student table and the GUID.
• This provides fields such as name and email address.

Sending Notifications

With student details in hand, Nate configures an Email action:

• Recipient: The student’s email field.
• Subject: Includes the class name (e.g., “Start date for your Math class has changed”).
• Body: Personalized with the student’s name and the updated start date.

Testing the flow confirms that each related student receives a personalized email when the class date changes.

Key Takeaways

• Dataverse automatically generates a hidden table to manage many-to-many relationships.
• You can query this table in Power Automate by using its logical name with Set appended.
• Filter queries allow retrieval of only the relevant related records.
• By combining List Rows with Get Row by ID, you can enrich relationships with real-world data like student names and emails.
• This approach avoids complex fetchXML queries, making it accessible and straightforward.

Conclusion

Nate Halliwell demonstrates how to bridge many-to-many relationships across the Power Platform by leveraging the hidden relationship table in Dataverse. His approach makes it easy to retrieve related records and automate workflows such as notifying students of class changes. This tutorial rounds out the understanding of M2M relationships, extending their use beyond Canvas Apps into Power Automate for practical automation scenarios.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions onPower Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.