How We Manage Agency Receivables with Xero and Airtable

Receivables is one of those workflows nobody thinks about until it's broken. The team is invoicing on time, the bookkeeper is updating Xero, the bank is reconciling — and yet a few months in, someone notices that three clients are 60 days overdue and nobody has chased them. The reason is almost always the same: there's no single place where you can see "who owes us what, for how long, and when did we last say something about it."

This guide walks through the receivables system we use on our own books and deploy for agency clients. It's a layer on top of Xero and Airtable, connected by Make, that gives account managers a working surface and finance a clean ledger — without anyone copying invoice numbers between systems.

The Shape of the System

Three things move between the two systems:

1. Quotes → Invoices. When an account manager marks a quote as Approved, Make creates the invoice in Xero with all line items.
2. Payment status. Every hour, Make checks Xero for invoice status changes and updates the matching Airtable record.
3. Follow-up cadence. When an invoice ages past 7 / 21 / 45 days after due, Make sends staged follow-up emails.

For the foundational invoice push, see our Xero + Airtable invoice tutorial. This guide builds on that base.

The Airtable Schema

Five tables form the core. If you have an existing CRM-style base, you can fold these in alongside Projects and Deals — the patterns work the same.

Clients

• Name (primary)
• Xero Contact ID
• Account Manager (collaborator)
• Email (for invoice send-to)
• Currency (single-select)
• Default Payment Terms (number, days)
• Outstanding Balance (rollup of unpaid invoice totals)
• Aging — 0-30 / 31-60 / 61-90 / 90+ (rollup with conditions)

Quotes

• Quote Number (primary, formula QUO- + autonumber)
• Client (linked → Clients)
• Status (single-select: Draft / Sent / Approved / Lost / Invoiced)
• Approved Date
• Total (rollup of Quote Line Items)
• Notes

Quote Line Items

• Description (primary)
• Quote (linked → Quotes)
• Quantity, Unit Price, Amount (formula)
• Tax Code

Invoices

• Invoice Number (primary, formula INV- + autonumber)
• Client (linked → Clients)
• Source Quote (linked → Quotes, optional)
• Status (single-select: Draft / Ready to Send / Sent / Paid / Overdue / Void)
• Issue Date, Due Date, Paid Date
• Total (rollup of Invoice Line Items)
• Amount Paid (number, updated by Make)
• Xero Invoice ID, Xero Invoice URL
• Days Overdue (formula: IF({Status} = 'Sent', DATETIME_DIFF(TODAY(), {Due Date}, 'days'), 0))
• Follow-up Status (single-select: None / Reminder Sent / Second Notice / Personal Escalation)
• Last Follow-up Date

Invoice Line Items — same shape as Quote Line Items, linked to Invoices.

For the relational patterns, see Linked Records Explained.

The Quote-to-Invoice Flow

Two reasons to manage quotes in Airtable before they ever become invoices:

1. The account manager can build, send, and revise a quote without involving finance.
2. By the time the quote is Approved, the line items are already in Airtable — converting to an invoice is a copy of records, not a re-entry.

The flow:

1. Account manager creates a Quote record, adds line items, sets Status = Sent.
2. Client agrees. Account manager flips Status to Approved.
3. A button on the Quote record (or an automation) creates a new Invoice with the same client and copies the Quote Line Items into Invoice Line Items.
4. The Invoice goes through the same Ready to Send → push to Xero flow described in the base invoice tutorial.

The conversion can be a script action:

const { quoteId } = input.config();
const quotes = base.getTable('Quotes');
const quoteLineItems = base.getTable('Quote Line Items');
const invoices = base.getTable('Invoices');
const invoiceLineItems = base.getTable('Invoice Line Items');

const quote = await quotes.selectRecordAsync(quoteId, {
  fields: ['Client', 'Total'],
});
const lineQuery = await quoteLineItems.selectRecordsAsync({
  fields: ['Description', 'Quantity', 'Unit Price', 'Tax Code', 'Quote'],
});

const lines = lineQuery.records.filter((r) => {
  const q = r.getCellValue('Quote');
  return q && q[0]?.id === quoteId;
});

