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 Dictionary

Field	Type	Notes
Account Code	Text	Account Code
Account Name	Text	Account Name
Address Line 2	Text	Address Line2
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 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 reporting currency.
Attention To	Text	Attention To
Connection Name	Text	The name of the Connection. Typically the name of the Xero Organisation.
Contact ID	Text	The unique identifier for the Contact. This is guaranteed to be unique within a Xero Organisation.
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	Text	The date the Invoice is due for Payment by.
Expected Payment Date	Text	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 or bill, the Due Date.
Fully Paid On Date	Text	The date on which the Invoice was fully paid.
Gross Amount	Number	The Gross Amount in the invoice currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts and can be summed across all invoice line items..
Gross Amount (RC)	Number	The Gross Amount in the reporting currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts.
Internal URL	Text	A deeplink to the internal URL of the Invoice in Xero - use this to get to the Xero details screen for the invoice (or credit note).
Invoice Date	Text	The date of the invoice. Also known as the Tax Point date.
Invoice Number	Text	Invoice Number
Invoice Type	Text	From Xero.\n\n\n{ACCPAY', 'ACCPAYCREDIT', 'ACCREC', 'ACCRECCREDIT}\n\n\nACCPAY are Bills (or Purchase) Invoices\nACCREC are Sales Invoices.\n\n\nTypes ending CREDIT are Credit notes for the respective prefixes.
Is Discounted	Boolean	True if the invoice has been discounted.
Last Updated	Text	When the Invoice was created or updated last.
Line Amount	Number	Line Amount
Line Amount Type	Text	Line Amount Types
Line Item ID	Text	Line Item ID
Net Amount	Number	The Net Amount in the invoice currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts and can be summed across all invoice line items.
Net Amount (RC)	Number	The Net Amount in the reporting currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts.
Planned Payment Date	Text	The date, entered in Xero, when an Invoice or Bill is Planned for Payment.
Product Code	Text	Item Code
Quantity	Number	The number of items/products on the Invoice Line Item.
Reference	Text	The Invoice Reference often holds a Purchase Order number.
Remaining Past 0D	Number	The amount (in the invoice currency) of an Invoice that is past the Due Date.
Remaining Past 30D	Number	The amount (in the invoice currency) of an Invoice that is 30 days past the Due Date.
Remaining Past 60D	Number	The amount (in the invoice currency) of an Invoice that is 60 days past the Due Date.
Remaining Past 90D	Number	The amount (in the invoice currency) of an Invoice that is 90 days past the Due Date.
Remaining Past Due	Number	The amount (in the invoice currency) of an Invoice that is overdue.
Reporting Currency	Number	The ISO 3 letter code of the Reporting Currency.
Sent To Contact	Boolean	True if the Invoice has been sent to the Contact
Status	Text	{'AUTHORISED', 'DRAFT', 'FORECAST', 'PAID', 'SUBMITTED'}
Tax Amount	Number	The Tax Amount in the invoice currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts and can be summed across all invoice line items..
Tax Amount	Number	The Tax amount of the Invoice in the currency it was transacted in.
Tax Amount (RC)	Number	The Tax Amount in the reporting currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts.
Tax Type	Text	From Xero.\ne.g. {'EXEMPTINPUT', 'INPUT2', 'NONE', 'OUTPUT2', 'RRINPUT'}
Tracking Category 0	Text
Tracking Category 1	Text
Tracking Option 0	Text	Tracking Options are concatenated with the Tracking Category e.g. '[Tracking Category 0] - [Tracking Option 0]'
Tracking Option 1	Text	Tracking Options are concatenated with the Tracking Category e.g. '[Tracking Category 1] - [Tracking Option 1]'
Transaction Currency	Text	The ISO 3 letter code of the Invoice transaction currency.
Unit Amount	Number	The Unit Amount in the invoice currency. Note that Xero Invoice data is normalised to provide Net Amounts, Gross Amounts and Tax Amounts and can be summed across all invoice line items.
Unit Amount	Number	The Unit Line Amount in the transaction currency.