Multi-Location Inventory Tracking with Airtable and Make

The Business Situation

Larkspur Trade Supply is a fictional 24-person wholesaler operating three warehouse locations. The company maintains approximately 520 active stock-keeping units, or SKUs, across locations identified as WH-A, WH-B, and WH-C.

The inventory process involves an operations manager, three warehouse supervisors, six receiving and shipping employees, two procurement employees, and a finance employee who reviews material adjustments. The operations manager owns the inventory process and is responsible for resolving exceptions.

The business processes approximately 650 receipts, issues, transfers, and adjustments each month. It also records about 120 cycle-count lines per month.

Stock was previously tracked in four Google Sheets files: one sheet for each warehouse and a fourth consolidation sheet. Employees used Gmail to request transfers, report count differences, and ask whether stock was available at another location. Supporting delivery notes and count photographs were stored inconsistently in Google Drive folders.

The spreadsheets could show an approximate quantity, but they could not reliably explain how that quantity had changed. Transfers were particularly difficult because one employee reduced stock in the source spreadsheet while another employee had to increase stock in the destination spreadsheet.

The business needed a controlled inventory register that could show location-level quantities, receipts, issues, transfers, adjustments, count history, reorder levels, approvals, and automation failures without purchasing a full warehouse management system immediately.

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 Existing Process

The original process followed these steps:

1. A receiving or shipping employee completed a physical stock movement.
2. The employee opened the spreadsheet for the relevant warehouse.
3. The employee searched for the SKU and manually changed its quantity.
4. For transfers, the source employee reduced one spreadsheet and emailed the destination warehouse.
5. The destination employee increased the quantity in a different spreadsheet after the shipment arrived.
6. Adjustments and count differences were entered directly into the current quantity cell, often without preserving the previous value.
7. A supervisor reviewed spreadsheet changes periodically rather than approving each material movement.
8. The consolidation sheet used formulas to combine location totals. Broken references and inconsistent SKU formats required manual correction.
9. Procurement reviewed the consolidation sheet and emailed warehouse supervisors when stock appeared to be below its reorder level.

Directly editing a balance also removed important context. A quantity change from 40 to 36 did not indicate whether four units had been shipped, damaged, transferred, or removed after a count. That weakened reporting and made later reconciliation difficult.

There was no consistent identifier connecting an email, spreadsheet update, approval, and supporting document. Consequently, an investigation often required searching multiple inboxes and Drive folders.

What the New System Needed to Do

Implementation Approaches Considered

Consolidated Google Sheets

A single protected workbook could improve the existing process. Google Forms could append movement rows, and formulas could calculate balances. However, maintaining item-location relationships, sequential approvals, immutable ledger entries, and transfer posting would require extensive scripting and careful concurrency controls. The business would remain dependent on spreadsheet structure.

Airtable with Make

Airtable supports linked records, filtered views, formulas, rollups, forms, and interfaces. Make can validate submissions, route approvals, create paired ledger entries, copy documents, send Gmail messages, and maintain an error path. This approach met the required volume and offered clearer operational ownership than a consolidated spreadsheet.

Barcode application

A barcode application would reduce SKU typing and improve warehouse ergonomics. It was not selected initially because the company first needed standardized item IDs, location codes, movement rules, and ownership. A scanning interface can later be added without replacing the Airtable ledger.

Dedicated inventory software

A dedicated platform would be preferable if Larkspur required serial numbers, lot tracking, reservations, landed cost, purchasing, order allocation, carrier integration, or formal warehouse task management. Those requirements were outside the initial scope.

Custom application

A custom database application could provide stronger transactional behavior than Airtable, particularly for simultaneous stock posting. It was not justified for the representative transaction volume or implementation budget.

The Selected Solution

The selected design retained Google Workspace for employee identity, forms, email, and document storage. Airtable became the inventory system of record. Make became the automation and integration layer.

The original stock spreadsheets were retained as read-only migration evidence. They were no longer used to post daily movements.

Manual balance editing was removed. Employees still made the operational decisions that mattered: confirming physical receipt, approving a transfer, accepting an adjustment, resolving a count variance, and deciding whether to reorder stock.

System Architecture and Data Flow

1. Submission: A warehouse employee submits a Google Form. The response is written to the linked response sheet. Make watches for new rows on a scheduled interval and receives the timestamp, employee email, movement type, SKU, quantity, locations, reference, reason, and attachment link.

2. Idempotency: Make creates a source submission key from the form type and response row number. It checks a Make data store and Airtable before creating anything. A previously processed key is logged as a duplicate event and is not posted again.

3. Validation: Make normalizes the SKU and location codes, converts quantity to a number, and searches Airtable. Unknown items, inactive items, invalid locations, incomplete transfers, and malformed quantities are routed to manual review.

4. Request creation: A valid submission creates an Airtable Movement Request. Airtable returns its internal record identifier. Make stores that identifier, assigns the location owner, sets a due date, and writes an Audit Event.

5. Document handling: If a file was uploaded, Make copies it into the controlled Drive folder for the request. The returned Drive file ID and link are written to Airtable. A failed copy does not post the stock movement.

6. Approval routing: Make creates one or more Approval Task records according to movement type and threshold. Gmail sends the assigned approver a request summary and a prefilled link to the approval form.

7. Decision processing: The approval form writes to its response sheet. Make verifies that the collected email matches the assigned approver or an authorized delegate. It then updates the task, records the decision, and either starts the next approval stage or moves the request to Ready to Post.

8. Final validation: Before posting, Make checks the current source balance, destination status, approval completion, document requirements, and expected ledger entry count. Issues and transfers cannot create prohibited negative stock.

9. Ledger posting: Make creates deterministic draft ledger entries. A transfer receives a negative source entry and positive destination entry. After both entries are validated, Make changes them to Committed. Airtable rollups update location balances.

10. Completion: Make updates the request to Posted, records posting timestamps and entry IDs, creates an Audit Event, and sends a Gmail confirmation.

11. Monitoring: Scheduled Make scenarios inspect overdue approvals, low-stock balances, incomplete postings, and failed automation records. Status views in Airtable update from the underlying records.

12. Failure path: A failed module writes the error, retry count, and next retry time to Airtable. The request enters Failed or Manual Review. The operations manager receives an alert and can retry the record after correcting its cause.

• Intake: Google Forms with linked response sheets
• System of record: Airtable
• Automation layer: Make
• Document storage: Google Drive
• Notifications: Gmail
• Reporting: Airtable Interfaces and filtered views
• AI layer: Optional document extraction with mandatory human confirmation

Data Structure

The base uses an immutable movement ledger. Users do not type an on-hand quantity into a balance record. Airtable calculates it by rolling up committed ledger entries linked to the relevant item-location balance.

Items and locations

Stock balances and ledger entries

Movement requests

Related workflow records

• Count Records: Store Count ID, session, item, location, system quantity snapshot, counted quantity, variance, counter, counted time, evidence link, and resulting request.
• Approval Tasks: Store task ID, request, stage, assigned approver, backup approver, due date, decision, decision timestamp, comments, and approval response key.
• Audit Events: Store request, event type, previous status, new status, actor, event timestamp, source scenario, and details.
• Automation Runs: Store scenario, submission key, run status, attempt number, start time, completion time, error category, and recovery notes.

One Item has many Stock Balances. One Location has many Stock Balances. A Movement Request has one or two Ledger Entries. A transfer always expects two entries. A request can have several Approval Tasks and Audit Events.

Workflow Statuses and Ownership

A rejected request cannot be posted. A request returned for information can move back to Awaiting Approval only after an authorized employee corrects the record and Make revalidates it.

Committed ledger entries are never edited to conceal a mistake. A correction creates a reversal request and linked reversal entries, preserving the original history.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create an Airtable workspace and a separate base named Inventory Register - Production. Create a second base named Inventory Register - Test. Confirm that the selected subscription supports the required record volume, interfaces, permissions, and history expectations.

2. Create a dedicated Google Workspace automation identity such as inventory.automation@YOUR_DOMAIN. This account owns the Google Forms and linked response spreadsheets and has access to the controlled Drive evidence folder.

3. Create a Make organization or team and restrict scenario editing to the system owner and backup owner. Connect Airtable and Google Workspace using OAuth where available. If an Airtable personal access token is required, use a placeholder such as YOUR_AIRTABLE_TOKEN and grant only the minimum base and record permissions needed by the connector.

4. Authorize Make to read the form response spreadsheets, read and copy uploaded files, write Airtable records, and send Gmail messages from the automation mailbox. Do not store credentials in form descriptions, spreadsheets, or Airtable text fields.

5. Create the controlled Drive root folder Inventory Evidence. Give warehouse employees access only where operationally required. Give the automation identity permission to create folders and copy files.

6. Create an operations notification group or shared mailbox for failure alerts. The automation mailbox sends notifications, while the operations group receives replies and escalations.

7. Assign roles:

   • Operations manager: system owner, override authority, and escalation owner.
   • Backup operations manager: backup system owner.
   • Warehouse supervisors: movement approvers for their locations.
   • Warehouse employees: form submitters with no direct ledger-edit permission.
   • Procurement: reorder view access and item-master maintenance where authorized.
   • Finance reviewer: read access to adjustment value and high-value approval tasks.

8. Create test identities for a requester, source approver, destination approver, and operations manager. Use test SKUs and test location records that cannot be confused with production inventory.

Editor access to the underlying Airtable base should be limited. Most operational users can work through purpose-specific interfaces. Hiding a field in an interface is not a substitute for access control, so sensitive cost data should not be exposed to users with unrestricted base access.

Step 2: Build the Intake

Create three Google Forms owned by the automation identity:

• Inventory Movement Request
• Cycle Count Submission
• Inventory Approval Decision

Use form sections to show the correct location fields for each movement type. Google Forms supports section routing for suitable choice questions. The interface wording can change, so the implementation requirement is that a transfer collects both source and destination while a receipt collects only the destination.

Use numeric response validation for quantity. Do not use a 520-value SKU dropdown unless a controlled maintenance process will keep it synchronized. An exact SKU text field is easier to maintain, while Make performs authoritative validation against Airtable.

The confirmation message should state that the submission is a request and does not change inventory until approved and posted. It should direct urgent users to the operations contact rather than encouraging duplicate submissions.

Restrict the form to authenticated employees where possible. File upload questions generally require sign-in. Add a short privacy notice explaining that the employee identity, submission details, and uploaded evidence are retained for inventory control.

