Because deciphering "I won't be around tomorrow" shouldn't require a PhD in Human Communication

Let's be honest – managing team attendance feels like playing an endless game of charades. One person says "working from home," another says "won't be in," and someone else just sends a mysterious "can't make it." Meanwhile, you're sitting there trying to figure out who's where, when, and why, while your spreadsheet silently judges your life choices.

What if I told you there's a way to build an AI-powered attendance manager that can actually understand these cryptic messages and organize everything automatically? Buckle up, because we're about to turn your attendance chaos into a well-oiled machine.

What We're Building (Spoiler: It's Pretty Awesome)

This Attendance Manager Flow will:

• Automatically receive leave requests through webhooks (no more hunting through emails!)
• Lookup employee details from your database (because Sarah from Marketing needs to be distinguished from Sarah from Sales)
• Use AI to analyze messages in Vietnamese OR English (multilingual AI for the win!)
• Handle complex requests like "I'm working from home Monday and taking Tuesday off"
• Store everything neatly in a structured database (Marie Kondo would be proud)
• Return clean results that actually make sense

Think of it as having a super-organized assistant who never needs coffee and actually enjoys reading through attendance requests.

Step-by-Step Guide: From Chaos to Clarity

Building the Flow (Where the Magic Happens)

Step 1: The Welcome Mat (Webhook Trigger)

Node: Trigger

This is where everything begins. The Trigger node captures employee requests sent via chatbot or form.

Setup:

• Enable Webhook to receive requests.
• Map the input fields:
  • fullName (optional)
  • displayName (required)
  • email (required)
  • team (optional)
  • message (required → the actual leave/WFH/late text)
  • createTime (required → timestamp of the request)

This is like having a receptionist who never sleeps and always remembers to ask the right questions.

Step 2: The Detective (Employee Lookup)

Node: Diaflow - Employee Info (ddb-1)

Set up a quick table right inside the node:

Sample query:

SELECT *  

FROM "Diaflow - Employee Info"  

WHERE "email" = '{{ trigger.email }}'  

LIMIT 1;

This step figures out who's actually sending the message. Because "John" could be John from IT, John from Sales, or that mysterious John from accounting who nobody's ever actually seen.

Step 3: Analyze Attendance Message with AI

Node: Analyze and Parse Message (openai-0)

Here’s where the magic happens. This node uses AI to understand and normalize the employee’s message.

Prompt outline:

You are a smart assistant that extracts structured attendance data from informal messages, often written in Vietnamese or English.

‌

You will receive two JSON inputs:

---

📌

‌Input 1 — the message:

{

 "displayName": "   trigger.displayName   ",

 "email": "   trigger.email   ",

 "message": "   trigger.message    ",

 "createTime": "   trigger.createTime    "

}

📌 Input 2 — team directory lookup:

  ddb-1.output  

(This is an array of objects, each containing: "fullName", "displayName", "email", and "team")

---

🧠 Your task is to:

‌

1. Understand the message in Vietnamese or English.

‌

2. From Input 1, extract and split the message if it includes multiple **distinct types of attendance** (e.g., WFH and OFF). Each attendance type must be returned as a separate object in an array under the `output` key.

‌

3. For each object, extract the following fields:

- `fullName`: Look up from Input 2 by matching both `displayName` and `email`. If found, use `fullName`. If not found, return `displayName`.

- `email`: from `email`

- `team`: Look up from Input 2. If not found, return `"null"` as a string

- `originalMessage`: full message content from `message` (untouched)

‌

- `type`: MUST be one of the following 4 values based on **exact keyword match**:

  - `WFH`: includes "remote", "WFH", "làm ở nhà", "làm remote"

  - `OFF`: includes "nghỉ", "xin nghỉ", "day off", "off", "nghỉ phép", "nghỉ bệnh"

  - `Late`: includes "đến muộn", "đến trễ", "late"

  - `Leave Early`: includes "về sớm", "leave early", "xin về sớm"

  - If none of the above are detected → `"undefined"` (string)

‌

- `reason`: normalize to one of:

  - `"Health issue"`: if message includes "bệnh", "health", "sick", "sức khỏe"

  - `"Personal issue"`: if message includes "cá nhân", "personal"

  - `"Family issue"`: if message includes "gia đình", "family"

  - If none match → `"other"`

  - If no clear reason is found → return `"null"`

  - If `type` is `"undefined"` → return `"null"`

‌

- `detailedReason`:

  - If `reason` is one of the 3 categories above → return the same value

  - If `reason` is `"other"` → extract a short explanation phrase from the message (e.g., "car broken", "moving house")

  - If no reason is clear, return `"null"`

  - If `type` is `"undefined"` → return `"null"`

‌

