Work Order Management with Airtable, Forms, Make, and Gmail

The Business Situation

Cedarline Maintenance Services is a fictional 24-person business that maintains commercial buildings for property managers and small industrial customers. Its team consists of 18 field technicians, two dispatch coordinators, an operations manager, a parts coordinator, a customer administrator, and the owner.

The business handles approximately 220 maintenance requests each month. Requests range from routine repairs to urgent equipment failures. The company already uses Google Workspace for email and file storage, but work orders are tracked through phone calls, paper job sheets, Gmail, and several messaging conversations.

Dispatchers are responsible for collecting request details, deciding whether the work is covered by an existing agreement, assigning a technician, scheduling the visit, monitoring overdue jobs, and obtaining customer confirmation. Technicians record labour, parts, photos, and completion notes, but the information is not consistently returned to dispatch.

The company needs a practical work-order system without immediately purchasing a full field-service management platform. The initial implementation must support mobile and desktop intake, structured operational records, technician assignment, scheduling, alerts, overdue escalation, completion evidence, customer confirmation, and operational reporting.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The representative design uses Google Forms for accessible intake, Airtable as the operational system of record, Make as the automation layer, Google Drive for controlled photo and document storage, and Gmail for notifications. Airtable interfaces provide dispatch and management reporting.

The Existing Process

The original workflow proceeds as follows:

1. A customer calls, sends an email, or contacts an employee through a messaging application.
2. A dispatcher writes the problem on paper or adds a short line to a shared spreadsheet.
3. The dispatcher asks follow-up questions about the location, asset, access arrangements, and urgency.
4. A technician is selected from memory, recent message history, or a paper schedule.
5. The dispatcher sends the technician a message containing the address and a brief issue description.
6. The technician visits the location and records notes on paper or in a message.
7. Parts used and labour time are sometimes reported at the end of the day or week.
8. Photos remain on the technician’s phone or in a message thread.
9. The dispatcher asks the customer whether the issue was resolved.
10. A completed spreadsheet row is updated if someone remembers to do it.

The shared spreadsheet provides a list, but not a controlled workflow. It does not validate status transitions, create related labour and parts records, manage document permissions, or send escalation notifications. It also depends on dispatchers manually keeping every row current.

What the New System Needed to Do

Not every decision should be automated. Dispatch must continue to confirm safety implications, technician suitability, customer authorization, access arrangements, and schedule conflicts. The automation coordinates information and enforces routine rules, but it does not replace operational judgment.

Implementation Approaches Considered

Google Forms and Sheets

This option would retain familiar tools and could be automated with Google Apps Script. It is appropriate for a simple request list. However, the business needs linked customers, locations, assets, labour sessions, parts usage, documents, technicians, and event history. Maintaining those relationships across several sheets would increase formula, permission, and support complexity.

Airtable-Only Configuration

Airtable can provide forms, relational records, interfaces, and native automations. This is a viable alternative. The company nevertheless prefers Google Forms for known mobile users and external confirmation, while Make provides clearer cross-system routing, error handling, and Gmail integration.

Microsoft 365

Microsoft Forms, Lists, SharePoint, Outlook, and Power Automate could implement a comparable system. It was not selected because Cedarline Maintenance Services already manages identities, mail, and documents in Google Workspace. Introducing a second productivity environment would add administration without solving a business requirement.

Dedicated Field-Service Software

A dedicated platform becomes attractive when the business needs route optimization, offline mobile applications, customer portals, inventory replenishment, quoting, invoicing, contract entitlements, or accounting integration. At 220 requests per month, the representative business can first standardize its data and process with a smaller connected system.

Selected Connected Approach

The connected approach provides structured relational data while retaining familiar Google tools. It also leaves open a future migration path because customers, locations, assets, work orders, labour, parts, and event history are stored as separate entities.

The Selected Solution

Google Forms was retained because it works on mobile and desktop devices and is familiar to the company. Airtable was selected because it can represent linked customers, locations, assets, technicians, labour entries, parts, and work orders without forcing everything into one spreadsheet row.

Make connects the systems and provides routing beyond a simple form-to-record integration. Gmail remains the communication channel, while Google Drive retains control over photos rather than making files publicly accessible for attachment import.

Manual transcription, repeated status chasing, spreadsheet reconciliation, and ad hoc customer confirmation are removed. Human control remains over safety classification, schedule conflicts, high-value work approval, technician reassignment, work rejection, and final follow-up decisions.

System Architecture and Data Flow

• Intake: Google Forms for requests, technician actions, parts, approvals, and customer confirmation.
• System of record: Airtable base containing work orders and linked operational records.
• Automation layer: Make scenarios triggered by new Google Sheets response rows or scheduled Airtable queries.
• Document storage: Google Drive work-order folders with links stored in Airtable.
• Notifications: Gmail messages sent from a controlled operations mailbox.
• Reporting: Airtable views, formulas, and interfaces.
• AI layer: Optional API-based triage suggestions written to separate review fields.

