Work snippet
Airtable inventory ledger, job usage and supplier bill matching
A plumbing business with 30 technicians and three branch stores. Every stock movement, including parts used on ServiceM8 jobs, goes into an Airtable ledger, and a supplier bill waits until it agrees with what was actually received, within a set tolerance.
The problemStock used on jobs is not recorded, and bills are paid without checking what arrived.
What changesEvery movement is recorded, and a bill is paid only when it matches the delivery.
- Area
- Stock movements and bill matching
- Tools
- Airtable · ServiceM8 · Make
- Typical scope
- US$2,500-4,200
- Timeline
- About 5 weeks · fixed milestones
The problem
Stock is tracked in spreadsheets and group messages, parts used on jobs never come off a ledger, and supplier bills are keyed in before anyone has checked the delivery against the order.
- Van stock can sit below zero without a same-day flag
- Job cost and store records cannot be agreed
- Bills are paid for short or unreceived deliveries
- Transfers between stores go missing
Discovery findings
A review of branch stores, van stock, ServiceM8 materials used on jobs, and supplier bills for a 30-tech plumbing business with three stores.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | Stock on hand is a spreadsheet updated after the fact, not from movements | Negative van quantities, unnoticed for a week | A technician leaves for a job without the part |
| F2 | ServiceM8 materials used on a job do not reduce a ledger | Job materials lists versus store spreadsheets, no link | Job cost and stock cannot be agreed |
| F3 | Supplier bills are entered before goods-received is confirmed | Three bills last month for part deliveries not yet complete | Payment goes out for goods still in transit, or for a short delivery |
| F4 | Transfers between stores are noted in a group chat | Two transfers last month, only one recorded | One store shows stock it no longer has |
| F5 | Average cost is not kept, so a stocktake adjustment has no value | Adjustments in units only | The ledger value cannot be tied to a stocktake |
Approach
- Write every movement type
Receipt, transfer, usage, stocktake and write-off, each with a source.
- Pull usage from ServiceM8
Keyed so a replay cannot use the same job and item twice.
- Keep average cost on the movement
A later receipt does not rewrite history.
- Match bills to receipts, or hold them
A person posts the bill in the ledger only once it matches.
Current state and future state
Current state
Future state
Future-state modules
How it is built
- Module AMovement types
Goods received, transfer, usage on a job, stocktake adjustment, and write-off, each with a source and a timestamp.
- Module BJob usage
ServiceM8 materials on a completed or booked job, depending on the written rule, post a usage movement, keyed by job id and item.
- Module CAverage cost
A receipt updates average cost; a usage posts at the current average, stored on the movement.
- Module DBill match
A supplier bill is held until received quantity and price agree within a set tolerance.
- Module EBelow-zero alerts
A van or store that would go below zero is flagged the same day, and the usage is still recorded with the flag.
Field mapping
| Event | Airtable movement | Other system | Key |
|---|---|---|---|
| Goods received, with a delivery note | Type: receipt, qty, unit cost, store | None until the bill arrives | delivery id |
| Supplier bill | Matched to open receipts | A person posts the bill in the ledger once matched, or the hold stays | bill number + delivery id |
| Transfer store to van, or store to store | Type: transfer, from / to, qty | N/A | transfer id |
| ServiceM8 materials on a job | Type: usage, job id, item, qty, average cost at the time | ServiceM8 job materials list | job id + item + event time |
| Stocktake | Type: adjustment, counted qty, reason | N/A | stocktake id + item + store |
Worked exhibits
Exceptions and runbook
| Case | Rule | Who acts | First action |
|---|---|---|---|
| Bill before goods received | Hold until the receipt exists, or until a written exception | Store lead | Confirm whether the delivery is on site |
| Part delivery | Match what was received, leave the remainder open on the order | Flow, then purchasing if the remainder ages | Do not close the receipt early |
| Supplier credit | A negative receipt or a credit movement, never a silent edit of the original receipt | Finance | The original movement stays, the credit is a new line |
| Usage would take a van below zero | Record the usage, flag the same day | Store lead and the technician | A same-day transfer or a stocktake, not a deleted usage |
| Item not on the Airtable list | Park the usage or receipt, do not invent an item | Store lead | Add the item, then release |
How to read the exhibits
- The monthly counts show stock movements and holds across the month.
- A person still posts the matched bill in the ledger; the flow does not pay it.
- Below-zero is flagged, not hidden, so usage is not deleted to make the number look tidy.
Test plan
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Receipt then bill, inside tolerance | Enter a receipt, then a matching bill | Matched, ready for a person to post the bill | Auto-posted, or held with no reason |
| T2 | Bill price above tolerance | Raise the unit price 15% | Held, receipt still open | Treated as matched |
| T3 | Usage from a job | Post materials on a test job | One usage movement, average cost stored | A second movement on replay, or no cost |
| T4 | Transfer | Move 10 units from North to a van | North down 10, van up 10, one transfer id | One side only |
| T5 | Below zero | Use more than the van holds | Usage recorded, flag raised | The usage is dropped |
| T6 | Replay | Send the same job-usage event twice | One movement | Two movements |
Scope
| Area | Included |
|---|---|
| Discovery | Stores, vans, job materials and unmatched bills, with written findings |
| Airtable ledger | Items, locations, movements and average cost |
| ServiceM8 usage flow | Idempotent movements from job materials |
| Bill match and holds | Tolerance, reasons and owners |
| Alerts | Below-zero and aged unmatched receipts |
| Test plan | The PASS and FAIL checks in this write-up |
| Handover and support | SOPs, walkthrough, 14 days of fixes, optional monthly hour block |
| Not included | Physical stocktakes on site, or supplier contract negotiation |
Rollout, handover and monthly support
| Milestone | Deliverable | Timing | Share of fee |
|---|---|---|---|
| M1 Discovery and movement rules | Findings, item list and the movement book | Week 1 | 20% |
| M2 Ledger and usage flow | Receipts, transfers and job usage in a copy | Weeks 2 to 3 | 40% |
| M3 Bill match and alerts | Holds and below-zero flags live | Week 4 | 25% |
| M4 Handover | SOPs, 14 days of fixes | Week 5 | 15% |
Handover
- Written operating procedures for the happy path and each exception in the runbook
- A recorded walkthrough of the architecture, the queue and how to replay an item
- A credentials and ownership list, with every connection in your name
- Fourteen days of fixes after go-live, for defects in the agreed scope
Monthly support
A set block of hours each month, at US$35 per hour, agreed in writing before it starts. Typical use of the block: held bills, below-zero flags, new items, and stocktake adjustments
- Review of the exception queue and the weekly digest
- Small mapping or rule changes, agreed before they are made
- A short written note on what was done and what is still open
Typical scope for this kind of project: US$2,500-4,200
About 5 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing, to watch the flows, handle changes and work the exception queue.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.