Construction Job Costing and Margin Tracking in Airtable

The Business Situation

Cedar Vale Contracting is a fictional 42-person commercial renovation contractor. The company typically manages 12 to 18 active projects involving employed field crews, material purchases, equipment, and specialist subcontractors.

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 operations team includes project managers, site supervisors, estimators, and a procurement coordinator. A three-person finance team manages supplier invoices, customer billing, payroll inputs, and the accounting system. The company has 26 field employees, while project managers are responsible for budgets, purchasing decisions, change orders, and margin reviews.

Estimates were prepared in separate spreadsheets. Once a project was awarded, finance created an accounting job and project managers copied selected estimate totals into their own tracking sheets. Labour hours arrived through weekly spreadsheets or email. Purchase receipts were sent to a shared mailbox, while change orders were maintained in another spreadsheet.

The representative monthly workload was:

• 620 labour time entries.
• 180 material, subcontractor, equipment, and other purchase records.
• 24 change-order records.
• 45 customer invoice updates.
• 12 to 18 active jobs requiring weekly cost and margin review.

The company could eventually determine how much had been spent, but the information was usually incomplete or too late for operational decisions. Project managers could not reliably compare budget, incurred cost, committed cost, approved change orders, invoicing, and projected margin without asking finance to reconcile several systems.

The need for change was not primarily an accounting-system problem. It was an operational data problem. Costs were being captured late, job codes were inconsistent, and there was no dependable workflow for assigning exceptions or escalating overdue reviews.

The Existing Process

The original workflow followed these steps:

1. An estimator prepared the bid using a spreadsheet template with labour, materials, subcontractor, equipment, and overhead assumptions.
2. When the bid became a project, a project manager copied summary budget values into a separate project spreadsheet.
3. Finance created a corresponding job in the accounting system, sometimes using a different abbreviation or identifier.
4. Employees submitted time through emailed spreadsheets, messages, or supervisor summaries.
5. Receipts and supplier invoices were emailed to finance, often without a job code or cost code.
6. Project managers tracked commitments, purchase orders, and change orders in personal spreadsheets.
7. Finance issued customer invoices in the accounting system and emailed invoice totals to project managers.
8. At month-end, finance combined payroll data, supplier costs, subcontractor invoices, billing information, and project spreadsheets.
9. Project managers reviewed the resulting margin report and investigated unexpected variances.

The existing spreadsheets were not inherently unsuitable for estimating. The problem was that they had also become transaction databases, approval queues, document indexes, and reporting tools without the controls those functions required.

What the New System Needed to Do

The implementation requirements were agreed before selecting the final tools.

The system also needed a controlled manual override. Finance could correct a coding error, but the original submitted value, correction reason, person making the change, and correction date had to remain visible.

Implementation Approaches Considered

Improving Sheets or Excel

A controlled spreadsheet could standardize job codes and formulas, and it remained appropriate for estimating. It was less suitable as the transaction system because mobile submissions, linked documents, approvals, record-level ownership, and failure handling would require substantial scripting and governance.

Using the Accounting System

The accounting platform remained the authoritative source for the general ledger, accounts payable, accounts receivable, and official invoices. Its job-costing features could report posted financial transactions, but the contractor also needed visibility into field time, commitments, pending purchases, and unapproved change costs before they became accounting entries.

Using Airtable with an Automation Layer

Airtable provided linked records, formulas, rollups, filtered views, and operational interfaces. Google Forms offered familiar mobile intake. Make connected the intake sheets to Airtable, Drive, and Gmail. This approach fit the transaction volume and allowed the business to retain its estimating spreadsheets and accounting software.

Purchasing Dedicated Construction Software

A dedicated platform could eventually provide deeper scheduling, procurement, drawing management, mobile field operations, and accounting integration. It was not selected for this representative phase because the immediate scope was structured job costing and margin monitoring rather than full construction management replacement.

Creating a Custom Application

A custom application would offer the strongest control over data structures and interfaces. The cost, implementation time, testing responsibility, and ongoing support burden were disproportionate to the current volume.

Reporting products were also reviewed. A business intelligence tool could produce more advanced dashboards, but it would not fix incomplete or inconsistently coded source data. Airtable Interfaces was selected for the first reporting layer. A dedicated reporting tool could be added after the data model had stabilized.

The Selected Solution

The selected design used Airtable as an operational job-costing subledger. It did not replace the accounting system or the estimating spreadsheets.

The estimating workbook was retained because estimators already understood it and its calculation detail was useful. When a job was awarded, approved budget lines were imported into Airtable as a fixed baseline.

The accounting system was retained because operational approval in Airtable did not constitute an accounting entry or payment authorization. Finance recorded the accounting transaction separately and returned its external reference to Airtable.

Manual copying, inbox searching, spreadsheet consolidation, reminder emails, and routine margin arithmetic were removed. Human control remained over budget approval, purchase approval, change-order approval, cost-code correction, accounting posting, invoice issuance, payment release, forecast changes, and final job closure.

System Architecture and Data Flow

• Intake: Four internal Google Forms for time, purchases, change-order lines, and invoice updates
• System of record: Airtable
• Automation layer: Make
• Document storage: Google Drive
• Notifications: Gmail
• Reporting: Airtable Interfaces and scheduled Gmail summaries
• AI layer: Optional cost-code review after the core workflow is stable

1. A user submits a form. Google Forms validates required fields and writes the response to its linked Google Sheet. File uploads are initially stored in the Google Drive folder managed by the form.

2. Make detects a new response row. A scheduled Make scenario watches the applicable response sheet. The row number, spreadsheet identity, and form type are combined into a controlled source event key.

3. The event is checked for duplication. Make searches Airtable for the source event key before creating a transaction. If a matching record exists, the response sheet is marked as already processed rather than creating another record.

4. The job is validated. Make searches the Jobs table for an exact active job code. A missing, closed, or ambiguous job sends the response to the exception path.

5. The employee and cost code are validated. Time entries require an active employee and loaded cost rate. Purchases and change orders require a valid cost code and matching job budget line.

6. Values are normalized. Dates are stored in the base time zone, numeric fields are converted to currency or decimal values, text is trimmed, and controlled dropdown values are mapped to Airtable options.

7. The operational record is created. Make creates a Time Entry, Purchase, Change Order, or Invoice record. Airtable returns its internal record identifier.

8. Documents are filed. Make resolves the job’s Drive folder, moves each uploaded file to the appropriate subfolder, and stores the resulting Drive link on the Airtable transaction.

9. Ownership is assigned. The job’s project manager, finance owner, or designated approver is copied to the transaction. If review is required, Make creates a linked Task record.

10. Notifications are sent. Gmail sends confirmation or action messages containing the business record ID, job code, amount, due date, and a controlled review-interface link.

11. Rollups update the job position. Airtable recalculates actual cost, open commitments, current budget, forecast cost, contract value, projected margin, invoiced amount, and unbilled value.

12. Scheduled controls monitor the workflow. Make checks overdue tasks, pending approvals, missing documents, unprocessed form rows, and negative budget variances. Failures are written to the Automation Log and routed to a manual-review view.

Every successful scenario writes the Airtable record ID, processing status, and processing timestamp back to the response sheet. If creation fails, the sheet row remains visible with an error status and the Automation Log contains the failure details.

Data Structure

The Airtable base contains related tables rather than one large transaction table. A Job links to many Budget Lines, Time Entries, Purchases, Change Orders, Invoices, and Tasks. Each transaction links to one Job. Cost transactions also link to one Budget Line.

Jobs and Budget Lines

Operational Transactions

Control and Audit Fields

Each form-created transaction contains the following automation fields:

The Employees table stores employee email, role, active status, manager, delegated approver, and loaded cost rate. Loaded rates are restricted to finance and authorized administrators. The Cost Codes table stores the approved code, category, description, active status, and whether a document or special approval is required.

The Tasks table stores Task ID, linked transaction, task type, owner, due date, status, reminder count, last reminder date, escalation level, completion date, and notes. The Automation Log stores scenario name, source event key, record ID, execution time, result, retry count, safe error message, and recovery status.

Workflow Statuses and Ownership

Records move backward when an approver requests more information, finance identifies a reconciliation difference, or an authorized user reopens a closed exception. Rejection requires a reason. A correction does not overwrite approval evidence from an earlier version; a material correction returns the transaction to approval.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create an automation identity. Use a dedicated Google Workspace account such as YOUR_AUTOMATION_EMAIL_ADDRESS. It owns or has durable access to forms, response sheets, the job-costing Drive folders, and the Gmail connection. Do not build production scenarios under a temporary employee account.

2. Create the Airtable workspace and base. Assign one operations systems owner and one backup owner. Project managers may edit operational records. Finance can edit budgets, rates, invoice references, and reconciliation fields. Field users submit forms but do not need base access.

3. Confirm required product features. The selected subscriptions must support the required Airtable record volume and permissions, Make scheduling and scenario operations, organizational Google Forms, file uploads, and adequate Drive storage. Product packaging changes, so verify current vendor documentation rather than relying on a plan name.

4. Create a Make team. Restrict scenario editing to the automation owner and backup owner. Connect Airtable using the supported OAuth connection or a narrowly scoped personal access token. Connect Google Sheets, Drive, and Gmail using the automation identity.

5. Apply least privilege. If a token is used, grant only the selected base and required record scopes. The Google connection should access only the forms, sheets, Drive folders, and mailbox needed by the workflow.

6. Create test assets. Duplicate the Airtable base structure, create test forms and response sheets, and create a separate Drive root folder. Use fictional sample projects and vendors. Production scenarios remain inactive during testing.

7. Create test users. Include a field submitter, project manager, finance approver, unavailable approver, unauthorized user, and automation administrator.

8. Document permission boundaries. Loaded labour rates, forecast overrides, customer billing values, and accounting identifiers are limited to finance and authorized managers. Public sharing is disabled.

No public webhook is required for the core design. Make polls linked Google Sheets and Airtable control views. This reduces public endpoint exposure and gives the team a visible response log.

Step 2: Build the Intake

Create four Google Forms, each with its own linked response sheet.

1. Require organizational sign-in and collect the authenticated email address. Do not limit users to one response because the forms are used repeatedly.

2. Use dropdowns for job codes, cost codes, cost type, purchase stage, and payment status. The automation owner updates the job dropdown as projects become active or close. Airtable still performs the authoritative validation.

3. Apply numeric response validation. Hours and invoice amounts must be greater than zero. Tax can be zero but not negative. Change-order values may be negative because credits are valid.

4. Use conditional sections. A subcontractor transaction requests a subcontractor description and supporting quote. A purchase marked Received requests a receipt. An invoice marked Disputed requires a dispute note.

5. Use Google Forms file upload only for authenticated internal users. External subcontractors send documents through the company’s approved channel, after which an internal coordinator submits or attaches them.

6. Add a short privacy notice explaining that the form is for business records and must not contain payment-card data, personal banking information, government identifiers, or unrelated personal information.

7. Set a confirmation message telling users that the response has been recorded and that they should not submit it again unless instructed. The generated business ID is sent later by email after successful processing.

8. Add processing columns to the right side of each response sheet: Source Event Key, Processing Status, Airtable Record ID, Processed At, and Processing Error. Forms writes only the response columns; Make updates the control columns.