- `start_date`: exact start datetime in format `"YYYY-MM-DD, HH:mm:ss"`

  - Use `createTime` as reference to resolve phrases like "mai", "thứ 3", "next Monday"

  - If message includes "sáng" → `"08:30:00"`

  - If message includes "chiều" → `"13:30:00"`

  - If no time is mentioned for a specific date, assume full day → use `"08:30:00"` as the start of day

‌

- `end_date`: exact end datetime in format `"YYYY-MM-DD, HH:mm:ss"`

  - If message includes "sáng" → `"12:00:00"`

  - If message includes "chiều" → `"18:00:00"`

  - If no time is mentioned for a specific date, assume full day → use `"18:00:00"` as the end of day

- `total_days`: The number of **weekdays only** (excluding Saturdays and Sundays) between `start_date` and `end_date`, with **0.5-day precision**:

  - If either the **start** or **end** day contains **“sáng”** (morning) or **“chiều”** (afternoon), count that day as **0.5**

  - If neither has “sáng” nor “chiều”, assume the day is **full** and count as **1.0**

  - If both are partial (e.g., "sáng" to "chiều"), count as **1.0 total**

  - For multi-day spans, only count **weekdays** (exclude Saturday and Sunday)

  - **Total_days = sum of applicable weekdays, considering any half-days**

‌

📌 Important Time Logic:

- If a date like `"thứ 3"` or `"Tuesday"` is mentioned **without** `"sáng"` or `"chiều"`, interpret it as a **full day (08:30–18:00)**

- Only apply `"sáng"` or `"chiều"` to the specific day it is explicitly linked to

‌

📌 Examples:

- “xin phép off sáng mai và làm remote thứ 3 tuần sau vì lý do sức khỏe” → return 2 objects:

  1. type: OFF, time: morning only, total: 0.5

  2. type: WFH, time: Tuesday full day, total: 1

- “xin phép off thứ 3 và làm remote sáng thứ 4 tuần sau vì lý do sức khỏe” → return 2 objects:

  1. OFF: Tuesday full day → total_days: 1.0

  2. WFH: Wednesday morning only → total_days: 0.5

- “xin nghỉ từ chiều thứ 6 đến sáng thứ 2 tuần sau” → total_days = 1.0 (Friday 0.5 + Monday 0.5, weekend excluded)

- “WFH từ thứ 4 tuần này đến sáng thứ 2 tuần sau ” → 3.5 (4 weekdays, last is half-day)

- unrelated message → one object with type: `"undefined"`, reason: `"null"`, detailedReason: `"null"`

‌

- "note": from `createTime` (convert from UTC to **Vietnamese time (GMT+7)** in format: "YYYY-MM-DD, HH:mm:ss" )

‌

---

‌

4. Return **ONLY** a valid array of JSON objects (no markdown, no explanation), all in English except `originalMessage` :

[

   {

     "fullName": "",

     "email": "",

     "team": "",

     "originalMessage": "",

     "type": "",

     "reason": "",

     "detailedReason": "",

     "start_date": "",

     "end_date": "",

     "total_days":

     "note":

   },

   ...

]

Step 4: Save Attendance into Database

Node: Diaflow - Attendance Manager (ddb-2)

Finally, we insert the parsed data into the Attendance Manager table.

Table schema:

CREATE TABLE "Diaflow - Attendance Manager"

Input:

• {{openai-0.output}}

Return Final Output

Your flow wraps up by returning a structured JSON response—perfect for logging, dashboards, or notifying HR directly in Diaflow Tables.

Real-World Example (Because Examples Make Everything Better)

Let's say an employee sends this message: "Request to take off tomorrow morning due to illness, and work remotely next Tuesday." Here's what happens:

1. Webhook receives the message and employee details
2. System looks up the employee in your database
3. AI analyzes and creates TWO separate records:
   • Record 1: OFF (tomorrow morning, health issue)
   • Record 2: WFH (next Tuesday, no specific reason)
4. Both records get saved to your Attendance Manager table
5. Clean results are returned for confirmation

The Beautiful End Result

Once your flow is up and running, you'll have:

• For Managers: Crystal-clear attendance tracking with zero guesswork 
• For HR: Automated reports that actually reflect reality 
• For Employees: The freedom to communicate naturally without forms 
• For Your Sanity: One less thing to manually manage

Why This Approach Actually Works

Unlike traditional attendance systems that force people into rigid categories, this flow adapts to how humans actually communicate. It handles:

• Multiple requests in one message
• Casual language ("won't be around")
• Formal requests ("requesting leave")
• Mixed languages (Vietnamese + English)
• Complex scenarios ("morning off, afternoon WFH")

Ready to Transform Your Attendance Chaos?

Building this Attendance Manager Flow isn't just about processing leave requests – it's about creating a system that understands your team and works the way humans actually communicate.

The setup might take an afternoon, but the time savings and reduced headaches will pay dividends for years to come. Plus, you'll have the satisfaction of watching AI seamlessly handle what used to be your most frustrating administrative task.