1. Request submission: A customer or dispatcher submits the Work Request Form. Google Forms validates required fields and writes the response to its linked Google Sheet. A malformed row remains in staging and is not accepted into operations.
2. Event detection: Make watches for new response rows. It constructs a source event ID from the form type, spreadsheet ID, and row number.
3. Duplicate check: Make searches Airtable for the source event ID. If a matching work order or workflow event exists, the execution stops without creating another record.
4. Reference resolution: Make searches for the location code and asset tag. Exact matches create Airtable links. Unknown references cause the work order to enter Needs Triage.
5. Work-order creation: Make creates the Airtable record. Airtable assigns an autonumber and calculates the human-readable work-order ID. Make retrieves the record to obtain that ID.
6. Folder creation: Make creates a Google Drive folder using the work-order ID and location code. The returned Drive folder ID and URL are written to Airtable. Failure creates a recoverable document exception without deleting the work order.
7. Assignment: Make checks the location, asset, service category, and active technician rules. An exact rule can populate a proposed or assigned technician. Missing or ambiguous matches remain with dispatch.
8. Notification: Gmail sends an intake acknowledgement and, when appropriate, an assignment message. Notification timestamps and returned message identifiers, when available, are stored.
9. Field activity: Technician Form submissions update status, open or close labour sessions, add parts records, and move uploaded photos into the work-order folder.
10. Completion: A completed job enters Completed Pending Confirmation. Gmail sends a prefilled Customer Confirmation Form link containing the work-order ID and correlation token.
11. Customer response: A confirmed resolution closes the job. A negative response moves it to Follow-up Required and alerts dispatch.
12. Monitoring: Scheduled Make scenarios identify unaccepted assignments, missed schedules, approaching deadlines, overdue jobs, missing confirmation, and automation errors.

Every scenario uses the same recovery principle: search for the source event before creating data, write returned identifiers immediately, and resume from the first incomplete action after a failure.

Data Structure

The Airtable base is named Maintenance Operations. It contains linked tables rather than one wide work-order table.

Customers, locations, assets, technicians, and work orders use internal IDs rather than names as integration keys. Names can change; identifiers should remain stable.

Airtable does not enforce database-style unique constraints on arbitrary fields. The Make scenarios therefore search for Source Event ID before creating records, run intake sequentially, and expose a duplicate-detection view for administrative review.

Workflow Statuses and Ownership

A status can move backward when information changes. For example, Completed Pending Confirmation moves to Follow-up Required if the customer reports that the issue remains unresolved. Scheduled can return to Waiting for Customer if site access is withdrawn.

Automation failures do not automatically replace the operational status. A work order can remain In Progress while its separate Automation Status becomes Warning. This prevents an integration problem from obscuring the real state of the maintenance job.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create separate production and sandbox Airtable bases. Use the sandbox for field changes, automation testing, and sample records.
2. Create the Google Forms and linked response Sheets under a controlled automation account, not an individual dispatcher’s personal account.
3. Create a Shared Drive for work-order documents if the organization’s Google Workspace configuration supports it. Otherwise, use a folder owned by a controlled automation account and document the ownership-transfer procedure.
4. Create an operations Gmail identity such as YOUR_EMAIL_ADDRESS. Use delegation or a dedicated licensed mailbox according to the organization’s identity policy.
5. Create a Make organization or team and restrict scenario editing to automation administrators. Dispatchers should not need access to Make.
6. Connect Google Sheets, Google Drive, and Gmail to Make through OAuth. The connected account must be able to read form response Sheets, manage the work-order Drive folder, and send approved operational email.
7. Connect Airtable through the supported OAuth connection. If a personal access token is required by policy, scope it only to the production base and the minimum record and schema permissions required by the scenarios.
8. Store API keys and connection credentials in Make connections or the organization’s approved secret store. Do not place secrets in Airtable fields, Google Sheets, prompts, or scenario notes.
9. Create test identities for a dispatcher, technician, operations manager, customer contact, and unavailable approver.
10. Document a connection owner and a backup owner so integrations do not stop when an employee leaves.

The production Forms, Sheets, folders, and Airtable base should not contain sample records used during development. Sandbox scenarios remain inactive except during controlled tests.

Step 2: Build the Intake

Create five Google Forms, each linked to a separate response Sheet.

Work Request Form Fields

• Submission type: Customer submission or dispatcher-entered request.
• Requester name, required.
• Requester email, required with email-format validation.
• Requester telephone, required for emergency requests.
• Customer account reference, optional.
• Location code, required for known locations.
• Location address, shown when the location code is unavailable.
• Asset tag, optional.
• Asset type, shown when an asset tag is unavailable.
• Issue category: HVAC, Plumbing, Electrical, General Repair, Grounds, Safety, or Other.
• Issue description, required with a practical minimum length such as 20 characters.
• Operational impact: No interruption, Reduced operation, Asset unusable, or Possible safety concern.
• Requested urgency: Emergency, Today, This week, or Flexible.
• Preferred access date and time.
• Access instructions.
• Permission to contact the requester about the work.

Use sections and answer-based branching. For example, selecting Possible safety concern displays an instruction to call the emergency contact number and confirms that submitting a form does not replace immediate emergency reporting.

The external request form should not require file uploads because Google Forms file uploads generally require the submitter to sign in with a Google account. Dispatch can add customer-supplied photos later, while authenticated technicians use the internal update form for uploads.

Technician Update Form Fields

• Work Order ID, required and validated against ^WO-[0-9]{6}-[0-9]{5}$.
• Technician email, required.
• Action: Accept Assignment, En Route, Start Work, Pause Work, Resume Work, Waiting for Parts, Waiting for Customer, Cannot Access, or Complete Work.
• Action timestamp, taken from the form response timestamp unless a correction is explicitly entered.
• Update notes, required for wait, access, and completion actions.
• Expected next-action date, required for waiting states.
• Completion notes, required in the completion section.
• Photo status: Attached or Not Appropriate.
• Photo upload, displayed for attached photos.
• Customer contact present, yes or no.

Use separate form sections so completion fields are required only when Complete Work is selected.

Parts Usage Form Fields

• Work Order ID.
• Technician email.
• Part stock code, when known.
• Part description.
• Quantity, numeric and greater than zero.
• Unit of measure.
• Source: Van stock, Warehouse, Supplier purchase, or Customer-provided.
• Receipt or part photo, optional for authenticated users.

One submission represents one part line. A technician using three different parts submits three short entries. This creates structured records without trying to encode multiple line items in one spreadsheet cell.

Approval and Confirmation Forms

Both forms include Work Order ID and Correlation Token fields populated through a prefilled link. Google Forms does not provide hidden fields, so users can see and potentially edit these values. Make must validate both values against Airtable before accepting the response.

The approval form also requires approver name, email, decision, and comments. The confirmation form requires confirmer name, response of Resolved or Needs Follow-up, and optional comments.

Keep confirmation messages free of sensitive building, asset, and customer details. Add a concise privacy notice explaining how contact and service information will be used. Where external abuse is a concern, distribute forms only to known customers, require account or location references, and route unmatched submissions to triage.

Step 3: Create the System of Record

1. Create the Airtable tables defined in the data structure section.
2. Configure Sequence as an autonumber field.
3. Configure Work Order ID as a formula derived from creation month and sequence.
4. Create single-select options exactly once and reuse them across views and interfaces.
5. Link Customers to Locations, Locations to Assets, and Work Orders to all three.
6. Link Work Orders to Technicians, Labour Sessions, Parts Usage, Documents, Workflow Events, and Automation Errors.
7. Set default values: Status New, Automation Status Pending, Retry Count 0, Escalation Level 0, and Customer Confirmation Pending.
8. Create last-modified fields for status, assignment, schedule, approval, and completion information.
9. Create filtered operational views before building Make scenarios.

Recommended views include:

• New and Untriaged
• Ready to Schedule
• Needs Assignment Email
• Starts Due Within 60 Minutes
• Overdue Open Work
• Open Labour Sessions
• Waiting for Parts
• Pending Customer Confirmation
• Follow-up Required
• Automation Failures
• Possible Duplicate Source Events

Use linked records rather than copying customer addresses and technician emails into every child table. Lookup fields can display current information, while selected snapshot fields preserve values that must not change historically.

Step 4: Connect the Tools

Make interface labels can change between connector versions. The important configuration is the trigger, source identifier, exact field mapping, returned record ID, and error path, not a particular button label.

Step 5: Build the Core Automation

Automation 1: Create a Work Order

• Trigger: A new row appears in the Work Request Form response Sheet.
• Conditions: Required fields are present, email format is valid, and no existing source event is found.
• Actions: Normalize input, resolve references, create the Airtable work order, retrieve the calculated Work Order ID, create a Drive folder, write an event, and send acknowledgement.
• Fields updated: Source Event ID, customer, location, asset, status, priority, owner, due date, folder fields, Automation Status, and Last Automation Run.
• Notification: Requester receives the work-order number; urgent requests also alert dispatch.
• Exception: Invalid or unmatched data enters Needs Triage. Technical failures create an incomplete execution or error record.

The exact action order is important:

1. Create request:YOUR_REQUEST_SHEET_ID:ROW_NUMBER.
2. Search Work Orders for that source event ID.
3. Stop if a record already exists.
4. Validate and normalize the input.
5. Search Locations by exact location code.
6. Search Assets by exact asset tag and linked location.
7. Determine the initial owner and due date.
8. Create the work order with Automation Status Processing.
9. Retrieve the created record so formula fields are available.
10. Create the Drive folder and save its returned ID and URL.
11. Create a Workflow Event with event type Work Order Created.
12. Send the acknowledgement email.
13. Set Automation Status to Healthy and record the execution time.

Automation 2: Assignment and Schedule Notification

• Trigger: A scheduled Make scenario queries the Airtable view Needs Assignment Email every five minutes.
• Conditions: Assigned Technician and Scheduled Start are present, status is Scheduled, and Assignment Notification Sent At is blank.
• Actions: Retrieve technician contact details, compose the assignment message, send Gmail, write a Workflow Event, and update the notification timestamp.
• Fields updated: Assignment Notification Sent At, Last Notification ID, Automation Status, and Last Automation Run.
• Notification: Technician receives work-order ID, location, schedule, issue, access instructions, and technician form link.
• Exception: Missing or inactive technician records return the work order to dispatch review.

Routine assignment rules search for an active primary technician on the asset or location. If that search returns exactly one valid match, Make populates the technician. If there are no matches or several equal matches, dispatch chooses manually.

Automation 3: Process Technician Actions

• Trigger: A new Technician Update Form response row.
• Conditions: Work-order ID exists, technician is active, and the source event has not been processed.
• Actions: Route by action, update status, open or close a labour session, process photos, and create a Workflow Event.
• Fields updated: Status, timestamps, next-action date, completion notes, automation fields, and related labour records.
• Notification: Dispatch receives exception messages for access failures, waits, or attempted updates by an unassigned technician.
• Exception: Invalid transitions enter manual review without overwriting the current status.

The router applies these rules:

• Accept Assignment records acceptance but leaves status as Scheduled.
• En Route changes Scheduled to En Route.
• Start Work or Resume Work creates a Labour Session with an open finish time and changes status to In Progress.
• Pause Work closes the latest open session and returns the record to Scheduled or a selected waiting status.
• Waiting for Parts closes the open session, requires a next-action date, and assigns the parts coordinator as operational owner.
• Waiting for Customer closes the open session and alerts dispatch.
• Complete Work closes the latest open session, validates completion notes and photo status, and changes status to Completed Pending Confirmation.

Automation 4: Record Parts Usage

• Trigger: A new Parts Usage Form response row.
• Conditions: Work order exists, quantity is positive, and source event is unique.
• Actions: Create a Parts Usage record, link it to the work order, process an optional receipt photo, and create an event.
• Fields updated: Parts records and rollup totals on the work order.
• Notification: Parts coordinator is notified when the stock code is unknown or supplier purchase evidence is missing.
• Exception: Invalid quantities or unknown work orders enter the manual-review queue.

Automation 5: Process Customer Confirmation

• Trigger: A new Customer Confirmation Form response row.
• Conditions: Work Order ID and Correlation Token match the same Airtable record, and the work order is awaiting confirmation.
• Actions: Record the response, create an event, and route by confirmation result.
• Fields updated: Customer Confirmation, Confirmed By, Confirmed At, confirmation comments, and status.
• Notification: A confirmed result sends a final acknowledgement; a follow-up result alerts dispatch and the operations manager.
• Exception: Mismatched tokens or already-closed work orders enter manual review.

A confirmed result moves the job to Closed. A negative result moves it to Follow-up Required. Automation does not delete or overwrite the original completion evidence.

Step 6: Add Approvals, Reminders, and Escalations

Each Customer record stores an authorized work limit. When dispatch enters an estimate, Make compares it with that limit.

• Work within the active standing authorization is marked Not Required or Approved, depending on the customer agreement.
• Work above the limit first requires internal review by the operations manager.
• After internal review, the authorized customer contact receives the approval form.
• Safety-related make-safe activity follows the company’s emergency policy and is not delayed by a routine form workflow.
• Rejected work moves to Waiting for Customer or Rejected, based on dispatch review.

The approval sequence is internal review followed by customer authorization. Parallel approval was not selected because the estimate may change during internal review.

Each reminder query checks Last Reminder At and Escalation Level. A message is not sent again unless its interval has elapsed. Unavailable internal approvers use a documented backup approver. External approval links can be forwarded, so dispatch verifies high-value authorization against the known customer contact before releasing work.

Approval evidence includes the work-order ID, decision, responder, submitted email, response timestamp, source event ID, approval comments, and a Workflow Event. Contracts requiring signatures or stronger identity assurance should use an approved electronic signature or customer portal rather than relying only on Google Forms.

Step 7: Add Documents and File Management

Create a Drive structure under a controlled root folder:

Work Orders/
  2026/
    2026-07/
      WO-202607-00482 - LC-014/
        Request/
        Technician Photos/
        Parts and Receipts/
        Completion/

Make creates the monthly and work-order folders if they do not exist. Store both the Drive folder ID and URL in Airtable. The ID is the stable integration key; the URL is for users.

Use this file naming convention:

WO-202607-00482_20260708_114200_technician-email_completion_original-name.jpg

Google Forms places authenticated file uploads in its own Drive folder. Make extracts each file ID from the response, retrieves the file, moves it to the appropriate work-order folder, applies the naming convention, and creates a Documents record containing the returned file ID and link.

• Do not make files public merely to import them into Airtable.
• Grant Drive access through controlled groups and assigned operational roles.
• Store a new file for each revision instead of overwriting evidence.
• Retain the original upload until Make confirms the move.
• Flag duplicate files using source event ID, file ID, and optional checksum where available.
• Set form upload limits appropriate to mobile connectivity and available Drive storage.
• Apply the company’s contractual and legal retention policy before enabling deletion or archiving.

If file processing fails, the work order remains operationally complete but receives Document Status Upload Error. The original upload stays in the form folder, and the error queue contains the source file reference for recovery.

Step 8: Add Reporting and Operational Views

Build Airtable views and interfaces for the following queues:

• New requests awaiting triage.
• Work awaiting assignment or schedule.
• Today’s scheduled work by technician.
• Overdue work by priority.
• Incomplete customer, location, or asset references.
• Waiting for parts with expected dates.
• Waiting for customer approval.
• Rejected and cancelled work.
• Upcoming deadlines.
• Recently completed work.
• Pending customer confirmation.
• Follow-up required.
• Open labour sessions.
• Automation failures and retries.
• Manual-review queue.

Management reporting uses Work Orders as the primary source and linked rollups from Labour Sessions and Parts Usage. Useful measures include:

• Requests created by week and month.
• Open work by status and priority.
• Average time from submission to scheduling.
• Average time from submission to completion.
• Percentage completed before due time.
• Waiting time by reason.
• Labour hours by technician and service category.
• Parts quantity and estimated cost by work order.
• Customer follow-up rate.
• Automation failure count and age.

Airtable formulas and interfaces update from the operational records. The dashboard owner is the operations manager; the automation administrator owns source integrity and failed-run monitoring. Alerts should be based on actionable thresholds, not every record change.

Step 9: Add Security and Governance Controls

