Optimizing Power Automated Flows with Many-to-Many Usage

Michael Megel's blog post offers a practical and user-friendly guide to working with many-to-many relationships in Power Automate flows. The crux of his exploration revolves around an issue he encountered which required access to related records from such relationships within Flow.

Rather than resorting to convoluted solutions like using FetchXML, API calls, or "black magic" with plugins, he discovered a straightforward approach that at its core entails merely three steps.

The basis of his problem laid in his Dataverse solution holding three tables. These tables, named Scheduling, Rules and Containers, are interconnected in a many-to-many relationship – multiple scheduling records can be linked to many rules and containers. His goal, therefore, was to loop over all related rules and containers for a single Scheduling record using Power Automate Flow.

To begin with, he elaborated on how he designed his three tables and established the two many-to-many relationships within his Dataverse solution, one between Scheduling and Rule, and the other between Scheduling and Container.

This posed a stumbling block as he could not directly access his Relationship table in Dataverse. To circumvent this, he modified the Main form in his Scheduling table, introducing a new section with a Subgrid to display related records.

Having prepared his tables and made the necessary modification to his Main form, he moved on to the creation of a Power Automate Flow.

Despite setting up an uncomplicated flow, he found that he received no data about his related tables. The answer, he found, was to expand his query by using his collection names from the relationship.

This ‘expand query’ principle facilitated his progress, enabling him to add his related rules and Containers accordingly.

A General Exploration of Many-to-many Relationships

Many-to-many relationships present a unique challenge in data modeling because they allow multiple records to be associated with multiple records. The example of a Scheduling table associated with Rules and Containers, as illustrated in the blog post, are classic illustrations of such relationships. While they can initially seem complex to utilize and access, the approach outlined by Michael provides a user-friendly and straightforward way to handle many-to-many relations in Power Automate flows. This ensures that advanced techniques, like FetchXML or plugins, are not required to effectively manage and utilize these relationships in a practical, real-world scenario.

Read the full article USING MANY TO MANY IN POWER AUTOMATED FLOWS

Learn about USING MANY TO MANY IN POWER AUTOMATED FLOWS

Confronted with the challenge of accessing related records in a Power Automate flow from a many-to-many relationship, the only solutions that seemed to exist were difficult and convoluted, involving FetchXml, Xrm "black magic," or API calls. Fortunately, a simpler method was uncovered - a three-step process. Let's explore it.

To provide some background, the author's Dataverse solution has three tables, with one table, 'Scheduling', providing scheduling information for executing rules within assigned containers. The issue was iterating through the related rules and containers for each Scheduling record using Power Automate.

The structure of the Dataverse solution comprises three tables which have been created previously. These tables serve as the vehicle for transporting the components between Dataverse environments (Dev, Test, and Prod), as part of the Application Lifecycle Management (ALM).

Many-to-many relationships had been set up between 'Scheduling' and 'Rules' and 'Scheduling' and 'Containers'. The aim was to see these relationships reflected when running the workflow in Power Automate, but initially, related tables weren't visible.

Now, what was the simple solution? Expanding the query! More specifically, using "Expand Query" with the appropriate collection names was the key to revealing the related Containers in the relationships table. As there were two related collections, both were added to the "Get Record by ID" action in Power Automate.

With this approach, when running the flow, results showed a collection for each related table. But how to use these records from there? Two variables (Array type) were initialized for 'rules' and 'containers', which then held the body of the action and accessed the expanded relation.

This query expansion allowed access to the related Dataverse records from a many-to-many relationship in the workflow tool. No perplexing FetchXml or Xrm magic required. Instead, "Expand Query" was employed, adding the collection names from the relationships. These were stored in array variables, utilizable in "Apply to each" actions.

In conclusion, efficiently accessing and using related records in a workflow using Power Automate is feasible and simple. This method effectively allows for smooth and straightforward execution of scheduling procedures. Remember, keep learning - no challenge is insurmountable!

More links on about USING MANY TO MANY IN POWER AUTOMATED FLOWS

Using Many to Many in Power Automated Flows

Oct 7, 2022 — Do you know how to access related records from a many-to-many relation in Power Automate Flows? No, here is my solution with 3 simple steps.

Lookup related Many to Many (N:N) records using Flow

Jul 19, 2019 — Solved: In my data model I have established a many to many (N:N) relationship between two Entities. When I Get a CDS Record in Flow, how can I lookup.

Keywords

Power Automated Flows, Many to Many, Using Many-to-Many, Automation Process, Microsoft Power Automate, Power Automate Workflow, Data Management Power Automate, Automation Flows, Power Automate Techniques, Advanced Power Automate.