Manaz Afzal Book a consultation

Work snippet

Airtable inventory ledger, job usage and supplier bill matching

Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.

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

What discovery would examine

A review of branch stores, van stock, ServiceM8 materials used on jobs, and supplier bills for a 30-tech plumbing business with three stores.

Findings from the review
IDFindingEvidenceRisk if left
F1Stock on hand is a spreadsheet updated after the fact, not from movementsNegative van quantities, unnoticed for a weekA technician leaves for a job without the part
F2ServiceM8 materials used on a job do not reduce a ledgerJob materials lists versus store spreadsheets, no linkJob cost and stock cannot be agreed
F3Supplier bills are entered before goods-received is confirmedThree bills last month for part deliveries not yet completePayment goes out for goods still in transit, or for a short delivery
F4Transfers between stores are noted in a group chatTwo transfers last month, only one recordedOne store shows stock it no longer has
F5Average cost is not kept, so a stocktake adjustment has no valueAdjustments in units onlyThe ledger value cannot be tied to a stocktake

Approach

  1. Write every movement type

    Receipt, transfer, usage, stocktake and write-off, each with a source.

  2. Pull usage from ServiceM8

    Keyed so a replay cannot use the same job and item twice.

  3. Keep average cost on the movement

    A later receipt does not rewrite history.

  4. Match bills to receipts, or hold them

    A person posts the bill in the ledger only once it matches.

Current state and future state

Stock movements, current and future

Current state

SupplierDelivers
Spreadsheet / chatUpdated later
ServiceM8 jobMaterials listed, stock unchanged
Supplier billPaid without a match

Future state

Goods inAirtable movement
Transfers and usageFrom ServiceM8 jobs
Bill matchAgainst received qty
Hold if notReason and owner

Future-state modules

Supplier deliveryGoods receivedAirtable ledgerMovements + average costServiceM8Usage on jobsBill matchOr holdStocktakeAdjustment with a source

How it is built

Module-level build
  1. Module AMovement types

    Goods received, transfer, usage on a job, stocktake adjustment, and write-off, each with a source and a timestamp.

  2. 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.

  3. Module CAverage cost

    A receipt updates average cost; a usage posts at the current average, stored on the movement.

  4. Module DBill match

    A supplier bill is held until received quantity and price agree within a set tolerance.

  5. 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

Movement and bill map
EventAirtable movementOther systemKey
Goods received, with a delivery noteType: receipt, qty, unit cost, storeNone until the bill arrivesdelivery id
Supplier billMatched to open receiptsA person posts the bill in the ledger once matched, or the hold staysbill number + delivery id
Transfer store to van, or store to storeType: transfer, from / to, qtyN/Atransfer id
ServiceM8 materials on a jobType: usage, job id, item, qty, average cost at the timeServiceM8 job materials listjob id + item + event time
StocktakeType: adjustment, counted qty, reasonN/Astocktake id + item + store

Worked exhibits

One month of movements, walkthrough figures
Receipt lines214
Usage lines from jobs1,086
Transfers37
Bills matched first time61of 70
Bills held9short delivery or price
Van-below-zero flags4same day
A held bill and a usage movement
Airtable · receipt R-331
item20 mm valve
qty received40
storeNorth
unit cost$18.40
statusOpen, not fully billed
Bill hold
supplier invoiceINV-1044
qty on bill40
unit price$21.00
varianceAbove tolerance
statusHeld
ownerStore lead

Exceptions and runbook

Exception runbook
CaseRuleWho actsFirst action
Bill before goods receivedHold until the receipt exists, or until a written exceptionStore leadConfirm whether the delivery is on site
Part deliveryMatch what was received, leave the remainder open on the orderFlow, then purchasing if the remainder agesDo not close the receipt early
Supplier creditA negative receipt or a credit movement, never a silent edit of the original receiptFinanceThe original movement stays, the credit is a new line
Usage would take a van below zeroRecord the usage, flag the same dayStore lead and the technicianA same-day transfer or a stocktake, not a deleted usage
Item not on the Airtable listPark the usage or receipt, do not invent an itemStore leadAdd the item, then release

How to read the exhibits

  1. The monthly counts show stock movements and holds across the month.
  2. A person still posts the matched bill in the ledger; the flow does not pay it.
  3. Below-zero is flagged, not hidden, so usage is not deleted to make the number look tidy.

Test plan

PASS / FAIL checks
IDCheckMethodPASS whenFAIL when
T1Receipt then bill, inside toleranceEnter a receipt, then a matching billMatched, ready for a person to post the billAuto-posted, or held with no reason
T2Bill price above toleranceRaise the unit price 15%Held, receipt still openTreated as matched
T3Usage from a jobPost materials on a test jobOne usage movement, average cost storedA second movement on replay, or no cost
T4TransferMove 10 units from North to a vanNorth down 10, van up 10, one transfer idOne side only
T5Below zeroUse more than the van holdsUsage recorded, flag raisedThe usage is dropped
T6ReplaySend the same job-usage event twiceOne movementTwo movements

Scope

AreaIncluded
DiscoveryStores, vans, job materials and unmatched bills, with written findings
Airtable ledgerItems, locations, movements and average cost
ServiceM8 usage flowIdempotent movements from job materials
Bill match and holdsTolerance, reasons and owners
AlertsBelow-zero and aged unmatched receipts
Test planThe PASS and FAIL checks in this write-up
Handover and supportSOPs, walkthrough, 14 days of fixes, optional monthly hour block
Not includedPhysical stocktakes on site, or supplier contract negotiation

Rollout, handover and monthly support

MilestoneDeliverableTimingShare of fee
M1 Discovery and movement rulesFindings, item list and the movement bookWeek 120%
M2 Ledger and usage flowReceipts, transfers and job usage in a copyWeeks 2 to 340%
M3 Bill match and alertsHolds and below-zero flags liveWeek 425%
M4 HandoverSOPs, 14 days of fixesWeek 515%
Handover and monthly support

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.

Related work snippets

Next step

Bring me the work that repeats.

I scope it in writing, test it against your current process on your own data and hand over everything I build.

Book a consultation