Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power Automate

Building a Dynamic Multi-Condition Approval Flow in Power Automate: Routing Requests to Different Approvers Based on Form Input, Dollar Thresholds, and Department Rules

Real approval workflows don't have one approver — they have routing rules based on dollar amounts, departments, and request categories. This lesson teaches you to build a data-driven multi-condition approval flow in Power Automate that dynamically resolves the right approvers without hard-coding a single email address.

🔥 Expert26 min readOct 4, 2026Updated Oct 4, 2026
Building a Dynamic Multi-Condition Approval Flow in Power Automate: Routing Requests to Different Approvers Based on Form Input, Dollar Thresholds, and Department Rules
On this page
  • Introduction
  • Prerequisites
  • Designing the Decision Matrix First
  • Setting Up the Data Sources
  • The Microsoft Forms Trigger
  • The SharePoint Approver Lookup List
  • Building the Flow: Step by Step
  • Step 1: Initialize Variables
  • Step 2: Look Up the Requester's Manager
  • Step 3: Look Up Department-Specific Approvers
  • Step 4: Look Up the CFO Email
  • Step 5: Resolve the Primary Approver
  • Step 6: Determine Whether Secondary (CFO) Approval Is Needed
  • Step 7: Determine Whether Specialist Approval Is Needed
  • Executing the Approval Chain
  • Stage 1: Primary Approval
  • Setting Up the Timeout
  • Stages 2 and 3: Parallel Conditional Approvals
  • Handling the Post-Decision Actions
  • Writing Back to SharePoint
  • Notification Emails
  • Handling Multi-Stage Approval Chains for Special Scenarios
  • The Array-of-Approvers Pattern
  • Expressions Deep Dive: Dynamic Routing Logic
  • Safe Navigation for Missing Data
  • Computing the Routing Tier
  • Generating Audit-Ready Approval Summaries
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Mistake 1: Using Dynamic Content Instead of Variables for Routing
  • Mistake 2: Parallel Branch Race Condition on SharePoint Update
  • Mistake 3: The Empty Approver String Problem
  • Mistake 4: Nested Conditions That Become Unmaintainable
  • Mistake 5: Not Handling Approver Changes Mid-Flight
  • Mistake 6: Float Conversion Failures on Currency Inputs
  • Summary & Next Steps

Building a Dynamic Multi-Condition Approval Flow in Power Automate: Routing Requests to Different Approvers Based on Form Input, Dollar Thresholds, and Department Rules

Introduction

Picture this: your company's purchase request process is a mess. Requests under $500 go to a direct manager. Requests between $500 and $5,000 go to the department head. Anything above $5,000 also needs the CFO's sign-off. And if the request comes from the IT department, there's a separate hardware procurement officer who needs to be looped in regardless of the dollar amount. Oh, and the Legal department has their own General Counsel for anything contract-related. Right now, someone is manually reading every request email and forwarding it to the right person — and they're making mistakes.

This is exactly the kind of problem Power Automate was built to solve, and it's the exact scenario where most tutorials let you down. They show you a simple "if amount > 1000, approve" example and call it a day. Real business approval routing is a decision tree with multiple interacting dimensions: the requester's department, the dollar value, the request category, and sometimes the requester's seniority level. Getting those conditions wrong doesn't just produce a bad flow — it produces compliance violations, budget overruns, and a workforce that stops trusting automation entirely.

By the end of this lesson, you'll have built a production-ready multi-condition approval flow that captures structured input from Microsoft Forms, evaluates several routing rules in the correct logical order, dispatches approval requests to dynamically determined approvers, handles timeouts and rejections gracefully, and writes the final outcome back to a SharePoint list for auditing. You'll also understand the architecture decisions — why we make certain choices about how to structure conditions — so you can adapt the pattern to your own organization's rules.

What you'll learn:

  • How to design a routing decision matrix before writing a single action in the designer
  • How to use variables and nested conditions to evaluate multi-dimensional business rules without spaghetti logic
  • How to dynamically populate approver email addresses using expressions rather than hard-coded values
  • How to compose multi-stage approval chains where downstream approvers only appear when upstream conditions are met
  • How to handle edge cases: timeouts, rejections, null form inputs, and missing approver data

Prerequisites

You should be comfortable with Power Automate fundamentals before tackling this lesson. Specifically, you need:

  • A working understanding of how automated cloud flows are triggered and how the three flow types differ
  • Familiarity with conditions, loops, and variables — this lesson builds heavily on those primitives
  • A Microsoft Forms form already set up with the fields described below
  • A SharePoint list configured to receive approval outcomes
  • Basic comfort with building approval workflows — we'll extend those patterns significantly here