Google Forms cannot dynamically validate the job against Airtable during submission. That validation occurs immediately after intake. Incomplete or invalid responses are retained and assigned for correction rather than silently discarded.

Step 3: Create the System of Record

1. Create the following Airtable tables: Employees, Cost Codes, Jobs, Budget Lines, Time Entries, Purchases, Change Orders, Invoices, Tasks, and Automation Log.

2. Add an Autonumber field named Sequence to each transaction and task table. Add a formula field that generates readable IDs.

3. Configure linked relationships. Jobs link to Project Manager and Finance Owner in Employees. Budget Lines link to Jobs and Cost Codes. Transactions link to Jobs, and cost transactions link to Budget Lines.

4. Import active employees and loaded cost rates. Restrict loaded-rate visibility. The rate copied to a time entry is a historical snapshot, so later employee-rate changes do not rewrite old job costs.

5. Import the controlled cost-code list. A representative list includes LAB-100 Labour, MAT-200 Materials, SUB-300 Subcontractors, EQP-400 Equipment, and OTH-500 Other Direct Cost. More detailed codes can be used if employees can apply them consistently.

6. Create each awarded job with one controlled job code. Import the approved estimate as Budget Line records. Do not allow project managers to overwrite Original Budget after approval.

7. Create filtered views for Active Jobs, New Transactions, Pending Operations Approval, Pending Finance Approval, Missing Documents, Overdue Tasks, Negative Budget Variance, Automation Failures, and Ready for Reconciliation.

8. Enable record history appropriate to the selected Airtable subscription. Use the separate Automation Log because product-level revision history alone is not a complete integration log.

Airtable does not enforce a relational database unique constraint on the Source Event Key. The implementation therefore combines an exact lookup before creation, sequential scenario processing, a source-sheet status, and periodic duplicate reconciliation.

Step 4: Connect the Tools

For each form, create one Make intake scenario. The trigger watches new response rows. Interface labels may vary by connector version, but the underlying sequence is the same: detect row, validate, search, create, move documents, assign task, send email, and update the source row.

Use the following source event key pattern:

TIME|YOUR_SPREADSHEET_ID|Time Responses|ROW_NUMBER
PURCHASE|YOUR_SPREADSHEET_ID|Purchase Responses|ROW_NUMBER
CHANGE|YOUR_SPREADSHEET_ID|Change Responses|ROW_NUMBER
INVOICE|YOUR_SPREADSHEET_ID|Invoice Responses|ROW_NUMBER

The tokens are mapped from the trigger output. Spreadsheet identity, sheet name, and row number are controlled values, not user-entered values.

Step 5: Build the Core Automation

Time-entry intake

• Trigger: A new row appears in the Daily Time Entry response sheet.
• Conditions: Processing Status is blank; job is Active; employee email is active; cost code exists; hours are greater than zero; source event key does not already exist.
• Actions: Look up Job, Employee, and Budget Line; copy the employee’s loaded rate; create Time Entry; create an exception task for entries above 12 hours; send confirmation; update the source row.
• Fields updated: Job, Budget Line, Work Date, Hours, Loaded Cost Rate, Labour Cost, Submitted By, Source Event Key, Automation Status, Airtable Record ID, and Processed At.
• Notification: Submitter receives the Time Entry ID. Supervisor receives an alert only for exceptions or scheduled review.
• Exception: Missing employee, inactive job, invalid cost code, future date, duplicate, or missing loaded rate creates a Needs Review record.

The exact order is important. Make must copy the rate before creating the record, because using an Airtable lookup that changes later could restate historical labour cost. The source row is marked Completed only after the Airtable record exists.

Purchase and commitment intake

• Trigger: A new row appears in the Purchase or Commitment response sheet.
• Conditions: Valid active job, valid budget line, positive total, known submitter, nonduplicate event, and required document present.
• Actions: Create Purchase; file attachments; calculate approval route; assign project manager; create approval task when required; send confirmation and approval request; update the response sheet.
• Fields updated: Cost Type, Vendor, Description, amounts, Status, Approval Status, Job, Budget Line, Document Link, owner, source key, and automation fields.
• Notification: Submitter receives the Purchase ID. The first approver receives an action email.
• Exception: A missing required receipt, unknown job, invalid cost code, or file error creates a linked correction task.

Costs already incurred remain visible in actual cost even when approval is pending. The approval state controls authorization and accounting workflow, not whether management can see the cost exposure.

Change-order intake

• Trigger: A new row appears in the Change Order Line response sheet.
• Conditions: Job and cost code are valid; revenue and cost adjustments are numeric; source event is not duplicated.
• Actions: Create Change Order record; link it to the budget line; file supporting documents; create operations approval task; calculate whether finance approval is also required.
• Fields updated: Group ID, description, revenue adjustment, cost adjustment, status, approvers, due date, document link, and automation controls.
• Notification: Operations manager receives the first approval request.
• Exception: Unsupported documents, missing business justification, or an invalid budget line cause More Information status.

A change affecting multiple cost codes uses multiple line records with the same Change Order Group ID. Revenue and cost amounts are allocated across those lines so job-level rollups do not double count the change.

Invoice update intake

• Trigger: A new row appears in the Invoice Update response sheet.
• Conditions: Submitter belongs to finance; job is valid; invoice number is not already used; amount is positive; accounting reference is present.
• Actions: Create or update Invoice; file the invoice document; link the accounting identifier; recalculate invoiced and unbilled values; update the response sheet.
• Fields updated: Invoice Number, Invoice Date, Invoice Amount, Payment Status, External System ID, Document Link, and audit fields.
• Notification: Project manager receives an alert for newly issued, disputed, or voided invoices.
• Exception: Duplicate invoice numbers, mismatched job codes, or unauthorized submitters go to finance review.

Duplicate prevention and recovery

