Dynamic Approvers from Dataverse in Power Automate Approvals

1. The "Approval Matrix" Pattern in Dataverse

Instead of embedding business logic (like who should approve a request) directly into our flow, a much better practice is to store this information in a configuration table. This is often called an "Approval Matrix." When approval rules change, you simply update a row in a table instead of editing and redeploying the Power Automate flow. Given your background in building configurable systems, you'll recognize this as a key pattern for separating logic from data.

Let's design a simple Approval Matrix table in Dataverse. We could call it Approval Configuration. It would contain the following columns:

• Name (Primary Column, Text): The type of request (e.g., "Expense Report > $1000", "New Hardware Request").
• Approvers (Many-to-Many Relationship to the User table): This allows you to assign one or more users as approvers for this request type. Using a M:N relationship is more flexible than a simple lookup.
• Approval Type (Choice): With options like "First to Respond" and "Everyone Must Approve".
• Requires Manager Approval (Yes/No): To handle multi-stage scenarios.

The video below demonstrates this concept perfectly using a SharePoint list. We will adapt this logic for our Dataverse-centric approach.

2. Retrieving Approval Configuration and Approvers

With our Approval Matrix table designed, the first step in our flow (right after the trigger) is to fetch the correct configuration for the incoming request.

For this, we'll use the Dataverse List rows action.

1. Filter the Configuration: We'll use the Filter Query field to find the specific row in our Approval Configuration table that matches the request. For example, if our trigger is "When a row is added" for an Expense Report table, and the report's Category is 'Travel', our filter query might look like crXXX_name eq 'Travel Expense'. This is the OData syntax equivalent of a WHERE clause in SQL.

2. Extract the Emails Efficiently: A naive approach would be to get the configuration row, then loop through the related approvers and use a Get a row by ID action on the User table for each one to get their email. This is inefficient, slow, and can quickly burn through your Power Platform API request limits.

   A much more professional and performant method is to use an Expand Query. This powerful OData feature allows you to retrieve data from related tables in a single call. In our List rows action, we can expand the N:N relationship to the User table to pull back the approvers' emails simultaneously.

The article below explains this concept clearly.

Here is an image showing what this looks like in the flow designer. Note how multiple user fields are being expanded in one go.

3. Formatting Approver Emails for the Approval Action

The Expand Query will give us an array of User objects, each containing an email address. However, the 'Start and wait for an approval' action requires a single string of semicolon-separated email addresses in its Assigned to field.

To transform our array into this string, we use a two-step pattern:

1. Select Action: This data operation takes an array and creates a new array containing only the properties you specify. We'll use it to create a simple array of just the email addresses.
2. join() Expression: This expression takes an array and a separator and concatenates the array items into a single string.

The following video segment demonstrates this Select and Join pattern, along with how to dynamically set the approval type.

[
  {
    "crXXX_approvers": [
      { "internalemailaddress": "alice@example.com" },
      { "internalemailaddress": "bob@example.com" }
    ]
  }
]

4. Updating the Dataverse Record

After the approval is complete, the final step is to update the original Dataverse record with the outcome. As we saw in the previous lesson, we use a Condition to check the Outcome of the approval and then use the Update a row action.

A common challenge when using the Update a row action is providing the correct Row ID. You must pass the unique identifier (GUID) of the record that triggered the flow.

This short video demonstrates the full Dataverse-centric pattern, including the crucial step of correctly identifying the Row ID for the update action.

Conclusion

Today we have significantly leveled up our approval workflow. By externalizing the approval rules into a Dataverse configuration table, we've created a solution that is robust, scalable, and easy to maintain.

Key Takeaways:

• The Approval Matrix pattern separates your business rules from your automation logic, a professional development best practice.
• Use the List rows action with a Filter Query to retrieve the specific approval configuration for a request.
• Leverage the Expand Query to fetch data from related tables (like User emails) in a single, performant API call.
• Use the Select and join() pattern to transform the array of approver objects into the semicolon-separated string required by the approval action.
• Remember to use the correct trigger output to get the Row ID when updating the original Dataverse record.

What's Next

So far, our flows have primarily interacted with services that have pre-built connectors like Dataverse and Approvals. But what happens when you need to connect to a custom in-house API or a third-party service that doesn't have a dedicated connector?

In our next lesson, we will begin Module 7 by learning how to use the generic HTTP action to call external REST APIs. This will open up a vast new world of integration possibilities for your Power Automate flows.