If you've done the simpler approvals lessons and you're ready to go deeper, you're in exactly the right place.


Designing the Decision Matrix First

Here's the mistake most Power Automate developers make: they open the designer and start dropping conditions immediately. Two hours later, they have twelve nested "if" blocks, three branches that share common logic through copy-paste, and a flow so visually complex that even they can't trace execution. When a rule changes — and it always does — they have to hunt through every branch to find what needs updating.

The cure is to design your routing matrix on paper (or a spreadsheet) before touching the designer. Think of the routing decision as a function with inputs and outputs:

Inputs:

  • RequestDepartment — string: "Finance", "IT", "Legal", "Operations", "Marketing"
  • RequestAmount — number: dollar value of the request
  • RequestCategory — string: "Hardware", "Software", "Services", "Travel", "Contract"
  • RequesterEmail — string: the submitter's email address

Output:

  • PrimaryApprover — the first person who must approve
  • SecondaryApprover — optional; required for high-value requests
  • SpecialistApprover — optional; required for department-specific categories

Now write out the rules in plain English before expressing them in Power Automate:

  1. All requests → Primary approver is the requester's direct manager (looked up from a SharePoint list or Azure AD)
  2. Amount ≥ $5,000 → Also requires CFO approval as a secondary stage
  3. Department = IT AND Category = Hardware or Software → Also requires IT Procurement Officer approval, regardless of amount
  4. Department = Legal OR Category = Contract → Also requires General Counsel approval
  5. Amount between $500 and $4,999 AND Department ≠ IT AND Department ≠ Legal → Department Head approval instead of (not in addition to) direct manager

Rule 5 is the tricky one — it replaces the primary approver rather than adding a new stage, which means we need to resolve the primary approver before we know whether to override it.

Key insight

Routing rules often have precedence — some rules replace others, while some stack on top. Document this explicitly before building. If you skip this step, you'll discover mid-build that two rules conflict and you won't have a principled way to resolve it.


Setting Up the Data Sources

The Microsoft Forms Trigger

Your form needs to capture exactly the fields your routing matrix depends on. Set up your Microsoft Forms form with these questions:

  • Request Title (Short answer)
  • Department (Dropdown: Finance, IT, Legal, Operations, Marketing)
  • Request Category (Dropdown: Hardware, Software, Services, Travel, Contract)
  • Dollar Amount (Number)
  • Business Justification (Long answer)
  • Requester Email — here's a nuance: rather than asking users to type their email (which introduces typos), set this as a hidden field pre-populated from the responder's Microsoft 365 account, or capture it from the trigger itself

On the trigger side, when you use "When a new response is submitted" from the Microsoft Forms connector, you get a Response ID. You need a second action — "Get response details" — to actually retrieve the field values. This two-step pattern is how the Forms connector works, and it's one of the more confusing onboarding experiences for new users. See Automating Microsoft Forms Responses: Collecting, Routing, and Storing Survey Data with Power Automate for a detailed walkthrough of this pattern.

Tip

The "Responders' Email" field is automatically available in the "Get response details" output if your form requires sign-in. This is far more reliable than asking users to type their own email address — use it.

The SharePoint Approver Lookup List

Rather than hard-coding approver emails in your flow (a maintenance nightmare when people change roles), create a SharePoint list called ApproverMatrix with these columns:

  • Department (Single line of text)
  • DepartmentHead (Person or Group)
  • DepartmentHeadEmail (Single line of text — store the email separately for easy expression access)
  • SpecialistRole (Single line of text: "IT Procurement", "General Counsel", empty for others)
  • SpecialistEmail (Single line of text)

Also create a second list called ManagerDirectory with:

  • EmployeeEmail (Single line of text, indexed for performance)
  • ManagerEmail (Single line of text)
  • ManagerDisplayName (Single line of text)

And store global approvers (CFO email, etc.) in a GlobalApprovers SharePoint list with:

  • Role (Single line of text)
  • ApproverEmail (Single line of text)

This pattern makes your flow data-driven. When the CFO changes, you update a SharePoint list item — not the flow itself.

Warning