• Restrict Airtable base editing to dispatch, operations, and authorized administrators.
• Give view-only interface access where users do not need to edit records.
• Keep form response Sheets restricted to automation administrators.
• Use technician forms for field updates instead of granting every technician full base access.
• Validate technician email against the active Technicians table.
• Restrict Drive folders through groups and role-based access.
• Do not include internal notes, cost limits, or unrelated customer information in technician email.
• Store OAuth connections and API credentials only in approved connection stores.
• Review Make, Google, and Airtable activity logs according to the organization’s available subscription features.
• Remove former employees from Google groups, Airtable, Make, and shared folders promptly.
• Back up Airtable data and critical configuration on a defined schedule.
• Document retention requirements for photos, customer details, asset history, and approval evidence.
• Prohibit sensitive personal data, access codes, payment details, and regulated information from optional AI prompts unless an approved policy explicitly permits it.
• Require human approval for safety, authorization, rejection, and high-impact customer communications.

Technician update links should not expose all open work orders. A submitted Work Order ID is validated against the technician’s assignment or an authorized helper role. Unauthorized updates create a security event and do not change the work order.

Step 10: Deploy and Test

1. Build and test the forms, base, and scenarios in the sandbox environment.
2. Create sample customers, locations, assets, technicians, approval rules, and work orders.
3. Run every normal and exception path using test email addresses.
4. Conduct user acceptance testing with one dispatcher, the operations manager, two technicians, and one internal customer-contact tester.
5. Pilot the workflow with a limited customer group and three technicians for two weeks.
6. Keep the old request list read-only during the pilot so records can be reconciled.
7. Correct field names, instructions, message templates, and escalation timing before full activation.
8. Activate production Make scenarios in sequence: intake, assignment, technician actions, documents, confirmation, then escalation.
9. Monitor every production execution during the first several operating days.
10. Publish a one-page technician guide and a dispatch operating procedure.
11. Assign the automation administrator as technical support owner and the operations manager as business owner.
12. Maintain a rollback plan that disables outbound scenarios while preserving submitted form responses and existing Airtable records.

Launch communication should explain which form to use, what information is mandatory, where dispatch sees exceptions, and how staff report a failed or incorrect automation result.

Code and Configuration

The core implementation does not require a custom script. Google Forms writes to Sheets natively, and Make provides the required watch, search, create, update, route, email, and file actions. Avoiding custom code reduces the number of separately deployed components.

The following Airtable formulas and Make configuration values are still required.

Airtable Work Order ID Formula

Place this formula in the Work Order ID field. It combines the Airtable creation month with the autonumber sequence.

"WO-" & DATETIME_FORMAT(CREATED_TIME(), "YYYYMM") & "-" & RIGHT("00000" & {Sequence}, 5)

Test by creating several sandbox records and confirming that the IDs are unique, consistently padded, and unaffected by changes to customer or location names.

Airtable Correlation Token Formula

Use the stable Airtable record ID as a correlation value for confirmation and approval forms. This is a correlation control, not a security credential.

RECORD_ID()

Airtable SLA State Formula

IF(
  OR({Status} = "Closed", {Status} = "Cancelled", {Status} = "Rejected"),
  "Final",
  IF(
    NOT({Due At}),
    "No Due Date",
    IF(
      NOW() > {Due At},
      "Overdue",
      IF(
        DATETIME_DIFF({Due At}, NOW(), "hours") <= 4,
        "Due Soon",
        "On Track"
      )
    )
  )
)

This formula is useful for views, but Make should compare the actual Due At value during scheduled escalation runs. Formula refresh timing should not be the only deadline control.

Processing-Time Formula

IF(
  AND({Created Date}, {Completed At}),
  ROUND(DATETIME_DIFF({Completed At}, {Created Date}, "minutes") / 60, 2)
)

Make Environment Values

Store these values in documented scenario variables or a secured configuration data store. Replace every placeholder before activation.

AIRTABLE_BASE_ID=YOUR_AIRTABLE_BASE_ID
WORK_ORDERS_TABLE=Work Orders
WORKFLOW_EVENTS_TABLE=Workflow Events
AUTOMATION_ERRORS_TABLE=Automation Errors

REQUEST_SHEET_ID=YOUR_REQUEST_SHEET_ID
TECHNICIAN_UPDATE_SHEET_ID=YOUR_TECHNICIAN_UPDATE_SHEET_ID
PARTS_SHEET_ID=YOUR_PARTS_SHEET_ID
APPROVAL_SHEET_ID=YOUR_APPROVAL_SHEET_ID
CONFIRMATION_SHEET_ID=YOUR_CONFIRMATION_SHEET_ID

WORK_ORDER_ROOT_FOLDER_ID=YOUR_FOLDER_ID
OPERATIONS_EMAIL=YOUR_EMAIL_ADDRESS
BUSINESS_TIMEZONE=YOUR_BUSINESS_TIMEZONE

Source Event Identifiers

Request:
request:YOUR_REQUEST_SHEET_ID:ROW_NUMBER

Technician update:
technician-update:YOUR_TECHNICIAN_UPDATE_SHEET_ID:ROW_NUMBER

Part usage:
part:YOUR_PARTS_SHEET_ID:ROW_NUMBER

Approval:
approval:YOUR_APPROVAL_SHEET_ID:ROW_NUMBER

Customer confirmation:
confirmation:YOUR_CONFIRMATION_SHEET_ID:ROW_NUMBER

Before any create action, search for an exact source event match. When a scenario is retried after partial completion, it should retrieve the existing record and continue with missing actions rather than create a duplicate.

Priority and Due-Date Rules

These are representative operating assumptions, not universal service levels. If contractual deadlines use business hours, holidays, or customer-specific calendars, calculate them through an approved calendar service or a dedicated business-calendar table rather than adding elapsed hours.

