Power Platform
Copilot Studio
Power Automate
Dataverse
Employee Leave
Request Agent
Most companies handle leave requests through email or chat. Requests get missed, there’s no consistent record, and employees have no visibility into what happened. This project turns an unstructured conversational process into a structured, automated, and fully auditable system — without removing the human element where it matters.
4
Architecture layers working together
2
Routing paths — automated and human review
0
Manual steps for one-day requests
100%
Of requests stored as structured records
Stack
Copilot Studio
Power Automate
Dataverse
Adaptive Cards
Approvals Connector
Context
The problem
When leave requests travel by email or chat, the same four things tend to go wrong — regardless of company size or how organized the team is. The problems compound each other: no record means no visibility, no visibility means no way to report, and no consistency means different employees get handled differently.
01 — Requests get missed
The manager was busy, the message got buried, nobody followed up. The employee assumes it was approved; the manager didn’t register it.
02 — No consistent record
Some managers keep spreadsheets. Others keep nothing. When questions arise about leave history, there’s no single source of truth.
03 — No employee visibility
An employee submits a request and then waits, not knowing if it was seen, if it’s being reviewed, or if it was silently rejected.
04 — No reporting possible
There’s no way to see leave patterns across the team without manually going through email threads — which nobody does.
The ask was to make the process conversational — so employees could submit in natural language — while making it structured enough to be reliable and auditable.
Architecture
How it’s built — 4 layers
Each layer has one clear job. They connect cleanly and can be extended independently — a new channel, a new approval step, or a reporting layer can be added without touching the others.
Copilot Studio
The AI agent — conversational layer
What the employee talks to. Runs on generative orchestration so it understands natural language without scripted trigger phrases. “I need a day off,” “Can I take some time in July,” and “I want to book leave next week” all trigger the same intent. The agent knows what to do next regardless of phrasing.
Adaptive Card
Structured data collection — inside the conversation
Instead of collecting information through multiple chat messages, the agent presents a structured form directly in the conversation. The employee sees dropdowns, date pickers, and a submit button — but never leaves the chat window. This gives the system clean, typed, structured data rather than free text it would need to parse.
Agent Flow — Power Automate
Routing logic and process automation
When the employee submits the form, the agent passes all inputs to this flow. The flow makes the routing decision: one-day requests are approved immediately, multi-day requests go to the manager via the Approvals connector. It then writes the result to Dataverse and passes the decision back to the agent so the employee gets an immediate response in the conversation.
Dataverse
Structured data storage — the audit trail
Every leave request — the employee, the dates, the reason, the decision, and the manager’s comment — is stored as a structured record. This is what makes the process reportable, auditable, and improvable over time. A Power BI dashboard or a Power Apps management interface can be built directly on top of this data without any additional connectors.
Design decisions
Why each choice was made
The tools are straightforward. The interesting decisions were about how to use them — and what to avoid.
Why Copilot Studio instead of a form?
A form would solve the data collection problem but not the experience problem. Employees already ignore email-based forms because they feel like administrative overhead. A conversational interface that lives where people already work — Teams, a browser, a company intranet — has a much higher chance of actually being used. Copilot Studio also handles the orchestration between the conversation and the automation natively: the agent calls the flow, passes the right inputs, receives the output, and responds to the employee, all without custom code.
Not chosen — form
Solves data collection. Doesn’t solve adoption. People ignore admin overhead tools.
Chosen — conversational agent
Lives where people work. Higher adoption. Handles orchestration natively without custom code.
Why generative orchestration instead of classic?
Classic orchestration routes conversations based on trigger phrases — if the employee says one of a predefined list of sentences, the right topic fires. Generative orchestration uses a language model to understand intent regardless of how it’s phrased. Employees don’t follow scripts. “I need Friday off,” “I want to book some leave in August,” and “Can I take a holiday next week?” all mean the same thing. With classic orchestration, each of those would need to be explicitly listed as a trigger phrase or it wouldn’t work. With generative orchestration, all three are handled automatically. The structured part of the interaction still happens inside the Adaptive Card, where the fields are fixed and the input is controlled — so nothing is left to interpretation when data collection actually happens.
Not chosen — classic orchestration
Requires predefined trigger phrases. Breaks when employees phrase requests differently.
Chosen — generative orchestration
Understands intent regardless of phrasing. No phrase lists to maintain.
Why a checkbox for “one-day request” instead of calculating from dates?
Date calculations in automation flows require careful type handling. A date arriving from an Adaptive Card as a string needs to be converted before it can be compared. If the conversion fails, or if the date format differs by locale, the routing condition breaks silently. By letting the employee explicitly signal whether it’s a one-day request, the routing condition becomes a simple string comparison — reliable, transparent, and clear. It also gives the employee explicit confirmation of which path their request will take, which reduces uncertainty.
Not chosen — date arithmetic
Type handling risk. Locale-dependent. Can break silently. Adds complexity with no benefit.
Chosen — explicit checkbox
Simple string comparison. Reliable. Clear to the employee which path they’re on.
Why is the one-day threshold the right automation boundary?
The real design question in any automation project is not which tools to use — it’s what to automate and what to keep human. Automating everything would mean some requests that deserve review get approved without one. Routing everything to a manager eliminates the benefit of automation entirely and creates unnecessary work. A one-day request is the kind of routine, low-risk decision that most organizations would agree doesn’t require manager involvement every time. Multi-day requests — which may affect project schedules, team coverage, or overlap with other leave — are exactly where human judgment adds value. The threshold is simple, defensible, and easy to explain to any stakeholder.
Implementation
The Adaptive Card
An Adaptive Card is a structured UI element that renders inside a chat interface. It looks like a form — it has dropdowns, date pickers, text fields, and a submit button — but it lives inside the conversation rather than redirecting the employee to a separate page. The card collects four pieces of structured data and one optional field.
Reason for leave
Select reason ▾
Options: Personal, Medical, Holiday, Other
One-day request?
☐ This is a one-day request
Hides the end date field when checked. Determines routing path.
End date (multi-day only)
Pick a date 📅
Only shown when one-day checkbox is unchecked
Notes (optional)
Add any context for your manager…
Submit Request
Logic
The routing logic
Once the employee submits the form, the Agent Flow evaluates one condition: is this a one-day request? That single boolean drives two completely different paths. Both paths end with a record in Dataverse and a response to the employee in the conversation.
-
Employee submits the form with one-day checkbox checked
-
Flow evaluates condition: one-day = true
-
Record written to Dataverse with Status: Approved
-
Result passed back to the agent
-
Employee sees confirmation in the conversation within seconds
-
Manager receives no action item — record is visible in Dataverse
-
Employee submits the form with date range
-
Flow evaluates condition: one-day = false
-
Approval request sent to manager via Approvals connector — includes employee name, dates, reason, and notes
-
Manager clicks Approve or Reject in their approvals inbox and optionally adds a comment
-
Flow receives the response, writes the final record to Dataverse including manager comment
-
Agent tells the employee the outcome in the conversation
Data layer
The Dataverse tables
Two tables were created for this solution. Dataverse was chosen over SharePoint or a spreadsheet for three reasons: it handles relational data cleanly with proper field types, it enforces data types so a date is always stored as a date and a choice field always holds a valid option, and it integrates natively with Power Platform so reporting and management interfaces can be built on top without additional connectors.
Employee Manager Mapping
- Employee Email Email
- Manager Email Email
The flow looks up this table to know where to send the approval request. In production this would typically come from Azure Active Directory — so the flow would automatically know each employee’s manager. For this self-contained demo environment without Active Directory integration, the mapping table serves the same purpose.
Leave Requests
- Employee Name Text
- Employee Email Email
- Reason for Leave Text
- Start Date Date
- End Date Date
- Status Choice
- Notes Text
- Manager Comment Text
Status is a choice column with three options: Pending, Approved, Rejected. Every request starts as Pending. The flow updates it to Approved or Rejected when the decision is made.
Why Dataverse over SharePoint or a spreadsheet
- Handles relational data cleanly — choice columns, date fields, and typed columns are all natively supported
- Enforces data types — a date is always a date, a choice always holds a valid option, no freeform corruption
- Integrates natively with Power Platform — the flow writes records, the agent can read them, Power BI or Power Apps can report on top without extra connectors
- Supports row-level security — records can be restricted to relevant people without custom logic
Result
End to end
EmployeeOpens chat, says “I need time off”
→
Copilot StudioUnderstands intent, presents form
→
Adaptive CardEmployee fills in and submits
→
Agent FlowEvaluates condition, routes
→
DataverseRecord written with full history
→
EmployeeSees outcome in the conversation
For the organization, leave management stops living in inboxes and starts living in a system that can be reported on, audited, and improved over time. The manager is only involved when they need to be. One-day requests don’t create work for anyone. Multi-day requests get a proper review. And every request, regardless of path, ends up as a structured record with its full history — who asked, what dates, what reason, what the outcome was, and what the manager said.
The broader design principle
- This project uses the Microsoft Power Platform the way it’s designed for enterprise: Copilot Studio for the conversational layer, Power Automate for process logic, Dataverse for the data layer, and the Approvals connector for human-in-the-loop decision making
- Each component does one job and connects cleanly — a new channel, a new approval step, or a reporting layer can be added without touching the others
- The interesting design work was not choosing tools but deciding what to automate, what to keep human, and where the line should be — the tools just execute the decision