If your ManagerDirectory list grows beyond a few hundred rows, add an index on EmployeeEmail. SharePoint list lookups use OData $filter queries, and unindexed columns on large lists will cause "Request throttled" errors or, worse, silently return empty results when the 5,000-item threshold is exceeded. Enable column indexing in List Settings → Indexed Columns.


Building the Flow: Step by Step

Step 1: Initialize Variables

Right after your trigger and "Get response details" action, add a "Initialize variable" action for each piece of routing data you'll need. Using variables here — rather than referencing dynamic content directly in every branch — keeps your expressions readable and ensures you can update a value in one place.

Add these variables (all as String type unless noted):

  • varRequesterEmail → Set to respondersEmail from the Forms response
  • varDepartment → Set to the Department field from the form
  • varCategory → Set to the Request Category field
  • varAmountRaw → Set to the Dollar Amount field (String initially)
  • varAmount → Initialize as Float, set to float(variables('varAmountRaw'))
  • varPrimaryApproverEmail → Initialize as empty string
  • varSecondaryApproverEmail → Initialize as empty string
  • varSpecialistApproverEmail → Initialize as empty string
  • varManagerEmail → Initialize as empty string
  • varDeptHeadEmail → Initialize as empty string
  • varApprovalOutcome → Initialize as empty string

That's a lot of variables, but each one serves a purpose. The varAmountRaw / varAmount split exists because Forms returns numeric fields as strings, and you need an explicit float() conversion before you can use greater-than or less-than comparisons.

Warning

Power Automate's condition engine is strongly typed. If you try to compare a string "4500" against the number 5000 using a "greater than" condition, the comparison will behave unpredictably — sometimes passing when it shouldn't, because string comparison ("4500" > "5000") is alphabetical, not numeric. Always convert form numeric fields explicitly with float() or int() before comparing.

Step 2: Look Up the Requester's Manager

Add a "Get items" action targeting your SharePoint ManagerDirectory list with an OData filter:

EmployeeEmail eq '@{variables('varRequesterEmail')}'

Then add a condition to check whether the lookup returned a result:

  • Condition: length(body('Get_items')?['value']) is greater than 0

In the Yes branch, set varManagerEmail:

first(body('Get_items')?['value'])?['ManagerEmail']

In the No branch, you have a real decision to make. Options:

  1. Send an email to HR or IT asking them to add the employee to the directory, then terminate the flow
  2. Fall back to a default approver (a dedicated "unmatched requests" mailbox)
  3. Use Azure AD via the Office 365 Users connector's "Get manager (V2)" action as a fallback

Option 3 is the most elegant — it uses real organizational hierarchy data without requiring manual list maintenance. Add "Get manager (V2)" with the user's email, and set varManagerEmail to outputs('Get_manager_(V2)')?['body/mail'].

Step 3: Look Up Department-Specific Approvers

Add another "Get items" action targeting ApproverMatrix, filtered by:

Department eq '@{variables('varDepartment')}'

Store the results. Then set:

varDeptHeadEmail = first(body('Get_dept_approvers')?['value'])?['DepartmentHeadEmail']
varSpecialistEmail = first(body('Get_dept_approvers')?['value'])?['SpecialistEmail']

Step 4: Look Up the CFO Email

Add one more "Get items" targeting GlobalApprovers:

Role eq 'CFO'

Store first(body('Get_cfo')?['value'])?['ApproverEmail'] in a local Compose action called composeCFOEmail — you'll reference this later.

Tip

For truly static data like CFO emails or global threshold values, you can also use Compose actions and environment variables to store lookup results early in the flow and reference them by action name downstream. This avoids redundant SharePoint calls and makes the flow faster.

Step 5: Resolve the Primary Approver

This is where the routing matrix comes to life. You need to determine whether the primary approver is the direct manager or the department head based on the amount and department rules.

Add a Condition action:

Condition A: Amount is between $500 and $4,999 AND Department is NOT IT AND Department is NOT Legal

In Power Automate's condition editor, you build this as:

  • Group 1: variables('varAmount') greater than or equal to 500 AND variables('varAmount') less than 5000
  • Group 2 (AND): variables('varDepartment') does not equal IT
  • Group 3 (AND): variables('varDepartment') does not equal Legal

If Yes (mid-range amount, standard department): Set varPrimaryApproverEmail to variables('varDeptHeadEmail')

If No (either low amount, or special department): Set varPrimaryApproverEmail to variables('varManagerEmail')

Note what we're doing here: we're resolving the primary approver into a variable rather than immediately branching execution. This keeps the rest of the flow linear rather than exponentially branching.