Configure intake scenarios for sequential processing. Before creating a record, search the applicable Airtable table for an exact Source Event Key. If found, update the source sheet with the existing Airtable record ID and stop.

If Make creates an Airtable record but fails before updating the source sheet, the next execution finds the existing source key and repairs the sheet rather than creating a second record.

Users recover a failed record by correcting the source or Airtable exception, setting Recovery Requested to Yes, and allowing the recovery scenario to process it. The original error and retry count remain in the log.

Step 6: Add Approvals, Reminders, and Escalations

Approval thresholds in this representative scenario are policy assumptions, not universal recommendations.

Implement approvals using Airtable decision fields and Make scheduled scenarios:

1. Create the approval task with status Open, owner, due date, and approval stage.
2. Set Approval Requested At before sending the Gmail message. This prevents a retry from sending the same initial request twice.
3. Send an email containing the business record ID, transaction summary, due date, and YOUR_AIRTABLE_REVIEW_INTERFACE_URL.
4. The approver opens the controlled interface, reviews the source data and documents, then selects Approve, Reject, or More Information and enters a decision note.
5. A Make scenario runs every 15 minutes and searches for decisions with no Decision Processed At value.
6. For approval, complete the current task and either create the next sequential task or set the transaction to Approved.
7. For rejection, require a reason, set the transaction to Rejected, complete open approval tasks, and notify the submitter.
8. For More Information, assign a correction task to the submitter and preserve the prior approval stage.
9. When corrected information changes a material amount, cost code, job, or document, return the record to the first required approval.

The reminder scenario runs each weekday morning. It sends a reminder one day before the due date, one overdue reminder per day, and an escalation to the owner’s manager after two calendar days overdue. The initial implementation uses calendar days. A holiday calendar can be added if business-day calculations are required.

An unavailable approver has a Delegated Approver field in the Employees table. Before assigning a task, Make checks Active Status and Delegation End Date. If no available approver exists, the task is assigned to the department manager and marked Delegation Exception.

Step 7: Add Documents and File Management

Create the following Google Drive structure:

Job Costing/
  2026/
    CV-2026-041 - Office Level 3 Fit-out/
      01 Contracts/
      02 Change Orders/
      03 Purchases and Receipts/
      04 Billing/
      05 Closeout/

1. Create the job folder when the job changes from Setup to Active. Store the Drive Folder ID on the Jobs record.
2. Use the stable Drive folder ID for automation. Do not rely only on a folder name because users can rename folders.
3. For each uploaded form file, obtain the Drive file ID, select the destination subfolder, move the file, and save its controlled link in Airtable.
4. Use a naming pattern such as PUR-2026-00125_Vendor_2026-07-14_original-filename.pdf. Replace unsupported filename characters and retain the original filename in Airtable.
5. Process multiple uploads with an iterator so every file is moved and logged separately.
6. Do not make files publicly accessible to satisfy an integration. Access is inherited from the controlled job folder.
7. Use Drive version history or create a new revision record rather than silently replacing approved evidence.
8. Identify duplicates by Drive file ID and source event key, not filename alone.
9. If a required file fails to move, retain the transaction, set Document Status to Failed, create a correction task, and retry the file action without recreating the transaction.
10. Archive closed-job folders according to the company’s accounting, contractual, legal, and insurance retention requirements.

Google Forms and Drive upload limits vary. Test representative photographs, PDFs, multiple files, and files near the organization’s permitted size before deployment.

Step 8: Add Reporting and Operational Views

Create Airtable views and Interfaces for the following operational needs:

• New records received today.
• Time entries awaiting supervisor review.
• Purchases awaiting operations or finance approval.
• Overdue tasks by owner.
• Records with missing documents or information.
• Rejected and returned transactions.
• Costs and commitments by job and cost code.
• Negative budget variance.
• Approved change revenue not yet invoiced.
• Upcoming approval and billing deadlines.
• Recently reconciled transactions.
• Automation failures and requested retries.
• Average processing time by transaction type.
• Volume by status and owner.
• Manual-review queue.

The job margin interface displays original contract value, approved change revenue, current contract value, original budget, current budget, actual cost, open commitments, forecast cost, projected margin, margin percentage, invoiced amount, and unbilled contract value.

Project managers receive a weekly Gmail summary of active jobs. A job is highlighted when forecast cost exceeds current budget, projected margin falls below the approved baseline, a change order remains unapproved beyond its target date, or an automation failure is unresolved.

Airtable formulas and rollups refresh when source records change. Make generates scheduled digests and escalations. The operations manager owns dashboard definitions, while finance owns cost and billing definitions.

Step 9: Add Security and Governance Controls

• Use least-privilege Airtable roles and controlled Interfaces for reviewers.
• Restrict employee rates, accounting references, forecast overrides, and sensitive notes.
• Disable public base, form-result, and Drive-folder sharing.
• Store Make credentials in managed connections, not Airtable fields, form descriptions, or scenario notes.
• Use an automation identity protected by multifactor authentication.
• Review Google, Airtable, and Make activity logs after material changes or suspected access issues.
• Remove former employees from Workspace, Airtable, Make, and Drive groups through the offboarding process.
• Export or back up critical Airtable data on an agreed schedule and test restoration.
• Retain approval evidence, corrections, accounting references, and documents according to applicable requirements.
• Do not store payment-card details, personal banking credentials, government identifiers, or unrelated employee information.
• Require human approval for purchases, changes, accounting entries, payments, and customer invoices.
• Limit any optional AI input to approved operational fields and exclude confidential information that is not needed for classification.

Regulatory and contractual obligations differ by jurisdiction and project. Finance and legal advisers should determine the applicable record-retention, tax, employment, privacy, and construction-document requirements.

