How a Small Diagnostics Company Automated Its Bill-of-Materials Inventory — and Stopped Counting Parts by Hand
A small diagnostics company automated bill-of-materials inventory in Airtable, eliminating manual lot lookups and building a compliant audit trail. Here is how.
When you're running two new production lines and your bill of materials has 300 parts, the last thing you want to do is manually look up lot numbers and update inventory after every assembly run. For a small molecular-diagnostics company on the US West Coast, that's exactly what was happening — and it was only going to get worse as output scaled.
Photo by CHUTTERSNAP on Unsplash
The Problem
The company tracked inventory in Airtable. Each part was issued by lot number — and a single lot could span multiple warehouse locations ("Stores" and "CER"). When an assembly was completed, someone had to:
Look up every one of the ~300 BOM line items for that system
Find the matching lot numbers, split across locations
Decrement the on-hand quantity for each lot
Record the material scrap for anything that didn't make it into the final assembly
With inventory tracked by lot — not by part number — there was no simple one-to-one lookup. A single part can draw from two or three lots depending on stock levels. The quantities needed also varied by system type, stored in separate columns in the Parts table rather than a single normalized field. Doing this for every assembly run, on two production lines, by hand, wasn't sustainable.
What Connex Built
The Connex team built a JavaScript automation inside Airtable Scripting that runs when an assembly is marked complete. It works in three stages:
Lot resolution: The script iterates through every BOM line item for the selected system, builds an array of the lot numbers to draw from (prioritizing "Stores" and "CER" locations), and calculates the exact quantity to withdraw from each lot.
Dynamic quantity lookup: Rather than hard-coding quantities, the script reads a formula field called QtyNeeded that dynamically selects the right column based on the system type — so the same automation works across different assembly configurations.
Idempotency guard: A check prevents the automation from running twice on the same Work Order line item — critical in a regulated environment where double-counting a lot withdrawal would create a traceability error.
The automation also creates linked records in a Material Scrap table and ties each transaction back to the Work Order, giving the team a full lot-history audit trail. An intermediate staging table handles the multi-lot array construction and can be pruned on a schedule once records are processed.
The build required roughly ten working sessions over six weeks to get right — the complexity came from Airtable Scripting's lack of native record filtering within a query result (requiring a manual loop to match lot numbers), plus the multi-location inventory split and the per-system column logic.
The Outcome
Assembly inventory processing that previously required manual lookup and data entry across ~300 BOM items now runs automatically on completion of a Work Order. The company can press a single button and have all lot withdrawals, quantity decrements, and scrap records created without touching a spreadsheet.
📊
Estimated impact: 10–20 minutes of manual data entry eliminated per assembly run (based on 300-item BOM with multi-lot inventory lookup across locations). With two production lines running, this compounds quickly at scale. Estimate basis: Connex session notes documenting the before-state complexity and the number of iteration loops required to replicate it in code.
Beyond the time savings, the idempotency guard and audit trail address a compliance concern the team hadn't fully articulated at the start: in a regulated diagnostics environment, lot traceability isn't just convenient — it's a recordkeeping requirement. The automation now creates that record automatically and consistently.
The company returned in early 2026 with a follow-on engagement to address automation errors in a QR code label printing program — a second system built on the same Airtable foundation, handled in a single ZoomFlow session.
Why This Pattern Matters
Small manufacturers and early-stage production teams often build their first inventory system in a spreadsheet tool — and it works until it doesn't. The inflection point usually comes when assembly complexity outpaces what a human can accurately track in real time. This engagement is a clean example of bridging that gap: keeping the no-code platform the team already knew, extending it with a scripting layer that handled the complexity, and doing it without a full ERP migration.
The same pattern — lot-tracked inventory + bill of materials + scripted decrement automation — applies to any production operation where parts are fungible across lots and manual reconciliation creates bottlenecks or traceability risk.
Build inventory automation that works at scale
If your team is tracking parts by lot number and manually reconciling inventory after each assembly run, you're burning time that compounds as production scales. Connex builds Airtable automations that handle the lookups, decrements, and audit trails so your operators don't have to. Book a free discovery call to talk through your setup.
How a mid-sized countertop manufacturer replaced their inbound status-inquiry phone queue with an AI voice agent connected to Moraware. Read the case study.
A Colorado cabinet door manufacturer producing 550–600 doors/day replaced paperwork-based tracking with an automated lot planning and conflict detection system.