Step 6: Determine Whether Secondary (CFO) Approval Is Needed

After resolving the primary approver, add another condition:

Condition B: variables('varAmount') greater than or equal to 5000

If Yes: Set varSecondaryApproverEmail to outputs('composeCFOEmail')

If No: Leave varSecondaryApproverEmail empty (no action needed)

Step 7: Determine Whether Specialist Approval Is Needed

Add a third condition block with two parallel checks. In Power Automate, you express "OR" logic within a single condition group by switching the combiner. Set up:

Condition C:

  • variables('varDepartment') equals IT AND (variables('varCategory') equals Hardware OR variables('varCategory') equals Software)
  • OR variables('varDepartment') equals Legal
  • OR variables('varCategory') equals Contract

If Yes: Set varSpecialistApproverEmail to variables('varSpecialistEmail')

If No: Leave empty

At this point, you've resolved all three approver slots using three clean, non-nested condition blocks. Your flow graph looks like a straight line with small branches at each condition — not a Christmas tree of nested indentation.

This architecture is the key insight of this lesson. Rather than branching the execution path at each rule, we branch only the variable assignments. The actual approval actions that follow see clean, resolved variables.


Executing the Approval Chain

Now comes the part where we actually dispatch approvals. The structure is:

  1. Stage 1: Primary approver (always runs)
  2. Stage 2: Secondary/CFO approver (conditional on Stage 1 approval AND amount threshold)
  3. Stage 3: Specialist approver (conditional on Stage 1 approval AND specialist rule)

Stages 2 and 3 can run in parallel with each other if Stage 1 passes — they're independent of each other. This is where understanding parallel branching and concurrency patterns becomes important.

Stage 1: Primary Approval

Add a "Start and wait for an approval" action:

  • Approval type: Approve/Reject - First to respond
  • Title: Purchase Request: @{triggerOutputs()?['body/summary']} - $@{variables('varAmount')}
  • Assigned to: variables('varPrimaryApproverEmail')
  • Details: Compose a rich message block including requester name, department, category, justification, and amount
  • Item link: Link to the SharePoint record you'll create (or already have) for this request

After the approval action completes, set varApprovalOutcome to outputs('Start_and_wait_for_an_approval')?['body/outcome'].

Add a condition: varApprovalOutcome equals Approve

If rejected: Branch here to send a rejection notification email to the requester, update the SharePoint record with status "Rejected - Stage 1", and terminate the flow.

If approved: Continue to Stage 2/3.

Key insight

The "Start and wait for an approval" action is synchronous — the flow pauses at this step until the approver responds or the timeout is reached. For human-in-the-loop approvals, this is exactly what you want. However, be aware that Power Automate flows have a maximum run duration of 30 days. For approvals that might sit for weeks, consider using Adaptive Card-based approval patterns with explicit timeout handling rather than relying on the 30-day limit.

Setting Up the Timeout

Within the "Start and wait for an approval" action's advanced options, set:

  • Timeout: P3D (ISO 8601 duration: 3 days)

Then configure a "Configure run after" on a parallel branch to handle the timeout case. After the approval action, add an action set to run "has timed out" — in this branch, send a reminder email to the approver and potentially to their manager, then either escalate or terminate depending on your policy.

Stages 2 and 3: Parallel Conditional Approvals

After Stage 1's approval confirmation, add a Parallel branch structure. To add parallel branches in the designer, click the "+" button between two actions, then select "Add a parallel branch."

Branch A (Secondary/CFO): Add a condition: length(variables('varSecondaryApproverEmail')) greater than 0

If Yes: Add another "Start and wait for an approval" targeting the CFO email. Use a modified title: [CFO SIGN-OFF REQUIRED] Purchase Request: @{...} - $@{variables('varAmount')}

If No: Add a "Compose" action with value "NotRequired" — you need something in the No branch to avoid empty parallel branch errors.

Branch B (Specialist): Add a condition: length(variables('varSpecialistApproverEmail')) greater than 0

If Yes: Add approval targeting the specialist email, with context about why they're involved: include the department and category in the title so they understand the routing.

If No: Same pattern — Compose with "NotRequired".

After both parallel branches converge, you need to evaluate whether both required approvers approved. Add a condition:

AND(
  or(
    equals(length(variables('varSecondaryApproverEmail')), 0),
    equals(outputs('CFO_Approval')?['body/outcome'], 'Approve')
  ),
  or(
    equals(length(variables('varSpecialistApproverEmail')), 0),
    equals(outputs('Specialist_Approval')?['body/outcome'], 'Approve')
  )
)