const today = new Date();
const todayISO =
  today.getFullYear() +
  '-' +
  String(today.getMonth() + 1).padStart(2, '0') +
  '-' +
  String(today.getDate()).padStart(2, '0');

const invoiceId = await invoices.createRecordAsync({
  Client: quote.getCellValue('Client'),
  'Source Quote': [{ id: quoteId }],
  Status: { name: 'Draft' },
  'Issue Date': todayISO,
});

const newLines = lines.map((l) => ({
  fields: {
    Description: l.getCellValue('Description'),
    Quantity: l.getCellValue('Quantity'),
    'Unit Price': l.getCellValue('Unit Price'),
    'Tax Code': l.getCellValue('Tax Code'),
    Invoice: [{ id: invoiceId }],
  },
}));

while (newLines.length > 0) {
  await invoiceLineItems.createRecordsAsync(newLines.splice(0, 50));
}

await quotes.updateRecordAsync(quoteId, { Status: { name: 'Invoiced' } });

Wire a button on the Quote record's Interface Designer page to this script and the conversion is one click.

Pulling Payment Status Back from Xero

The reverse-sync is what makes Airtable a useful receivables view. Without it, the team would still need to open Xero to know whether an invoice is paid.

A second Make scenario runs hourly:

1. Trigger: Schedule (every hour during business hours).
2. Xero — List Invoices: filter to invoices updated in the last 24 hours, status PAID or AUTHORISED or VOIDED.
3. Iterator over the returned invoices.
4. Airtable — Search Records on Invoices by Xero Invoice ID to find the matching record.
5. Filter — only continue if a matching record was found (skip orphan invoices created directly in Xero).
6. Airtable — Update Record with:
   • Status (mapped from Xero status)
   • Amount Paid (sum of payments from Xero)
   • Paid Date (if fully paid)
7. Branch — if the invoice has just moved to Paid and Follow-up Status is anything other than None, post a Slack note to the account manager: "Invoice INV-0123 paid — you can stop chasing this one."

Mapping Xero statuses to your Airtable single-select:

The hourly poll is plenty for a receivables workflow — clients don't notice an hour. If you want real-time, Xero supports webhook subscriptions, but the overhead usually isn't worth it.

The Aging Rollups

The Outstanding Balance and aging fields on the Clients table are what make the dashboard useful. Build them as rollups on the Invoices linked field:

For rollup pattern details, the rollup field's "Only include records that meet conditions" toggle is what gates each bucket.

Now your Clients table at a glance shows every client's outstanding balance broken down by age. Sorting the table by 90+ days descending puts the receivables disasters at the top of the list every morning.

The Follow-up Sequence

Automated follow-ups are where most receivables systems get either too aggressive or too silent. Our cadence:

The Make scenario:

1. Trigger: Schedule (daily at 9am local time).
2. Airtable — Search Records on Invoices where Status = Sent and Days Overdue is 7, 21, 45, or 60 (one row matching exactly per bucket).
3. Iterator over the matched records.
4. Router — branch by the Days Overdue value.
5. On each branch:
   • For 7 and 21 day branches: Gmail Send Email using a template, set From to the account manager's email (loaded from the Client record), attach the invoice PDF (downloaded from Xero in the previous step).
   • For 45 day branch: Slack message to the account manager.
   • For 60 day branch: Slack message to a finance channel.
6. Airtable — Update Record on the Invoice: increment Follow-up Status and set Last Follow-up Date.

Two rules that prevent the system from becoming spammy:

Stop on reply. Add an inbound email trigger that watches for replies to the follow-up thread. When a reply lands, set the invoice's Follow-up Status to "Paused — awaiting human" and skip the next automated step. The account manager handles it from there.

Skip recently contacted. Add a filter to the daily scan: skip any invoice where Last Follow-up Date is less than 5 days ago. Prevents accidental double-sends if you change the cadence.

The Email Templates

Keep them short, write them like a person, and put the payment link prominently. A couple of templates we use:

7-day reminder:

Subject: Quick check on invoice INV-0123

Hi {{Client First Name}},

