Customer Record Lookup from Email
When a specific email arrives (e.g., with 'Lookup' in the subject), parse the customer ID from the body, retrieve the row from SQL, and reply with the customer details.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
FlowLibs - Customer Record Lookup from Email is an intermediate-level cloud flow that demonstrates an email-driven SQL Server lookup pattern. When a new email arrives containing a configurable keyword in the subject line (default: "Lookup"), the flow extracts a customer ID from the email body, queries a SQL Server table for the matching record, and sends a formatted HTML reply email with the full customer details back to the original sender.
This flow showcases parsing trigger data for downstream action inputs, SQL Server single-row retrieval by primary key, dynamic HTML email generation with styled formatting, and conditional execution based on email subject content.
Use Case
Sales teams frequently need to look up customer records while working in email. Rather than switching to a CRM or database tool, a sales rep can simply send an email with "Lookup" in the subject and the customer ID in the body. The flow automatically retrieves the record and replies with a professionally formatted summary — keeping the rep in their email workflow.
Example: A sales rep sends an email with subject "Lookup Request" and body "CUST-1042". The flow parses "CUST-1042", queries the Customers table, and replies with a styled HTML email showing all fields for that customer record.
Flow Architecture
When a new email arrives
Office 365 Outlook — OnNewEmailV3Polling trigger that checks the Inbox every 3 minutes, filtered by the configurable subject keyword (default: 'Lookup').
Initialize SQL Server Name
Initialize variableLoads the SQL Server hostname from the flowlibs_SqlServerName environment variable into a flow variable. Runs in parallel with the other three init steps.
Initialize SQL Database Name
Initialize variableLoads the target database name from the flowlibs_SqlDatabaseName environment variable into a flow variable. Runs in parallel.
Initialize Customer Lookup Table Name
Initialize variableLoads the customer table name from the flowlibs_CustomerLookupTableName environment variable into a flow variable. Runs in parallel.
Initialize Subject Keyword
Initialize variableLoads the trigger keyword from the flowlibs_CustomerLookupSubjectKeyword environment variable into a flow variable. Runs in parallel.
Check Subject Contains Keyword
If conditionCase-insensitive check that the email subject contains the configured keyword: contains(toLower(Subject), toLower(varSubjectKeyword)).
- Extract Customer ID From Body — Compose action that trims the email body preview and extracts the first line as the customer ID.
- Get Customer Record From SQL — SQL Server GetItem_V2 — retrieves the single row matching the extracted customer ID from the configured SQL table.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SqlServerName | String | your-server.database.windows.net | SQL Server hostname. Shared across multiple SQL Server demo flows. |
| flowlibs_SqlDatabaseName | String | FlowLibsDemoDB | Target database name. Shared across multiple SQL Server demo flows. |
| flowlibs_CustomerLookupTableName | String | [dbo].[Customers] | SQL table containing customer records. Must have a primary key column for ID-based lookup. |
| flowlibs_CustomerLookupSubjectKeyword | String | Lookup | Keyword in the email subject that triggers the lookup. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Office 365 Outlook | shared_office365 | OnNewEmailV3 (trigger) SendEmailV2 |
| SQL Server | shared_sql | GetItem_V2 |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Add error handling for invalid customer IDs
- Configure run-after on the SQL Get Customer Record action for the 'Failed' status so the flow gracefully handles bad or missing IDs (e.g., send a 'no record found' reply instead of erroring out).
- Show specific columns instead of full JSON
- Extend the HTML template in the Compose Customer Details HTML step to render named fields (Name, Email, Phone, etc.) in a table rather than dumping the entire row as a JSON string.
- Add a CC recipient for audit trail
- Update the Reply With Customer Details action to CC a shared mailbox or distribution list so every lookup is captured for compliance or coaching.
- Switch to a shared mailbox trigger
- Replace the OnNewEmailV3 trigger with SharedMailboxOnNewEmailV2 to poll a team inbox instead of an individual mailbox — useful for a sales team alias.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Trigger subject filter from env var
Reads the configurable keyword inside the trigger's subject filter so the flow can be re-pointed without editing the trigger.
EXPR.02Case-insensitive subject comparison
Lower-cases the incoming subject so the contains() check works regardless of how the sender capitalized the keyword.
EXPR.03Extract first line of email body as customer ID
Trims the body preview, splits on a newline (URL-decoded %0A), takes the first segment, and trims again to get a clean customer ID.
EXPR.04Serialize full SQL row to JSON string
Converts the SQL action's body object to a string so it can be embedded inline in the HTML reply.
EXPR.05Get original sender for reply
Pulls the sender's email address from the trigger so the SendEmailV2 action replies to the right person.
EXPR.06Build reply subject line
Prefixes the original subject with 'RE: ' for the reply email.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.