This expression says: "For each optional approver, either they weren't required OR they approved." Write this in the expression editor of a condition action. Both conditions must be true to proceed.

Warning

Power Automate's parallel branches share a single execution context, but the actions inside each branch run concurrently. If both branches try to update the same SharePoint item simultaneously, you'll get a conflict error. Collect all your update data in variables during the parallel branches, then do the actual SharePoint update after the branches converge. This is a common race condition that's easy to avoid if you think about it ahead of time.


Handling the Post-Decision Actions

Writing Back to SharePoint

After all approvals are collected (or rejected), update your SharePoint request tracking list. Use "Update item" with a comprehensive status field:

For the final approved case:

  • Status: "Approved"
  • PrimaryApprover: variables('varPrimaryApproverEmail')
  • SecondaryApprover: variables('varSecondaryApproverEmail') (or "N/A" if empty)
  • SpecialistApprover: variables('varSpecialistApproverEmail') (or "N/A" if empty)
  • ApprovedDate: utcNow()
  • TotalProcessingTimeHours: Calculate using div(sub(ticks(utcNow()), ticks(triggerBody()?['timestamp'])), 36000000000)

For the audit trail, also write the approval outcome details from each approver's response — their comments are stored in outputs('Start_and_wait...')?['body/responses'][0]['comments'].

Notification Emails

Send a final status email to the requester. Compose it dynamically:

Subject: Your Purchase Request Has Been @{variables('varApprovalOutcome')}

Dear @{outputs('Get_manager_(V2)')?['body/displayName']},

Your purchase request for $@{variables('varAmount')} (@{variables('varCategory')}) 
has been reviewed and @{variables('varApprovalOutcome')}.

Reviewed by:
- Primary: @{variables('varPrimaryApproverEmail')}
@{if(greater(length(variables('varSecondaryApproverEmail')), 0), 
  concat('- CFO: ', variables('varSecondaryApproverEmail')), '')}
@{if(greater(length(variables('varSpecialistApproverEmail')), 0), 
  concat('- Specialist: ', variables('varSpecialistApproverEmail')), '')}

The if() expressions in the email body conditionally include lines about secondary and specialist approvers only when they were involved — which makes the notification relevant and clean rather than showing "N/A" placeholders.

For organizations heavily invested in Teams, consider sending the final notification as a Teams channel message to the requester's department channel as well. The pattern for Power Automate with Teams notifications can be layered on here cleanly.


Handling Multi-Stage Approval Chains for Special Scenarios

Let's extend the flow for an advanced pattern: the IT department scenario where both the IT Procurement Officer AND the CFO need to approve for hardware purchases above $5,000. This means you have three approvers in sequence: Manager → IT Procurement → CFO.

The naive approach is to add a third "Start and wait" inside a nested condition. The better approach is to recognize this as a multi-stage approval chain and design it deliberately. This is covered in depth in Designing Multi-Stage Approval Chains in Power Automate: Sequential, Parallel, and Delegated Sign-Off Patterns, but the core principle is: resolve all approvers before you start any approvals, then sequence them using a loop over an array.

The Array-of-Approvers Pattern

Instead of hard-coding three "Start and wait" actions, build an array of approvers dynamically, then loop through it:

After all your variable-resolution conditions, add a "Compose" action to build the approver sequence:

@{
  createArray(
    variables('varPrimaryApproverEmail'),
    if(greater(length(variables('varSpecialistApproverEmail')), 0),
       variables('varSpecialistApproverEmail'),
       null),
    if(greater(length(variables('varSecondaryApproverEmail')), 0),
       variables('varSecondaryApproverEmail'),
       null)
  )
}

Then use a "Filter array" action to remove null entries:

@{filter(outputs('Compose_approver_array'), not(equals(item(), null)))}

Now you have a clean ordered array of only the approvers who are actually required. Use an "Apply to each" loop over this filtered array. Inside the loop:

  1. Send approval to items('Apply_to_each') (the current approver email)
  2. Evaluate the outcome
  3. If rejected: set a varRejectedBy variable, break out of the loop using the "Configure run after" + terminate pattern
  4. If approved: continue to the next iteration

This approach means the number of approval stages is determined entirely by the data, not the flow structure. Adding a new approval tier someday means updating your routing matrix variables — not restructuring the flow.

Tip