Prefilled Customer Confirmation Link

Use Google Forms’ prefilled-link function once during configuration to identify the form and field entry IDs. Store the resulting template in Make.

https://docs.google.com/forms/d/e/YOUR_CONFIRMATION_FORM_ID/viewform?usp=pp_url&entry.WORK_ORDER_FIELD_ID=WORK_ORDER_VALUE&entry.TOKEN_FIELD_ID=TOKEN_VALUE

Make replaces WORK_ORDER_VALUE and TOKEN_VALUE with URL-encoded Airtable values. Test spaces, plus signs, and other special characters even though the standard work-order ID does not contain them.

Native Scenario Configuration

Enable Make incomplete executions for unhandled technical failures. Error routes should write sanitized error details when Airtable is available. Authentication errors and invalid configuration should not be retried indefinitely.

Failure Handling and Operational Reliability

Idempotency means a repeated event has the same effect as processing it once. The source event ID, Airtable record ID, Drive file ID, and notification timestamps provide idempotency controls throughout this implementation.

The Automation Errors table acts as a business-level dead-letter queue. It stores records that cannot be completed automatically after limited retries. It should contain the scenario name, source event ID, affected record, failed operation, sanitized error, attempt count, next retry time, and recovery status.

Reconciliation runs daily. It compares recent Google Form response rows with Workflow Events, checks work orders without folders, identifies completion records without confirmation messages, and detects open labour sessions older than the allowed maximum.

A Complete Example

At 8:12 a.m. on July 8, 2026, a customer contact submits a request for location LC-014. The asset is rooftop unit RTU-2. The description says that the unit is running but not cooling the warehouse office area. The operational impact is Asset unusable, and the requested urgency is Today.

1. Google Forms validates the required fields and writes row 187 to the request response Sheet.
2. Make creates source event ID request:YOUR_REQUEST_SHEET_ID:187.
3. Airtable contains no matching source event, so processing continues.
4. Make converts the location and asset references to uppercase and finds exact linked records.
5. The location’s service rule identifies an active HVAC technician as the proposed technician.
6. The deterministic impact rule suggests High priority. Dispatch confirms it because there is no reported safety concern.
7. Make creates the Airtable record. Airtable generates WO-202607-00482 and returns a representative record identifier such as recExample00482.
8. The due time is set to 8:12 a.m. on July 9 under the representative 24-hour High-priority rule.
9. Make creates WO-202607-00482 - LC-014 in Drive and writes the returned folder ID to Airtable.
10. Gmail acknowledges the request and sends the work-order number to the requester.
11. Dispatch reviews technician availability and schedules the visit for 10:00 a.m.
12. The assignment scenario sends the technician the work order, location, issue description, access notes, and update-form link.
13. At 9:42 a.m., the technician submits En Route. Make changes the status and records the event.
14. At 10:05 a.m., the technician submits Start Work. Make creates an open Labour Session.
15. The technician identifies a failed capacitor and submits one Parts Usage entry for stock code CAP-45, quantity one.
16. The estimated work remains within the customer’s standing authorization, so no additional approval is required.
17. At 11:41 a.m., the technician submits Complete Work, notes that the capacitor was replaced and the unit tested, and uploads before-and-after photos.
18. Make closes the labour session at 96 minutes, moves the photos to the Drive folder, and changes the status to Completed Pending Confirmation.
19. Gmail sends the customer a prefilled confirmation link containing the work-order ID and Airtable correlation token.
20. At 1:10 p.m., the customer selects Resolved. Make validates the work order and token, stores the response, creates a Workflow Event, and changes the status to Closed.

If the photo move had failed, the status would still have reflected operational completion, but Document Status would have shown Upload Error. If the customer had selected Needs Follow-up, the work order would have entered the dispatch follow-up queue instead of closing.

Implementation Cost

All amounts below are representative planning assumptions in US dollars. They are not vendor quotes or verified client costs. Current subscription features and prices must be confirmed directly with each provider.

The $155 core recurring software allowance used in the savings calculation consists of Airtable and Make. Existing Google Workspace cost is not treated as zero overall; it is excluded only because the representative company already uses it. Additional storage, accounts, or administrative features should be added when applicable.

Estimated Time and Cost Savings

The estimate measures administrative handling, status coordination, and reconciliation. It does not treat technician repair time as automation savings.

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

220 × 22 ÷ 60 = 80.67 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

(220 × 7 ÷ 60) + (220 × 12% × 8 ÷ 60) + 4 = 33.19 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

80.67 - 33.19 = 47.48 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

47.48 × $38 = $1,804.24

Net estimated monthly value: Monthly labour value minus recurring tool costs

$1,804.24 - $155 = $1,649.24

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

$8,912 ÷ $1,649.24 = approximately 5.4 months

Recovered time does not automatically reduce payroll. It may provide additional dispatch capacity, quicker turnaround, reduced overtime, less administrative work for technicians, and the ability to handle higher request volume without adding the same amount of coordination effort.

Non-financial benefits include clearer ownership, fewer follow-up messages, more complete work records, faster customer responses, better auditability, structured asset history, more consistent escalation, and improved reporting.

Readers should replace the volume, handling time, exception rate, labour cost, subscription allowance, implementation hours, and maintenance estimate with their own figures. They should also account for seasonal demand, travel scheduling, contract requirements, data cleanup, and adoption time.

Adding AI to the Automation