The Cycle Count Submission form requires Count Session ID, SKU, location, counted quantity, count time, counter email, notes, and optional evidence. Counted quantity can be zero but cannot be negative.

The Approval Decision form requires Request ID, Decision, and Comments. Decision values are Approve, Reject, and More Information Required. It collects the authenticated approver email. Make verifies this identity against the open Approval Task, so forwarding an approval link does not transfer approval authority.

Link each form to a separate Google Sheets response spreadsheet. Treat these sheets as append-only transport logs. Do not sort, delete, or reuse response rows because the source row number forms part of the idempotency key.

Step 3: Create the System of Record

Create the following Airtable tables:

1. Items
2. Locations
3. Stock Balances
4. Movement Requests
5. Ledger Entries
6. Count Records
7. Approval Tasks
8. Audit Events
9. Automation Runs

Configure linked records and reciprocal relationships before importing balances. Use consistent names such as Request ID, Movement Type, and Entry Status. Avoid changing field names after Make mappings have been activated.

Import and clean the 520-item master. Normalize SKU case, remove leading and trailing spaces, identify inactive items, and resolve duplicate descriptions. Import the three location records and owner assignments.

Create one Stock Balance record for each active item-location combination. For 520 items and three locations, this produces 1,560 balance records. Populate the plain-text helper fields SKU Text and Location Code Text so the Balance Key formula remains stable.

Do not enter the opening quantity directly into On Hand. Create an approved opening-balance Movement Request and one committed Ledger Entry for every nonzero item-location opening balance. Label the source reference as the migration snapshot date. This preserves a traceable starting point.

Use deterministic identifiers:

• Movement request: MR-M-YYYYMMDD-ROWNUMBER
• Count-derived request: MR-C-YYYYMMDD-ROWNUMBER
• Approval task: AT-REQUESTID-01
• Ledger entry: LE-REQUESTID-01 and, for transfers, LE-REQUESTID-02
• Audit event: generated from request ID, event type, and timestamp

Create filtered views for Received, Validation Failed, Awaiting Approval, Awaiting Destination Confirmation, Awaiting Information, Ready to Post, Posting, Posted Today, Rejected, Failed, Below Reorder Level, and Count Variances.

Step 4: Connect the Tools

Create separate Make connections for the test and production environments. Scenario names should begin with TEST- or PROD- to reduce activation mistakes.

The connections operate as follows:

1. Google Forms to Google Sheets: Google Forms writes each response to its linked response sheet.
2. Google Sheets to Make: A scheduled Watch New Rows trigger retrieves appended submissions through the connected Google account.
3. Make to Airtable: Make searches master records, creates requests and tasks, and stores the returned Airtable record IDs.
4. Make to Google Drive: Make reads the form upload link, copies the file to the managed folder, and returns the new file ID and link.
5. Make to Gmail: Make sends task and status messages from the automation mailbox.
6. Gmail to Approval Form: The approver follows a prefilled form link. The approval response returns through its linked response sheet and a separate Make scenario.
7. Airtable to Make: Scheduled scenarios search for overdue tasks, retryable failures, low stock, and incomplete postings.

Every destination identifier is written back to Airtable. This includes the internal Airtable request record ID, copied Drive file ID, approval response key, and ledger entry record IDs. If a Gmail module returns a provider message identifier, it may also be stored, but successful sent time remains the required evidence.

Step 5: Build the Core Automation

Automation A: Receive and validate a movement

• Trigger: New row in the movement response sheet.
• Conditions: Source submission key does not exist in the Make data store or Movement Requests table.
• Actions: Normalize fields, validate item and locations, create the request, copy evidence, assign an owner, create an audit event, and route approval.
• Fields updated: Request ID, Status, Owner, Due Date, Automation Status, Last Automation Run, Document Link.
• Notification: Confirmation to the requester and approval task to the owner.
• Exception: Create a Validation Failed request when enough data exists to identify the submission; otherwise create an Automation Run failure and notify operations.

Configure the actions in this order:

1. Read the next response row.
2. Construct the source submission key.
3. Check the Make data store for the key.
4. Search Airtable for the same key.
5. Stop successfully if either check indicates prior completion.
6. Normalize email, movement type, SKU, quantity, locations, and reference.
7. Search Items for one exact active SKU.
8. Search Locations for required active location codes.
9. Apply movement-specific validation.
10. Create the Movement Request with Status Received and Automation Status Running.
11. Store the returned Airtable record ID.
12. Copy supporting evidence if supplied.
13. Assign the owner from the affected location.
14. Create the first Approval Task.
15. Update the request to Awaiting Approval.
16. Create an Audit Event.
17. Send confirmation and approval Gmail messages.
18. Write the source key to the Make data store only after the Airtable request exists.

Potential business duplicates are handled separately from technical duplicate events. Make searches for requests with the same movement type, reference number, SKU, quantity, and locations within a configurable time window. A match is marked Potential Duplicate and requires review, because repeated lines can be legitimate.

Automation B: Process an approval response

• Trigger: New row in the approval response sheet.
• Conditions: An open Approval Task exists for the supplied Request ID, and the collected email matches the assigned approver or authorized delegate.
• Actions: Update the task, create an audit event, and route the request according to the decision.
• Fields updated: Decision, Decision By, Decision At, Comments, Approval Status, Status.
• Notification: Next approver, requester, or operations manager receives the appropriate message.
• Exception: Unauthorized or duplicate approval responses are logged and do not change inventory.