The "Apply to each" loop in Power Automate doesn't have a native "break" statement. The standard workaround is to use a flag variable (varEarlyExit) initialized to false, then wrap your loop body in a condition that checks the flag. When you want to exit, set the flag to true. All subsequent iterations still run but execute no work. For expensive operations, you can also use a "Do Until" loop instead, which gives you an explicit exit condition.


Expressions Deep Dive: Dynamic Routing Logic

The real power of this flow comes from the expressions that make routing data-driven. Here are the key patterns you need to master for production flows.

Safe Navigation for Missing Data

When looking up approvers from SharePoint, always use ? (null-conditional) operators and wrap with coalesce():

coalesce(first(body('Get_dept_approvers')?['value'])?['DepartmentHeadEmail'], 'fallback@company.com')

This ensures that if the department isn't found in your matrix (because someone submitted a form with a department you forgot to add), the flow routes to a fallback rather than crashing.

Computing the Routing Tier

You can express the routing tier as a single expression for logging purposes:

if(
  greaterOrEquals(variables('varAmount'), 5000),
  'Tier3-CFORequired',
  if(
    and(
      greaterOrEquals(variables('varAmount'), 500),
      less(variables('varAmount'), 5000)
    ),
    'Tier2-DeptHead',
    'Tier1-DirectManager'
  )
)

Log this to your SharePoint record's ApprovalTier column. When your compliance team wants to audit whether the right rules were applied, this single field tells the story without them having to re-derive it. The expression language capabilities in Power Automate are deep enough to handle this entire classification in a single Compose action.

Generating Audit-Ready Approval Summaries

Store the full routing logic as a JSON object in a SharePoint multi-line text field:

{
  "requestId": "@{guid()}",
  "evaluatedAt": "@{utcNow()}",
  "inputs": {
    "department": "@{variables('varDepartment')}",
    "category": "@{variables('varCategory')}",
    "amount": @{variables('varAmount')}
  },
  "routing": {
    "tier": "@{outputs('Compose_routing_tier')}",
    "primaryApprover": "@{variables('varPrimaryApproverEmail')}",
    "secondaryApprover": "@{variables('varSecondaryApproverEmail')}",
    "specialistApprover": "@{variables('varSpecialistApproverEmail')}"
  },
  "outcome": "@{variables('varApprovalOutcome')}"
}

This JSON blob becomes your machine-readable audit log. If you ever connect Power BI to this SharePoint list, you can parse this column and build routing analytics dashboards.


Hands-On Exercise

Build this complete scenario from scratch using your own Microsoft 365 tenant:

Scenario: Your company needs a purchase request flow for a team of 10 people. The rules are:

  • Requests under $250: automatic approval (no human needed), just log it
  • Requests $250–$2,499: direct manager must approve
  • Requests $2,500–$9,999: department head must approve
  • Requests $10,000+: department head AND CFO must approve
  • Any request categorized as "External Consultant": Legal must also review, regardless of amount

Step 1: Create the Microsoft Forms form with fields: Requester Name, Department (Finance, Operations, Marketing), Request Category (Equipment, Software, Travel, External Consultant, Other), Dollar Amount, Business Justification.

Step 2: Create a SharePoint site with three lists:

  • PurchaseRequests — columns: Title, RequesterEmail, Department, Category, Amount, Status, ApprovalTier, PrimaryApprover, SecondaryApprover, SpecialistApprover, SubmittedDate, DecisionDate
  • DepartmentDirectory — columns: Department, HeadEmail, LegalContactEmail
  • GlobalApprovers — columns: Role, Email

Populate DepartmentDirectory with your own email as both head and legal contact for testing. Populate GlobalApprovers with a CFO role pointing to your email.

Step 3: Build the flow using the patterns from this lesson:

  • Initialize all variables
  • Implement the auto-approval branch for amounts under $250 (use a condition right after variable initialization; the Yes branch writes to SharePoint and sends a notification, then terminates — no approval action needed)
  • Resolve primary, secondary, and specialist approvers using the variable pattern
  • Execute approvals sequentially using the array-of-approvers loop pattern
  • Write the final outcome to PurchaseRequests with full audit data

Step 4: Test with five scenarios:

  1. Amount: $100, Category: Equipment → Should auto-approve
  2. Amount: $1,500, Department: Finance, Category: Travel → Should route to Finance head
  3. Amount: $8,000, Department: Marketing, Category: Software → Should route to Marketing head only (below CFO threshold)
  4. Amount: $15,000, Department: Operations, Category: Equipment → Should route to Operations head, then CFO
  5. Amount: $3,000, Department: Finance, Category: External Consultant → Should route to Finance head AND Legal