AI is optional and should be added only after the core forms, records, status rules, notifications, and exception handling operate reliably.

Potential AI uses include summarizing long issue descriptions, suggesting an issue category, identifying missing diagnostic details, comparing new descriptions with prior asset history, and supporting semantic search across completion notes.

AI is not needed for required fields, exact asset matching, deadline calculations, approval thresholds, email validation, status transitions, or permission rules. Those tasks are more reliable when handled through forms, lookups, formulas, and deterministic automation.

The core automation creates records, assigns ownership, sends notifications, escalates overdue work, tracks documents, and records confirmation. AI adds value only when interpreting unstructured descriptions.

The Recommended AI Enhancement

The recommended enhancement is AI-assisted intake triage. After a work order is created, Make sends a limited issue description and non-sensitive asset context to an approved AI API. The model returns a concise summary, suggested category, missing information, suggested priority, confidence score, and safety flag.

• Trigger: A validated work order is created with at least 20 characters of issue description.
• AI input: Work-order ID, asset type, issue description, operational impact, and requested urgency.
• Record update: Separate fields named AI Summary, AI Suggested Category, AI Suggested Priority, AI Missing Information, AI Confidence, and AI Safety Flag.
• Human review: Dispatch accepts, edits, or ignores every suggestion.
• Low confidence: Confidence below 0.85 automatically enters manual review.
• Safety handling: Any safety flag alerts dispatch but does not automatically classify the situation as safe or unsafe.
• Failure behaviour: The work order continues through normal triage without AI output.

Reusable System Instruction

You assist a maintenance dispatcher by analyzing a reported equipment or facility issue.

Return only the requested structured JSON.

Do not make a final safety decision, authorize work, reject a request, select a technician, or promise a response time.

Use only the supplied information. Do not invent symptoms, causes, parts, costs, or completed actions.

Treat suggested_priority as a recommendation for human review.

Set safety_flag to true when the description may indicate immediate risk, including smoke, fire, exposed electrical conductors, gas odour, flooding near electrical equipment, structural instability, or risk of injury.

If important information is missing, list concise questions in missing_information.

Reusable User Prompt

Analyze this maintenance request.

Work order ID: {{Work Order ID}}
Asset type: {{Asset Type}}
Reported category: {{Issue Category}}
Operational impact: {{Operational Impact}}
Requested urgency: {{Requested Urgency}}
Issue description: {{Issue Description}}

Allowed categories:
HVAC
Plumbing
Electrical
General Repair
Grounds
Safety
Other

Allowed priorities:
Emergency
High
Normal
Low

Structured Output Schema

{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "summary": {
      "type": "string"
    },
    "suggested_category": {
      "type": "string",
      "enum": [
        "HVAC",
        "Plumbing",
        "Electrical",
        "General Repair",
        "Grounds",
        "Safety",
        "Other"
      ]
    },
    "suggested_priority": {
      "type": "string",
      "enum": [
        "Emergency",
        "High",
        "Normal",
        "Low"
      ]
    },
    "missing_information": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "safety_flag": {
      "type": "boolean"
    },
    "confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    },
    "rationale": {
      "type": "string"
    }
  },
  "required": [
    "summary",
    "suggested_category",
    "suggested_priority",
    "missing_information",
    "safety_flag",
    "confidence",
    "rationale"
  ]
}

Make HTTP Request Configuration

Use an HTTP request module when the approved AI provider does not have a suitable maintained Make connector. For the OpenAI Responses API, configure the request as follows and verify the current provider documentation before deployment.

• Method: POST
• Endpoint: https://api.openai.com/v1/responses
• Authentication header: Authorization: Bearer YOUR_API_KEY
• Content-Type header: application/json
• Expected success status: 200
• Pagination: None for an individual response request.

{
  "model": "YOUR_APPROVED_MODEL",
  "instructions": "You assist a maintenance dispatcher by analyzing a reported equipment or facility issue. Return only the requested structured JSON. Do not make a final safety decision, authorize work, reject a request, select a technician, or promise a response time. Use only the supplied information. Do not invent symptoms, causes, parts, costs, or completed actions. Treat suggested_priority as a recommendation for human review. Set safety_flag to true when the description may indicate immediate risk. If important information is missing, list concise questions in missing_information.",
  "input": "Work order ID: {{Work Order ID}}\nAsset type: {{Asset Type}}\nReported category: {{Issue Category}}\nOperational impact: {{Operational Impact}}\nRequested urgency: {{Requested Urgency}}\nIssue description: {{Issue Description}}",
  "text": {
    "format": {
      "type": "json_schema",
      "name": "maintenance_triage",
      "strict": true,
      "schema": {
        "type": "object",
        "additionalProperties": false,
        "properties": {
          "summary": {
            "type": "string"
          },
          "suggested_category": {
            "type": "string",
            "enum": [
              "HVAC",
              "Plumbing",
              "Electrical",
              "General Repair",
              "Grounds",
              "Safety",
              "Other"
            ]
          },
          "suggested_priority": {
            "type": "string",
            "enum": [
              "Emergency",
              "High",
              "Normal",
              "Low"
            ]
          },
          "missing_information": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "safety_flag": {
            "type": "boolean"
          },
          "confidence": {
            "type": "number",
            "minimum": 0,
            "maximum": 1
          },
          "rationale": {
            "type": "string"
          }
        },
        "required": [
          "summary",
          "suggested_category",
          "suggested_priority",
          "missing_information",
          "safety_flag",
          "confidence",
          "rationale"
        ]
      }
    }
  },
  "max_output_tokens": 500
}

Parse the returned output text as JSON and validate it again in Make. Confirm that all required keys exist, enum values match Airtable options, confidence is between zero and one, and the response belongs to the expected work order.

Record the model name, prompt version, processing timestamp, confidence, and provider request identifier when available. Do not store the API key or full sensitive payload in Airtable.

Retry HTTP 429 and transient server failures with delayed attempts. Do not retry invalid requests or authentication failures until the configuration is corrected. If AI remains unavailable, set AI Status to Unavailable and leave dispatch in the normal rule-based workflow.

Benefits of the AI Enhancement

• Dispatchers spend less time condensing long request descriptions.
• Categories are suggested more consistently across differently worded requests.
• Missing asset, symptom, access, or impact details are highlighted earlier.
• Potential safety language is brought to human attention more quickly.
• Structured summaries improve filtering and management reporting.
• Unstructured maintenance descriptions become easier to search.

These are AI-specific benefits. Record creation, assignment routing, reminders, escalation, file management, customer confirmation, and audit history are already provided by the core automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

Assume 70 percent of the 220 monthly work orders contain enough unstructured text to benefit from AI-assisted triage. That produces 154 eligible requests.

Manual triage component without AI:

154 × 2 ÷ 60 = 5.13 hours

AI review time:

154 × 0.5 ÷ 60 = 1.28 hours

Correction time:

154 × 12% × 2 ÷ 60 = 0.62 hours

Failure fallback time:

154 × 2% × 2 ÷ 60 = 0.10 hours

Net additional capacity:

5.13 - 1.28 - 0.62 - 0.10 = 3.13 hours per month

Representative net monthly value:

(3.13 × $38) - $12 = $106.94

The AI estimate does not assume perfect output or removal of human review. The value is modest compared with the core automation and should be validated through a controlled pilot.

Testing Checklist

Use sample data and test accounts before processing real customer or employee information.

Ongoing Maintenance

The operations manager is the business owner. The automation administrator is the primary technical owner, with a trained backup administrator.

Monitor form schema changes carefully. Renaming or reordering questions can alter Sheet columns and break mappings. Add or change fields in the sandbox first, update Make mappings, run regression tests, and then promote the change.

Archive closed work according to retention rules, but preserve identifiers and event history needed for customer, asset, accounting, or audit purposes. Periodically confirm that Drive links still resolve and that folders remain owned by controlled accounts.

When to Move to Dedicated Software

The connected implementation does not need to be replaced simply because it has been operating for some time. A move should be based on measurable requirements and risk.

Signs that a dedicated field-service or maintenance-management platform may be appropriate include:

• Transaction volume causes frequent API limits, delayed scenarios, or manual batching.
• Dispatch requires route optimization and live geographic scheduling.
• Technicians require a supported offline mobile application.
• Customers need a secure portal for requests, approvals, status, documents, and history.
• Inventory requires reservations, replenishment, serial tracking, and multiple warehouses.
• Work orders must create quotes, invoices, purchase orders, or accounting entries.
• Multiple locations need separate permissions, operating calendars, and management structures.
• Regulatory requirements demand formal electronic signatures, inspection templates, or controlled audit records.
• Advanced role-based permissions exceed the practical controls of forms and base interfaces.
• Exception rates or monthly maintenance effort continue to increase.
• Spreadsheet staging or Airtable record volume affects performance and administration.
• The business requires contractual vendor support and formal service commitments.
• Complex preventive maintenance, recurring schedules, warranties, or service-level agreements become central.
• Security risk increases because too many external links or connected applications must be maintained.

Before migration, export and clean the structured customer, location, asset, work-order, labour, parts, document, and event data. The relational design used here provides a stronger migration source than paper records or an unstructured spreadsheet.

Implementation Checklist

• Confirm request volume, priorities, service levels, owners, and exception rules.
• Approve Google Forms, Airtable, Make, Google Drive, and Gmail as the selected tools.
• Create production and sandbox accounts.
• Assign business, technical, security, and backup owners.
• Configure least-privilege permissions and controlled OAuth connections.
• Create Customers, Locations, Assets, Technicians, Work Orders, Labour Sessions, Parts Usage, Documents, Workflow Events, Assignment Rules, and Automation Errors tables.
• Configure unique work-order and source event identifiers.
• Build request, technician, parts, approval, and confirmation forms.
• Link each form to a controlled response Sheet.
• Define and test every source-to-destination field mapping.
• Build intake validation and duplicate prevention.
• Build assignment, schedule, and technician notification workflows.
• Build technician status and labour-session processing.
• Build parts and photo processing.
• Configure approval thresholds and human review.
• Configure reminders, escalation intervals, and backup owners.
• Create Drive folders, naming rules, access controls, and retention rules.
• Create dispatch queues, exception views, and management reporting.
• Configure incomplete executions, retries, error records, and manual recovery.
• Document formulas, variables, connections, credentials, and form entry IDs.
• Run normal, duplicate, security, failure, recovery, reporting, and audit tests.
• Pilot with a limited technician and customer group.
• Activate production scenarios in a controlled sequence.
• Validate implementation cost and recurring subscription assumptions.
• Replace representative savings assumptions with actual measured handling times.
• Add AI only after the core workflow is stable.
• Require human review of every AI suggestion.
• Assign daily, weekly, monthly, and quarterly maintenance tasks.
• Define measurable criteria for moving to dedicated field-service software.