Step 10: Deploy and Test

1. Build the base, forms, sheets, Drive folders, and Make scenarios in the test environment.
2. Load fictional jobs, cost codes, employees, budgets, purchases, changes, and invoices.
3. Run normal, invalid, duplicate, approval, rejection, document, notification, and recovery tests.
4. Ask field employees, one supervisor, one project manager, and one finance user to complete user acceptance testing.
5. Reconcile test job totals manually against the underlying records and spreadsheet calculations.
6. Pilot the workflow on two active jobs for two weekly reporting cycles.
7. During the pilot, continue the prior control report as a temporary comparison, but identify which system is authoritative for each field.
8. Fix mappings, permissions, form wording, and dashboard definitions before broader activation.
9. Activate intake scenarios first, followed by approvals, reminders, weekly reporting, and recovery scenarios.
10. Publish a short operating guide covering submission, approval, correction, escalation, and support.
11. Monitor every Make execution during the first several business days and review Automation Failures daily during the pilot.
12. Keep production forms inactive or unpublished until the production Airtable and Drive connections have been verified.
13. Define rollback as pausing scenarios, preserving all received form rows, and returning temporarily to the controlled spreadsheet process without deleting records.

Code and Configuration

The core implementation does not require custom JavaScript, Python, or Google Apps Script. Google Forms, Google Sheets, Airtable, Make, Google Drive, and Gmail provide the required triggers and actions natively.

The following Airtable formulas are placed in the named formula fields. Field names must match exactly.

Airtable Record ID
RECORD_ID()

Time Entry ID
"TE-" & DATETIME_FORMAT(CREATED_TIME(), "YYYY") & "-" & RIGHT("00000" & {Sequence}, 5)

Purchase ID
"PUR-" & DATETIME_FORMAT(CREATED_TIME(), "YYYY") & "-" & RIGHT("00000" & {Sequence}, 5)

Change Order ID
"CO-" & DATETIME_FORMAT(CREATED_TIME(), "YYYY") & "-" & RIGHT("00000" & {Sequence}, 5)

Task ID
"TASK-" & DATETIME_FORMAT(CREATED_TIME(), "YYYY") & "-" & RIGHT("00000" & {Sequence}, 5)

Budget Line Key
{Job Code Lookup} & "|" & {Cost Code Lookup}

Labour Cost
IF(
  AND({Hours}, {Loaded Cost Rate}),
  ROUND({Hours} * {Loaded Cost Rate}, 2)
)

Purchase Total Cost
ROUND({Amount Before Tax} + {Tax Amount}, 2)

Purchase Incurred Cost
IF(
  OR(
    {Status} = "Received",
    {Status} = "Invoiced",
    {Status} = "Paid"
  ),
  {Total Cost},
  0
)

Purchase Open Commitment
IF(
  AND(
    {Approval Status} = "Approved",
    OR(
      {Status} = "Approved",
      {Status} = "Ordered"
    )
  ),
  {Total Cost},
  0
)

Approved Change Revenue
IF({Status} = "Approved", {Revenue Adjustment}, 0)

Approved Change Cost
IF({Status} = "Approved", {Cost Adjustment}, 0)

Current Budget
ROUND({Original Budget} + {Approved Change Cost}, 2)

Actual Cost
ROUND({Labour Actual} + {Purchase Actual}, 2)

Forecast at Completion
MAX(
  {Current Budget},
  {Actual Cost} + {Open Commitments}
)

Budget Variance
ROUND({Current Budget} - {Forecast at Completion}, 2)

Current Contract Value
ROUND({Original Contract Value} + {Approved Change Revenue}, 2)

Projected Margin
ROUND({Current Contract Value} - {Forecast Cost}, 2)

Projected Margin Percent
IF(
  {Current Contract Value} > 0,
  {Projected Margin} / {Current Contract Value}
)

Unbilled Contract Value
ROUND({Current Contract Value} - {Invoiced Amount}, 2)

Days Overdue
IF(
  AND(
    {Status} != "Completed",
    {Due At} < NOW()
  ),
  DATETIME_DIFF(NOW(), {Due At}, "days"),
  0
)

Configure Budget Line and Job rollup fields with SUM(values). Purchase Actual rolls up Purchase Incurred Cost, while Open Commitments rolls up Purchase Open Commitment. This prevents the same purchase from being included as both incurred and committed.

The Make intake scenario configuration is:

Scenario: Purchase Intake

1. Schedule or Google Sheets trigger
   Spreadsheet: YOUR_PURCHASE_RESPONSE_SPREADSHEET_ID
   Sheet: Purchase Responses
   Process rows where Processing Status is blank

2. Set controlled Source Event Key
   PURCHASE|spreadsheet identity|sheet name|row number

3. Search Airtable Purchases
   Exact field: Source Event Key

4. Duplicate route
   If found:
   Update source row with existing Airtable Record ID
   Set Processing Status to Already Processed
   Stop this route

5. Search Airtable Jobs
   Exact Job Code
   Status must be Active or approved Closeout state

6. Search Airtable Employees
   Exact collected email
   Active must be true

7. Search Airtable Budget Lines
   Exact Budget Line Key: Job Code|Cost Code

8. Validation router
   Valid route: continue
   Invalid route: write error, create log, notify correction owner

9. Create Airtable Purchase
   Map all validated fields
   Set Automation Status to Processing
   Store original submitted values

10. Process each uploaded Drive file
    Resolve Job Drive Folder ID
    Move file to Purchases and Receipts
    Store Drive file ID and controlled URL

11. Determine approval route
    Apply amount, cost type, budget, and policy conditions

12. Create linked Task when approval or correction is required

13. Send Gmail confirmation and action email

14. Update Airtable Purchase
    Automation Status: Completed
    Last Automation Run: current timestamp