Step 5: For each test, verify in the flow run history that:

  • The correct ApprovalTier was computed
  • The correct approver emails were populated in the variables
  • The SharePoint record was updated with accurate routing data

Use the run history debugger to inspect variable values at each step — this is exactly how you'd diagnose routing errors in production.


Common Mistakes & Troubleshooting

Mistake 1: Using Dynamic Content Instead of Variables for Routing

Symptom: Your condition works in testing but routes to the wrong approver in production when certain form fields are empty.

Cause: You referenced the Forms dynamic content (Body/Department) directly in a condition rather than through a variable. When the form field is empty or has unexpected casing (the form returned "it" instead of "IT"), your condition failed silently.

Fix: Always normalize your inputs when you set the variables. Use trim(toUpper(outputs('Get_response_details')?['body/responders0Department'])) to ensure consistent casing before any comparisons. Store this normalized value in your variable, and all subsequent conditions work reliably.

Mistake 2: Parallel Branch Race Condition on SharePoint Update

Symptom: The SharePoint "Update item" action inside one parallel branch fails intermittently with a 409 Conflict error.

Cause: Both parallel branches tried to update the same SharePoint list item at the same moment. SharePoint's REST API uses optimistic concurrency — the second update sees the item's ETag has changed since the first update and refuses.

Fix: Move all SharePoint updates to after the parallel branches converge. Use variables to collect the data you need from each branch, then do a single "Update item" action with all the data at once.

Mistake 3: The Empty Approver String Problem

Symptom: The flow sends an approval to an empty email address and the approval action hangs indefinitely.

Cause: Your specialist approver lookup returned no results (the department exists but has no specialist), so varSpecialistApproverEmail stayed empty. Your condition checking length(varSpecialistApproverEmail) > 0 should have caught this, but you accidentally checked the wrong variable.

Fix: Always add a "Terminate" or explicit null-check before every "Start and wait for an approval" action. The approval action will not gracefully handle an empty Assigned To field — it will either error or create an approval that nobody can see.

Warning

An approval action with a blank or malformed "Assigned to" address doesn't always fail immediately. Sometimes it creates an orphaned approval request that sits in the approvals center with no recipient. These approvals accumulate over time and confuse users who browse the approvals center. Add an explicit guard condition before every approval action.

Mistake 4: Nested Conditions That Become Unmaintainable

Symptom: Your flow has five levels of nested conditions and you can no longer tell which branch handles which scenario without tracing through manually.

Cause: You built routing logic using nested conditions (branching execution) instead of sequential variable-resolution conditions (branching only data assignments).

Fix: Refactor using the pattern in this lesson. Each routing rule gets its own flat condition block that sets a variable. After all rules are evaluated, the flow continues in a single linear path using those variables. If the refactor is too large to do at once, extract the routing logic into a child flow that accepts the inputs and returns the resolved approver emails. Orchestrating child flows for routing resolution is an excellent architectural pattern for complex decision logic.

Mistake 5: Not Handling Approver Changes Mid-Flight

Symptom: A flow is waiting for CFO approval. The CFO changes (resignation, leave). The flow hangs forever because the old CFO's email is no longer monitored.

Fix: Use the "Wait for approval with timeout" approach with a 48-72 hour timeout for every stage. In the timeout branch, look up the current approver from your SharePoint matrix again (not from the variable set at flow start) and re-send. This re-lookup catches personnel changes that happened after the flow started. For very long-running approvals, this pattern is described in depth in Building Approval Workflows with Power Automate.

Mistake 6: Float Conversion Failures on Currency Inputs

Symptom: The flow crashes on the float() conversion when a user types "$4,500.00" or "4500 USD" in the amount field.

Cause: Microsoft Forms number fields should prevent this, but if you're receiving amount data from other sources (email parsing, SharePoint column), the value may contain currency symbols or commas.

Fix: Add a Compose action to sanitize before conversion:

float(replace(replace(replace(variables('varAmountRaw'), '$', ''), ',', ''), ' ', ''))

Chain the replace() functions to strip symbols before calling float(). Add error handling on the conversion action — if float() fails, the request is malformed and should route to a human reviewer immediately rather than crashing.


Summary & Next Steps