Hope you're well. Just a friendly nudge — invoice INV-0123 from
{{Issue Date}} (£{{Amount Due}}) is showing as outstanding on
our side. Let me know if you need anything from us to get it
processed.

Pay here: {{Payment URL}}

— {{Account Manager First Name}}

21-day reminder:

Subject: Invoice INV-0123 — 21 days past due

Hi {{Client First Name}},

Following up on invoice INV-0123 (£{{Amount Due}}). It's now 21
days past the due date of {{Due Date}}. Could you let me know
the expected payment date so I can update our records?

I've attached the PDF in case it's helpful.

Pay here: {{Payment URL}}

— {{Account Manager First Name}}

These tend to land well because they don't sound like a finance system. They sound like the account manager dropping a quick note. Which, structurally, they are — the email is genuinely sent from that person's address; only the trigger is automated.

The Dashboard

Build two Interface Designer pages on top of the base.

Finance dashboard. A single page showing:

• Total Outstanding (number — sum of Outstanding Balance across all clients)
• Outstanding by Age (bar chart with 0-30 / 31-60 / 61-90 / 90+ buckets)
• Top 10 Outstanding Clients (filtered list)
• Recent Payments (last 7 days, filtered list)
• Invoices Past 45 Days (filtered list with account manager assignments visible)

Account manager dashboard. Filtered to "current user" so each AM sees only their book:

• My Outstanding Balance (number)
• My Open Invoices (filtered list grouped by status)
• My Quotes Awaiting Approval (filtered list)
• Buttons: Send Reminder Now, Mark Paused, Open in Xero

Both pages share the same data. Each role sees what they need to act.

What This Replaces

Before this system, an agency typically has:

• A weekly meeting where finance and account managers reconcile Xero against memory
• A spreadsheet someone updates by hand for executive reporting
• Account managers asking finance "is X paid yet?" via Slack DM a few times a day
• Overdue follow-ups that happen when someone remembers

After:

• A live dashboard finance opens every morning that shows the same numbers as Xero
• Account managers see their book in their own dashboard, no DMs needed
• Follow-ups happen on a cadence without human prompting
• The weekly meeting becomes a 15-minute exception review instead of a reconciliation

Edge Cases Worth Handling

A handful of situations that come up in production.

Partial payments. Xero tracks them at the payment level. Make pulls the sum of payments into Airtable's Amount Paid field. The display logic in the dashboard formats £{Amount Paid} of £{Total} instead of a single number so partial payments are visible.

Multiple invoices in one payment. Common with retainer clients. Xero allocates the payment across invoices and updates each one separately, so the sync still works. The Make scenario doesn't need to know about the allocation logic — it processes each Xero invoice update independently.

Disputed invoices. Add a Disputed status to the Invoice statuses. Disputed invoices are excluded from automated follow-ups (filter in the daily scan) and show up in a separate dashboard list for the account manager to resolve.

Foreign currency. If you bill in multiple currencies, the Outstanding Balance rollup needs to convert. Easiest fix: maintain a Currency Rate field on each Client (updated manually or by a separate scenario), then compute Outstanding in Base Currency at the invoice level via a formula. The rollup sums the converted value.

Short-pay write-offs. A client pays £4,950 on a £5,000 invoice and you accept the difference. In Xero, you reconcile with a write-off. In Airtable, the Amount Paid will be £4,950 but Status moves to Paid via a credit note. As long as the sync handles both payment status and credit notes, the math works out.

When This Is Overkill

You don't need this system if:

• Your invoice volume is under 10 per month
• All your clients pay on time
• You only have one or two account managers and you all sit in the same room
• You don't currently have a receivables problem

The setup time is real (2-3 days), and it's worth it when you're feeling the pain of either too many overdue invoices or too much manual reconciliation. Smaller agencies can run a lighter version — push invoices to Xero, manually mark paid in Airtable when payments land, skip the follow-up automation.

Where to Go Next

The foundational invoice push is in our Xero + Airtable invoice tutorial. For broader automation patterns, the Make guide covers iterators, routers, and error handling — all of which show up in the scenarios here.

For Xero's API specifics — invoice fields, payment endpoints, credit notes — the Xero Developer Accounting API documentation is the canonical reference.