15. Update Google Sheets row
    Processing Status: Completed
    Airtable Record ID: returned Airtable record ID
    Processed At: current timestamp

Error handler:
    Increment Retry Count
    Create or update Automation Log
    Set source row to Failed or Needs Review
    Retry temporary service errors
    Do not retry validation errors until corrected

Use this Gmail approval template in Make. Replace the mapped values with connector tokens:

Subject: Approval required: PURCHASE_ID for JOB_CODE

A purchase requires your review.

Record: PURCHASE_ID
Job: JOB_CODE - JOB_NAME
Vendor: VENDOR
Description: DESCRIPTION
Total: TOTAL_COST
Cost code: COST_CODE
Submitted by: SUBMITTER
Due: DUE_DATE

Review the record and supporting document:
YOUR_AIRTABLE_REVIEW_INTERFACE_URL

Select Approve, Reject, or More Information in Airtable.
Do not approve by replying to this email.

After configuration, test each scenario using a new sample row. Confirm that the Airtable record, Drive document, task, email, source-sheet status, formulas, and Automation Log all contain the expected values. Use Make execution history to inspect module inputs and outputs. Redact credentials and sensitive data before sharing logs.

Activate scenarios only after changing all test IDs to production IDs and repeating a production smoke test with a controlled sample. Likely configuration errors include a renamed sheet tab, expired Google connection, inaccessible Drive folder, incorrect Airtable field name, invalid select option, or missing linked-record identifier.

Failure Handling and Operational Reliability

Temporary 429 and server errors can be retried three times using increasing delays such as 30 seconds, 120 seconds, and 300 seconds. Validation errors should not be repeatedly retried because the source data must change first.

The Airtable Automation Log acts as the durable manual-review queue. Make’s execution history supports technical diagnosis, but the business should not depend on an automation-platform log as the only evidence that a cost was received.

A daily reconciliation scenario searches response-sheet rows that remain blank, Processing, or Failed beyond the expected interval. It compares their source event keys with Airtable and either repairs the sheet status or creates an exception.

A Complete Example

Cedar Vale Contracting has an active job named Office Level 3 Fit-out with job code CV-2026-041.

The original contract value is $186,000. The original cost budget is $164,000, consisting of $52,000 labour, $61,000 materials, $38,000 subcontractors, $8,000 equipment, and $5,000 other direct cost.

1. A site supervisor submits the Purchase or Commitment form from a phone. The input includes job code CV-2026-041, cost code MAT-200, an approved electrical supplier, a description of lighting components, $3,240 before tax, $162 tax, status Received, and a receipt photograph.

2. Google Forms writes the response to row 214 of the linked sheet. Make creates the source event key PURCHASE|YOUR_SPREADSHEET_ID|Purchase Responses|214.

3. Make searches Airtable for that key and finds no prior transaction. It then confirms that the job is Active, the submitter is an active employee, and the MAT-200 budget line exists for the job.

4. Make creates the purchase. Airtable assigns the readable ID PUR-2026-00125 and calculates a total cost of $3,402.

5. The uploaded receipt is moved to the job’s Purchases and Receipts folder. Its Drive file ID and controlled link are stored on the purchase record.

6. Because the amount exceeds $2,500, Make assigns sequential approval to the project manager and finance manager. The project manager receives the first Gmail request.

7. The project manager reviews the receipt, budget line, and description in Airtable and selects Approve. The decision processor records the approver and timestamp, completes the first task, and creates the finance task.

8. Finance confirms that the cost code and tax treatment are appropriate, then approves the transaction. The Purchase status remains Received, Approval Status becomes Approved, and the task is completed.

9. The material Budget Line previously contained $47,800 actual cost and $9,500 open commitments. The new purchase increases actual cost to $51,202. The forecast remains $61,000 because actual plus commitments, $60,702, does not exceed the current material budget.

10. Finance posts the supplier transaction in the accounting system and records external reference AP-7841 in Airtable. The transaction moves to Reconciled.

11. The source sheet is updated with the Airtable internal record ID, Completed status, and processing timestamp. The next weekly job summary includes the revised material actual cost.

If the receipt move had failed, the purchase would still have been created so the cost was visible. It would have entered Missing Document status, a task would have been assigned, and only the document step would have been retried.

Implementation Cost

All amounts below are representative planning assumptions for this fictional scenario. They are not vendor quotations or verified client costs. Current pricing and required subscription features should be confirmed directly with each provider.

An internal implementation could avoid the optional professional fee, but it would require additional internal configuration, testing, documentation, and support time. Free or already-owned software still requires administration and maintenance.

Estimated Time and Cost Savings

The estimate uses these representative assumptions:

• 869 monthly records: 620 time entries, 180 purchases, 24 change-order lines, and 45 invoice updates.
• Current average handling time: 7 minutes per record, including intake, follow-up, copying, and reconciliation.
• New average handling time: 2.25 minutes per record.
• Exception rate: 8 percent.
• Exception handling time: 4 additional minutes per exception.
• Monthly maintenance: 4 hours.
• Loaded labour cost: $39 per hour.
• Recurring software cost: $185 per month.
• One-time economic implementation cost: $14,283.

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

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

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

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

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

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

Recovered time does not automatically reduce payroll. It may provide additional project-management capacity, quicker turnaround, reduced overtime, less administrative work, and the ability to manage more jobs without increasing reconciliation effort at the same rate.

Non-financial benefits include clearer ownership, fewer follow-up emails, earlier cost visibility, more consistent coding, improved document retrieval, stronger approval evidence, more dependable margin reporting, and less dependency on one finance employee.

Readers should replace monthly volume, handling times, exception rate, labour cost, software allowance, implementation hours, and maintenance requirements with their own measured figures.

Adding AI to the Automation

AI is optional and should be introduced only after job codes, cost codes, forms, approval rules, formulas, and exception handling operate reliably.

Potential uses include:

• Suggesting a cost code from an ambiguous purchase description.
• Comparing a selected code with the description and purchase type.
• Summarizing long change-order descriptions for reviewers.
• Extracting receipt values for comparison with submitted fields.
• Flagging semantically similar purchase descriptions for duplicate review.
• Identifying missing information in unstructured notes.

Required fields, arithmetic, exact duplicate keys, approval thresholds, job status, user permissions, and margin calculations should remain rule-based. AI is not needed for those tasks.

The Recommended AI Enhancement

The recommended enhancement is an AI-assisted purchase cost-code review. It checks the submitted description against the permitted cost codes and identifies a likely mismatch. It does not approve a purchase or automatically change its accounting classification.

• Trigger: A valid Purchase record reaches Completed automation status and has not received an AI review.
• AI input: Purchase ID, cost type, vendor category, description, selected cost code, and the active allowed cost-code list.
• Excluded input: Employee email, banking data, payment-card data, personal identifiers, confidential contract text, and receipt images unless separately approved.
• Expected output: Strict JSON containing a suggested code, category, mismatch indicator, missing-information list, confidence band, and short rationale.
• Validation: Make confirms that every required property exists and that the suggested code is active in Airtable.
• Record update: Store the suggestion and AI Review Status. Never overwrite the submitted code automatically.
• Human review: Route mismatches, low confidence, and invalid output to the project manager or finance reviewer.
• Fallback: If AI is unavailable, retain the submitted code and use the ordinary human review process.

Use the following reusable system instruction:

You review construction purchase descriptions for cost-code consistency.

Use only the allowed cost codes supplied in the user message.
Do not approve, reject, post, or authorize any transaction.
Do not invent missing facts.
If the description is ambiguous, return low confidence and identify the missing information.
A confidence label is only a routing aid and is not a calibrated probability.
Return only JSON that follows the required schema.

Use this reusable user prompt:

Review this construction purchase.

Purchase ID: {{PURCHASE_ID}}
Cost type: {{COST_TYPE}}
Vendor category: {{VENDOR_CATEGORY}}
Description: {{DESCRIPTION}}
Selected cost code: {{SELECTED_COST_CODE}}

Allowed cost codes:
{{ALLOWED_COST_CODE_LIST}}

Determine whether the selected code is consistent with the description.
Suggest one allowed code.
Flag missing information that prevents reliable classification.
Do not make an approval or accounting decision.

A Make HTTP module can call the OpenAI Responses API using an approved structured-output-capable model. Store YOUR_API_KEY in a protected Make connection or secret field, not in Airtable.

Method: POST
URL: https://api.openai.com/v1/responses

Headers:
Authorization: Bearer YOUR_API_KEY
Content-Type: application/json

{
  "model": "YOUR_APPROVED_MODEL",
  "input": [
    {
      "role": "system",
      "content": [
        {
          "type": "input_text",
          "text": "You review construction purchase descriptions for cost-code consistency. Use only the allowed cost codes supplied in the user message. Do not approve, reject, post, or authorize any transaction. Do not invent missing facts. If the description is ambiguous, return low confidence and identify the missing information. A confidence label is only a routing aid and is not a calibrated probability. Return only JSON that follows the required schema."
        }
      ]
    },
    {
      "role": "user",
      "content": [
        {
          "type": "input_text",
          "text": "Purchase ID: {{PURCHASE_ID}}\nCost type: {{COST_TYPE}}\nVendor category: {{VENDOR_CATEGORY}}\nDescription: {{DESCRIPTION}}\nSelected cost code: {{SELECTED_COST_CODE}}\nAllowed cost codes:\n{{ALLOWED_COST_CODE_LIST}}\nDetermine whether the selected code is consistent with the description. Suggest one allowed code. Flag missing information. Do not make an approval or accounting decision."
        }
      ]
    }
  ],
  "text": {
    "format": {
      "type": "json_schema",
      "name": "purchase_cost_code_review",
      "strict": true,
      "schema": {
        "type": "object",
        "additionalProperties": false,
        "properties": {
          "purchase_id": {
            "type": "string"
          },
          "suggested_cost_code": {
            "type": "string"
          },
          "suggested_category": {
            "type": "string",
            "enum": [
              "Labour",
              "Material",
              "Subcontractor",
              "Equipment",
              "Other"
            ]
          },
          "selected_code_mismatch": {
            "type": "boolean"
          },
          "missing_information": {
            "type": "array",
            "items": {
              "type": "string",
              "enum": [
                "vendor",
                "amount",
                "job_code",
                "description",
                "receipt",
                "scope_detail",
                "none"
              ]
            }
          },
          "confidence": {
            "type": "string",
            "enum": [
              "high",
              "medium",
              "low"
            ]
          },
          "rationale": {
            "type": "string",
            "maxLength": 240
          }
        },
        "required": [
          "purchase_id",
          "suggested_cost_code",
          "suggested_category",
          "selected_code_mismatch",
          "missing_information",
          "confidence",
          "rationale"
        ]
      }
    }
  }
}

A valid parsed result resembles:

{
  "purchase_id": "PUR-2026-00125",
  "suggested_cost_code": "MAT-200",
  "suggested_category": "Material",
  "selected_code_mismatch": false,
  "missing_information": [
    "none"
  ],
  "confidence": "high",
  "rationale": "The description identifies purchased lighting components, which are direct project materials."
}

On an HTTP 200 response, Make locates the returned output-text content, parses the JSON, validates the purchase ID, and checks the suggested code against the active Cost Codes table. Response structures and connector mappings can change, so inspect a successful test response before fixing the output mapping.

Retry rate-limit and server failures using controlled delays. Do not retry invalid requests without correction. Log model name, prompt version, request identifier, usage values, result status, and review outcome. Avoid storing unnecessary full prompts if the underlying purchase fields already provide the audit record.

Benefits of the AI Enhancement

The core automation already provides timely capture, exact validation, calculations, routing, reminders, and reporting. The AI enhancement adds value only where descriptions are unstructured or ambiguous.

• Less reading time for routine purchase descriptions.
• More consistent identification of possible cost-code mismatches.
• Quicker routing of ambiguous descriptions to human review.
• Structured reporting on common classification problems.
• Earlier identification of missing scope detail.

The model’s confidence label should not be treated as an objective probability. Exact validation and human review remain necessary.

What Remains Rule-Based or Human-Controlled

• Job and cost-code validity: Airtable lookup rules determine whether a code exists and is active.
• Cost calculations: Airtable formulas calculate labour cost, actual cost, commitments, forecast, and margin.
• Duplicate prevention: Source event keys and exact transaction checks remain deterministic.
• Purchase approval: Authorized managers approve or reject purchases.
• Change-order approval: Operations and finance approve scope, revenue, and cost effects.
• Accounting entry: Finance determines the official ledger treatment.
• Payment release: Authorized finance personnel release payments.
• Customer invoicing: Finance issues and adjusts official invoices.
• Forecast override: Project managers provide and approve judgment-based cost-to-complete values.
• Job closure: Operations and finance confirm completion, final billing, open commitments, and document retention.

These decisions carry financial, contractual, legal, or operational consequences. AI may summarize or suggest, but it does not provide final authorization.

Estimating the Additional Value of AI

The following estimates are planning assumptions that require validation through a controlled pilot:

The weighted AI-supported review time is approximately 0.98 minute per purchase:

Weighted review time: 80% × 0.6 minutes + 20% × 2.5 minutes = 0.98 minute

Gross additional time recovered: 180 × (1.8 – 0.98) ÷ 60 = approximately 2.46 hours

Net additional capacity after monitoring: 2.46 – 0.5 = approximately 1.96 hours

Estimated labour value: 1.96 × $39 = approximately $76

Net value after AI usage allowance: $76 – $8 = approximately $68 per month

This relatively modest estimate helps prevent AI from being treated as the primary source of value. The larger operational benefit comes from structured intake, deterministic validation, connected records, approvals, and reporting.

Testing Checklist

Use fictional sample data before processing real project, employee, supplier, or customer information.

Ongoing Maintenance

The operations systems manager is the primary owner. A finance systems user is the backup owner. Business definitions remain jointly owned by operations and finance.

Scenario changes should be documented with date, owner, reason, affected fields, test evidence, and rollback instructions. Production changes should first be tested against the duplicate base and forms.

When to Move to Dedicated Software

The implementation does not need to be replaced simply because the company grows. It should be reviewed when its operating risk or maintenance burden becomes disproportionate.

Potential upgrade indicators include:

• Transaction volume approaches the practical limits of the selected Airtable and Make configuration.
• Many crews require offline mobile entry in unreliable connectivity areas.
• Multiple divisions need separate security, accounting entities, or cost-code structures.
• Record-level permissions become more complex than controlled interfaces can support.
• Formal audit requirements demand stronger database constraints or immutable event records.
• Customers, suppliers, or subcontractors require secure external portals.
• Scheduling, drawings, requests for information, procurement, safety, and field reporting must share one project platform.
• Accounting synchronization must become real-time and bidirectional.
• Exception rates remain high because the data model no longer represents project complexity.
• Automation maintenance consumes excessive management time.
• Advanced forecasting requires resource schedules, earned value, or detailed cost-to-complete models.
• Vendor support, service-level agreements, data residency, or regulatory controls become mandatory.
• Mobile photo capture, location controls, signatures, or offline operation become core requirements.

At that point, the company can evaluate construction project-management platforms, construction accounting systems, enterprise resource planning software, or a custom application. The controlled job codes, cost codes, status definitions, approval rules, and historical records created here provide useful migration inputs.

Implementation Checklist

• Confirm job-costing, margin, approval, billing, reporting, and audit requirements.
• Select the intake, system-of-record, automation, storage, notification, and reporting tools.
• Create durable automation accounts and named system owners.
• Configure least-privilege permissions and test users.
• Define controlled job codes, cost codes, transaction IDs, and ownership rules.
• Create Jobs, Budget Lines, Employees, Cost Codes, transaction, Task, and Automation Log tables.
• Import approved jobs, budgets, employees, and rates.
• Build and validate the four Google Forms.
• Link each form to a response sheet with processing-control columns.
• Create Drive folders and document naming conventions.
• Connect Google Sheets, Airtable, Drive, and Gmail through Make.
• Document every source-to-destination field mapping.
• Configure duplicate checks and source event keys.
• Build time, purchase, change-order, invoice, and recovery scenarios.
• Configure approval thresholds and sequential approval routes.
• Add reminders, escalations, delegation, rejection, and reassignment.
• Create document-failure and missing-information paths.
• Build operational, margin, variance, billing, and failure views.
• Apply security, retention, backup, logging, and offboarding controls.
• Install Airtable formulas and verify rollups manually.
• Test valid, invalid, duplicate, failure, approval, document, and recovery cases.
• Run user acceptance testing with field, operations, and finance users.
• Pilot with selected jobs before phased deployment.
• Document representative software, labour, maintenance, and implementation costs.
• Replace savings assumptions with measured internal figures.
• Add AI only after the core automation is reliable and governed.
• Name the primary maintenance owner and backup owner.
• Review upgrade criteria as volume, risk, security, and workflow complexity increase.