What you've built in this lesson is fundamentally different from a toy approval flow. It's a data-driven routing engine where:

  • Approver assignments come from SharePoint lists, not hard-coded values
  • Routing rules are evaluated sequentially as variable assignments, producing a clean set of resolved approvers before any approval action runs
  • The approval chain is dynamic — the number of stages adapts to what each request requires
  • Timeouts, rejections, and empty-approver scenarios are all explicitly handled
  • Every decision is logged to a structured audit record that can be queried later

The pattern of "resolve all data, then execute" is a principle that extends far beyond approval flows. It applies to any multi-branch automation: email routing, record classification, task assignment. If you find yourself drawing decision trees with deeply nested execution branches, ask whether you can flatten them into sequential variable-resolution blocks instead.

Where to go from here:

  • Extend the approver matrix to support delegation — when a department head is on leave, the matrix should point to their backup. This requires a DateRange lookup on the ApproverMatrix list and a utcNow() comparison in your expression.
  • Connect this flow to Microsoft Teams so approvers receive rich Adaptive Cards instead of email — the response handling integrates cleanly with the pattern you've built here. See Implementing Adaptive Card-Based Human-in-the-Loop Approvals for the full pattern.
  • If your organization has complex approval chains with 5+ stages and hundreds of daily requests, consider extracting the routing logic into a child flow and the approval execution into a separate child flow. Orchestrating child flows and scoped execution covers exactly this architecture, including how to pass context between parent and child flows without losing data.
  • Add robust error handling to every SharePoint lookup and approval action. Production approval flows fail in exactly the places you least expect — SharePoint throttling, email delivery failures, approver email address changes — and graceful handling of those failures is what separates a production flow from a proof of concept.

The routing matrix you've designed here is your most important artifact. Keep it in SharePoint, keep it current, and your flow will handle organizational changes without a single update to the flow itself.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Flow Automation Basics

Previous

Designing Multi-Stage Approval Chains in Power Automate: Sequential, Parallel, and Delegated Sign-Off Patterns

Next

Automating Microsoft 365 Group Membership Changes with Power Automate: Triggering Flows on Join and Leave Events, Notifying Owners, and Updating SharePoint Records in Real Time

Related Insights

Power AutomateExpert

Automating Recurring Approval Workflows with Expiry Dates in Power Automate: Triggering on SharePoint Date Columns, Notifying Owners, and Routing Renewals Through Multi-Step Sign-Off

28 min
Power AutomatePractitioner

Implementing Power Automate Child Flow Fan-Out and Result Aggregation Patterns for Parallel High-Volume Record Processing Without Exceeding Action and Concurrency Limits

23 min
Power AutomatePractitioner

Automating Windows Printer and Print Queue Management in Power Automate Desktop: Selecting Printers, Submitting Print Jobs, and Monitoring Queue Status in Unattended RPA Workflows

22 min

On this page

  • Introduction
  • Prerequisites
  • Designing the Decision Matrix First
  • Setting Up the Data Sources
  • The Microsoft Forms Trigger
  • The SharePoint Approver Lookup List
  • Building the Flow: Step by Step
  • Step 1: Initialize Variables
  • Step 2: Look Up the Requester's Manager
  • Step 3: Look Up Department-Specific Approvers
  • Step 4: Look Up the CFO Email
  • Step 5: Resolve the Primary Approver
  • Step 6: Determine Whether Secondary (CFO) Approval Is Needed
  • Step 7: Determine Whether Specialist Approval Is Needed
  • Executing the Approval Chain
  • Stage 1: Primary Approval
  • Setting Up the Timeout
  • Stages 2 and 3: Parallel Conditional Approvals
  • Handling the Post-Decision Actions
  • Writing Back to SharePoint
  • Notification Emails
  • Handling Multi-Stage Approval Chains for Special Scenarios
  • The Array-of-Approvers Pattern
  • Expressions Deep Dive: Dynamic Routing Logic
  • Safe Navigation for Missing Data
  • Computing the Routing Tier
  • Generating Audit-Ready Approval Summaries
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Mistake 1: Using Dynamic Content Instead of Variables for Routing
  • Mistake 2: Parallel Branch Race Condition on SharePoint Update
  • Mistake 3: The Empty Approver String Problem
  • Mistake 4: Nested Conditions That Become Unmaintainable
  • Mistake 5: Not Handling Approver Changes Mid-Flight
  • Mistake 6: Float Conversion Failures on Currency Inputs
  • Summary & Next Steps