Work snippet 19 / 34Purchasing + approvals
Airtable purchase orders into Xero, with approval levels and an audit trail
How I'd approach purchase orders for a high-end builder with a dozen live sites, so every order is approved before it is placed, checked against the cost code budget, matched to the delivery and the bill, and recorded in a log that stands up to audit.
Work snippet with example figures.
- Area
- Purchasing + approvals
- Tools
- Airtable · Xero · Make
- Typical scope
- US$3,000–5,000
- Timeline
- About 6 weeks · 4 fixed milestones
01Context
A high-end builder working across residential and commercial fit-out, with a dozen live sites, project managers, site managers, an accounts team and two directors. Orders are raised on site and by project managers, and the accountant wants to know who approved what and when.
The systems in play
In the example, purchase orders are raised by email and a shared sheet, bills arrive in Xero days later, and the budget by cost code is checked after the fact. The company wants approvals before the order, and a record that stands up to an audit.
| System | What it does for the business |
|---|---|
| Airtable base | Jobs, cost codes with budgets, suppliers, purchase orders, receipts and the approvals log. Where orders are raised and approved. |
| Xero | The ledger, suppliers and bills. Receives approved purchase orders and the bills that match them. |
| Sync flows | Create or update the Xero purchase order when approval is complete, and read back bills that carry a PO reference. Make in the example. |
| Site and project managers | Raise orders and record deliveries. |
| Directors and finance lead | Approve above their thresholds and verify new suppliers. |
| Accountant | Reads the approvals log at year-end and during audit. |
02Problem
What the owner sees
- Orders placed before anyone approved them
- Budgets that are overspent before the bill arrives
- Supplier bills with no purchase order behind them
- No answer to "who approved this, and what did it say when they did?"
A purchase order is a promise to pay. When it lives in email, the amount can change after approval, the approver can be forgotten and the budget is only checked when the bill lands. The fix is a short, strict process and a log that cannot be quietly edited.
| Everyday situation | What goes wrong |
|---|---|
| Order raised by phone | The supplier delivers and the PO is written afterwards |
| Value changed after approval | The approved amount and the amount ordered are no longer the same |
| Budget not checked | A cost code is overspent and nobody sees it until the bill |
| Part delivery | The bill is for part of the order and the rest is forgotten |
| New supplier or changed bank details | A payment goes to the wrong account |
| Approver away | Orders wait, or someone approves on their behalf by email |
03Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookA read-only review of 12 months of purchase orders, bills and the budget sheet. Orders with no approval trail and bills with no order are counted.
- Agree the rulesApproval levels, budget checks, tolerance on bills, change rules and supplier checks are written down.
- Build and connectThe Airtable base is built around those rules. Approved orders reach Xero as purchase orders. Bills are read back and matched.
- CheckScripted scenarios, a three-way match on past orders and a review of the log with the accountant before go-live.
| Order value (US$) | Approver | Extra condition |
|---|---|---|
| Under 2,500 | Site manager | Within the cost code budget |
| 2,500 to 14,999.99 | Project manager | Within the cost code budget |
| 15,000 to 49,999.99 | Project manager and a director | Both approve, in that order |
| 50,000 and over | Two directors | Finance lead confirms the budget |
| Any value over budget | A director | A budget transfer or a reason, recorded |
| New supplier | Finance lead | Tax ID and bank details checked before the first order |
| Rise of more than 5% or 500 after approval | The same approvers again | The order returns to approval as a new version |
04What the setup includes
What the setup includes, and why each piece is there. The detail of how it is configured is agreed in the scoping call.
Show the setup, layer by layer
| Layer | What is set up and why |
|---|---|
| Jobs and cost codes | Each job carries its budget by cost code, with committed and billed amounts worked out from the orders and bills, so the remaining budget is always visible. |
| Purchase order form | Raised against a job and cost code, with lines, a supplier, a delivery date and a version number. A number such as J2217-0045 ties it to the job. |
| Approval engine | Works out the approvers from the value, the budget check and the supplier status. Nothing reaches Xero before the last approval. |
| Approvals log | An append-only table: each action creates a row with the order, version, action, person, time, value and comment. Rows are never edited. A nightly export is stored outside Airtable. |
| Receipts | Deliveries recorded against order lines, in full or in part, so the bill can be matched to what arrived. |
| Supplier control | New suppliers and bank detail changes are held for the finance lead, who confirms them by calling a known number. |
| Xero sync | Approved orders are created or updated in Xero as purchase orders. A cancelled order is cancelled in both places. |
| Bill matching | Bills in Xero with a PO reference are read back and compared to the order and the receipt, with a tolerance. |
How the data moves
- Order raised against job and cost code
- Budget check
- Approvals in order
- Approved order in Xero
- Delivery recorded
- Bill arrives in Xero
- Three-way match
- Exceptions list
05Edge cases
These are agreed with the finance lead and the directors and tested with scripted scenarios.
| Situation | How it is handled |
|---|---|
| Value rises after approval | Within the limit, the change is logged. Above it, the order returns for approval as a new version and the Xero order is updated only after that. |
| Approver away | A named delegate is set in advance with a start and an end date, and each delegated action is labeled in the log |
| Approver is also the person who raised the order | Not allowed. The next level approves. |
| Order split to stay under a threshold | Orders to the same supplier for the same job and cost code within seven days are added together for the approval level |
| Part delivery | The order stays open for the balance. A bill for part is matched to the receipt, not the order. |
| Bill with no order | Held on the exceptions list and not paid until a director approves a retrospective order |
| Bill above the order | Matched within the tolerance. Above it, the bill is held and the difference is explained or the order is revised. |
| Supplier credit note | Matched to the original order and receipt and shown on the job |
| Order cancelled after approval | Cancelled with a reason in both systems, and its amount returns to the budget |
| Deleted or edited log rows | The log is append-only by design, and a nightly export makes a change visible |
| Urgent order out of hours | A short form with a director’s approval by message, recorded in the log within one working day |
06Worked example
The tables below use one job and five orders with example figures. They show the shape of the checks, not a client result.
| Item | Amount |
|---|---|
| Budget for the cost code | 120,000.00 |
| Committed on approved orders | 96,500.00 |
| Billed so far | 61,200.00 |
| Remaining budget not yet committed | 23,500.00 |
| New order PO J2217-0045 | 28,000.00 |
| Over budget by | 4,500.00 |
| Outcome | A director approves, with a budget transfer from cost code 09 Contingency |
| Version | Action | Person | When | Value (US$) | Comment |
|---|---|---|---|---|---|
| 1 | Raised | Site manager | Day 1, 08:14 | 28,000.00 | Slab pour, level 2 |
| 1 | Budget check | System | Day 1, 08:14 | 28,000.00 | Over budget by 4,500.00 |
| 1 | Approved | Project manager | Day 1, 11:02 | 28,000.00 | Within project manager level |
| 1 | Approved | Director | Day 1, 16:40 | 28,000.00 | Transfer 4,500.00 from 09 Contingency |
| 1 | Sent to Xero | System | Day 1, 16:41 | 28,000.00 | Purchase order created |
| 2 | Change raised | Project manager | Day 4, 09:20 | 30,100.00 | Rise of 7.5%, returns for approval |
| 2 | Approved | Director | Day 4, 13:05 | 30,100.00 | Additional pump hire |
| 2 | Updated in Xero | System | Day 4, 13:06 | 30,100.00 | Purchase order updated |
| Order | Ordered | Received | Billed | Result |
|---|---|---|---|---|
| J2217-0045 | 30,100.00 | 30,100.00 | 30,100.00 | Matched |
| J2217-0046 | 9,600.00 | 9,600.00 | 9,840.00 | Held: billed 240.00 (2.5%) above the order, tolerance is 2% |
| J2217-0047 | 4,200.00 | 3,150.00 | 3,150.00 | Matched to the receipt; 1,050.00 stays open |
| J2217-0048 | 15,500.00 | 15,500.00 | Received, not yet billed: on the unbilled receipts list | |
| No order | 2,250.00 | Held: bill with no order |
07How the result is checked
The checks prove two things: the process cannot be bypassed, and the log tells the truth. They run on scripted scenarios before go-live and on a regular schedule afterwards.
| Check | How | Pass condition |
|---|---|---|
| Approval levels | A scripted order at each threshold and over budget | The right approvers are required, in the right order, every time |
| No bypass | An unapproved order, an edited value and a self-approval attempt | Nothing reaches Xero and each attempt appears in the log |
| Log integrity | Nightly export compared to the live log | No row changed or removed |
| Xero agreement | Approved orders in Airtable compared to purchase orders in Xero | Equal in number, supplier and value |
| Budget | Committed and billed by cost code compared to the orders and bills | Equal to the cent |
| Past orders | Three-way match run over 12 months of history | Every difference explained or on the list |
| Supplier changes | Bank detail changes in the last quarter | Each confirmed by the finance lead with a note |
| Accountant review | A read-through of the log and a trace of five orders from request to bill | Each trace complete without asking anyone |
08Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Audit | Read-only review of 12 months of orders, bills and the budget sheet, a 45-minute call, written findings and a fixed quote |
| Rules | Approval levels, budget checks, tolerance and supplier controls in writing, signed by the directors and the finance lead |
| Airtable base | Jobs, cost codes, suppliers, orders, receipts and the approvals log, with views for each role |
| Approval engine | Approvers worked out from value, budget and supplier status, with delegation |
| Xero sync | Approved orders created and updated in Xero, with a cancel path and a duplicate guard |
| Bill matching | A three-way match with a tolerance and an exceptions list |
| Audit pack | A nightly export of the log and a short guide for the accountant |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Paying suppliers. Processing bills beyond the matching check. Replacing Xero or Airtable. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and rules | Findings, a fixed quote and the approval rules in writing | Week 1 | 20% |
| M2 Base and approvals | Orders, budgets and approvals working on a pilot job | Weeks 2 to 3 | 30% |
| M3 Xero sync and matching | Orders reach Xero once, bills matched, scenarios pass | Weeks 4 to 5 | 35% |
| M4 Go-live and handover | Live on all jobs, audit pack, SOPs, handover video, 14 days of fixes | Week 6 | 15% |
Typical scope for this kind of project: US$3,000–5,000
About 6 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I'll say where I'd look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.