← All recipes

Weekly AR aging digest from QuickBooks Online (Make)

Every week, get a draft email with open invoices grouped by customer and aged into Current, 1-30, 31-60, 61-90, and 90+ day buckets, sorted by who owes the most.

Steps

1. Add QuickBooks Online → 'Search for Invoices' with the query select * from Invoice where Balance > '0' MAXRESULTS 1000.
2. Add an Array aggregator (source: the search) that collects one item per invoice: DocNumber, Customer = {{1.CustomerRef.name}}, DueDate = {{formatDate(1.DueDate; "YYYY-MM-DD")}}, Balance, and Days past due = {{round((formatDate(parseDate(formatDate(now; "YYYY-MM-DD"); "YYYY-MM-DD"); "X") - formatDate(1.DueDate; "X")) / 86400)}}.
3. In the same aggregator, add a Bucket field with nested if() on that days value: <= 0 Current, <= 30 1-30, <= 60 31-60, <= 90 61-90, otherwise 90+. Also add a CustBucket field of customer name, a pipe, and the bucket (e.g. Acme|31-60) for the per-customer sums.
4. Add an Iterator over the distinct customers: {{distinct(map(2.array; "Customer"))}}.
5. Add an Array aggregator (source: that iterator) with Total = {{sum(map(2.array; "Balance"; "Customer"; 3.value))}} and one sum per bucket, e.g. {{sum(map(2.array; "Balance"; "CustBucket"; join(add(emptyarray; 3.value; "Current"); "|")))}}, repeated for 1-30, 31-60, 61-90, and 90+.
6. Add an Iterator over the customers sorted by total, highest first: {{sort(4.array; "desc"; "Total")}}.
7. Add a Text aggregator that builds one HTML table row per customer (name, each bucket, and total), formatting every amount as {{formatNumber(5.Total; 2; "."; ",")}}.
8. Add Microsoft 365 Email → 'Create a Draft Email' addressed to yourself, content type HTML, subject AR aging – {{formatDate(now; "YYYY-MM-DD")}}, with bucket totals (e.g. {{formatNumber(sum(map(4.array; "cur")); 2; "."; ",")}}), the grand total {{formatNumber(sum(map(2.array; "Balance")); 2; "."; ",")}}, and the table rows. Keep it as a draft.

Tips

• Pass the separators to formatNumber() explicitly: formatNumber(value; 2; "."; ","). Left at the default, amounts came out European-style (3.756,02).
• Sub-customers show up as their own rows. QuickBooks' report groups them under the parent, so totals match but the layout is different.
• Once it works, set the scenario schedule to weekly (e.g. Monday morning).
• Test it in a QuickBooks sandbox first and tie the totals to the A/R Aging Summary report for the same date.

Tested: Tested against a QuickBooks Online sandbox company with 20 open invoices: every aging bucket, the grand total, and each customer's amounts matched QuickBooks' own A/R Aging Summary exactly; a draft was created, not sent.

Keep a human check here: Low risk

Steps are based on the vendor docs as of Oct 2026; verify module names in your account. Apps rename triggers and actions sometimes. Try it with test data or a sandbox company first.

Build it

Related recipes