An Approve decision opens the next sequential task if one is required. Otherwise, the request moves to Ready to Post. Reject changes the request to Rejected. More Information Required changes the request to Awaiting Information and emails the requester.

Automation C: Post an approved movement

• Trigger: A scheduled search finds a request in Ready to Post, or the approval scenario invokes the posting route.
• Conditions: All required tasks are approved, required evidence exists, and the current stock check passes.
• Actions: Set Posting, create deterministic draft ledger entries, verify the entry count, commit entries, update the request, and write an audit event.
• Fields updated: Status, Automation Status, Posted At, ledger links, Retry Count, Error Message.
• Notification: Posting confirmation to requester and relevant supervisors.
• Exception: Compensate a partial commit where possible, mark Failed, and send the record to manual review.

Signed quantities are derived as follows:

• Receipt: positive quantity at destination.
• Issue: negative quantity at source.
• Transfer: negative quantity at source and positive quantity at destination.
• Adjustment Increase: positive quantity at destination.
• Adjustment Decrease: negative quantity at source.
• Count Adjustment: signed variance at the counted location.

Before posting an issue, transfer, or decrease, Make retrieves the current Stock Balance. It subtracts the requested quantity and blocks posting if the result violates the company’s no-negative-stock rule.

A transfer uses two deterministic entry IDs. Make searches for each ID before creating it. Both entries are created as Draft. After Make confirms that exactly two valid draft entries exist, it changes them to Committed. If the second commit fails after the first succeeds, the error route attempts to return the first entry to Draft and marks the request Failed. The reconciliation scenario then confirms that no partially committed transfer remains.

Airtable does not provide the same multi-record transaction guarantees as a relational database. The draft-and-commit pattern, idempotent entry IDs, compensation route, and reconciliation check reduce risk for this volume, but they do not create a database transaction.

Automation D: Process a cycle count

• Trigger: New row in the cycle-count response sheet.
• Conditions: Count Session ID exists, item and location match the issued count task, and counted quantity is zero or greater.
• Actions: Create a Count Record, calculate variance against the session snapshot, and create an adjustment request when variance is nonzero.
• Fields updated: Counted Quantity, Variance, Counted At, Counter, Count Status, resulting Request ID.
• Notification: Confirmation to the counter; approval task to the supervisor when adjustment is required.
• Exception: Unknown sessions, duplicate counts, or movements after the count cutoff enter manual review.

Count sessions should be opened from Airtable before physical counting. Each issued count line captures the expected system quantity and cutoff time. If another committed movement occurs after that cutoff and before the count is submitted, Make flags the count for reconciliation rather than silently calculating against a changed balance.

Automation E: Monitor reorder levels

• Trigger: Scheduled daily search of Stock Balances.
• Conditions: Reorder Status equals REORDER, item and location are active, and no notification has been sent within the configured suppression period.
• Actions: Add the balance to the procurement view and update Last Reorder Alert At.
• Fields updated: Reorder Status, Last Reorder Alert At, alert batch identifier.
• Notification: One summarized Gmail message to procurement by location.
• Exception: Missing reorder levels appear in a separate configuration view.

The reorder automation does not create or approve a purchase order. Procurement reviews demand, existing orders, supplier constraints, and order quantities before acting.

Step 6: Add Approvals, Reminders, and Escalations

These thresholds are representative business rules, not general accounting recommendations. Each organization should set thresholds according to stock value, risk, insurance, finance policy, and audit requirements.

Make creates Approval Task 01 first. A sequential second task remains Pending Sequence until the first task is approved. The second approver does not receive an email before the first decision.

To create the prefilled approval link, open the Approval Decision form, generate a prefilled link with a placeholder Request ID, and copy the generated URL. Store that URL pattern in Make. Replace only the placeholder value with the URL-encoded Request ID. Interface wording may vary, so use the form’s generated link rather than manually guessing its field identifier.

A scheduled scenario runs hourly:

1. Search open tasks with Reminder Sent At blank and Due At approaching the first reminder point.
2. Send the first reminder and stamp Reminder Sent At.
3. Search tasks past Due At with Escalated At blank.
4. Send an escalation to the assigned approver, backup approver, and operations manager.
5. Stamp Escalated At and create an Audit Event.

If an approver is unavailable, the operations manager changes the task’s assigned approver to the configured backup. Make records the prior and new approver in an Audit Event. Delegation is request-specific and does not permanently change location ownership.

Rejected requests require comments. A returned request enters Awaiting Information. An authorized coordinator corrects the data, records what changed in an Audit Event, and initiates revalidation. Approval evidence consists of task ID, assigned approver, verified response email, decision, timestamp, comments, response source key, and the resulting status change.

Step 7: Add Documents and File Management

Use the following Drive structure:

Inventory Evidence/
  2026/
    07/
      MR-M-20260715-0218/
        MR-M-20260715-0218_transfer-note.pdf
        MR-M-20260715-0218_count-photo.jpg

Make creates the year and month folders if they do not exist, then creates a request folder. It copies each uploaded file rather than relying only on the original Google Forms upload location.

