Overview

The Invoices table contains both Sales and Bills (aka Purchase) Invoices, as well as any respective credit notes.

Amounts are signed so that they can be summed to create totals. Positive amounts are income and negative amounts are used for expenses.

Invoices provide a rich view of the financial transactions with Customers and Suppliers but because they don't hold all financial transactions (for example Salary and Tax) you will not see all financial data working with Invoices alone.

Invoice Amounts

To make reporting easier and provide access to Invoice line descriptions, product codes and amounts without needing to switch between an invoice header record and invoice details, a record is returned for every Invoice Line of the Invoice.

The Invoice Due, Credited, Paid, Net, Remaining, Gross, Tax and their reporting currency equivalents, are split proportionately for the Invoice across all Invoice lines.

This may not be immediately obvious in Power BI if, for example, you pull out the Net Amount and Invoice Number, because Power BI automatically sum amounts.

No need to worry about Tax Exclusive or Inclusive

If you've previously worked with raw Xero data, you'll have had to cope with the conversion of the two different Invoice representations; either exclusive or tax or inclusive.

Don't worry. We've done that so you don't need to.

Forecasting with Repeating Invoices

To simplify the forecasting of future sales or bills, Connectorly automatically creates Invoice records for any repeating invoices according to their schedule that is yet to be created, up to three years in the future (or until the end date that is set).

These invoices are distinguished by a Status of FORECAST, which joins the Xero values of AUTHORISED, DRAFT, PAID and SUBMITTED.

Multi Currency Xero Support

As well as providing the Net, Tax and Gross values of the original Invoice or Credit Note, values for your chosen Reporting Currency are calculated. These are signified by (RC) after their name.

If you are working with Xero data from multiple organisations or even from a single multi-currency Xero, use the Reporting Currency (RC) values for any calculations, to avoid adding amounts that are in different currencies.

Data Model

Field	Type	Notes
Account Code	Text	The Account Code for the invoice line. e.g. 200
Account Name	Text	The corresponding Account Name. e.g. Sales If want to report and link other sources of financial information (for example to link Budgets and Invoices data to compare actuals and forecasts) then use the linked Account Name field from the Accounts table.
Amount Credited	Number	The Amount of any credit notes applied to the invoice in the currency the Invoice was transacted in.
Amount Credited (RC)	Number	The Amount of any credit notes applied to the invoice in the reporting currency.
Amount Due	Number	The Amount still due in the currency the Invoice was transacted in.
Amount Due (RC)	Number	The Amount still due in the in the reporting currency.
Amount Paid	Number	The Amount paid in the currency the Invoice was transacted in.
Amount Paid (RC)	Number	The Amount paid in the in the reporting currency.
Connection Name	Text	The name of the Connection. Typically the name of the Xero Organisation.
Contact ID	Text	The primary key for the Contact.
Contact Name	Text	The name of the Contact (Supplier or Customer).
Currency Rate	Number	The FX Rate used to calculate the Reporting Currency Amounts. Rates are taken as the closing rates as reporting by the ECB on the invoice date.
Data current as of	Datetime	When the Data was taken from Xero.
Description	Text	The Invoice Line Description.
Due Date	Date	The date the Invoice is due for Payment by.
Expected Payment Date	Date	Uses the Xero Planned Payment Date if one exists. If not it is calculated based on the Average Days to pay for the Contact or if this is the first invoice the Due Date.
Fully Paid On Date	Date	The date on which the Invoice was fully paid.
Gross Amount	Number	The Gross Amount (Net and Tax) in the currency the Invoice was transacted in.
Gross Amount (RC)	Number	The Gross Amount (Net and Tax) in the reporting currency.
Has Attachments	Logical
Invoice Date	Date	The date of the invoice. Also known as the Tax Point date.
Invoice Number	Text
Invoice Type	Text	From Xero. {ACCPAY', 'ACCPAYCREDIT', 'ACCREC', 'ACCRECCREDIT} ACCPAY are Bills (or Purchase) Invoices ACCREC are Sales Invoices. Types ending CREDIT are Credit notes for the respective prefixes.
Is Discounted	Logical
Last Updated	Datetime	When the Invoice was created or updated last.
Net Amount	Number	The Net (without Tax) amount of the Invoice in the currency it was transacted in.
Net Amount (RC)	Number	The Net (without Tax) amount in the reporting currency.
Past Due Interval	Duration	How long past the Due date the invoice remains unpaid.
Product Code	Text
Quantity	Number	The number of items on the Invoice Line Item.
Reference	Text	The Invoice Reference often holds a Purchase Order number.
Remaining Past 0d	Number	An Invoice with an amount that is past the Due Date.
Remaining Past 30d	Number	An Invoice with an amount that is 30 days past the Due Date.
Remaining Past 60d	Number	An Invoice with an amount that is 60 days past the Due Date.
Remaining Past 90d	Number	An Invoice with an amount that is 90 days or more past the Due Date.
Remaining Past Due	Number	An Invoice that is overdue.
Reporting Currency	Text	The ISO 3 letter currency code.
Sent To Contact	Text
Status	Text	{'AUTHORISED', 'DRAFT', 'FORECAST', 'PAID', 'SUBMITTED'}
Tax Amount	Number	The Tax amount of the Invoice in the currency it was transacted in.
Tax Amount (RC)	Number	The Tax amount of the Invoice in the reporting currency.
Tax Type	Text	From Xero. e.g. {'EXEMPTINPUT', 'INPUT2', 'NONE', 'OUTPUT2', 'RRINPUT'}
Tracking Category 0	Text
Tracking Category 1	Text
Tracking Option 0	Text
Tracking Option 1	Text
Transaction Currency	Text	The ISO 3 letter code of the Invoice transaction currency.
Unit Amount	Number	The Unit Line Amount in the transaction currency.

The Connectorly Xero Data Model