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 or bill, 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. |
Planned Payment Date | Date | The date, entered in Xero, when an Invoice or Bill is Planned for Payment. |
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. |