Post QuickBooks payables aging to Microsoft Teams
What the company owes, and for how long, in the finance team's Teams channel before every payment run. QuickBooks goes into your data warehouse first, where the snapshot is read next to bills and payments, and only then reaches Teams.
%20scale(1.25)%22%20fill%3D%22%23fff%22%20d%3D%22M12%200A12%2012%200%200%200%200%2012a12%2012%200%200%200%2012%2012%2012%2012%200%200%200%2012-12A12%2012%200%200%200%2012%200zm.642%204.1335c.9554%200%201.7296.776%201.7296%201.7332v9.0667h1.6c1.614%200%202.9275-1.3156%202.9275-2.933%200-1.6173-1.3136-2.9333-2.9276-2.9333h-.6654V7.3334h.6654c2.5722%200%204.6577%202.0897%204.6577%204.667%200%202.5774-2.0855%204.6666-4.6577%204.6666H12.642zM7.9837%207.333h3.3291v12.533c-.9555%200-1.73-.7759-1.73-1.7332V9.0662H7.9837c-1.6146%200-2.9277%201.316-2.9277%202.9334%200%201.6175%201.3131%202.9333%202.9277%202.9333h.6654v1.7332h-.6654c-2.5725%200-4.6577-2.0892-4.6577-4.6665%200-2.5771%202.0852-4.6666%204.6577-4.6666Z%22%2F%3E%0A%3C%2Fsvg%3E%0A)
QuickBooksConnectedA/P Aging Summar…A/R Aging Summar…AccountsBalance Sheet re…
Microsoft TeamsRealmReportReportDaily 09:00QuickBooks Endpoints
26 QuickBooks endpoints you can import. Each lands in its own table in your data warehouse.
A/P Aging Summary report
A/P Aging Summary as of report_date (window end): one row per vendor with aging buckets.
11 fieldsTime seriesA/R Aging Summary report
A/R Aging Summary as of report_date (window end): one row per customer with Current, 1-30, 31-60, 61-90, 91+ and Total columns.
11 fieldsTime seriesAccounts
Chart of accounts, active and inactive.
20 fieldsBalance Sheet report
Balance Sheet as of the run window end (Assets, Liabilities, Equity by account).
12 fieldsTime seriesBill payments
Payments against vendor bills; joins to Bill through Line[].LinkedTxn.
23 fieldsBills
Vendor bills (payables).
25 fieldsCash Flow report
Statement of Cash Flows for the run window.
12 fieldsTime seriesCompany info
One row per company: names, country, fiscal-year start.
15 fieldsCredit memos
Credits issued to customers (reduce receivables; negative revenue in reconciliations).
23 fieldsCustomers
Customers, including inactive ones and sub-customers, with open balance and a flattened billing address.
28 fieldsDeposits
Bank deposits, including the ones that group undeposited customer payments.
21 fieldsGeneral Ledger Detail report
General Ledger: every posting line per account for the run window, with opening balance and totals.
12 fieldsTime seriesInvoice changes (CDC, incl. deletes)
Change data capture for Invoice: every invoice created, updated or deleted since the run window start, full payload, deleted ones with status = Deleted.
9 fieldsTime seriesInvoices
Invoices with header amounts, customer and Line[] as JSON (SalesItemLineDetail.ItemRef, Qty, UnitPrice, Amount).
36 fieldsItems
Products and services with price, purchase cost and the three account links.
25 fieldsJournal entries
Manual and adjusting journal entries; the postings the transaction tables miss.
15 fieldsPayments
Customer payments.
21 fieldsPreferences
One row per company: home currency, multi-currency flag, fiscal-year and tracking settings.
17 fieldsProfit and Loss report
Profit and Loss per run window, one column per month (summarize_column_by=Month), rows nested by account group.
12 fieldsTime seriesPurchases
Expenses paid directly (cash, check, card) without a bill.
26 fieldsRefund receipts
Money refunded to customers.
26 fieldsSales by Customer report
Sales by Customer Summary for the run window.
12 fieldsTime seriesSales receipts
Sales paid at the time of sale (no receivable).
27 fieldsTransfers
Money moved between balance-sheet accounts.
16 fieldsVendor Expenses report
Expenses by Vendor for the run window.
12 fieldsTime seriesVendors
Vendors, including inactive ones, with open payable balance.
19 fields
Why QuickBooks does not post straight to Microsoft Teams
QuickBooks holds payables aging as a report you open. A Teams channel reaches colleagues who never open it, so the figure has to arrive with its causes. The snapshot is kept and joined to bills in the warehouse first.
- A payables aging report is only comparable with last month's if it was saved
- A Teams channel reaches approvers who need the definition with the figure
- The data analyst owns the join to bills and the wording
QuickBooksAging snapshotMicrosoft Tea…
Data warehouseJoined by idStep 1: Load QuickBooks into your data warehouse first
Create a Data Mart with QuickBooks as the source and choose where the data lands. The QuickBooks connector is a custom connector built in OWOX Data Marts, and the data goes into any popular data warehouse.
- The A/P Aging Summary report is a dated snapshot among the 26 endpoints
- Authorize QuickBooks Online with OAuth and give the company (realm) id
- Open the QuickBooks connector for your data warehouse below
QuickBooksData MartGoogle BigQuery
AWS Athena
Snowflake
AWS Redshift
Databricksquickbooks.report_aged_payabl…Step 2: Link QuickBooks payables aging to bills and payments
In your data warehouse the model joins A/P aging, Bills and Bill payments on realm_id, so each aging row can be traced, by the vendor id inside report_rows, to the bills still open and the payments already made.
- Every payables snapshot keeps the company and the time it was run
- Bills and bill payments say what moved a bucket
- One definition of payables serves the channel and the close
Step 3: Post QuickBooks reports to a Teams channel
Create a Microsoft Teams destination in OWOX with the email address of the channel, taken from the channel menu in Teams, then add a report to the published QuickBooks Data Mart. Write the message in Markdown and check it in Preview.
- Every Teams channel can receive email, and OWOX sends the report to that address
- Type / to place the A/P Aging Summary report table: report_time, end_period, currency, report_columns
- Create & Run posts the first report straight away
Microsoft TeamsWeeklyWeekly payables agingreport_timeend_periodcurrencyreport_columnsStep 4: Put a short QuickBooks analysis under the table in Teams
Add two or three lines beneath the table in the same message. Pick a send condition on the report, and open the Triggers tab to set the day and time of the run.
- The lines rest on report_time and currency from the table above them
- Figures come from a fresh run of the Data Mart before each message
- Send always, only when empty or only when not empty: chosen per report
Microsoft TeamsWeekly payables agingreport_timeend_periodcurrencyreport_columnsAnalysisreport_columns names the age buckets, current to oldestreport_rows has one row per vendorcurrency matters when bills are paid in more than oneStep 5: Reuse QuickBooks payables aging
The Data Mart behind your Teams message also works in OWOX, where your assistant for data can read it. Ask about any report, then get it in Google Sheets or Excel in one click.
- Ask which bucket grew since the last snapshot
- Change the report once and the next Teams message reads the change
Google SheetsOpen
ExcelOpen
- 0.41.0Dates from Google Sheets arrive as dates, not text
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.40.0Connecting a plan from Google Sheets takes fewer guesses
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for QuickBooks in Teams
A payables report opened in QuickBooks reaches whoever opens it. OWOX puts it in front of the people who approve payments, with its context.
- Payables in context. The snapshot is joined to bills and bill payments before it is posted.
- You own the data. QuickBooks lands in your own data warehouse, in tables you control, before anything reaches Teams.
- Approvers see it first. The message lands where payment runs are agreed.
- One place to fix. When QuickBooks's API changes, you publish one new connector version, so the Teams setup is left alone.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.