The destination file name begins with the Request ID and retains a sanitized version of the original name. Remove path characters and control characters. If the destination name already exists, append the source file ID or a short timestamp instead of overwriting it.

Store the copied Drive file ID and controlled link in Airtable. The source Google Forms file ID is also retained for deduplication and troubleshooting.

Restrict shared links to authenticated users in the organization unless a documented business requirement permits otherwise. Evidence folders inherit access from the controlled root folder. Do not grant public link access merely to make automation easier.

If an adjustment requires evidence and the file copy fails, the request remains in Document Exception rather than moving to approval. Make retries transient Drive failures. A persistent failure requires the submitter or operations coordinator to supply the document again.

Retention should follow the company’s finance, inventory, contractual, and legal policies. Larkspur’s representative design assigns a documented retention category to each file rather than deleting evidence solely because a request has been posted.

Step 8: Add Reporting and Operational Views

Create Airtable grid views and interfaces for the following queues:

• New records not yet validated
• Requests awaiting approval
• Destination transfer confirmations
• Overdue approval tasks
• Requests awaiting information
• Validation exceptions
• Posting failures
• Potential duplicates
• Rejected requests
• Items by owner and location
• Balances at or below reorder level
• Negative or unexpected balances
• Upcoming cycle counts
• Count variances awaiting approval
• Recently posted movements
• Average processing time by movement type
• Volume by status and location
• Manual-review queue

The operational dashboard uses Airtable records directly, so no separate extract is required for the initial implementation. Dashboard ownership belongs to the operations manager, with procurement responsible for reorder views and warehouse supervisors responsible for their location queues.

Processing time is measured from Submitted At to Posted At. Approval time is measured separately so the team can distinguish automation delay from human decision time.

Alert thresholds include requests in Received or Ready to Post for more than 15 minutes, requests in Posting for more than 10 minutes, overdue approvals, and any failed request without an assigned recovery owner.

Airtable formula fields may not recalculate continuously at the exact second. Time-critical reminders therefore use Make’s scheduled date comparison rather than relying only on a visual overdue formula.

Step 9: Add Security and Governance Controls

• Give warehouse employees form access without direct access to ledger tables.
• Give supervisors access only to the interfaces and records needed for their locations where the selected Airtable permissions support that design.
• Restrict cost fields and high-value adjustment views to operations, procurement, and finance roles.
• Use OAuth connections or narrowly scoped credentials in Make.
• Keep production and test credentials separate.
• Restrict Google Forms and Drive evidence to authenticated users.
• Compare approval response email to the assigned task owner before accepting a decision.
• Record manual overrides with actor, reason, timestamp, prior state, and new state.
• Remove former employees from Google Workspace, Airtable, Make, Drive groups, and notification groups promptly.
• Review Airtable, Google, and Make activity logs according to the available subscription features.
• Export or back up master data, requests, ledger entries, and configuration on a documented schedule.
• Never place access tokens in an Airtable record, Google Form, or Gmail template.
• Define which supplier, employee, pricing, and customer data may be processed by any optional AI service.
• Keep inventory posting, approval, reversal, and write-off decisions under deterministic rules and human authority.

Regulatory and retention requirements vary by industry and jurisdiction. A business handling controlled, perishable, medical, hazardous, or serialized goods would require additional controls not assumed in this representative scenario.

Step 10: Deploy and Test

1. Build the Airtable structure and Make scenarios against the test base.
2. Create test copies of all Google Forms and response sheets.
3. Import 20 test SKUs and all three test locations.
4. Create test opening balances through ledger entries.
5. Run normal and failure test cases using sample data only.
6. Ask one requester and one supervisor from each location to complete user acceptance testing.
7. Reconcile every test request against its expected ledger entries and balance effect.
8. Document field mappings, scenario ownership, approval rules, recovery steps, and credential owners.
9. Pilot production use at WH-A for a limited set of SKUs.
10. Reconcile Airtable to a supervised physical count at the end of the pilot.
11. Enable WH-B and WH-C only after pilot issues are resolved.
12. Set the old spreadsheets to read-only and record the cutover timestamp.
13. Monitor failed runs, overdue tasks, and ledger reconciliation daily during the first month.

Rollback means pausing production scenarios, preventing new form submissions, exporting all movements since cutover, and applying a documented reconciliation procedure. It does not mean deleting posted ledger entries.

Code and Configuration

The core implementation does not require a custom script. Google Forms, linked response sheets, Airtable formulas, Make modules, routers, filters, data stores, and Gmail actions provide the required behavior. Avoiding custom code reduces the number of runtime environments the small operations team must maintain.

Airtable formulas

Place the following formula in the Balance Key field of Stock Balances:

{SKU Text} & "|" & {Location Code Text}

Place this formula in Signed Quantity Active on Ledger Entries:

IF({Entry Status}="Committed",{Signed Quantity},0)

Configure On Hand as a rollup over the linked Ledger Entries and the Signed Quantity Active field:

SUM(values)

Use this formula for Reorder Status:

IF(
  AND(
    {Reorder Level}!="",
    {On Hand}<={Reorder Level}
  ),
  "REORDER",
  ""
)

Use this formula for processing minutes:

IF(
  {Posted At},
  DATETIME_DIFF({Posted At},{Submitted At},'minutes')
)

Use this display formula for overdue status. Make remains responsible for actual reminder timing:

IF(
  AND(
    {Due Date},
    {Status}!="Posted",
    {Status}!="Rejected",
    {Status}!="Cancelled",
    NOW()>{Due Date}
  ),
  "Overdue",
  ""
)

Test each formula with positive, zero, blank, and reversed ledger entries. If a field name changes, update the formula and Make mappings together.

Normalized movement object

The following JSON shows the normalized object Make should assemble before creating the Airtable request. It is a configuration reference, not an API request. Replace mapping tokens with the corresponding fields from the Google Sheets trigger.

{
  "submission_key": "GF-MOV-{{SOURCE_ROW_NUMBER}}",
  "request_id": "MR-M-{{YYYYMMDD}}-{{PADDED_ROW_NUMBER}}",
  "submitted_at": "{{FORM_TIMESTAMP_ISO}}",
  "requester_email": "{{NORMALIZED_EMAIL}}",
  "movement_type": "{{ALLOWED_MOVEMENT_TYPE}}",
  "sku": "{{UPPERCASE_SKU}}",
  "quantity": 24,
  "source_location": "WH-A",
  "destination_location": "WH-C",
  "reference_type": "Transfer",
  "reference_number": "TR-1842",
  "priority": "Normal",
  "notes": "{{TRIMMED_NOTES}}",
  "source_file_id": "{{GOOGLE_DRIVE_FILE_ID}}"
}

Quantity must be a number, not a formatted string. Blank location values should be represented as empty values rather than the text null. Validate the object before any approval task is created.

Make scenario configuration

Each scenario should have a top-level error route. When a request record already exists, the route updates Automation Status, Last Automation Run, Retry Count, Exception Type, and Error Message. When no request exists, it creates an Automation Run failure using the source submission key.

Use Make routers for movement type and approval decision branches. Put a filter before every Airtable create action. The filter should verify that validation succeeded and that the deterministic target ID does not already exist.

Set scenarios that process stock movements to avoid overlapping execution where the platform’s scenario controls permit it. This reduces the chance that two movements read the same balance simultaneously. Final balance validation must still run immediately before posting.

For transient service failures, schedule explicit retries using Retry Count and Next Retry At. Stop automated retries after three representative attempts and move the record to manual review. Authentication failures should not be retried repeatedly because they usually require reconnection by an administrator.

Testing the configuration

Submit a test transfer for a known SKU with sufficient stock. Confirm that the source key is unique, two approval stages are created, two draft ledger entries become committed, and the source and destination balances change by equal and opposite quantities.

Replay the same response bundle in Make. The duplicate check should end the run without creating a second request or ledger entry.

If a formula returns blank unexpectedly, confirm field names, field types, linked records, and Entry Status. If Make cannot find a form upload, verify that the connected Google identity owns the form or has access to the original upload folder.

Failure Handling and Operational Reliability

The Failed and Validation Failed views act as the manual-review queue. The Automation Runs table acts as a dead-letter register for events that could not produce a valid business record.

Idempotency exists at three levels: source submission key, deterministic business record ID, and deterministic ledger entry ID. A retry searches for all three before creating anything.

The reconciliation scenario compares posted requests with expected committed entry counts. Receipts, issues, and adjustments require one committed entry. Transfers require two entries whose signed quantities sum to zero. A mismatch creates an immediate exception.

Error messages stored in Airtable should exclude credentials, raw authorization headers, and unnecessary personal data. Detailed platform logs remain restricted to automation administrators.

A Complete Example

A warehouse employee at WH-A submits response row 218 through the Inventory Movement Request form.

1. Make creates source key GF-MOV-218 and request ID MR-M-20260715-0218.
2. The data store and Airtable searches find no previous record with that key.
3. Make converts the SKU to uppercase and finds one active Items record.
4. It validates WH-A and WH-C as different active locations.
5. The source balance is 82 units and the destination balance is 17 units.
6. Airtable creates the Movement Request and returns its internal record ID. Make stores that ID in the Automation Run.
7. Make copies the PDF into Inventory Evidence/2026/07/MR-M-20260715-0218 and stores the returned Drive file ID.
8. The WH-A supervisor receives task AT-MR-M-20260715-0218-01 by Gmail.
9. The supervisor approves through the approval form. Make verifies the collected email and opens destination task AT-MR-M-20260715-0218-02.
10. The WH-C supervisor confirms the transfer. The request moves to Ready to Post.
11. Make rechecks the WH-A balance. At least 24 units remain available.
12. Make creates draft entry LE-MR-M-20260715-0218-01 for negative 24 at WH-A and draft entry LE-MR-M-20260715-0218-02 for positive 24 at WH-C.
13. After validating both records, Make commits them.
14. The WH-A balance changes from 82 to 58. The WH-C balance changes from 17 to 41.
15. The request changes to Posted, Approval Status becomes Approved, and Posted At is recorded.
16. Gmail sends a completion notice to the requester and both supervisors.

If the Make trigger later replays response row 218, the source key check finds the existing request. The replay is logged but does not create another transfer.

Implementation Cost

All amounts below are representative planning assumptions, not vendor quotes or verified client costs. Current subscriptions, usage allowances, taxes, currencies, implementation rates, and record requirements must be checked before purchase.

The core savings calculation uses $200 in recurring software costs and treats four maintenance hours as labor. Optional AI and professional support are excluded from the core calculation.

Estimated Time and Cost Savings

The representative assumptions are:

• 770 workflow records per month, comprising 650 movements and 120 count lines
• 8 minutes of current handling per record
• 2 minutes of routine handling after implementation
• 8 percent exception rate
• 6 additional minutes for each exception
• 4 monthly maintenance hours
• $35 loaded hourly labor cost
• $200 recurring monthly software cost
• $6,750 one-time implementation cost

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 represent additional warehouse capacity, quicker transfer processing, less overtime, fewer reconciliation tasks, improved procurement response, or less dependency on one spreadsheet owner.

Non-financial benefits include clearer ownership, fewer email follow-ups, a consistent approval trail, more complete records, faster stock inquiries, documented count history, and more reliable location-level reporting.

Readers should replace the monthly volume, handling time, exception rate, review time, maintenance requirement, hourly cost, software allowance, and implementation cost with their own measured figures.

Adding AI to the Automation

AI should be added only after item IDs, supplier references, movement rules, approvals, and ledger posting work reliably.

The core benefits do not require AI. Required fields validate known values. Exact lookups validate SKUs and locations. Formulas calculate balances. Threshold rules route approvals. Make sends reminders and creates deterministic ledger entries.

Potential AI applications include extracting packing-slip lines, summarizing adjustment notes, classifying free-text damage reasons, identifying likely missing document information, and supporting semantic search across receiving evidence.

AI should not calculate stock, approve adjustments, decide whether a write-off is acceptable, select an item from an uncertain match, or post ledger entries. Those tasks require exact data, deterministic controls, or human judgment.

The Recommended AI Enhancement

The recommended enhancement extracts candidate receipt lines from supplier packing slips. It reduces repetitive typing while leaving item matching, quantity confirmation, approval, and inventory posting under deterministic and human control.

• Trigger: A receiving employee submits an optional Batch Receipt Document form with supplier, location, reference, and packing-slip file.
• AI input: The document image or PDF, supplier name, and reference supplied by the employee.
• Expected output: Supplier document details and candidate line items in structured JSON.
• Validation: Make checks JSON structure, numeric quantities, document reference, and exact Supplier SKU matches in Airtable.
• Record update: Valid candidates create AI Receipt Draft records, not committed movements.
• Human review: A receiving employee confirms every candidate SKU and quantity in Airtable.
• Low confidence: Any low-confidence, unmatched, or incomplete line enters manual review.
• Prohibited data: Credentials, payment-card data, bank details, unrelated personal data, and customer information not needed for receipt processing.
• Logging: Store document ID, provider request identifier when available, processing time, result status, validation failures, and reviewer decision.
• Failure behavior: Use the standard individual receipt form.

Use the following system instruction:

You extract receiving data from supplier packing slips.

Return only JSON that conforms to the supplied schema.

Do not approve inventory, calculate an inventory balance, or infer an internal SKU.
Copy supplier part numbers exactly as shown.
Do not invent missing quantities, references, dates, units, or descriptions.
Use null for an unknown scalar field and an empty array for no extracted lines.
Add a clear item to missing_fields when required information is absent.
Confidence is extraction confidence only and must not be treated as approval.

Use this reusable user prompt:

Extract candidate receipt information from the attached document.

Submitted supplier: {{SUPPLIER_NAME}}
Submitted warehouse: {{DESTINATION_LOCATION}}
Submitted reference: {{REFERENCE_NUMBER}}

For every line, return:
1. supplier_sku exactly as printed
2. description exactly as printed
3. quantity as a number
4. unit if printed
5. line_confidence from 0 to 1

Do not map supplier_sku to an internal item.
List conflicting or missing information in missing_fields.
Return only JSON matching the schema.

Configure the AI connector to require output matching this schema where the selected provider and module support structured output:

{
  "type": "object",
  "additionalProperties": false,
  "required": [
    "supplier_name",
    "document_reference",
    "document_date",
    "lines",
    "missing_fields",
    "document_confidence"
  ],
  "properties": {
    "supplier_name": {
      "type": ["string", "null"]
    },
    "document_reference": {
      "type": ["string", "null"]
    },
    "document_date": {
      "type": ["string", "null"]
    },
    "lines": {
      "type": "array",
      "items": {
        "type": "object",
        "additionalProperties": false,
        "required": [
          "supplier_sku",
          "description",
          "quantity",
          "unit",
          "line_confidence"
        ],
        "properties": {
          "supplier_sku": {
            "type": ["string", "null"]
          },
          "description": {
            "type": ["string", "null"]
          },
          "quantity": {
            "type": ["number", "null"]
          },
          "unit": {
            "type": ["string", "null"]
          },
          "line_confidence": {
            "type": "number",
            "minimum": 0,
            "maximum": 1
          }
        }
      }
    },
    "missing_fields": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "document_confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    }
  }
}

After parsing the output, Make rejects lines with a blank supplier SKU, null quantity, nonpositive quantity, or no exact Airtable Supplier SKU match. A reported confidence below 0.85 routes the line to manual review, but confidence above that threshold does not remove human review.

Once a receiving employee confirms the draft, Make creates standard Receipt requests. Those requests follow the same approval and ledger process as manually entered receipts.

Monitor AI usage by document count, line count, processing cost where reported, correction rate, failure rate, and average human review time. Stop AI processing and use the normal form if the provider is unavailable or document input is unsupported.

Benefits of the AI Enhancement

• Less repetitive entry of supplier part numbers and quantities
• Faster conversion of multi-line packing slips into reviewable drafts
• Consistent capture of document reference and date
• Visible identification of missing fields
• Structured data from PDFs and images
• Measured correction and failure rates

These benefits are specific to reading unstructured supplier documents. Location-level balances, transfer integrity, approvals, reminders, audit events, and reorder views come from the core rule-based automation.

What Remains Rule-Based or Human-Controlled

• Internal SKU matching: Make uses an exact Supplier SKU lookup. A person resolves unmatched or ambiguous items.
• Physical receipt confirmation: A receiving employee confirms that goods actually arrived.
• Quantity acceptance: A person compares the extracted quantity with the shipment and source document.
• Inventory posting: Deterministic Make rules create ledger entries only after approval.
• Adjustment approval: Supervisors and the operations manager approve write-offs and count variances.
• Negative stock prevention: A fixed rule blocks prohibited balances.
• Purchase decisions: Procurement reviews reorder recommendations and supplier constraints.
• Reversals: An authorized manager approves corrections to committed movements.

These controls remain human or rule-based because an extraction model can misread documents, omit lines, or return plausible but incorrect content.

Estimating the Additional Value of AI

Assume 180 receipt lines arrive across 30 multi-line documents each month.

The additional capacity compared with core automation is approximately 2.57 hours per month. At $35 per hour, the labor value is approximately $89.95. After a representative $20 AI usage allowance, the net additional monthly value is approximately $69.95.

This estimate assumes a 10 percent correction rate and 3 percent document-processing failure rate. AI does not eliminate human review, and a different document mix could produce a smaller or larger benefit.

Testing Checklist

Use sample data and nonproduction files before processing real inventory information.

Ongoing Maintenance

The operations manager is the primary system owner. The backup operations manager owns continuity when the primary owner is unavailable. Scenario changes require test execution and documented approval before production activation.

Archive closed requests and evidence according to retention policy, but preserve the ledger and links needed to explain current balances. Monitor integration announcements and authentication changes from Airtable, Google, and Make.

When to Move to Dedicated Software

The implementation should not be replaced merely because it uses low-code tools. It should be reviewed when the operational requirements exceed its controls or create excessive maintenance.

Relevant upgrade signals include:

• Movement volume creates sustained processing queues or rate-limit pressure.
• Users require rapid barcode scanning across many warehouse tasks.
• The company needs lot, batch, expiry, serial-number, or regulated traceability.
• Stock must be reserved against orders in real time.
• Transfers require in-transit inventory, partial receipts, or carrier integration.
• Multiple units of measure and conversion rules become common.
• Inventory valuation, landed cost, purchasing, and accounting need direct integration.
• Permissions must be enforced at a more granular record or field level.
• Formal audit requirements exceed the available platform history and controls.
• Multiple employees frequently post against the same balance at the same time.
• Exception rates or manual reconciliation continue increasing.
• Airtable record growth or interface performance approaches the selected subscription’s practical limits.
• Customers or suppliers require a secure external portal.
• Warehouse staff need offline mobile operation.
• The business requires contractual vendor support and recovery commitments.

Possible next-step categories include inventory management systems, warehouse management systems, enterprise resource planning platforms, or a transactional application backed by PostgreSQL or another relational database. The Airtable data model can still provide useful migration definitions for items, locations, balances, movements, approvals, and audit events.

Implementation Checklist

• Confirm movement types, volumes, locations, owners, and approval thresholds.
• Select Airtable, Google Forms, Make, Gmail, and controlled Drive storage.
• Create production and test accounts, bases, forms, sheets, folders, and scenarios.
• Assign primary and backup system owners.
• Apply least-privilege permissions and separate test credentials.
• Clean item IDs, supplier SKUs, location codes, owner emails, and reorder levels.
• Create Items, Locations, Stock Balances, Movement Requests, Ledger Entries, Count Records, Approval Tasks, Audit Events, and Automation Runs.
• Import opening quantities through committed migration ledger entries.
• Build movement, count, and approval forms.
• Link forms to append-only response sheets.
• Document every source-to-destination field mapping.
• Configure idempotency keys and deterministic request, task, and entry IDs.
• Build validation, assignment, approval, posting, and reconciliation scenarios.
• Configure sequential transfer approvals.
• Configure adjustment threshold approvals.
• Add reminders, escalation, delegation, rejection, and information-return paths.
• Create managed document folders, names, permissions, and retention categories.
• Create operational, overdue, exception, reorder, count, and failure views.
• Add formula, rollup, and reporting fields.
• Configure error routes, retries, dead-letter records, and manual recovery.
• Test normal, duplicate, invalid, unauthorized, partial, and failed scenarios.
• Pilot one location before phased deployment.
• Reconcile physical stock and ledger balances at cutover.
• Document representative software, labor, maintenance, and savings assumptions.
• Add AI document extraction only after the core workflow is stable.
• Require structured AI output, deterministic validation, and human confirmation.
• Schedule permission, credential, integration, cost, backup, and exception reviews.
• Define upgrade criteria for volume, scanning, traceability, permissions, integrations, and transactional requirements.