Manaz Afzal Book a consultation

Work snippet

Airtable purchase orders into Xero, with approval levels and an audit trail

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

A residential builder running about a dozen sites. Orders are raised in Airtable against the job’s cost-code budget, approved in value bands, and only then sent to Xero; each supplier bill is matched to its order and delivery, and anything outside tolerance is held with the reason.

The problemPurchase orders live in email and bills arrive with nothing to match them against.

What changesEach order is approved by value before Xero sees it, and every bill is matched to its order.

Area
Purchasing and bill matching
Tools
Airtable · Xero · Make
Typical scope
US$3,000-5,000
Timeline
About 6 weeks · fixed milestones

The problem

Materials are ordered by phone or text before anyone signs off, bills turn up with no order to check them against, and when a price goes up nobody can show who agreed to it.

  • Spend is visible only when the supplier bill arrives
  • A site can go over its cost-code budget with no warning
  • The same bill can be entered twice
  • Approvals live in personal inboxes and cannot be audited

What discovery would examine

A review of purchase orders, site budgets and supplier bills for a builder with a dozen live sites, plus the current path from a verbal order to a Xero bill.

Findings from the review
IDFindingEvidenceRisk if left
F1Orders are placed by message or phone before anyone approves themA week of supplier invoices with no purchase order numberSpend is visible only when the bill arrives
F2There is no record of who approved a value riseEmails in personal inboxes, some deletedA disputed bill cannot be traced
F3Bills are entered in Xero without a match to an order or a deliveryXero bills with a blank referenceOver-delivery and double bills are easy to miss
F4A site can exceed its cost-code budget with no warningTwo sites over budget mid-month, noticed at month-endThe overspend is already committed
F5The same supplier bill was entered twice in one quarterTwo Xero bills, one supplier invoice numberThe second was paid

Approach

  1. Write the bands and the match rule

    Value levels, deputies, and the tolerance for a bill versus the order and the delivery.

  2. Build the PO and the budget check

    Every line has a site and a cost code, and a remaining budget figure.

  3. Post only after approval

    Make sends the approved order to Xero, with an idempotency key.

  4. Match bills or hold them

    A nightly audit log is exported, and the hold queue has an owner.

Current state and future state

Order path, current and future

Current state

Site requestMessage or phone
SupplierOften already packing
Xero billKeyed at the end
ApprovalIf at all, after the fact

Future state

Airtable POBudget checked
Approvals by valueIn order, logged
Send to XeroOnly once approved
Bill matchTolerance, or hold

Future-state modules

Airtable POCost code and budgetApproval chainBy value bandXero PO / billAfter approvalHold queueMismatchAudit logAppend-only

How it is built

Module-level build
  1. Module APO form and budget

    A purchase order is raised in Airtable against a site and a cost code, and checked against the remaining budget.

  2. Module BApproval levels

    Value bands route the order to the right people, in order. A value rise sends it back.

  3. Module CPost to Xero

    Only an approved order is sent. The Airtable record id is the idempotency key.

  4. Module DBill match

    A supplier bill is matched to the order and the delivery within a set tolerance, or held with a reason.

  5. Module EAudit log

    Every approval, rejection, value change and posting is appended and exported nightly.

Field mapping

Field map, Airtable purchase order to Xero
Airtable fieldXero fieldRuleIdempotency
PO number, record idPO reference / trackingCreated only after final approvalAirtable record id
SupplierContactMust already exist; a new supplier is parked for a personSupplier id
Lines: item, qty, unit cost, taxPO and bill linesQuantities and tax codes must match the orderLine id
Site and cost codeTracking / accountWritten on every lineN/A
Approved totalPO totalA later rise must go through approval againRevision number
Delivery qtyBill qty toleratedWithin the written tolerance, else holdBill number + PO number

Worked exhibits

Approval bands and one month of orders
Orders raised86
Approved and sent to Xero71
Held for a mismatch9quantity or price
Rejected or withdrawn6
Bills paid with no order0after go-live of the match
BandApproverSecond approverValue rise
Up to $1,000Site leadNoneBack to the site lead
$1,000 to $5,000Site lead, then project leadProject leadBack to both, in order
Above $5,000Project lead, then directorDirectorBack to both, in order
New supplier, any valueDirector onlyN/ADirector again
A held bill on the match desk
Airtable · PO-2144
SiteLot 18
Cost codeMaterials · electrical
Approved total$4,280
Delivered$4,280, in full
StatusApproved, sent
Xero · bill draft
Supplier invoiceINV-8831
Amount$4,820
Variance$540 above tolerance
StatusHeld
ReasonPrice rise on two lines, no revised PO

Exceptions and runbook

Exception runbook
CaseRuleWho actsFirst action
Bill above toleranceHold, do not post as a draft for paymentProject leadRevise the PO, or reject the extra and request a credit
Bill arrives before the deliveryHold until the delivery is recorded, or until a written exceptionSite leadConfirm whether the goods are on site
Split deliveryMatch each delivery to the remaining quantityFlow, then site lead if a remainder sits openThe PO stays open until the last delivery or a close
Approver on leaveThe written deputy for that band is used; the log still names bothOperations adminThe deputy is listed before the absence
Bill with no orderHold in the no-PO queue, never auto-postedDirectorRaise a retrospective PO, or reject the bill

How to read the exhibits

  1. The monthly counts walk through purchase orders, bills and holds.
  2. The held bill shows the match rule at work: a price rise does not become payable on its own.
  3. The audit log is append-only, so a later change does not erase who approved the first version.

Test plan

PASS / FAIL checks
IDCheckMethodPASS whenFAIL when
T1Under the first bandRaise a $400 POSite lead only, then a Xero POIt is sent before approval
T2Value riseRaise $800, then change to $1,400Returns to the chain for the new bandThe original approval still stands
T3ReplaySend the approved record twiceOne Xero POA second PO
T4ToleranceA bill 2% over, inside the written toleranceMatched and draftedHeld, or posted with no note
T5Above toleranceA bill 12% overHeld with a reason, not payablePosted for payment
T6Audit logApprove, reject, then change a valueEach step is in the nightly export, in orderA missing or overwritten row

Scope

AreaIncluded
DiscoveryCurrent order path, site budgets and unmatched bills, with written findings
Airtable PO and budgetsForm, cost codes, remaining budget and revision history
Approval chainValue bands, deputies and the value-rise rule
Xero postingApproved orders only, with idempotent keys
Bill matchTolerance, hold queue and reasons
Audit logNightly append-only export
Handover and supportSOPs, walkthrough, 14 days of fixes, optional monthly hour block
Not includedNegotiating supplier prices, or a full inventory system

Rollout, handover and monthly support

MilestoneDeliverableTimingShare of fee
M1 Discovery and rulesFindings, bands, tolerance and the account mapWeek 120%
M2 PO, approvals and testsLive in a copy, scripted cases passedWeeks 2 to 440%
M3 Bill match and audit logHold queue and nightly export liveWeek 525%
M4 HandoverSOPs, deputies list, 14 days of fixesWeek 615%
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: the hold queue, deputy cover, and any change to approval bands or match tolerance

  • 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$3,000-5,000

About 6 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