Cross-system questions: joining Autotask tickets with Xero invoices using an AI agent

Why this question is hard

Xero knows who owes money. Autotask knows who is raising tickets. Neither knows that "Acme Pty Ltd" in Xero and "ACME" in Autotask are the same client.

The usual ways to answer it are all slow or expensive:

How the join matches companies

A cross-system join needs a shared identity. actionlake maps each connector's records onto shared entities, such as crm.company, finance.invoice and service.ticket, through the connector's own entities block. Two rows are the same company when they agree on the strongest identifier both sides carry:

1. Tax number, when both systems hold one.
2. Domain, normalised the way two systems would differ.
3. Name, normalised: case, punctuation and suffixes such as Pty, Ltd, Limited, Inc and LLC are ignored, so "Acme Pty Ltd" and "ACME Limited" match.

Inside one vendor, a record's reference to its company is exact. An Autotask ticket carries its company's id, so tickets join to Autotask companies by reference with no guessing. Across vendors, ids never match each other; the company's name or tax number does.

That is why this question has three fetch steps. Xero invoices meet Autotask companies by name or tax number. Autotask tickets meet the same companies by reference.

Before you start

You need both systems connected in one actionlake tenant, and a key that may read both:

• Xero: see connect Claude to Xero.
• Autotask: see connect Claude to Autotask.
• A key with ["xero.read:*", "autotask.read:*"] is enough. ask only reads.

The ask call

The person asks in plain words: "Which clients are more than 30 days overdue and still have open tickets?" The agent sends this:

{
  "intent": "The owner asked which overdue clients are still raising support tickets.",
  "steps": [
    { "action": "xero.invoices.list", "input": { "status": "AUTHORISED" }, "limit": 100, "max_pages": 20, "as": "inv" },
    { "action": "autotask.companies.query", "input": {}, "limit": 200, "max_pages": 10, "as": "companies" },
    { "action": "autotask.tickets.query", "input": { "last_activity_after": "2026-09-22T00:00:00Z" }, "limit": 200, "max_pages": 10, "as": "tickets" },
    { "join": {
        "left": "inv", "right": "companies", "on": "crm.company",
        "where": ["inv.days_overdue >= 30"],
        "group_by": "crm.company",
        "select": ["sum(inv.amount_due) as overdue", "count(inv.id) as invoices"]
    } },
    { "join": {
        "left": "companies", "right": "tickets", "on": "crm.company",
        "where": ["tickets.status != closed"],
        "group_by": "crm.company",
        "select": ["count(tickets.id) as open_tickets"]
    } }
  ]
}

What each part does:

• Fetch steps. Each names an action, its input and a page size (limit). max_pages bounds what a step may spend; without it, ask pages to the end so a join never runs over half the data.
• days_overdue is derived from each invoice's due date. Any connector that maps due_date gets it.
• where filters after the join. tickets.status != closed uses the word the connector maps; the vendor's own code works too.
• group_by: crm.company gives one row per client, labelled with its name.
• Two joins, one call. The first says which Autotask clients are overdue in Xero. The second counts their open tickets. Both answers come back together, keyed by company name, and the agent reads them side by side.

What comes back

The answer is the joined table, not the rows. An illustrative result:

join 1  inv × companies on crm.company      matched 4 · unmatched inv 31, companies 112
  Acme Pty Ltd          overdue 10,400.00   invoices 2
  Harbour Dental        overdue  4,120.00   invoices 1
  ...
join 2  companies × tickets on crm.company  matched 58
  Acme Pty Ltd          open_tickets 3
  Harbour Dental        open_tickets 1
  ...
billed_calls 6 · the join is free

Every join reports its unmatched rows on both sides. A join that matched nothing says so; it is never a silent empty table. If a step was cut short by max_pages, the answer is marked partial.

When names differ more than suffixes and punctuation, add "match": "fuzzy". Near-miss pairs come back with a match_score, so you can review them rather than trust them blindly.

Check it first, free

The agent can send the same payload to check before it runs. check validates each step against its action's schema, confirms the key may run every step and both connections exist, refuses a join whose sides cannot reach crm.company, and estimates the calls. It sends nothing to Xero or Autotask and costs nothing. See dry run.

What it costs

If the three steps above fetch six pages, the question costs six upstream calls, well inside the Free plan's 100,000 a month. Asked again within the cache window, it can cost nothing. On Starter, US$49 a month covers 250,000 calls. See pricing.

There is a token saving too. The model never reads the invoices or tickets, only the two short tables. We do not publish a percentage, because it depends on your data; the mechanism is that rows stay in the gateway.

Run it every Monday

A question asked weekly should be a plan. The agent saves the same steps with plans.save, and a schedule runs it:

{
  "op": "schedules.save",
  "name": "overdue-and-busy",
  "plan": "plan.overdue-and-busy",
  "cron": "0 8 * * 1",
  "timezone": "Australia/Sydney",
  "intent": "Weekly list of overdue clients with open tickets, for the account managers."
}

There are no triggers or webhooks in actionlake by design. A schedule plus "what changed since the last run?" covers most cases. See agent plans.

Other pairs that work the same way

The same pattern joins any two connectors that map a shared entity: