Xero Connection
This documentation is based on version 21.0.8257 of the connector.
This documentation is based on version 21.0.8257 of the connector.
The Jitterbit Connector for Xero models Xero Accounting, Australian Payroll, Files, Assets and Projects objects as a relational database.
The connector supports the following Xero APIs:
It is also recommended that you set the Tenant property, which can be the name or ID of a Xero organization. Xero allows you to authorize the connector to access multiple organizations. By default the connector will pick the first one, which may not be the one you expect if the connector is granted access to additional organizations.
If you need to confirm what organizations the connector has access to, you can connect without an Tenant and read the Tenants view. This will provide both the ID and the name of each connected organization. You should use the ID to set the Tenant property when possible since multiple organizations can have the same name.
All connections to Xero are authenticated using OAuth. The connector supports using PKCE applications and OAuth applications. For desktop applications, the connector's default application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.
When the connector starts, it will open a browser and Xero will request your login information. The connector will use the credentials you provide to access your Xero data. These credentials will be saved and automatically refreshed as needed.
Xero uses the OAuth authentication standard. The connector facilitates this in various ways as described below.
See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.
See Creating a Custom OAuth App to register your application with Xero. Ths is only recommended if you need to cutomize the authorization screen itself, such as providing your own callback URL or branding.
You can connect without setting any connection properties for your user credentials.
When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.
When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:
To create Xero data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.
Creating a custom OAuth auth code app is required for the following flow, as PKCE is not supported with it. See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.
Set the following properties on the headless machine:
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
After the OAuth settings file is generated, set the following properties to connect to data:
Instead of completing the above OAuth flow by hand, you may also follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.
This flow does not require the use of a custom OAuth application.
On a second machine, install the connector and connect with the following properties set:
Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
The following sections show how to create a custom OAuth app for use in the Custom Credentials authentication flow.
Follow the steps below to register a public application to obtain the OAuth client credentials:
After you click Save, you are shown your OAuth Client ID and can create a new OAuth Client Secret. You will set these to OAuthClientId and OAuthClientSecret in the authenticate guide, Custom Credentials.
In addition to the default auth code application type, you can also create applications using PKCE. This is recommended for developers writing client side applications because PKCE does not require a client secret.
Registering a PKCE application is the similar to the process for registering an OAuth application. The only difference that Xero does not return an OAuthClientSecret because it is not used in the PKCE flow.
The Xero API has usage limitations that may be encountered while using the Jitterbit Connector for Xero. Note that all of these apply on a per-application and per-organization basis. An application may exceed these limits if it is querying multiple organizations as long as it obeys the limits within each organization.
At most 5 API calls from the application may be active against the same organization at once. Typically this limit is not an issue because the connector will automatically retry the current request if this occurs.
At most 60 API calls per minute may be made from the application to the same organization. This limit is also not an issue as Xero reports a recommended delay time that the connector will use to avoid hitting the limit again.
At most 5000 API calls per day may be made from the application to the same organization. Hitting this limit is rare but can happen with certain tables (such as history tables and some reports) or certain settings (such as AutoExpandDetails) are used, as they require the connector to make a single API call for each invoice/contact/etc. that is read.
If this limit is hit frequently then the first step should be avoiding the tables or settings that trigger it. It is also possible to convert the queries into versions which read fewer rows at one time. This is usually done by replacing simple selects with subqueries that pick out small groups of rows, and then using multiple versions of these queries at different times:
/* Retreives history for about 1/16 of the invoices */ SELECT * FROM HistoryInvoices WHERE InvoiceId IN ( SELECT InvoiceId FROM Invoices WHERE InvoiceId LIKE '0%' )
The RetryDailyLimit option is also available but its use is strongly discouraged, as in the worst cases the delays that Xero suggests can span several hours during which the connector will be unavailable. It is typically better to restructure how you request data to fit within the limits than to ignore the limits and execute queries that take multiple hours to run.
This section details a selection of advanced features of the Xero connector.
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.
The Jitterbit Connector for Xero allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:For example:
{ "MyView": { "query": "SELECT * FROM Contacts WHERE MyColumn = 'value'" }, "MyView2": { "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)" } }Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
SELECT * FROM Customers WHERE City = 'Raleigh';An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
The Jitterbit Connector for Xero models five Xero APIs as relational databases: the Accounting, Australian Payroll, Files, Fixed Assets and Projects APIs. Set the Schema property and use the information in each section to select the proper API based on your project needs.
The Accounting API contains the company's financial information, such as contacts, invoices and reporting. It also contains history tables which records changes to most kinds of entities.
See Accounting Data Model for the available entities in the Accounting API.
The Australian Payroll API contains information on the company's employees, such as timesheets, leave and tax declarations.
See Australian Payroll Data Model for the available entities in the Australian Payroll API.
The Files API contains information on the files and attachments managed by Xero. It can be used to upload and download files, and attach them to accounting entities.
See Files Data Model for the available entities in the Files API.
The Fixed Assets API contains information on the company's fixed assets, such as registration, depreciation and resale.
See Fixed Assets Data Model for the available entities in the Fixed Assets API.
The Projects API contains information on the company's projects, such as billing and time tracking.
See Projects Data Model for the available entities in the Projects API.
The Jitterbit Connector for Xero models the Xero Accounting API as relational tables, views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
Tables describes the available tables.
Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.
Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Accounts | Create, delete, and query accounts for a Xero organisation. |
BankTransactions | Create, update, delete, and query bank transactions for a Xero organisation. |
BankTransfers | Usage information for the operation BankTransfers.rsd. |
BatchPayments | Create and query BatchPayments for a Xero organisation. |
ContactGroups | Create, update, and query contact groups for a Xero organisation. |
Contacts | Create, update, and query contacts for a Xero organisation. |
CreditNotes | Create, update, delete, and query credit notes for a Xero organisation. |
Employees | Create, update, and query employees for a Xero organisation. |
ExpenseClaims | Create, update, and query expense claims for a Xero organisation. |
HistoryBankTransactions | Create and query BankTransaction history and notes for a Xero organization. |
HistoryBatchPayments | Create and query BatchPayment history and notes for a Xero organization. |
HistoryContacts | Create and query Contact history and notes for a Xero organization. |
HistoryCreditNotes | Create and query CreditNote history and notes for a Xero organization. |
HistoryInvoices | Create and query Invoice history and notes for a Xero organization. |
HistoryPurchaseOrders | Create and query PurchaseOrder history and notes for a Xero organization. |
Invoices | Create, update, delete, and query Invoices for a Xero organisation. |
Items | Create, update, delete, and query items for a Xero organisation. |
LinkedTransactions | Create, update, and query LinkedTransactions for a Xero organisation. |
ManualJournals | Create, update, and query manual journals for a Xero organisation. |
Payments | Create and query payments for a Xero organisation. |
PurchaseOrders | Create, update, delete, and query purchase orders for a Xero organisation. |
Receipts | Create, update, delete, and query receipts for a Xero organisation. |
TaxRates | Usage information for the operation TaxRates.rsd. |
Create, delete, and query accounts for a Xero organisation.
The Accounts table allows you to SELECT and INSERT accounts for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The following fields are required for inserts: Code, Name, and Type.
INSERT INTO Accounts (Name,Code,Type) VALUES ('John Doe','2000','BANK')
Name | Type | ReadOnly | Description |
AccountId [KEY] | String | True |
The unique, Xero-assigned identifier of the account. |
Code | String | False |
Customer-defined alphanumeric account code. For example, 200 or SALES. |
Name | String | False |
Name of the account. |
Type | String | False |
The account type. Valid values are BANK, CURRENT, CURRLIAB, DEPRECIATN, DIRECTCOSTS, EQUITY, EXPENSE, FIXED, LIABILITY, NONCURRENT, OTHERINCOME, OVERHEADS, PREPAYMENT, REVENUE, SALES, TERMLIAB, PAYGLIABILITY, SUPERANNUATIONEXPENSE, SUPERANNUATIONLIABILITY, and WAGESEXPENSE. |
Description | String | False |
Description of the account. All accounts except bank accounts return this element. |
TaxType | String | False |
The tax type. For example, INPUT or NONE. |
EnablePaymentsToAccount | Boolean | False |
Describes whether the account can have payments applied to it. |
ShowInExpenseClaims | Boolean | False |
Describes whether the account code is available for use with expense claims. |
Class | String | True |
The account class type. For example, ASSET or EQUITY. |
Status | String | False |
The account status code. |
SystemAccount | String | True |
Returned only for system accounts, such as DEBTORS, CREDITORS, etc. |
BankAccountNumber | String | False |
The bank account number associated with the account. BANK account types only. |
CurrencyCode | String | False |
The currency code associated with the account. BANK account types only. |
ReportingCode | String | True |
The reporting code. Only available for NZ organisations. |
ReportingCodeName | String | True |
The reporting code name. Only available for NZ organisations. |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the user record. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
BankAccountType | String | False |
Valid types are BANK, CREDITCARD, and PAYPAL |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query bank transactions for a Xero organisation.
The BankTransactions table allows you to SELECT, INSERT, UPDATE, and DELETE bank transactions for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new bank transaction record, the following fields are required in addition to at least one line item: Type, Contact_ContactName or Contact_ContactId, and BankAccount_AccountId or BankAccount_Code. To create a new line item, the following fields are required: LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, and LineItem_AccountCode.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO BankTransactions(Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES ( 'RECEIVE', 'John Doe', '200', '<LineItem><Description>LineItem1Desc</Description><Quantity>1</Quantity><AccountCode>100</AccountCode></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><AccountCode>100</AccountCode></LineItem>' )
INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete a record from the BankTransactions table, you must update the Status field to an appropriate value.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the ID of the bank transaction. |
BankTransactionId | String | False |
The ID of the bank transaction. |
Type | String | False |
The transaction type. Valid values are RECEIVE, SPEND, RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, RECEIVE-TRANSFER, SPEND-OVERPAYMENT, SPEND-PREPAYMENT, and SPEND-TRANSFER. |
LineAmountTypes | String | False |
How line item values are provided relative to tax. Can be either Exclusive, Inclusive or NoTax |
Contact_ContactName | String | False |
The name of the contact associated with the transaction. |
Contact_ContactId | String | False |
The ID of the contact associated with the transaction. |
BankAccount_AccountId | String | False |
The ID for the associated bank account. |
BankAccount_AccountName | String | False |
The Name for the associated bank account. |
BankAccount_Code | String | False |
The code for the associated bank account. |
LineItem_LineItemId | String | False |
The Xero-generated ID of the bank transaction line item |
LineItem_Description | String | False |
A description for the line item in the bank transaction. |
LineItem_Quantity | Double | False |
The quantity of the subject of the line item. This value must be greater than or equal to 0. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the subject of the line item. This amount must be positive. |
LineItem_AccountCode | String | False |
The code for the referenced account. Including this field is recommended when posting. |
LineItem_Item_ItemId | String | False |
The ID of the item the lineitem refers to. |
LineItem_Item_Name | String | False |
The name of the item the lineitem refers to. |
LineItem_Item_Code | String | False |
The code of the item the lineitem refers to. |
LineItem_TaxType | String | False |
The tax code for the transaction. This field is used as an override for the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | False |
The automatically calculated percentage of tax for the line amount, based on the tax rate. |
LineItem_LineAmount | Decimal | False |
The amount of the line item. If either Quantity or UnitAmount are omitted, Xero will calculate the missing value from LineAmount. |
LineItem_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
IsReconciled | Boolean | False |
This field shows whether this transaction is reconciled. |
Date | Date | False |
The date of the transaction. |
Reference | String | False |
An internal Xero reference for the transaction. |
CurrencyRate | Decimal | False |
Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used. |
URL | String | False |
A link to a source document. |
Status | String | False |
The bank transaction status code. The valid values are AUTHORISED and DELETED. |
SubTotal | Decimal | False |
The total of the transaction, excluding taxes. |
TotalTax | Decimal | False |
The tax on the bank transaction. |
Total | Decimal | False |
The total of the transaction, including tax. |
PrepaymentId | String | False |
Xero generated unique identifier for a Prepayment. This will be returned on BankTransactions with a Type of SPEND-PREPAYMENT or RECEIVE-PREPAYMENT. |
OverpaymentId | String | False |
Xero generated unique identifier for an Overpayment. This will be returned on BankTransactions with a Type of SPEND-OVERPAYMENT or RECEIVE-OVERPAYMENT. |
UpdatedDateUTC | Datetime | True |
The date when the transaction was last updated. |
FullyPaidOnDate | Date | False |
The date when the transaction was fully paid. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Usage information for the operation BankTransfers.rsd.
The BankTransfers table allows you to SELECT and INSERT bank transfers for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The following fields are required for inserts: FromBankAccount_Code, ToBankAccount_Code, and Amount
INSERT INTO BankTransfers (FromBankAccount_Code,ToBankAccount_Code,Amount) VALUES ('3d08fd48-434d-4c18-a57b-831663ab70d2','ceef65a5-a545-423b-9312-78a53caadbc4','1000.00')
Name | Type | ReadOnly | Description |
BankTransferId [KEY] | String | True |
The unique, Xero-assigned identifier for the bankTransfer. |
FromBankAccount_Code | String | False |
The bank account code that you will be sending from. |
FromBankAccount_AccountId | String | False |
The bank account account ID that you will be sending from. |
FromBankAccount_Name | String | False |
The bank account name that you will be sending from. |
ToBankAccount_Code | String | False |
The bank account code that you will be sending to. |
ToBankAccount_AccountId | String | False |
The bank account account ID that you will be sending to. |
ToBankAccount_Name | String | False |
The bank account name that you will be sending to. |
Amount | Decimal | False |
The amount of the money to transfer. |
Date | Date | False |
The date of the transfer YYYY-MM-DD. |
CurrencyRate | Decimal | True |
The currency rate. |
FromBankTransactionID | String | True |
The Bank Transaction ID for the source account. |
ToBankTransactionID | String | True |
The Bank Transaction ID for the destination account. |
HasAttachments | Boolean | True |
Boolean to indicate if a Bank Transfer has an attachment. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query BatchPayments for a Xero organisation.
The BatchPayments table allows you to SELECT and INSERT batch payments for a Xero organization. The ID column is generated by the connector this value combines the index of the individual payment within the batch with the unique, Xero-generated BatchPaymentId
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new batch payment, the AccountId and Date are required in addition to at least one payment item; the Payment_InvoiceId and Payment_Amount fields are required to insert a payment item.
You can also provide multiple payments using an XML aggregate which contains the data for each payment in the batch. The elements supported are the same as the names of the "Payment_" columns without the "Payment_" prefix.INSERT INTO BatchPayments(AccountId, Date, PaymentAggregate) VALUES ( 'AAAAA-AAAAA-AAAAA-AAAAA', '2019-03-01', '<Payment><InvoiceID>BBBBB-...</InvoiceID><Amount>50.21</Amount></Payment> <Payment><InvoiceID>CCCCC-...</InvoiceID><Amount>12.96</Amount></Payment>' )
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The batch payment index combined with the ID of the batch payment. |
BatchPaymentId | String | True |
The ID of the invoice. This field is assigned by Xero. |
AccountId | String | False |
The ID of the account used to make the payment. It must either be a BANK account or have payments enabled. |
Particulars | String | False |
(NZ only) The particulars that will appear on the statement. |
Code | String | False |
(NZ only) The transaction code that will appear on the statement. |
Reference | String | False |
(NZ only) The transaction reference that will appear on the statement. |
Details | String | False |
(Non-NZ only) The details sent to the organization's bank as a reference for the bank transaction. |
Narrative | String | False |
(UK only) A description that is only visible in the Xero statement line |
Date | Date | False |
The date the payment is being made. |
Payment_PaymentId | String | True |
The ID of the Payment. This field is assigned by Xero. |
Payment_InvoiceId | String | False |
The ID of the Invoice the payment was made against |
Payment_BankAccountNumber | String | False |
The account number of the bank the payment is being made to. |
Payment_Particulars | String | False |
(NZ only) The particulars that will appear on the statement. |
Payment_Code | String | False |
(NZ only) The transaction code that will appear on the supplier's bank account. |
Payment_Reference | String | False |
(NZ only) The transaction reference that will appear on the supplier's bank account. |
Payment_Details | String | False |
The details sent to the supplier's bank as a reference for the bank transaction. |
Payment_Amount | Decimal | False |
The amount being paid. |
PaymentAggregate | String | False |
Used to define Payment rows using XML values. Should be provided on INSERT only. |
Type | String | True |
Either PAYBATCH for bill payments, or RECBATCH for sales payments. |
Status | String | True |
Either AUTHORISED or DELETED |
TotalAmount | Decimal | True |
The total of all the payments included in the batch |
IsReconciled | Boolean | True |
Whether the batch payment has been reconciled |
UpdatedDateUTC | Datetime | True |
The date when the batch payment was last updated. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query contact groups for a Xero organisation.
The ContactGroups table allows you to manage both contact groups as well as the members of those contact groups.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
When inserting with a contact group, you can either insert a new contact group or add contacts into an existing group.
To insert a new contact group, only the Name field should be given:
INSERT INTO ContactGroups (Name) VALUES ('Contractors')
To add a contact into an existing group, both the ContactGroupId and the Contact_ContactId must be provided. The value for the Contact_ContactId field can be retrieved by querying the Contacts table.
INSERT INTO ContactGroups (ContactGroupId, Contact_ContactId) VALUES ('11111-11111-11111-11111', '22222-22222-22222-22222')
Multiple contacts may be provided using the ContactAggregate:
INSERT INTO ContactGroups (ContactGroupId, ContactAggregate) VALUES ( '11111-11111-11111-11111', '<Contact><ContactID>22222-22222-22222-22222</ContactID></Contact> <Contact><ContactID>33333-33333-33333-33333</ContactID></Contact>')
-- Renaming the group UPDATE ContactGroup SET Name = 'External Contractors' WHERE ContactGroupId = '11111-11111-11111-1111' -- Deleting the group UPDATE ContactGroup SET Status = 'DELETED' WHERE ContactGroupId = '11111-11111-11111-1111'
DELETE FROM ContactGroups WHERE ID = '3|11111-11111-11111-11111'
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the ID of the Contact Group |
ContactGroupId | String | False |
The unique Xero identifier for the contact group. |
Name | String | False |
The contact group name. |
Status | String | False |
The status of the contact group. |
ContactAggregate | String | False |
Used to define Contact rows using XML values. Only allowed when performing an INSERT into an existing ContactGroup. |
Contact_ContactId | String | False |
The unique identifier of the contact group member |
Contact_Name | String | True |
THe name of the contact group member |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query contacts for a Xero organisation.
The Contacts table allows you to SELECT, INSERT, and UPDATE contacts for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The Name field is required to add a new contact.
INSERT INTO Contacts (Name) VALUES ('John Doe')
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Name | Type | ReadOnly | Description |
ContactId [KEY] | String | True |
The unique Xero identifier for the contact. |
Name | String | False |
The contact name. |
AccountNumber | String | False |
The account number associated with the contact. |
ContactNumber | String | False |
An identifier from an external system. Although this field is read-only on the Xero contact screen, it can be updated via the API. |
ContactStatus | String | False |
The status of the contact. Available status types are ACTIVE or ARCHIVED. |
FirstName | String | False |
The first name of the contact. The maximum length of this field is 255 characters. |
LastName | String | False |
The last name of the contact. The maximum length of this field is 255 characters. |
EmailAddress | String | False |
The email address of the contact. The maximum length of this field is 500 characters. |
SkypeUserName | String | False |
The Skype username of the contact. |
ContactPerson1_FirstName | String | False |
First name of contact person. |
ContactPerson1_LastName | String | False |
Last name of contact person. |
ContactPerson1_EmailAddress | String | False |
Email address name of contact person. |
ContactPerson1_IncludeInEmails | Boolean | False |
Whether to include contact person in emails. |
ContactPerson2_FirstName | String | False |
First name of contact person. |
ContactPerson2_LastName | String | False |
Last name of contact person. |
ContactPerson2_EmailAddress | String | False |
Email address name of contact person. |
ContactPerson2_IncludeInEmails | Boolean | False |
Whether to include contact person in emails. |
ContactPerson3_FirstName | String | False |
First name of contact person. |
ContactPerson3_LastName | String | False |
Last name of contact person. |
ContactPerson3_EmailAddress | String | False |
Email address name of contact person. |
ContactPerson3_IncludeInEmails | Boolean | False |
Whether to include contact person in emails. |
ContactPerson4_FirstName | String | False |
First name of contact person. |
ContactPerson4_LastName | String | False |
Last name of contact person. |
ContactPerson4_EmailAddress | String | False |
Email address name of contact person. |
ContactPerson4_IncludeInEmails | Boolean | False |
Whether to include contact person in emails. |
BankAccountDetails | String | False |
The bank account number of the contact. |
TaxNumber | String | False |
The region-specific tax number of the contact: the ABN (Australia), GST (New Zealand), VAT (UK), or Tax ID Number (US and global). |
AccountsReceivableTaxType | String | False |
The default tax type used for the contact on accounts-receivable invoices. |
AccountsPayableTaxType | String | False |
The default tax type used for the contact on accounts-payable invoices. |
Street_AddressLine1 | String | False |
Line 1 of the address. |
Street_AddressLine2 | String | False |
Line 2 of the address. |
Street_AddressLine3 | String | False |
Line 3 of the address. |
Street_AddressLine4 | String | False |
Line 4 of the address. |
Street_City | String | False |
The city. |
Street_Region | String | False |
The region. |
Street_PostalCode | String | False |
The postal code. |
Street_Country | String | False |
The country. |
Street_AttentionTo | String | False |
The attention-to line of the address. |
POBox_AddressLine1 | String | False |
Line 1 of the address. |
POBox_AddressLine2 | String | False |
Line 2 of the address. |
POBox_AddressLine3 | String | False |
Line 3 of the address. |
POBox_AddressLine4 | String | False |
Line 4 of the address. |
POBox_City | String | False |
The city. |
POBox_Region | String | False |
The region. |
POBox_PostalCode | String | False |
The postal code. |
POBox_Country | String | False |
The country. |
POBox_AttentionTo | String | False |
The attention-to line of the address. |
DDI_PhoneNumber | String | False |
The phone number without area or country code. |
DDI_PhoneAreaCode | String | False |
The area code. |
DDI_PhoneCountryCode | String | False |
The country code. |
Default_PhoneNumber | String | False |
The phone number without area or country code. |
Default_PhoneAreaCode | String | False |
The area code. |
Default_PhoneCountryCode | String | False |
The country code. |
Fax_PhoneNumber | String | False |
The phone number without area or country code. |
Fax_PhoneAreaCode | String | False |
The area code. |
Fax_PhoneCountryCode | String | False |
The country code. |
Mobile_PhoneNumber | String | False |
The phone number without area or country code. |
Mobile_PhoneAreaCode | String | False |
The area code. |
Mobile_PhoneCountryCode | String | False |
The country code. |
UpdatedDateUTC | Datetime | True |
The timestamp of when the contact was last updated. |
ContactGroupId | String | True |
Displays which contact groups a contact is included in. |
IsSupplier | Boolean | True |
This field shows whether a contact has any accounts-payable (AP) invoices. This field is set automatically when an AP invoice is generated for a contact. |
IsCustomer | Boolean | True |
This field shows whether a contact has any accounts-receivable (AR) invoices. This field is set automatically when an AR invoice is generated for a contact. |
DefaultCurrency | String | False |
The default currency for the contact. |
Website | String | True |
The website of the contact. |
BrandingThemeId | String | True |
The ID of the branding theme. |
BrandingThemeName | String | True |
The Name of the branding theme. |
PurchasesDefaultAccountCode | String | False |
The default purchases account code for contacts. |
SalesDefaultAccountCode | String | False |
The default sales account code for contacts. |
BatchPayments_BankAccountNumber | String | True |
Batch payment bank account number for the contact. |
BatchPayments_BankAccountName | String | True |
Batch payment bank account name for the contact. |
BatchPayments_Details | String | True |
Batch payment details for the contact. |
Discount | Decimal | True |
The default discount rate for the contact. |
Balances_AccountsReceivable_Outstanding | Decimal | True |
The outstanding and overdue amounts for sales invoices (accounts receivable). |
Balances_AccountsReceivable_Overdue | Decimal | True |
The outstanding and overdue amounts for bills (accounts payable). |
Balances_AccountsPayable_Outstanding | Decimal | True |
The outstanding and overdue amounts for sales invoices (accounts receivable). |
Balances_AccountsPayable_Overdue | Decimal | True |
The outstanding and overdue amounts for bills (accounts payable). |
PaymentTerms_Bills_Day | Int | True |
The default payment terms for the contact. |
PaymentTerms_Bills_Type | String | True |
The default payment terms for the contact. |
PaymentTerms_Sales_Day | Int | True |
The default payment terms for the contact. |
PaymentTerms_Sales_Type | String | True |
The default payment terms for the contact. |
SalesTrackingCategory1_Name | String | False |
The default sales tracking category name for contacts. |
SalesTrackingOption1_Name | String | False |
The default sales tracking option name for contacts. |
SalesTrackingCategory2_Name | String | False |
The default sales tracking category name for contacts. |
SalesTrackingOption2_Name | String | False |
The default sales tracking option name for contacts. |
PurchaseTrackingCategory1_Name | String | False |
The default purchase tracking category name for contacts. |
PurchaseTrackingOption1_Name | String | False |
The default purchase tracking option name for contacts. |
PurchaseTrackingCategory2_Name | String | False |
The default purchase tracking category name for contacts. |
PurchaseTrackingOption2_Name | String | False |
The default purchase tracking option name for contacts. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query credit notes for a Xero organisation.
The CreditNotes table allows you to SELECT, INSERT, UPDATE, and DELETE credit note line items for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new credit note, the Contact_Name field is required. For a credit note to be approved, the following fields must be defined in addition to at least one line item: Contact_Name, Date, and LineAmountTypes.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO CreditNotes (Type, Contact_Name, LineItemAggregate) VALUES ( 'ACCPAYCREDIT', 'John Doe', '<LineItem><Description>LineItemDesc1</Description><Quantity>1</Quantity></LineItem> <LineItem><Description>LineItemDesc2</Description><Quantity>2</Quantity></LineItem>' )
INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the ID of the credit note. |
CreditNoteId | String | False |
The ID of the credit note. |
CreditNoteNumber | String | False |
A unique identifier for the credit note that identifies it to the user. |
Reference | String | False |
An optional field to store a reference. |
Type | String | False |
The credit note type. The valid values are ACCPAYCREDIT and ACCRECCREDIT. |
Contact_ContactId | String | False |
The ID of the contact. |
Contact_Name | String | False |
The name of the contact. |
Date | Date | False |
The date the credit note was created. |
Status | String | False |
The status of the credit note. |
LineAmountTypes | String | False |
The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Credit notes cannot be approved without this field being defined. |
LineItem_LineItemId | String | False |
The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | False |
The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields. |
LineItem_Quantity | Double | False |
The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the subject of the line item. Invoices cannot be approved without these fields. |
LineItem_ItemCode | String | False |
The user-defined item code. |
LineItem_AccountCode | String | False |
The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields. |
LineItem_Item_ItemId | String | False |
The ID of the item the lineitem refers to. |
LineItem_Item_Name | String | False |
The name of the item the lineitem refers to. |
LineItem_Item_Code | String | False |
The code of the item the lineitem refers to. |
LineItem_TaxType | String | False |
The description for the line item of the credit note. This value must be at least one character. Invoices cannot be approved without these fields. |
LineItem_TaxAmount | Decimal | False |
The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields. |
LineItem_LineAmount | Decimal | False |
The total of the Quantity field multiplied by the UnitAmount field with discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | False |
The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type credit notes. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
SubTotal | Decimal | False |
The subtotal of the credit note, excluding taxes. |
TotalTax | Decimal | False |
The total tax on the credit note. |
Total | Decimal | False |
The total of the credit note (the subtotal plus the total tax). |
UpdatedDateUTC | Datetime | True |
The date when the credit note was last updated. |
CurrencyCode | String | False |
The currency used for the credit note. |
FullyPaidOnDate | Date | False |
The date when the credit note was fully paid. |
SentToContact | Boolean | False |
This field indicates whether the credit note has been sent to the contact via the Xero app. |
BrandingThemeId | String | False |
The ID of the branding theme. |
CurrencyRate | Decimal | False |
Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used. |
RemainingCredit | Decimal | False |
The remaining credit for this credit note. |
Allocation1_AppliedAmount | Decimal | True |
The amount to be applied from this credit note to a given invoice. |
Allocation1_Date | Date | True |
The date the allocation was made. |
Allocation1_InvoiceId | String | True |
The ID of the invoice this allocation has been applied to. |
Allocation1_InvoiceNumber | String | True |
The Invoice this allocation has been applied to. |
Allocation2_AppliedAmount | Decimal | True |
The amount to be applied from this credit note to a given invoice. |
Allocation2_Date | Date | True |
The date the allocation was made. |
Allocation2_InvoiceId | String | True |
The ID of the invoice this allocation has been applied to. |
Allocation2_InvoiceNumber | String | True |
The Invoice this allocation has been applied to. |
PaymentIds | String | True |
A comma-delimited list of payment Ids associated with the credit notes. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query employees for a Xero organisation.
The Employees table allows you to SELECT, INSERT, and UPDATE employees for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new employee record, the following fields are required: FirstName and LastName.
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Name | Type | ReadOnly | Description |
EmployeeId [KEY] | String | True |
The unique identifier of the employee. This field is assigned by Xero. |
Status | String | False |
The status of the employee. Valid values are ACTIVE and DELETED. |
FirstName | String | False |
The first name of the employee. |
LastName | String | False |
The last name of the employee. |
ExternalLink_URL | String | False |
A URL to an external resource. |
ExternalLink_Description | String | False |
A description for the external link. |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the employee record. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query expense claims for a Xero organisation.
The ExpenseClaims table allows you to SELECT, INSERT, and UPDATE expense claims for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
To create a new expense claim record, the UserId field must be defined; in addition, at least one ReceiptId must be specified. One can be provided either via the ReceiptId column:
INSERT INTO ExpenseClaims(UserId, ReceiptId) VALUES ( 'XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY' )
Multiple may also be specified by including them in the ReceiptAggregate:
INSERT INTO ExpenseClaims(UserId, ReceiptAggregate) VALUES ( 'XXXXX-XXXXX-XXXXX-XXXXX', '<Receipt><ReceiptID>YYYYY-YYYYY-YYYYY-YYYYY</ReceiptID></Receipt> <Receipt><ReceiptID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</ReceiptID></Receipt>' )
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Name | Type | ReadOnly | Description |
ExpenseClaimId [KEY] | String | True |
The unique identifier of the expense claim. |
UserId | String | False |
The ID of the associated user. |
ReceiptAggregate | String | False |
Used to define Receipt rows using XML values. Should be provided on INSERT only. |
ReceiptId | String | False |
The ID of the receipt. |
Status | String | True |
The status code for the expense claim. The valid values are SUBMITTED, AUTHORISED, and PAID. |
UpdatedDateUTC | Datetime | True |
The date when the expense claim was last updated. |
Total | Decimal | True |
The total amount of an expense claim being paid. |
AmountDue | Decimal | True |
The amount due to be paid for an expense claim. |
AmountPaid | Decimal | True |
The amount still to pay for an expense claim. |
PaymentDueDate | Date | True |
The date when the expense claim is due to be paid. |
ReportingDate | Date | True |
The date the expense claim will be reported in Xero. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query BankTransaction history and notes for a Xero organization.
The HistoryBankTransactions table allows you to read the notes created on a transaction, and the history of changes to that transaction. If a BankTransactionId is not provided, the history of all BankTransactions will be retrieved.
A BankTransactionId and Details is required for inserting a note:INSERT INTO HistoryBankTransactions(BankTransactionId, Details) VALUES ('...', 'Refund for overpayment'),
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
BankTransactionId | String | True |
The ID of the bank transaction the history item belongs to. |
Changes | String | True |
What type of change happened on the bank transactions |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query BatchPayment history and notes for a Xero organization.
The HistoryBatchPayments table allows you to read the notes created on a batch payment, and the history of changes to that batch payment. If a BatchPaymentId is not provided, the history of all batch payments will be retrieved.
An BatchPaymentId and Details is required for inserting a note:INSERT INTO HistoryBatchPayments(BatchPaymentId, Details) VALUES ('...', 'Batch submitted on 3/1/2019')
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
BatchPaymentId | String | True |
The ID of the item the history item belongs to. |
Changes | String | True |
What type of change happened on the item |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query Contact history and notes for a Xero organization.
The HistoryContacts table allows you to read the notes created on a contact, and the history of changes to that contact. If a ContactId is not provided, the history of all Contacts will be retrieved.
A ContactId and Details is required for inserting a note:INSERT INTO HistoryContacts(ContactId, Details) VALUES ('...', 'Reliable customer'),
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
ContactId | String | True |
The ID of the contact the history item belongs to. |
Changes | String | True |
What type of change happened on the contact |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query CreditNote history and notes for a Xero organization.
The HistoryCreditNotes table allows you to read the notes created on a credit note, and the history of changes to that credit note. If a CreditNoteId is not provided, the history of all CreditNotes will be retrieved.
SELECT * FROM HistoryCreditNotes WHERE CreditNoteId = '...'A CreditNoteId and Details is required for inserting a note:
INSERT INTO HistoryCreditNotes(CreditNoteId, Details) VALUES ('...', 'Offered 12% discount'),
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
CreditNoteId | String | True |
The ID of the credit note the history item belongs to. |
Changes | String | True |
What type of change happened on the credit note |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query Invoice history and notes for a Xero organization.
The HistoryInvoices table allows you to read the notes created on a invoice, and the history of changes to that invoice. If a InvoiceId is not provided, the history of all Invoices will be retrieved.
An InvoiceId and Details is required for inserting a note:INSERT INTO HistoryInvoices(InvoiceId, Details) VALUES ('...', 'Payment expected on December 1st'),
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
InvoiceId | String | True |
The ID of the invoice the history item belongs to. |
Changes | String | True |
What type of change happened on the invoice |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query PurchaseOrder history and notes for a Xero organization.
The HistoryPurchaseOrders table allows you to read the notes created on a purchase order, and the history of changes to that purchase order. If a PurchaseOrderId is not provided, the history of all PurchaseOrders will be retrieved.
A PurchaseOrderId and Details is required for inserting a note:INSERT INTO HistoryPurchaseOrders(PurchaseOrderId, Details) VALUES ('...', 'Rescheduled delivery for October 9th'),
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
A combination of the position of the history item and when it occurred. |
PurchaseOrderId | String | True |
The ID of the purchase order the history item belongs to. |
Changes | String | True |
What type of change happened on the purchase order |
Date | Datetime | True |
When the change occurred |
User | String | True |
The name of the user that made the change |
Details | String | False |
The change that was made |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query Invoices for a Xero organisation.
The Invoices table allows you to SELECT, INSERT, UPDATE, and DELETE invoice line items for a Xero organization. The ID column is generated by the connector this value combines the index of the line item with the unique, Xero-generated InvoiceId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new invoice, the Type and ContactName fields must be set in addition to at least one line item. Note that also at least the LineItem_LineAmount or LineItem_UnitAmount are also required fields for the LineItem aggregate.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES ( 'ACCPAY', 'Boom FM', '<LineItem><Description>LineItem1Desc</Description><Quantity>1</Quantity><UnitAmount>30</UnitAmount></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><UnitAmount>50</UnitAmount></LineItem>' )
INSERT INTO Invoices (InvoiceId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 10)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
This means that, out the three fields LIneItem_DiscountAmount, LineItem_DiscountAmount and LineItem_DiscountRate, only one can be used as part of an INSERT or UDPATE query. For example, neither of the below queries are valid:
INSERT INTO Invoices(Invoice, LineItem_LineAmount, LineItem_DiscountRate) VALUES ('2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e', 4999.99, 6.75) UPDATE Invoices SET LineItem_DiscountRate = 12.50, LineItem_DiscountAmount = 99.99 WHERE ID = '1|2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e'
It also means that the connector will remove the two other values when assigning any one of these fields. For example, if you were to assign a LineItem_LineAmount to an Invoice, the connector would remove any discount values attached to that Invoice.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the ID of the invoice. |
InvoiceId | String | False |
The ID of the invoice. This field is assigned by Xero. |
Type | String | False |
The type of the invoice. The valid values are ACCPAY and ACCREC. |
ContactId | String | False |
The ID of the contact. |
ContactName | String | False |
The name of the contact. This value is required when inserting. |
LineItem_LineItemId | String | False |
The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | False |
The description for the line item. This field must contain at least 1 character and is required to create an approved invoice. |
LineItem_Quantity | Double | False |
The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the associated item in the line item. This field is required for invoice approval. |
LineItem_ItemCode | String | False |
The code that identifies the associated item. |
LineItem_AccountCode | String | False |
The account code. This must be active for the organisation. This field is required for invoice approval. |
LineItem_Item_ItemId | String | False |
The ID of the item the lineitem refers to. |
LineItem_Item_Name | String | False |
The name of the item the lineitem refers to. |
LineItem_Item_Code | String | False |
The code of the item the lineitem refers to. |
LineItem_TaxType | String | False |
The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | False |
The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | False |
The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | False |
The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineItem_DiscountAmount | Decimal | False |
The discount amount being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
Date | Date | False |
The date the invoice was created. |
DueDate | Date | False |
The date the invoice is due. |
LineAmountTypes | String | False |
This field specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
InvoiceNumber | String | False |
The unique, alphanumeric code identifying the invoice. For ACCREC-type invoices. When this field is missing it is automatically generated from the Organisation Invoice Settings. For ACCPAY-type invoices, this field stores a nonunique alphanumeric code identifying the invoice. |
Reference | String | False |
An additional reference number for ACCREC-type invoices. |
BrandingThemeId | String | False |
The ID of the branding theme. |
URL | String | False |
A URL to a source document. |
CurrencyCode | String | False |
The ISO 4217 currency code. |
CurrencyRate | Decimal | False |
The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used. |
Status | String | False |
The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED. |
SentToContact | Boolean | False |
This field indicates whether the invoice should be marked as sent. This field can be set on only invoices that have been approved. |
ExpectedPaymentDate | Date | True |
The expected payment date. This field is shown on sales invoices (accounts receivable). |
PlannedPaymentDate | Date | True |
The planned payment date. This field is shown on bills (accounts payable). |
SubTotal | Decimal | True |
The subtotal for the invoice, which excludes taxes. |
TotalTax | Decimal | True |
The total tax for the invoice. |
Total | Decimal | True |
The total for the invoice, including tax -- the sum of SubTotal and TotalTax. |
TotalDiscount | Decimal | True |
Total of discounts applied on the invoice line items. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
PaymentIds | String | True |
A comma-delimited list of payment Ids associated with the invoice. |
PrePaymentIds | String | True |
A comma-delimited list of prepayment Ids associated with the invoice. |
OverPaymentIds | String | True |
A comma-delimited list of overpayment Ids associated with the invoice. |
CreditNoteIds | String | True |
A comma-delimited list of credit note Ids applied to the invoice. |
AmountDue | Decimal | True |
The amount remaining to be paid on the invoice. |
AmountPaid | Decimal | True |
The sum of payments received for the invoice. |
CISDeduction | Decimal | True |
The deduction withheld by a contractor to be paid to HMRC. Only available for UK organizations. |
FullyPaidOnDate | Date | True |
The date the invoice was fully paid. Only returned on fully paid invoices. |
AmountCredited | Decimal | True |
The sum of all credit notes, overpayments, and prepayments applied to the invoice. |
UpdatedDateUTC | Datetime | True |
The date when the invoice was last updated. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query items for a Xero organisation.
The Items table allows you to SELECT, INSERT, and UPDATE items for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new item record, the Code field must be defined.
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Name | Type | ReadOnly | Description |
ItemId [KEY] | String | True |
The unique, Xero-assigned identifier for the item. |
Code | String | False |
A user-defined code that identifies the item. |
InventoryAssetAccountCode | String | False |
The inventory asset account for the item. The account must be of type INVENTORY. The COGSAccountCode in PurchaseDetails is also required to create a tracked item. |
Name | String | False |
The name of the item. |
PurchaseDescription | String | False |
The purchase description of the item. |
Description | String | False |
A description of the item. |
PurchaseDetails_UnitPrice | String | False |
The unit Price of the item. |
PurchaseDetails_COGSAccountCode | String | False |
Cost of goods sold account. Only applicable to the purchase details of tracked items. |
PurchaseDetails_AccountCode | String | False |
The code of the account to be used for the purchased item. |
PurchaseDetails_TaxType | String | False |
The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc. |
SalesDetails_UnitPrice | String | False |
The unit price of the sold item. |
SalesDetails_COGSAccountCode | String | False |
Cost of goods sold account. Only applicable to the purchase details of tracked items. |
SalesDetails_AccountCode | String | False |
The code of the account to be used for the sold item. |
SalesDetails_TaxType | String | False |
The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc. |
IsTrackedAsInventory | Boolean | False |
True for items that are tracked as inventory. An item will be tracked as inventory if the InventoryAssetAccountCode and COGSAccountCode are set. |
TotalCostPool | Decimal | True |
The value of the item on hand. Calculated using average cost accounting. |
QuantityOnHand | Double | True |
The quantity of the item on hand. |
IsSold | Boolean | False |
Boolean value, defaults to true. When IsSold is true the item will be available on sales transactions in the Xero UI. If IsSold is updated to false then Description and SalesDetails values will be nulled. |
IsPurchased | Boolean | False |
Boolean value, defaults to true. When IsPurchased is true the item is available for purchase transactions in the Xero UI. If IsPurchased is updated to false then PurchaseDescription and PurchaseDetails values will be nulled. |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the item record. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query LinkedTransactions for a Xero organisation.
Name | Type | ReadOnly | Description |
LinkedTransactionID [KEY] | String | True |
The Xero identifier for an Linked Transaction |
SourceTransactionID | String | False |
The identifier of the source transaction (the purchase component of a billable expense). Currently, only invoices with a type of ACCPAY can be a source transaction. |
SourceLineItemID | String | False |
The line item identifier from the source transaction. |
ContactID | String | False |
The identifier for the contact on the target transaction i.e. the customer that the expense is being billed on to. |
TargetTransactionID | String | False |
The identifier of the target transaction (the sale component of a billable expense). Currently, only invoices with a type of ACCREC can be a target transaction. |
TargetLineItemID | String | False |
The line item identifier from the target transaction. When allocating a billable expense to a target transaction the TargetLineItemID is optional. |
Status | String | False |
The status of the linked transaction. This is derived from the statuses of the source and target transactions and cannot be explicitly set/updated. |
Type | String | False |
This will always be BILLABLEEXPENSE. More types may be added in future. |
UpdatedDateUTC | Datetime | True |
The last modified date in UTC format |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query manual journals for a Xero organisation.
The ManualJournals table allows you to SELECT, INSERT, and UPDATE manual journals journal lines for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ManualJournalId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new manual journal record, the Narration field is required in addition to at least two line items. A journal record must contain at least one credit and one debit, so that the record as a whole is balanced.
INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES ( 'Accrued expenses', '<JournalLine><LineAmount>55.00</LineAmount><AccountCode>200</AccountCode></JournalLine> <JournalLine><LineAmount>-55.00</LineAmount><AccountCode>201</AccountCode></JournalLine>' )
INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The journal line index combined with the ID of the manual journal. |
ManualJournalId | String | False |
The ID of the manual journal. |
Narration | String | False |
A description of the journal being posted. |
JournalLine_LineAmount | Decimal | False |
The total for the line item. This value is positive for debits and negative for credits. |
JournalLine_AccountCode | String | False |
The account code that identifies the account. |
JournalLine_Description | String | False |
The description for the manual journal. |
JournalLine_TaxType | String | False |
This field can be used to override the default tax code for the selected account. |
JournalLine_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLine_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
JournalLineAggregate | String | False |
Used to define JournalLine rows using XML values. Should be provided on INSERT only. |
Date | Date | False |
The date the journal was posted. |
Status | String | False |
The status code for the manual journal. The valid values are VALID, POSTED, DELETED, and VOIDED. |
URL | String | False |
A link to a source document. |
ShowOnCashBasisReports | Boolean | False |
This field indicates whether this journal should be included on cash-basis reports. The default value is true. |
LineAmountTypes | String | False |
The line amount type, which specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
UpdatedDateUTC | Datetime | True |
The date when the manual journal was last updated. |
HasAttachments | Boolean | True |
This field indicates whether the invoice has an attachment. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create and query payments for a Xero organisation.
The Payments table allows you to SELECT and INSERT payments for a Xero organization.
Note that this tables does not include payments on expense claims - to retrieve those, you will need to read from the ExpenseClaimPayments view.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new payment record, the InvoiceNumber and AccountCode fields are required.
Name | Type | ReadOnly | Description |
PaymentId [KEY] | String | True |
The ID of the payment. |
Date | Date | False |
The date the payment is being made. |
Amount | Decimal | False |
The amount of the payment. This value must be less than or equal to the outstanding amount owing on the invoice. |
CurrencyRate | Decimal | False |
The exchange rate when the payment is received. This field is only used for non-base-currency invoices and credit notes. |
Reference | String | False |
A optional description for the payment. |
PaymentType | String | False |
The payment type. |
Status | String | False |
The status of the payment. |
UpdatedDateUTC | Datetime | False |
The date when the payment was last updated. |
IsReconciled | Boolean | False |
This field indicates whether the payment has been reconciled. Only available when querying for one payment at a time. |
BatchPaymentId | String | True |
If the payment was submitted through BatchPayments, this is the ID of the batch it was provided with. |
InvoiceId | String | False |
The ID of the invoice. When applying a payment, specify either the InvoiceId or the InvoiceNumber. |
InvoiceNumber | String | False |
The invoice number of the invoice or credit note. When applying a payment, specify either the InvoiceId or the InvoiceNumber. |
CreditNoteId | String | False |
The ID of the credit note. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber. |
CreditNoteNumber | String | False |
The credit note number. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber. |
AccountId | String | False |
The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled. |
AccountCode | String | False |
The account code of the account used to make the payment. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query purchase orders for a Xero organisation.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the ID of the purchase order. |
PurchaseOrderId | String | False |
The ID of the purchase order. |
PurchaseOrderNumber | String | False |
A unique identifier for the purchase order that identifies it to the user. |
Reference | String | False |
An optional field to store a reference. |
Type | String | False |
The purchase order type. The valid values are ACCPAYCREDIT and ACCRECCREDIT. |
Contact_ContactId | String | False |
The ID of the contact. |
Contact_Name | String | False |
The name of the contact. |
DeliveryDate | Date | False |
The date the purchase order is to be delivered. |
ExpectedArrivalDate | Date | False |
The date the purchase is expected to arrive. |
DeliveryAddress | String | False |
The address the goods are to be delivered to. |
AttentionTo | String | False |
The person the delivery is going to. |
Telephone | String | False |
The phone number of the person accepting the delivery. |
DeliveryInstructions | String | False |
Free-form field for information needed to complete the delivery. |
Date | Date | False |
The date the purchase order was created. |
Status | String | False |
The status of the purchase order. |
LineAmountTypes | String | False |
The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Purchase orders cannot be approved without this field being defined. |
LineItem_LineItemId | String | False |
The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | False |
The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields. |
LineItem_Quantity | Double | False |
The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the subject of the line item. Invoices cannot be approved without these fields. |
LineItem_ItemCode | String | False |
The user-defined item code. |
LineItem_AccountCode | String | False |
The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields. |
LineItem_TaxType | String | False |
The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields. |
LineItem_TaxAmount | Decimal | False |
The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields. |
LineItem_LineAmount | Decimal | False |
The total of the Quantity field multiplied by the UnitAmount field with discounts applied. |
LineItem_DiscountRate | Double | False |
The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type purchase orders. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
SubTotal | Decimal | False |
The subtotal of the purchase order, excluding taxes. |
TotalTax | Decimal | False |
The total tax on the purchase order. |
Total | Decimal | False |
The total of the purchase order (the subtotal plus the total tax). |
LineItem_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
UpdatedDateUTC | Datetime | True |
The date when the purchase order was last updated. |
HasAttachments | Boolean | True |
Indicates if the PurchaseOrder has a file attached. |
CurrencyCode | String | False |
The currency used for the purchase order. |
BrandingThemeId | String | False |
The ID of the branding theme. |
CurrencyRate | Decimal | False |
Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query receipts for a Xero organisation.
The Receipts table allows you to SELECT, INSERT, UPDATE, and DELETE receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ReceiptId.
Note that this table only includes draft receipts - to retrieve receipts that have already been claimed, you will need to read from the ExpenseClaimReceipts view.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a receipt record, the following fields are required in addition to at least one line item: Date, Contact_Name, and User_UserId. To add a new line item, the following fields are required: LineItem_Description, LineItem_UnitAmount, and LineItem_AccountCode.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO Receipts (Date, Contact_Name, User_UserId, LineItemAggregate) VALUES ( '4/19/2006 9:16:48 AM', 'John Doe', 'c81045b2-5740-4aea-bf8a-3956941af387', '<LineItem><Description>Coffee with client</Description><UnitAmount>13.00</UnitAmount><AccountCode>200</AccountCode></LineItem> <LineItem><Description>Bagel</Description><UnitAmount>2.00</UnitAmount><AccountCode>200</AccountCode></LineItem>' )
INSERT INTO Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the unique, Xero-generated identifier for the receipt. |
ReceiptId | String | False |
The unique, Xero-generated identifier for the script. |
Date | Date | False |
The date of the receipt. |
Contact_ContactId | String | False |
The ID of the contact. A contact must be specified when creating a receipt. |
Contact_Name | String | False |
The name of the contact. |
LineItem_Description | String | False |
The description for the item. |
LineItem_UnitAmount | Decimal | False |
The unit amount of the item. |
LineItem_AccountCode | String | False |
The code for the associated account. |
LineItem_Quantity | Double | False |
The quantity of the item. |
LineItem_TaxType | String | False |
The tax type. This field can be used to override the default tax code for the selected account. |
LineItem_LineAmount | Decimal | False |
The total of the unit amount multiplied by the quantity. |
LineItem_TrackingCategory1_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | False |
The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | False |
The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | False |
The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | False |
The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | False |
The percentage discount being applied to the line item. |
LineItemAggregate | String | False |
Used to define LineItem rows using XML values. Should be provided on INSERT only. |
User_UserId | String | False |
The user in the organisation that the expense claim receipt is for. |
Reference | String | False |
Additional reference number. |
LineAmountTypes | String | False |
This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
SubTotal | Decimal | False |
The total, excluding taxes, of the receipt. |
TotalTax | Decimal | False |
The total tax on the receipt. |
Total | Decimal | False |
The total of the receipt -- the sum of SubTotal plus TotalTax. |
Status | String | True |
The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED. |
ReceiptNumber | String | True |
The Xero-generated number of the receipt in sequence in the current claim for the given user. |
UpdatedDateUTC | Datetime | True |
The date when the receipt was last updated. |
HasAttachments | Boolean | True |
This field indicates whether the receipt has attachments. |
URL | String | True |
A link to a source document. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Usage information for the operation TaxRates.rsd.
The TaxRates table allows you to SELECT, INSERT, and UPDATE tax components for tax rates in a Xero organization. The ID column is generated by the connector this value combines the index of the line item with the unique, Xero-generated TaxType.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new tax rate, the Name field must be set in addition to at least one tax component; the TaxComponent_Name, TaxComponent_Rate, and TaxComponent_IsCompound fields are required to insert a new line item.
In addition to inserting a single row, this table offers two additional ways to insert into a table.INSERT INTO TaxRates (Name, TaxComponentAggregate) VALUES ( 'My Tax Rate', '<TaxComponent><Name>customTax1</Name><Rate>1.0000</Rate><IsCompound>false</IsCompound></TaxComponent> <TaxComponent><Name>customTax2</Name><Rate>2.0000</Rate><IsCompound>false</IsCompound></TaxComponent>' )
INSERT INTO TaxRates (TaxType, TaxComponent_Name, TaxComponent_Rate, TaxComponent_IsCompound) VALUES ('TAX001', 'customTax2', '2.0000', 'False')
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
Note: You cannot update predefined tax rates through the Xero API.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The tax component index combined with the TaxType. |
TaxType | String | True |
The tax type for the tax rate. There are default tax rates, additionally new tax rates can be setup for a Xero organisation. All new tax rates added have a TaxType of the format TAX001, TAX002 etc. |
Name | String | False |
The name for the tax rate. |
TaxComponent_Name | String | False |
The name of Tax Component |
TaxComponent_Rate | Double | False |
The tax Rate (up to 4dp). |
TaxComponent_IsCompound | Boolean | False |
Boolean to describe if Tax rate is compounded. |
TaxComponentAggregate | String | False |
Used to define TaxComponent rows using XML values. Should be provided on INSERT only. |
Status | String | False |
The Status of the Tax Rate e.g. ACTIVE, DELETED |
ReportTaxType | String | False |
The report tax type. Required for AU, NZ, and UK organisations (US and Global do not support this element). |
CanApplyToAssets | Boolean | True |
Boolean to describe if tax rate can be used for asset accounts. |
CanApplyToEquity | Boolean | True |
Boolean to describe if tax rate can be used for equity accounts. |
CanApplyToExpenses | Boolean | True |
Boolean to describe if tax rate can be used for expense accounts. |
CanApplyToLiabilities | Boolean | True |
Boolean to describe if tax rate can be used for liability accounts. |
CanApplyToRevenue | Boolean | True |
Boolean to describe if tax rate can be used for revenue accounts. |
DisplayTaxRate | Double | True |
Tax Rate (decimal to 4dp) e.g 12.5000. |
EffectiveRate | Double | True |
Effective Tax Rate (decimal to 4dp) e.g 12.5000. |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
AgedPayablesByContact | Returns Aged Payables for a Contact. |
AgedReceivablesByContact | Returns Aged Receivables for a Contact. |
BalanceSheet | Returns a monthly Balance Sheet. |
BankStatement | Returns the transactions listed on the account. |
BankSummary | Returns the balances and cash movements for each bank account. |
BrandingThemes | Returns the Branding Themes defined for the Xero organisation. |
BudgetLines | Query Budget line items for a Xero organisation |
Budgets | Query Budgets for a Xero organisation |
BudgetSummary | Returns a monthly Budget Summary. |
ContactCISSettings | Query CIS settings for contacts in a Xero organisation. |
CreditNoteAllocations | Query credit note allocations for a Xero organisation. |
Currencies | Retrieves the Currencies for a Xero organisation. |
ExecutiveSummary | Returns a summary of changes between a month and the previous month |
ExpenseClaimPayments | Create and query payments for a Xero organisation. |
ExpenseClaimReceipts | Create, update, delete, and query claimed receipts for a Xero organisation. |
HistoryBankTransfers | Create and query BankTransfer history and notes for a Xero organization. |
HistoryExpenseClaims | Create and query ExpenseClaim history and notes for a Xero organization. |
HistoryItems | Create and query Item history and notes for a Xero organization. |
HistoryOverpayments | Create and query Overpayment history and notes for a Xero organization. |
HistoryPayments | Create and query Payment history and notes for a Xero organization. |
HistoryPrepayments | Create and query Prepayment history and notes for a Xero organization. |
HistoryReceipts | Create and query Receipt history and notes for a Xero organization. |
HistoryRepeatingInvoices | Create and query RepeatingInvoice history and notes for a Xero organization. |
Journals | Query the line items in journals for a Xero organisation. |
Organisation | Query organisation data for a Xero organisation. |
OrganisationActions | Query organisation data for a Xero organisation. |
OverpaymentAllocations | Query overpayment allocations for a Xero organisation. |
Overpayments | Create and query Overpayments for a Xero organisation. |
PrepaymentAllocations | Query prepayment allocations for a Xero organisation. |
Prepayments | Create and query Prepayments for a Xero organisation. |
ProfitAndLoss | Returns a Profit and Loss report over a date range. |
Quotes | Query quotes for a Xero organization |
RepeatingInvoices | Query Repeating Invoice templates. |
Tenants | Query the list of organizations connected to your Xero account. |
TrackingCategories | Query Tracking Categories for a Xero organisation. |
TrialBalance | Retrieve a monthly Trial Balance. |
Users | Query users for a Xero organisation. |
Returns Aged Payables for a Contact.
If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.
Name | Type | Description |
ID [KEY] | String | The row number, combined with the contact ID. |
ContactId | String | The ID of the contact this is reporting on. |
Date | Datetime | The Date the bill is created. |
Reference | String | Purchase order reference for this bill. |
DueDate | Datetime | Date the amount is due. |
Overdue | Decimal | Amount due passed due date. |
Currency | String | The currency used for all fields but DueLocal, or empty if the default currency is used |
Total | Decimal | Total amount due. |
Paid | Decimal | Amount paid. |
Credited | Decimal | Amount credited. |
Due | Decimal | Amount due. |
DueLocal | Decimal | Amount due in the default organisation currency. |
InvoiceId | String | The invoice for this bill. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
fromDate | Datetime | Show all payable invoices from this date for contact. |
toDate | Datetime | Show all payable invoices to this date for the contact. |
Returns Aged Receivables for a Contact.
If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.
Name | Type | Description |
ID [KEY] | String | The row number, combined with the contact ID. |
ContactId | String | The ID of the contact this is reporting on. |
Date | Datetime | The Date the invoice is created. |
Reference | String | Sale order reference for this bill. |
DueDate | Datetime | Date the amount is due. |
Overdue | Decimal | Amount due passed due date. |
Currency | String | The currency used for all fields but DueLocal. Is empty if multi-currency is not in use. |
Total | Decimal | Total amount due. |
Paid | Decimal | Amount paid. |
Credited | Decimal | Amount credited. |
Due | Decimal | Amount due. |
DueLocal | Decimal | Amount due in the default organisation currency. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
fromDate | Datetime | Show all payable invoices from this date for contact. |
toDate | Datetime | Show all payable invoices to this date for the contact. |
Returns a monthly Balance Sheet.
Name | Type | Description |
ID [KEY] | Integer | The row number. |
Label1 | String | The Label column of the BalanceSheet report. |
Label2 | String | The Value column of the BalanceSheet report. |
Label3 | String | The Label column of the BalanceSheet report. |
Label4 | String | The Value column of the BalanceSheet report. |
Label5 | String | The Label column of the BalanceSheet report. |
Label6 | String | The Value column of the BalanceSheet report. |
Label7 | String | The Label column of the BalanceSheet report. |
Label8 | String | The Value column of the BalanceSheet report. |
Label9 | String | The Label column of the BalanceSheet report. |
Label10 | String | The Value column of the BalanceSheet report. |
Label11 | String | The Label column of the BalanceSheet report. |
Label12 | String | The Value column of the BalanceSheet report. |
Label13 | String | The Value column of the BalanceSheet report. |
AccountId | String | The ID of the row's account from the BalanceSheet report. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Date | Datetime | As at date. |
Periods | Int | The number of periods to go back from ToDate. |
Timeframe | String | How big each period should be (MONTH, QUARTER or YEAR) |
TrackingOptionID1 | String | Show all payable invoices to this date for the contact. |
TrackingOptionID2 | String | Show all payable invoices to this date for the contact. |
PaymentsOnly | Boolean | Set this to true to get cash transactions only. |
StandardLayout | Boolean | If you set this parameter to true then no custom report layouts will be applied to response. |
Returns the transactions listed on the account.
A bankAccountId may be provided when querying this view. If one is not given, then this report will be run for each bank account stored in Xero.
Name | Type | Description |
BankAccountId | String | The ID of the bank account this is reporting on |
Date | Date | The date when the transaction occurred |
Description | String | The transaction's description |
Reference | String | The transaction's reference value |
Reconciled | String | Whether the transaction has been reconciled ('Yes' or 'No') |
Source | String | Where the transaction came from |
Amount | Float | The transaction's value |
Balance | Float | The statement's running total |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
fromDate | Date | The start date of the statement |
toDate | Date | The end date of the statement. Must be within 365 days of the start. |
Returns the balances and cash movements for each bank account.
Name | Type | Description |
ID [KEY] | Integer | The row number. |
BankAccount | String | The Bank account number. |
OpeningBalance | Decimal | Amount of funds in the account at the beginning of the financial period. |
CashReceived | Decimal | Amount added to the account. |
CashSpent | Decimal | Amount subtracted from the account. |
FXGain | Decimal | Changes in the account caused by currency conversions |
ClosingBalance | Decimal | Amount of funds in the account at the end of the financial period. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
toDate | Datetime | Starting date. |
fromDate | Datetime | Closing date. |
PaymentsOnly | Boolean | Set this to true to get cash transactions only. |
Returns the Branding Themes defined for the Xero organisation.
The BrandingThemes view allows you to SELECT branding themes for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
BrandingThemeId [KEY] | String | The unique Xero identifier for the branding theme. |
Name | String | The name of the branding theme. |
SortOrder | Int | The ranked order of the branding theme. The value for the default branding theme is 0. |
CreatedDateUTC | Datetime | The timestamp when the branding theme was created. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query Budget line items for a Xero organisation
Name | Type | Description |
Id [KEY] | String | The line item index combined with the ID of the account. |
BudgetId | String | The ID of the invoice. This field is assigned by Xero. |
AccountId | String | The account that applies to this budget line item. |
AccountCode | String | The code of this budget line item account. |
Period | String | The year and month that the balance applies to. |
Amount | Decimal | The amount budgeted to the account for the period. |
Notes | String | Any notes that apply to the balance. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
DateFrom | Datetime | Includes budget periods starting from this date. |
DateTo | Datetime | Includes budget periods until this date. |
Query Budgets for a Xero organisation
Name | Type | Description |
BudgetId [KEY] | String | The ID of the budget. This field is assigned by Xero. |
Type | String | The type of the budget, either OVERALL or TRACKING. |
Description | String | The budget description. |
TrackingCategory1_CategoryId | String | The ID of a tracking category. |
TrackingCategory1_OptionId | String | The ID a a tracking category option. |
TrackingCategory1_Name | String | The name of the tracking category. |
TrackingCategory1_Option | String | The value of the tracking category option. |
TrackingCategory2_CategoryId | String | The ID of a tracking category. |
TrackingCategory2_OptionId | String | The ID a a tracking category option. |
TrackingCategory2_Name | String | The name of the tracking category. |
TrackingCategory2_Option | String | The value of the tracking category option. |
UpdatedDateUTC | Datetime | The date when the budget was last updated. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Returns a monthly Budget Summary.
Name | Type | Description |
ID [KEY] | Integer | The row number. |
BankAccount | String | The Bank account number. |
Period1 | String | Period 1 of the report |
Period2 | String | Period 2 of the report |
Period3 | String | Period 3 of the report |
Period4 | String | Period 4 of the report |
Period5 | String | Period 5 of the report |
Period6 | String | Period 6 of the report |
Period7 | String | Period 7 of the report |
Period8 | String | Period 8 of the report |
Period9 | String | Period 9 of the report |
Period10 | String | Period 10 of the report |
Period11 | String | Period 11 of the report |
Period12 | String | Period 12 of the report |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Date | Datetime | As at date. |
Periods | Integer | The number of periods to compare (integer between 1 and 12). |
Timeframe | Integer | The period size to compare to: Specify 1 for month, 3 for quarter, or 12 for year). |
Query CIS settings for contacts in a Xero organisation.
Name | Type | Description |
ContactId [KEY] | String | The unique Xero identifier for the contact. |
CISEnabled | Boolean | Whether the contact is currently a CIS subcontractor |
Rate | Int | The CIS deduction rate for the contact, in percent |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query credit note allocations for a Xero organisation.
Name | Type | Description |
Id [KEY] | String | The allocation index combined with the ID of the credit note. |
CreditNoteId | String | The ID of the credit note. |
Allocation_AppliedAmount | Decimal | The amount to be applied from this credit note to a given invoice. |
Allocation_Date | Date | The date the allocation was made. |
Allocation_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation_InvoiceNumber | String | The Invoice this allocation has been applied to. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieves the Currencies for a Xero organisation.
The Currencies table allows you to SELECT currencies for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Code [KEY] | String | The ISO 4217 currency code. |
Description | String | A name that identifies the currency. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Returns a summary of changes between a month and the previous month
A date may be provided when querying this view. If it is not provided, then this report will compare the current month with the previous month.
Name | Type | Description |
Metric | String | The value that is being compared between months |
ThisMonth | Decimal | The value of the metric at this month |
PreviousMonth | Decimal | The value of the metric at the previous month |
Variance | Decimal | The percent difference between the previous month and this month. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
date | Date | The month to compare with the previous month. |
Create and query payments for a Xero organisation.
The ExpenseClaimPayments view allows you to SELECT payments to different expense claims for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.
Note that this table does not include other types of Payments - to retrieve those, you will need to read from the Payments table.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Id [KEY] | String | The line item index combined with the unique, Xero-generated identifier for the expense claim. |
ExpenseClaimId | String | The ID of the expense claim that this payment belongs to |
Date | Date | The date the payment is being made. |
Amount | Decimal | The amount of the payment. This value must be less than or equal to the outstanding amount owing on the expense claim. |
Reference | String | A optional description for the payment. |
AccountId | String | The ID of the account used to make the payment. The account type must be BANK or payments to the account must be enabled. |
AccountCode | String | The account code of the account used to make the payment. |
AccountName | String | The account code of the account used to make the payment. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create, update, delete, and query claimed receipts for a Xero organisation.
The ExpenseClaimReceipts view allows you to SELECT claimed receipt line items for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated ExpenseClaimID.
Note that this table does not include draft receipts - to retrieve those, you will need to read from the Receipts table.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Id [KEY] | String | The line item index combined with the unique, Xero-generated identifier for the expense claim. |
ExpenseClaimId | String | The ID of the expense claim that this receipt belongs to |
ReceiptId | String | The unique, Xero-generated identifier for the receipt. |
Date | Date | The date of the receipt. |
Contact_ContactId | String | The ID of the contact. A contact must be specified when creating a receipt. |
Contact_Name | String | The name of the contact. |
LineItem_Description | String | The description for the item. |
LineItem_UnitAmount | Decimal | The unit amount of the item. |
LineItem_AccountCode | String | The code for the associated account. |
LineItem_Quantity | Double | The quantity of the item. |
LineItem_TaxType | String | The tax type. This field can be used to override the default tax code for the selected account. |
LineItem_LineAmount | Decimal | The total of the unit amount multiplied by the quantity. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | The percentage discount being applied to the line item. |
User_UserId | String | The user in the organisation that the expense claim receipt is for. |
Reference | String | Additional reference number. |
LineAmountTypes | String | This field specifies whether line amounts include tax (default). When this field is not specified, line amounts totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
SubTotal | Decimal | The total, excluding taxes, of the receipt. |
TotalTax | Decimal | The total tax on the receipt. |
Total | Decimal | The total of the receipt -- the sum of SubTotal plus TotalTax. |
Status | String | The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED. |
ReceiptNumber | String | The Xero-generated number of the receipt in sequence in the current claim for the given user. |
UpdatedDateUTC | Datetime | The date when the receipt was last updated. |
HasAttachments | Boolean | This field indicates whether the receipt has attachments. |
URL | String | A link to a source document. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query BankTransfer history and notes for a Xero organization.
The HistoryBankTransfers view allows you to read the notes created on a transfer, and the history of changes to that transfer. If a BankTransferId is not provided, the history of all BankTransfers will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
BankTransferId | String | The ID of the bank transfer the history item belongs to. |
Changes | String | What type of change happened on the bank transfers |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query ExpenseClaim history and notes for a Xero organization.
The HistoryExpenseClaims view allows you to read the notes created on a expense claim, and the history of changes to that expense claim. If a ExpenseClaimId is not provided, the history of all ExpenseClaims will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
ExpenseClaimId | String | The ID of the expense claim the history item belongs to. |
Changes | String | What type of change happened on the expense claim |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Item history and notes for a Xero organization.
The HistoryItems view allows you to read the notes created on a item, and the history of changes to that item. If a ItemId is not provided, the history of all Items will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
ItemId | String | The ID of the item the history item belongs to. |
Changes | String | What type of change happened on the item |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Overpayment history and notes for a Xero organization.
The HistoryOverpayments view allows you to read the notes created on a overpayment, and the history of changes to that overpayment. If a OverpaymentId is not provided, the history of all Overpayments will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
OverpaymentId | String | The ID of the overpayment the history item belongs to. |
Changes | String | What type of change happened on the overpayment |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Payment history and notes for a Xero organization.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
PaymentId | String | The ID of the payment the history item belongs to. |
Changes | String | What type of change happened on the payment |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Prepayment history and notes for a Xero organization.
The HistoryPrepayments view allows you to read the notes created on a prepayment, and the history of changes to that prepayment. If a PrepaymentId is not provided, the history of all Prepayments will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
PrepaymentId | String | The ID of the prepayment the history item belongs to. |
Changes | String | What type of change happened on the prepayment |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Receipt history and notes for a Xero organization.
The HistoryReceipts view allows you to read the notes created on a receipt, and the history of changes to that receipt. If a ReceiptId is not provided, the history of all Receipts will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
ReceiptId | String | The ID of the receipt the history item belongs to. |
Changes | String | What type of change happened on the receipt |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query RepeatingInvoice history and notes for a Xero organization.
The HistoryRepeatingInvoices view allows you to read the notes created on a repeating invoice, and the history of changes to that repeating invoice. If a RepeatingInvoiceId is not provided, the history of all RepeatingInvoices will be retrieved.
Name | Type | Description |
Id [KEY] | String | A combination of the position of the history item and when it occurred. |
RepeatingInvoiceId | String | The ID of the repeating invoice the history item belongs to. |
Changes | String | What type of change happened on the repeating invoice |
Date | Datetime | When the change occurred |
User | String | The name of the user that made the change |
Details | String | The change that was made |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query the line items in journals for a Xero organisation.
The Journals view allows you to SELECT the journal lines in journals for a Xero organization. The ID column is generated by the connector; this value combines the index of the line item with the unique, Xero-generated JournalId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Id [KEY] | String | The journal line index combined with the unique Xero identifier of the journal. |
JournalId | String | The unique Xero identifier of the journal. |
JournalDate | Date | The date the journal was posted. |
JournalNumber | Integer | The journal number, assigned by Xero. |
CreatedDateUTC | Datetime | The date the journal was entered in the system. |
Reference | String | A reference value. |
SourceId | String | The identifier for the source transaction (e.g. InvoiceId). |
SourceType | String | The journal source type. The type of transaction that created the journal. (e.g ACCREC, ACCPAY, ACCRECCREDIT) |
JournalLine_JournalLineId | String | The unique Xero identifier of the journal line item. |
JournalLine_Description | String | The description of the journal line item. |
JournalLine_AccountId | String | The account associated with the line item in the journal. |
JournalLine_AccountCode | String | Customer-defined alphanumeric account code; e.g, 200 or SALES. |
JournalLine_AccountType | String | The type of the account; e.g., BANK, CURRENT, or CRRLIAB. |
JournalLine_AccountName | String | The name of the account. |
JournalLine_NetAmount | Decimal | The net amount of the line item. This value is positive for debits and negative for credits. |
JournalLine_GrossAmount | Decimal | The net amount plus the tax amount. |
JournalLine_TaxAmount | Decimal | The total tax on a journal line. |
JournalLine_TaxType | String | The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, etc. |
JournalLine_TaxName | String | A name that identifies the tax type. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ModifiedAfter | String | If set, only journals created or modified since this timestamp will be returned e.g. 2009-11-12T00:00:00. |
PaymentsOnly | Boolean | Set this to true to get cash transactions only. |
Offset | Int | If provided, only journals with a higher JournalNumber will be returned. |
Query organisation data for a Xero organisation.
The organization view allows you to SELECT organization data for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
ShortCode [KEY] | String | A unique identifier for the organisation. |
APIKey | String | Unique APIKey for Xero-to-Xero transactions. |
Name | String | Display name of the organisation shown in Xero. |
LegalName | String | Organisation name shown on reports. |
PaysTax | Boolean | Whether the organisation is registered with a local tax authority. |
Version | String | The version of Xero used by the organisation. |
BaseCurrency | String | Default currency for the organisation. |
CountryCode | String | Country code for the organisation. |
IsDemoCompany | String | Whether the organisation is a demo company. |
OrganisationStatus | String | Will be set to ACTIVE if you can connect to the organisation via the Xero API. |
RegistrationNumber | String | The registration number for NZ, AU, and UK organisations. |
TaxNumber | String | Tax number of the organisation. |
FinancialYearEndDay | String | Financial year end day of the organisation. For example, 1-31. |
FinancialYearEndMonth | String | Financial year end month of the organisation. For example, 1-12. |
PeriodLockDate | Date | Period lock date of the organisation. |
EndOfYearLockDate | Date | End of year lock date of the organisation. |
CreatedDateUTC | Datetime | Timestamp of when the organisation was created in Xero. |
UpdatedDateUTC | Datetime | Timestamp of the last change to the user record. |
OrganisationEntityType | String | The entity type of the organisation. For example, COMPANY. |
Timezone | String | The time zone for the organisation. |
Edition | String | BUSINESS or PARTNER. Parnter edition organizations have restricted functionality. |
Class | String | One of: DEMO, TRIAL, STARTER, STANDARD, PREMIUM, PREMIUM_20, PREMIUM_50, PREMIUM_100, LEDGER, GST_CASHBOOK, NON_GST_CASHBOOK. |
LineOfBusiness | String | Description of business type of the organisation. |
Street_AddressLine1 | String | The street address line 1. |
Street_AddressLine2 | String | The street address line 2. |
Street_AddressLine3 | String | The street address line 3. |
Street_AddressLine4 | String | The street address line 4. |
Street_City | String | The street address city. |
Street_PostalCode | String | The street address postal code. |
Street_Country | String | The street address country. |
Street_Region | String | The street address region. |
Street_AttentionTo | String | The street address attention-to line. |
POBox_AddressLine1 | String | The PO box address line 1. |
POBox_AddressLine2 | String | The PO box address line 2. |
POBox_AddressLine3 | String | The PO box address line 3. |
POBox_AddressLine4 | String | The PO box address line 4. |
POBox_City | String | The PO box address city. |
POBox_PostalCode | String | The PO box address postal code. |
POBox_Country | String | The PO box address country. |
POBox_Region | String | The PO box address region. |
POBox_AttentionTo | String | The PO box address attention-to line. |
DDI_PhoneNumber | String | The DDI phone number. |
DDI_PhoneAreaCode | String | The DDI area code. |
DDI_PhoneCountryCode | String | The DDI country code. |
Default_PhoneNumber | String | The default phone number. |
Default_PhoneAreaCode | String | The default area code. |
Default_PhoneCountryCode | String | The default country code. |
Fax_PhoneNumber | String | The fax phone number. |
Fax_PhoneAreaCode | String | The fax area code. |
Fax_PhoneCountryCode | String | The fax country code. |
Mobile_PhoneNumber | String | The mobile phone number. |
Mobile_PhoneAreaCode | String | The mobile area code. |
Mobile_PhoneCountryCode | String | The mobile country code. |
Facebook_URL | String | The URL to the Facebook page of the organisation. |
Twitter_URL | String | The URL to the Twitter page of the organisation. |
GooglePlus_URL | String | The URL to the Google Plus page of the organisation. |
LinkedIn_URL | String | The URL to the LinkedIn page of the organisation. |
Website_URL | String | A URL to the website of the organisation. |
PaymentTerms_Bills_Day | String | Default payment terms for bills (days). |
PaymentTerms_Bills_Type | String | Default payment terms for bills (type). |
PaymentTerms_Sales_Day | String | Default payment terms for sales (days). |
PaymentTerms_Sales_Type | String | Default payment terms for sales (type). |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query organisation data for a Xero organisation.
Name | Type | Description |
Name [KEY] | String | The name of the permission. |
Status | Bool | Whether the action is permitted to this app. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query overpayment allocations for a Xero organisation.
Name | Type | Description |
Id [KEY] | String | The allocation index combined with the ID of the overpayment. |
OverpaymentId | String | The ID of the overpayment. |
Allocation_AppliedAmount | Decimal | The amount to be applied from this overpayment to a given invoice. |
Allocation_Date | Date | The date the allocation was made. |
Allocation_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation_InvoiceNumber | String | The Invoice this allocation has been applied to. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Overpayments for a Xero organisation.
Name | Type | Description |
OverpaymentId [KEY] | String | The ID of the overpayment. |
Date | Date | The date the overpayment was made. |
Type | String | The overpayment type. The valid values are RECEIVE-OVERPAYMENT and SPEND-OVERPAYMENT. |
RemainingCredit | Decimal | The remaining credit balance on the overpayment. |
Contact_ContactId | String | The ID of the contact. |
Contact_Name | String | The name of the contact. |
Status | String | The status of the overpayment. Valid values are AUTHORISED, PAID, and VOIDED. |
SubTotal | Decimal | The subtotal for the overpayment, which excludes taxes. |
TotalTax | Decimal | The total tax for the overpayment. |
Total | Decimal | The total for the overpayment, including tax -- the sum of SubTotal and TotalTax. |
LineItem_LineItemId | String | The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | The description for the line item. This field must contain at least 1 character and is required to create an approved invoice. |
LineItem_Quantity | Double | The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive. |
LineItem_UnitAmount | Decimal | The unit amount of the associated item in the line item. This field is required for invoice approval. |
LineItem_ItemCode | String | The code that identifies the associated item. |
LineItem_AccountCode | String | The account code. This must be active for the organisation. This field is required for invoice approval. |
LineItem_TaxType | String | The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineAmountTypes | String | The LineAmount Type of the overpayment. Valid values are Exclusive, Inclusive, and NoTax. |
CurrencyCode | String | Currency used for the overpayment. |
CurrencyRate | String | The currency rate for a multicurrency overpayment. If no rate is specified, the XE.com day rate is used. |
UpdatedDateUTC | Datetime | The date when the overpayment was last updated. |
Allocation1_AppliedAmount | Decimal | The amount to be applied from this credit note to a given invoice. |
Allocation1_Date | Date | The date the allocation was made. |
Allocation1_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation1_InvoiceNumber | String | The Invoice this allocation has been applied to. |
Allocation2_AppliedAmount | Decimal | The amount to be applied from this credit note to a given invoice. |
Allocation2_Date | Date | The date the allocation was made. |
Allocation2_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation2_InvoiceNumber | String | The Invoice this allocation has been applied to. |
PaymentIds | String | A comma-delimited list of payment Ids associated with the overpayment. |
HasAttachments | Boolean | Boolean to indicate if a overpayment has an attachment. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query prepayment allocations for a Xero organisation.
Name | Type | Description |
Id [KEY] | String | The allocation index combined with the ID of the prepayment. |
PrepaymentId | String | The ID of the prepayment. |
Allocation_AppliedAmount | Decimal | The amount to be applied from this prepayment to a given invoice. |
Allocation_Date | Date | The date the allocation was made. |
Allocation_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation_InvoiceNumber | String | The Invoice this allocation has been applied to. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create and query Prepayments for a Xero organisation.
Name | Type | Description |
PrepaymentId [KEY] | String | The ID of the prepayment. |
Date | Date | The date the prepayment was made. |
Reference | String | A optional description for the payment. |
Type | String | The prepayment type. The valid values are RECEIVE-PREPAYMENT and SPEND-PREPAYMENT. |
RemainingCredit | Decimal | The remaining credit balance on the prepayment. |
Contact_ContactId | String | The ID of the contact. |
Contact_Name | String | The name of the contact. |
Status | String | The status of the prepayment. Valid values are AUTHORISED, PAID, and VOIDED. |
SubTotal | Decimal | The subtotal for the prepayment, which excludes taxes. |
TotalTax | Decimal | The total tax for the prepayment. |
Total | Decimal | The total for the prepayment, including tax -- the sum of SubTotal and TotalTax. |
LineItem_LineItemId | String | The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | The description for the line item. This field must contain at least 1 character and is required to create an approved invoice. |
LineItem_Quantity | Double | The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive. |
LineItem_UnitAmount | Decimal | The unit amount of the associated item in the line item. This field is required for invoice approval. |
LineItem_ItemCode | String | The code that identifies the associated item. |
LineItem_AccountCode | String | The account code. This must be active for the organisation. This field is required for invoice approval. |
LineItem_TaxType | String | The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineAmountTypes | String | The LineAmount Type of the prepayment. Valid values are Exclusive, Inclusive, and NoTax. |
CurrencyCode | String | Currency used for the prepayment. |
CurrencyRate | String | The currency rate for a multicurrency prepayment. If no rate is specified, the XE.com day rate is used. |
UpdatedDateUTC | Datetime | The date when the prepayment was last updated. |
Allocation1_AppliedAmount | Decimal | The amount to be applied from this credit note to a given invoice. |
Allocation1_Date | Date | The date the allocation was made. |
Allocation1_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation1_InvoiceNumber | String | The Invoice this allocation has been applied to. |
Allocation2_AppliedAmount | Decimal | The amount to be applied from this credit note to a given invoice. |
Allocation2_Date | Date | The date the allocation was made. |
Allocation2_InvoiceId | String | The ID of the invoice this allocation has been applied to. |
Allocation2_InvoiceNumber | String | The Invoice this allocation has been applied to. |
PaymentIds | String | A comma-delimited list of payment Ids associated with the prepayment. |
HasAttachments | Boolean | Boolean to indicate if a prepayment has an attachment. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Returns a Profit and Loss report over a date range.
There are a few different ways the range of dates can be specified when using this report:
1. When no parameters are specified, the report is run for the current month:
SELECT * FROM ProfitAndLoss
2. When just FromDate is specified, the report is run from FromDate to today:
SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01'In this form, FromDate must be within a year of the current date.
3. When FromDate and ToDate are specified, the report is run from FromDate to ToDate:
SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01' AND ToDate = '2018-04-01'In this form, FromDate must be within a year of ToDate.
4. When Periods and Timeframe are specified, the report is run from today back to the start of the Timeframe:
SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH'
Note: When specifying the 'Month' timeframe, the Xero endpoint may generate a report for the last 30 days from the start of the specified month, even for the months with 31 days. As a workaround, you should specify a 31-day month in the ToDate column as an additional query criteria.
SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND ToDate='2020-12-31'
5. When Periods, Timeframe and ToDate are specified, the report is run from ToDate back to the start of the Timeframe:
SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH' AND ToDate = '2018-04-01'
Name | Type | Description |
ID [KEY] | Integer | The row number. |
Label1 | String | The Label column of the ProfitAndLossStandard report. |
Label2 | String | The Value column of the ProfitAndLossStandard report. |
Label3 | String | The Label column of the ProfitAndLossStandard report. |
Label4 | String | The Value column of the ProfitAndLossStandard report. |
Label5 | String | The Label column of the ProfitAndLossStandard report. |
Label6 | String | The Value column of the ProfitAndLossStandard report. |
Label7 | String | The Label column of the ProfitAndLossStandard report. |
Label8 | String | The Value column of the ProfitAndLossStandard report. |
Label9 | String | The Label column of the ProfitAndLossStandard report. |
Label10 | String | The Value column of the ProfitAndLossStandard report. |
Label11 | String | The Label column of the ProfitAndLossStandard report. |
Label12 | String | The Value column of the ProfitAndLossStandard report. |
Label13 | String | The Value column of the ProfitAndLossStandard report. |
AccountId | String | The ID of the row's account from the ProfitAndLossStandard report. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
FromDate | Datetime | Show all payable invoices from this date for contact. |
ToDate | Datetime | Show all payable invoices to this date for the contact. |
Periods | Int | The number of periods to go back from ToDate. |
Timeframe | String | How big each period should be (MONTH, QUARTER or YEAR) |
TrackingCategoryID | String | If you specify the trackingCategoryID parameter then the Profit and Loss Report will show figures for each of the options in the category as separate columns. |
TrackingOptionID | String | If you specify this parameter in addition to the trackingCategoryID then just one option will be returned (i.e. 1 column only) |
TrackingCategoryID2 | String | If you specify a second trackingCategoryID parameter then the Profit and Loss Report will show figures for each combination of options from the two categories as separate columns. |
TrackingOptionID2 | String | If you specify this parameter in addition to a second trackingCategoryID then just one option will be returned combined with the option/s from the first tracking category. |
PaymentsOnly | Boolean | Set this to true to get cash transactions only. |
StandardLayout | Boolean | If you set this parameter to true then no custom report layouts will be applied to response. |
Query quotes for a Xero organization
Name | Type | Description |
Id [KEY] | String | The line item index combined with the ID of the Quote. |
QuoteId | String | The ID of the Quote. This field is assigned by Xero. |
ContactId | String | The ID of the contact. |
ContactName | String | The name of the contact. |
Date | Date | The date the quote was issued. |
ExpiryDate | Date | The date the quote expires. |
Status | String | The status of the quote. |
LineItem_LineItemId | String | The Xero generated identifier for a line item. |
LineItem_Description | String | The description of the line item. |
LineItem_Quantity | Double | The quantity of the associated item in the line item. |
LineItem_UnitAmount | Decimal | The unit amount of the associated item in the line item. |
LineItem_ItemCode | String | The code that identifies the associated item. |
LineItem_AccountCode | String | The account code. This must be active for the organisation. |
LineItem_TaxType | String | The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_DiscountRate | Double | The discount percentage being applied to the line item. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
QuoteNumber | String | The unique, alphanumeric code identifying the quote. |
LineAmountTypes | String | This field specifies whether line amounts include tax. The valid values are Exclusive, Inclusive, and NoTax. |
Reference | String | An additional reference number for ACCREC-type invoices. |
CurrencyCode | String | The ISO 4217 currency code. |
CurrencyRate | Decimal | The currency rate for a multicurrency invoice. If no rate is specified, the XE.com day rate is used. |
SubTotal | Decimal | The subtotal for the invoice, which excludes taxes. |
TotalTax | Decimal | The total tax for the invoice. |
Total | Decimal | The total for the invoice, including tax -- the sum of SubTotal and TotalTax. |
TotalDiscount | Decimal | Total of discounts applied on the invoice line items. |
BrandingThemeId | String | The ID of the branding theme. |
Terms | String | The terms of the quote. |
UpdatedDateUTC | Datetime | The date when the invoice was last updated. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query Repeating Invoice templates.
The RepeatingInvoice view allows you to SELECT the line items in repeating invoices for a Xero organization. The ID column is generated by the connector; this value combines the index of the line item with the unique, Xero-generated RepeatingInvoiceId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Id [KEY] | String | The line item index combined with the ID of the invoice. |
RepeatingInvoiceId | String | The ID of the invoice. This field is assigned by Xero. |
Type | String | The type of the invoice. The valid values are ACCPAY and ACCREC. |
ContactId | String | The ID of the contact. This value is required when inserting. |
ContactName | String | The name of the contact. This value is required when inserting. |
Schedule_Period | Integer | Integer used with the unit for the scheduled period. e.g. 1 (every 1 week). |
Schedule_Unit | String | the schedule unit. The valid values are WEEKLY or MONTHLY. |
Schedule_DueDate | Integer | Integer used with the due date type for the scheduled period. e.g. 20 (of following month). |
Schedule_DueDateType | String | The due date type of the schedule. The valid values are DAYSAFTERBILLDATE, DAYSAFTERBILLMONTH, OFCURRENTMONTH, and OFFOLLOWINGMONTH |
Schedule_StartDate | Date | Invoice date the first invoice in the repeating schedule. |
Schedule_NextScheduledDate | Date | The calendar date of the next invoice in the schedule to be generated. |
Schedule_EndDate | Date | Invoice end date. Only returned if the template has an end date set. |
LineItem_LineItemId | String | The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated. |
LineItem_Description | String | The description for the line item. This field must contain at least 1 character and is required to create an approved invoice. |
LineItem_Quantity | Double | The quantity of the associated item in the line item. This field is required for invoice approval and must be zero or positive. |
LineItem_UnitAmount | Decimal | The unit amount of the associated item in the line item. This field is required for invoice approval. |
LineItem_ItemCode | String | The code that identifies the associated item. |
LineItem_AccountCode | String | The account code. This must be active for the organisation. This field is required for invoice approval. |
LineItem_TaxType | String | The tax type. This field is used to override AccountCode, the default tax code for the selected account. |
LineItem_TaxAmount | Decimal | The automatically calculated percentage of tax, based on the tax rate, for the line amount. |
LineItem_LineAmount | Decimal | The total of the quantity multiplied by the unit amount with any discounts applied. |
LineItem_TrackingCategory1_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory1_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_CategoryName | String | The name of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionId | String | The ID of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_OptionName | String | The option of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_TrackingCategory2_Option_Stat | String | The status of a tracking category. The API allows two optional tracking categories to be defined. |
LineItem_DiscountRate | Double | The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type (sales) invoices. |
LineAmountTypes | String | This field specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax. |
Reference | String | An additional reference number for ACCREC-type invoices. |
BrandingThemeId | String | The ID of the branding theme. |
CurrencyCode | String | The ISO 4217 currency code. |
Status | String | The status code for the invoice. The valid values are DRAFT, SUBMITTED, and AUTHORISED. |
SubTotal | Decimal | The subtotal for the invoice, which excludes taxes. |
TotalTax | Decimal | The total tax for the invoice. |
Total | Decimal | The total for the invoice, including tax -- the sum of SubTotal and TotalTax. |
HasAttachments | Boolean | This field indicates whether the invoice has an attachment. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query the list of organizations connected to your Xero account.
Name | Type | Description |
TenantId [KEY] | String | A unique identifier for the tenant. |
Name | String | The name of the organization or practice |
Type | String | Whether the tenant is an organization or a practice |
IsActive | Boolean | Whether the connection is currently using this tenant |
Query Tracking Categories for a Xero organisation.
The TrackingCategories view allows you to SELECT tracking categories for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated TrackingCategoryId.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
Id [KEY] | String | The tracking option index combined with the unique Xero identifier of the tracking category. |
TrackingCategoryId | String | The unique Xero identifier of the tracking category. |
Name | String | The name of the tracking category. For example, Department or Region. |
Status | String | The status of a tracking category. This will always be ACTIVE. |
TrackingOptionId | String | The unique Xero identifier of the tracking option. |
OptionName | String | The name of the tracking category option. For example, North or South. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve a monthly Trial Balance.
Name | Type | Description |
ID [KEY] | Integer | The row number. |
Account | String | Account name. |
Debit | String | Debit trial balance for current month up to the date specified. |
Credit | String | Credit trial balance for current month up to the date specified. |
YTDDebit | String | Year to Date Debit. |
YTDCredit | String | Year to Date Credit. |
AccountId | String | Account ID |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Date | Datetime | As at date. |
PaymentsOnly | Boolean | Set this to true to get cash transactions only. |
Query users for a Xero organisation.
The Users table allows you to SELECT users for a Xero organization.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Name | Type | Description |
UserId [KEY] | String | The unique Xero identifier of the user. |
FirstName | String | First name of the user. |
LastName | String | Last name of the user. |
UpdatedDateUTC | Datetime | Timestamp of the last change to the user record. |
IsSubscriber | Boolean | Indicates if the user is the subscriber. |
OrganisationRole | String | Organisation role of the user, values include READONLY, INVOICEONLY, STANDARD, FINANCIALADVISER, MANAGEDCLIENT, and CASHBOOKCLIENT. |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
AllocatetoInvoice | Allocates a document against an Invoice. |
DownloadAttachment | Obtains an attachment from a specified document in Xero. |
GetLimitInfo | Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant. |
GetOAuthAccessToken | Obtains the OAuth access token to be used for authentication with Xero. |
GetOAuthAuthorizationURL | Obtains the OAuth authorization URL for authentication with various Xero services. |
ListAttachments | Retrieves a list of attachments that have been uploaded against a document in Xero. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Xero services. |
RevokeTenant | Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant. |
SendInvoiceEmail | Sends an email to the contacts of a sales invoice |
SwitchTenant | Changes the tenant this connection fetches data from. |
UploadAttachment | Obtains a list of attachments an item has in Xero. |
Allocates a document against an Invoice.
Name | Type | Required | Description |
Type | String | True | The type of document to allocate.
The allowed values are CreditNotes, Overpayments, Prepayments. |
DocumentId | String | True | The ID of the document to be allocated. |
InvoiceId | String | True | The invoice this document is being allocated against. |
AppliedAmount | String | True | The amount being applied to the invoice. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Date | String | False | The date the overpayment is applied. |
Name | Type | Description |
Success | String | A boolean indicating if the operation was successful. |
Obtains an attachment from a specified document in Xero.
Name | Type | Required | Description |
Table | String | True | The table containing the document with the attachment.
The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals. |
ObjectId | String | True | The unique ID of the item to obtain an attachment from. |
Filename | String | True | The name of the attachment to retrieve. |
DownloadPath | String | False | The path of the file to be saved. Must be a path to a file, not just a directory. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
Success | String | A boolean indicating if the operation was successful. |
FileData | String | Base 64 encoded file data for the attachment received. |
Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.
If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:
SELECT * FROM Organisation LIMIT 1
Name | Type | Description |
TenantId | String | ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one. |
AppRemaining | Integer | The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants. |
MinuteRemaining | Integer | The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant. |
DayRemaining | Integer | The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant. |
RetryAfter | Integer | The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant. |
LastModified | Datetime | When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time. |
Obtains the OAuth access token to be used for authentication with Xero.
Name | Type | Required | Description |
AuthMode | String | True | The type of authentication mode to use.
The allowed values are APP, WEB. The default value is WEB. |
AuthToken | String | False | The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
AuthKey | String | False | The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
CallbackURL | String | False | This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/'). |
Verifier | String | False | The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
PKCEVerifier | String | False | The PKCEVerifier returned by GetOAuthAuthorizationURL. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthAccessTokenSecret | String | The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Obtains the OAuth authorization URL for authentication with various Xero services.
Name | Type | Required | Description |
CallbackURL | String | False | This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal. |
Scope | String | False | The space-separated list of scopes you are requesting from Xero. By default all scopes are requested. |
State | String | False | A state token to include in the authorization URL. Not included by default. |
Name | Type | Description |
URL | String | The URL to complete user authentication. |
AuthToken | String | A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
AuthKey | String | A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
PKCEVerifier | String | A random value used as input for GetOAuthAccessToken in the PKCE flow. |
Retrieves a list of attachments that have been uploaded against a document in Xero.
Name | Type | Required | Description |
Table | String | True | The table containing the document to retrieve a list of attachments from.
The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals. |
ObjectId | String | False | The unique ID of the document to retrieve a list of attachments from. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
AttachmentId | String | The ID of the attachment. |
FileName | String | The file name of the attachment. |
URL | String | The URL of the attachment. |
MimeType | String | The Internet media type of the attachment. |
ContentLength | String | The size of the attachment in bytes. |
Refreshes the OAuth access token used for authentication with various Xero services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned from the original authorization code exchange. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to revoke, as given in the Tenants view |
Name | Type | Description |
Success | String | Whether the connection was successfully revoked |
Sends an email to the contacts of a sales invoice
Name | Type | Required | Description |
InvoiceId | String | True | The InvoiceId of the invoice to mail |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
Success | String | A boolean indicating if the operation was successful. |
Changes the tenant this connection fetches data from.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to attach to. If empty, the current tenant is not changed. |
Name | Type | Description |
ActiveTenant | String | The ID of the tenant that is currently active. |
Obtains a list of attachments an item has in Xero.
Name | Type | Required | Description |
Table | String | True | The table containing the document with the attachment.
The allowed values are Invoices, Receipts, CreditNotes, BankTransactions, BankTransfers, Contacts, Accounts, ManualJournals. |
ObjectId | String | True | The unique ID of the item to obtain an attachment from. |
FilePath | String | False | The location of the file to attach. |
FileData | String | False | Base-64 encoded data for the file. Required if FilePath is not defined. |
FileStream | String | False | Stream of input data. Required if Filename and FileData are not defined. |
Filename | String | False | The name of the file to attach. Required if FilePath is not defined. |
ContentType | String | False | The type of file to attach. |
IncludeOnline | String | False | Sets an attachment to be included with the invoice when viewed online. Set false by default. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
Success | String | A boolean indicating if the operation was successful. |
AttachmentId | String | The ID of the attachment. |
FileName | String | The file name of the attachment. |
URL | String | The URL of the attachment. |
MimeType | String | The Internet media type of the attachment. |
ContentLength | String | The size of the attachment in bytes. |
IncludeOnline | String | A boolean indicating if IncludeOnline was set to true. |
The Jitterbit Connector for Xero models the Xero Australian Payroll API as relational tables, Views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
However, note that deletes are not supported by the Australian Payroll API.
Tables describes the available tables.
Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.
Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Employees | Create, update, and query employees for a Xero organisation. |
LeaveApplications | Create, update, and query Leave Applications for a Xero organisation. |
PayItemsDeductions | Create, update, and query PayItems for a Xero organisation. |
PayItemsEarnings | Create, update, and query PayItems for a Xero organisation. |
PayItemsLeave | Create, update, and query PayItems for a Xero organisation. |
PayItemsReimbursements | Create, update, and query PayItems for a Xero organisation. |
PayrollCalendars | Create, update, and query Payroll Calendars for a Xero organisation. |
PayRuns | Create, update, and query payruns for a Xero organisation. |
SuperFunds | Retrieve, add and update Payroll Super Funds in a Xero organisation. |
Timesheets | Create, update, and query Timesheets for a Xero organisation. |
Create, update, and query employees for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
EmployeeID [KEY] | String | True |
The unique identifier of the employee. This field is assigned by Xero. | |
Status | String | False |
The status of the employee. Valid values are ACTIVE and TERMINATED. | |
Title | String | False |
The title of the employee. | |
FirstName | String | False |
The first name of the employee. | |
MiddleNames | String | False |
The middle names of the employee. | |
LastName | String | False |
The last name of the employee. | |
DateOfBirth | Date | False |
The employee's birthday. | |
JobTitle | String | False |
The job title of the employee. | |
Gender | String | False |
The gender of the employee. | |
Phone | String | False |
The employee's home phone number. | |
Mobile | String | False |
The employee's mobile phone number. | |
String | False |
The employee's email address. | ||
StartDate | Date | False |
The employee's hire date. | |
TerminationDate | Date | False |
The employee's termination date. | |
TerminationReason | String | False |
The reasion for the eompleeoy's termination, as a one-letter code. | |
OrdinaryEarningsRateID | String | False |
A reference to the employee's pay schedule | |
PayrollCalendarID | String | False |
A reference to the employee's pay schedule | |
IsAuthorisedToApproveLeave | Boolean | False |
Whether the employee is authorised to approve timeoff. | |
IsAuthorisedToApproveTimesheets | Boolean | False |
Whether the employee is authorised to approve timesheets. | |
EmployeeGroupName | String | False |
The name of the tracking category the employee belongs to, if one is assigned. | |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the employee record. | |
HomeAddress_AddressLine1 | String | False |
Address Line 1 for employee home address. | |
HomeAddress_AddressLine2 | String | False |
Address Line 2 for employee home address. | |
HomeAddress_City | String | False |
Suburb for employee home address. | |
HomeAddress_Region | String | False |
State abbreviation for employee home address. | |
HomeAddress_PostalCode | String | False |
PostalCode for employee home address. | |
HomeAddress_Country | String | False |
Country of HomeAddress. | |
TaxDeclaration_EmploymentBasis | String | False |
One of FULLTIME, PARTTIME, CASUAL, LABOURHIRE or SUPERINCOMESTREAM | |
TaxDeclaration_TFNExemptionType | String | False |
If the employee has no TFN, this may be one of NOTQUOTED, PENDING, PENSIONER or UNDER18 | |
TaxDeclaration_TaxFileNumber | String | False |
The last three digits of the employee's TFN | |
TaxDeclaration_AustralianResidentForTaxPurposes | Boolean | False |
Whether the employee is an Australian resident for tax purposes | |
TaxDeclaration_ResidencyStatus | String | False |
One of AUSTRALIANRESIDENT, FOREIGNRESIDENT or WORKINGHOLIDAYMAKER | |
TaxDeclaration_TaxFreeThresholdClaimed | Boolean | False |
Whether the employee claims to make below the tax free threshold | |
TaxDeclaration_TaxOffsetEstimatedAmount | Decimal | False |
Any other tax offsets the employee has claimed | |
TaxDeclaration_HasHELPDebt | Boolean | False |
Whether the employee has HECS or HELP debt | |
TaxDeclaration_HasSFSSDebt | Boolean | False |
Whether the employee has financial supplement debt | |
TaxDeclaration_HasTradeSupportLoanDebt | Boolean | False |
Whether the employee has a trade support loan | |
TaxDeclaration_UpwardVariationTaxWithholdingAmount | Decimal | False |
The extra withholding the employee has requested, in percent | |
TaxDeclaration_EligibleToReceiveLeaveLoading | Boolean | False |
If the employee is eligible to receive extra earnings when they take leave | |
TaxDeclaration_ApprovedWithholdingVariationPercentage | Decimal | False |
The extra withholding the employee has been granted, in percent | |
BankAccounts | String | True |
The Bank accounts for the employee in XML format. | |
PayTemplate | String | True |
The pay template for the employee in XML format. | |
OpeningBalances | String | True |
The opening balances for the employee in XML format. | |
LeaveBalances | String | True |
The leave balances for the empoloyee in XML format. | |
SuperMemberships | String | True |
The Super memberships for the employee in XML format. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query Leave Applications for a Xero organisation.
The Timesheets table allows you to SELECT and INSERT leave applications for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated LeaveApplicationID.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new leave application, the EmployeeID, LeaveTypeID, Title, StartDate and EndDate are required. In addition leave periods may also be provided.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO LeaveApplications(EmployeeID, LeaveTypeID, Title, StartDate, EndDate, LeavePeriodAggregate) VALUES ( 'XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 'Visit relatives', '2021-01-01', '2021-01-07', '<LeavePeriod><NumberOfUnits>8</NumberOfUnits></LeavePeriod> <LeavePeriod><NumberOfUnits>32</NumberOfUnits></LeavePeriod>' )
INSERT INTO LeaveApplications(LeaveApplicationID, LeavePeriods_LeavePeriod_NumberOfUnits) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 32)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
Name | Type | ReadOnly | Filterable | Description |
Id [KEY] | String | True |
The leave period line-item combined with the unique Xero identifier of the leave application. | |
LeaveApplicationID | String | True |
The unique identifier of the Leave Application. This field is assigned by Xero. | |
EmployeeID | String | False |
The Xero identifier for Payroll Employee. | |
LeaveTypeID | String | False |
The Xero identifier for Leave Type. | |
Title | String | False |
The title of the leave (max length = 50). | |
StartDate | String | False |
Start date of the leave (YYYY-MM-DD). | |
EndDate | String | False |
End date of the leave (YYYY-MM-DD). | |
Description | String | False |
The Description of the Leave (max length = 200). | |
LeavePeriodAggregate | String | False |
Used to define LeavePeriod rows using XML values. Should be provided on INSERT only. | |
LeavePeriods_LeavePeriod_PayPeriodStartDate | String | False |
The start of the payperiod the leave is being drawn from | |
LeavePeriods_LeavePeriod_PayPeriodEndDate | String | False |
The end of the payperiod the leave is being drawn from | |
LeavePeriods_LeavePeriod_LeavePeriodStatus | String | False |
Either SCHEDULED or PROCESSED | |
LeavePeriods_LeavePeriod_NumberOfUnits | String | False |
Amount of leave consumed from this payperiod | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query PayItems for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
DeductionTypeID [KEY] | String | True |
The unique identifier of the PayItem. This field is assigned by Xero. | |
Name | String | False |
Name of the deduction type. | |
DeductionCategory | String | False |
Category of the deduction. | |
AccountCode | String | False |
Customer defined alpha numeric account code. | |
ReducesTax | String | False |
Indicates that this is a pre-tax deduction. | |
ReducesSuper | String | False |
Indicates that this reduces your superannuation guarantee contribution liability. | |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the PayItem record. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query PayItems for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
EarningsRateID [KEY] | String | True |
The unique identifier of the PayItem. This field is assigned by Xero. | |
Name | String | False |
Name of the earnings rate. | |
DisplayName | String | False |
Display name of the earnings rate, as it will appear on payslips. | |
AccountCode | String | False |
Customer defined alpha numeric account code. | |
TypeOfUnits | String | False |
Type of units used to record earnings. | |
IsExemptFromTax | String | False |
You should only set this value if you are sure that a payment is exempt from PAYG withholding. | |
IsExemptFromSuper | String | False |
See the ATO website for details of which payments are exempt from SGC. | |
IsReportableAsW1 | Bool | False |
Whether the earnings rate is subject to W1 witholding. | |
EarningsType | String | False |
The type of Pay Items earning. Valid values are FIXED, ORDINARYTIMEEARNINGS, OVERATIMEEARNINGS, ALLOWANCE and LUMPSUMD. | |
AllowanceType | String | False |
When EarningsType is ALLOWANCE, this reports what the allowance is paid for. | |
AllowanceCategory | String | False |
Determines the kind of allowance when AllowanceType is OTHER. | |
RateType | String | False |
The rate type of the earning. Valid values are FIXEDAMOUNT, MULTIPLE, RATEPERUNIT | |
RatePerUnit | String | False |
Only applicable if RateType is RATEPERUNIT. | |
Multiplier | Double | False |
This is the multiplier used to calculate the rate per unit, based on the employee's ordinary earnings type. Only applicable if RateType is MULTIPLE. | |
AccrueLeave | String | False |
Indicates that this earnings rate should accrue leave. Only applicable if RateType is MULTIPLE. | |
Amount | String | False |
Option Amount for FIXEDAMOUNT RateType EarningsRate. | |
CurrentRecord | Bool | False |
Wehther the earnings rate may still be used. | |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the PayItem record. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query PayItems for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
LeaveTypeID [KEY] | String | True |
The unique identifier of the PayItem. This field is assigned by Xero. | |
Name | String | False |
Name of the leave type. | |
TypeOfUnits | String | False |
The type of units by which leave entitlements are normally tracked. | |
IsPaidLeave | String | False |
Set this to indicate that an employee will be paid when taking this type of leave. | |
ShowOnPaySlip | String | False |
Set this if you want a balance for this leave type to be shown on your employee's payslips. | |
NormalEntitlement | String | False |
The number of units the employee is entitled to each year. | |
LeaveLoadingRate | String | False |
Enter an amount here if your organisation pays an additional percentage on top of ordinary earnings when your employees take leave (typically 17.5%). | |
UpdatedDateUTC | String | False |
The Date time this row was last updated. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query PayItems for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
ReimbursementTypeID [KEY] | String | True |
The unique identifier of the PayItem. This field is assigned by Xero. | |
Name | String | False |
Name of the reimbursement type. | |
AccountCode | String | False |
Customer defined alpha numeric account code. | |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the PayItem record. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query Payroll Calendars for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
PayrollCalendarID [KEY] | String | True |
The unique identifier of the PaySchedule. This field is assigned by Xero. | |
Name | String | False |
The name of the Payroll Calendar. | |
CalendarType | String | False |
The type of payroll calendar. | |
StartDate | String | False |
The Start date of the upcoming pay period. | |
PaymentDate | String | False |
The date on which employees will be paid for the upcomming pay period. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query payruns for a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
PayRunID [KEY] | String | True |
The unique identifier of the payrun. This field is assigned by Xero. | |
PayrollCalendarID | String | False |
The status of the payrun. Valid values are ACTIVE and DELETED. | |
PayRunPeriodStartDate | String | False |
Period Start Date for the PayRun. | |
PayRunPeriodEndDate | String | False |
Period End Date for the PayRun. | |
PayRunStatus | String | False |
The status of the payrun. | |
PaymentDate | String | False |
Payment Date for the PayRun. | |
PayslipMessage | String | False |
Payslip message for the PayRun. | |
Wages | Decimal | False |
Total Wages for the PayRun. | |
Deductions | Decimal | False |
Total Deduction for the PayRun. | |
Tax | Decimal | False |
Total Tax for the PayRun. | |
Super | Decimal | False |
Total Super for the PayRun. | |
Reimbursement | Decimal | False |
Total Reimbursement for the PayRun. | |
NetPay | Decimal | False |
Total NetPay for the PayRun. | |
UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the payrun record. | |
Payslip | String | False |
The payslip for this pay run in XML format. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Retrieve, add and update Payroll Super Funds in a Xero organisation.
Name | Type | ReadOnly | Filterable | Description |
SuperFundID [KEY] | String | True |
Xero Identifier. | |
Type | String | True |
REGULATED, or SMSF. | |
Name | String | False |
Name of the Super fund. | |
ABN | String | False |
ABN of the Super Fund. | |
USI | String | False |
USI of the Regulated Super Fund. | |
BSB | String | False |
BSB of the Self Managed Super Fund. | |
AccountNumber | String | False |
The account number for the self managed super fund. | |
AccountName | String | False |
The account name for the self managed super fund. | |
ElectronicServiceAddress | String | False |
The electronic service address for the self managed super fund. | |
EmployerNumber | String | False |
Some funds assign a unique number to each employer. | |
SPIN | String | False |
The SPIN of the Regulated SuperFund (deprecated). | |
UpdatedDateUTC | String | False |
The date of the last update to this row | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, update, and query Timesheets for a Xero organisation.
The Timesheets table allows you to SELECT, INSERT and UPDATE timesheet lines for a Xero organization. The ID column is generated by the connector; the value of this field combines the index of the line item with the unique, Xero-generated TimesheetID.
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new timesheet, the EmployeeID, StartDate and EndDate fields are required.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.INSERT INTO Timesheets(EmployeeID, StartDate, EndDate, TimesheetLineAggregate) VALUES ( 'XXXXX-XXXXX-XXXXX-XXXXX', '2021-01-01', '2021-01-07', '<TimesheetLine><EarningsRateID>YYYYY-YYYYY-YYYYY-YYYYY</EarningsRateID><NumberOfUnits><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine> <TimesheetLine><EarningsRateID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</EarningsRateID><NumberOfUnits><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine>' )
INSERT INTO Timesheets(TimesheetID, Line_EarningsRateID, Line_NumberOfUnits_NumberOfUnit1, Line_NumberOfUnits_NumberOfUnit2, ...) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 8, 7, ...)
You can update any field that is not read-only.
Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.
The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
Name | Type | ReadOnly | Filterable | Description |
Id [KEY] | String | True |
The timesheet line-item combined with the unique Xero identifier of the timesheet. | |
TimesheetID | String | True |
The unique identifier of the Timesheet. This field is assigned by Xero. | |
EmployeeID | String | False |
The Xero identifier for an employee. | |
StartDate | Date | False |
Period start date. | |
EndDate | Date | False |
Period end date. | |
Status | String | False |
The status code for the timesheet. The valid values are DRAFT, PROCESSED, and APPROVED. | |
Hours | Double | False |
Timesheet total hours. | |
Line_EarningsRateId | String | False |
The Xero identifier for an Earnings Type. | |
Line_TrackingItemId | String | False |
The Xero identifier for a Tracking Category TrackingOptionID. The TrackingOptionID must belong to the TrackingCategory selected as TimesheetCategories under Payroll Settings. | |
Line_WorkLocationId | String | False |
The Xero identifier for a Work Location. | |
Line_NumberOfUnits_NumberOfUnit1 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit2 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit3 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit4 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit5 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit6 | String | False |
Number of units of a Timesheet line. | |
Line_NumberOfUnits_NumberOfUnit7 | String | False |
Number of units of a Timesheet line. | |
Line_UpdatedDateUTC | Datetime | True |
Timestamp of the last change to the Timesheet Line record. | |
TimesheetLineAggregate | String | False |
Used to define TimesheetLine rows using XML values. Should be provided on INSERT only. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
EmployeeBankAccounts | Retrieve the bank accounts for an Employee in a Xero organisation. |
EmployeePayTemplateDeductions | Retrieve the pay template deduction lines for an Employee in a Xero organisation. |
EmployeePayTemplateEarnings | Retrieve the pay template earnings lines for an Employee in a Xero organisation. |
EmployeePayTemplateLeave | Retrieve the pay template leave lines for an Employee in a Xero organisation. |
EmployeePayTemplateReimbursements | Retrieve the pay template reimbursement lines for an Employee in a Xero organisation. |
EmployeePayTemplateSupers | Retrieve the pay template super lines for an Employee in a Xero organisation. |
LeaveBalances | Retrieve the Leave Balance for an Employee in a Xero organisation. |
PaySlipDeductions | Query payslip deduction line-items for a Xero organisation. |
PaySlipEarnings | Query payslip earning line-items for a Xero organisation. |
PaySlipLeaveAccrual | Query payslip leave accrual line-items for a Xero organisation. |
PaySlipLeaveEarnings | Query payslip leave earnings line-items for a Xero organisation. |
PaySlipReimbursements | Query payslip reimbursements line-items for a Xero organisation. |
PaySlips | Create, update, and query payslips for a Xero organisation. |
PaySlipSuperannuations | Query payslip superannuation line-items for a Xero organisation. |
PaySlipTaxes | Query payslip tax line-items for a Xero organisation. |
PaySlipTimesheetEarnings | Query payslip timesheet-earnings line-items for a Xero organisation. |
Tenants | Query the list of organisations connected to your Xero account. |
Retrieve the bank accounts for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
StatementText | String | The text that will appear on the employee's bank statement | |
AccountName | String | The name of the account | |
BSB | String | The BSB number of the account | |
AccountNumber | String | The account number | |
Remainder | Boolean | If the remaining part of the employee's salary should be transferred to this account | |
Amount | Decimal | A fixed amount of the employee's salary which is transferred to this account | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the pay template deduction lines for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
DeductionTypeID | String | The ID of the Deductions Rate used for this line item | |
CalculationType | String | One of FIXEDAMOUNT, PRETAX or POSTTAX | |
Percentage | Decimal | The deduction rate, for PRETAX or POSTTAX deductions | |
Amount | Decimal | The deduction amount, for FIXEDAMOUNT deductions | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the pay template earnings lines for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
EarningsRateID | String | The ID of the Earnings Rate used for this line item | |
CalculationType | String | One of USEEARNINGSRATE, ENTEREARNINGSRATE or ANNUALSALARY | |
NumberOfUnitsPerWeek | Int | How many hours the employee works per week. Only used for ANNUALSALARY. | |
AnnualSalary | Decimal | The annual salary of the employee | |
RatePerUnit | Decimal | The rate per unit of the earnings line | |
NormalNumberOfUnits | Decimal | The units of time in the earnings line | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the pay template leave lines for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
LeaveTypeID | String | The ID of the leave type applied to this line | |
CalculationType | String | One of FIXEDAMOUNTEACHPERIOD, ENTIRERATEINPAYTEMPLATE or BASEDONORDINARYEARNINGS | |
AnnualNumberOfUnits | Decimal | The hours of leave accrued each year | |
FullTimeNumberOfUnitsPerPeriod | Decimal | The hours of leave accrued per pay period | |
NumberOfUnits | Decimal | A fixed amount of leave | |
EntitlementFinalPayPayoutType | String | One of NOTPAIDOUT or PAIDOUT | |
EmploymentTerminationPaymentType | String | One of O or R, only valid if EntitlementFinalPayPaoutType is PAIDOUT | |
IncludeSuperannuationGuaranteeContribution | Boolean | Whether ETP leave earnings are subject to SGC | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the pay template reimbursement lines for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
ReimbursementTypeID | String | The ID of the reimbursement type applied to this line | |
Description | String | The description of the reimbursement line | |
Amount | Decimal | The amount of the reimbursement line | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the pay template super lines for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID | String | The ID of the Employee. This field is assigned by Xero. | |
SuperMembershipID | String | The ID of the superannuation fund this line contributes to | |
ContributionType | String | One of SGC, SALARYSACRIFICE, EMPLOYERADDITIONAL or EMPLOYEE | |
CalculationType | String | One of FIXEDAMOUNT, PERCENTAGEOFEARNINGS or STATUTORY | |
ExpenseAccountCode | String | The code for the expense account used by this line | |
LiabilityAccountCode | String | The code for the expense account used by this line | |
MinimumMonthlyEarnings | Decimal | The minimum the employee must make in a month for this line to take effect | |
Percentage | Decimal | The percentage of the super line | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Retrieve the Leave Balance for an Employee in a Xero organisation.
Name | Type | Filterable | Description |
EmployeeID [KEY] | String | ID of the employee | |
LeaveTypeID [KEY] | String | ID of the leave type | |
LeaveName | String | The name of the leave type | |
NumberOfUnits | Double | The balance of the leave available | |
TypeOfUnits | String | The type of units | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip deduction line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipDeductions WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipDeductions WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
DeductionLine_DeductionTypeId | String | The unique identifier of the line-item's deduction type | |
DeductionLine_CalculationType | String | How the deduction is applied to earnings, either PRETAX, POSTTAX or FIXEDAMOUNT | |
DeductionLine_Amount | Decimal | The total amount of the deduction | |
DeductionLine_Percentage | Double | The percentage of income this deduction applies to | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip earning line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipEarnings WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipEarnings WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
EarningsLine_EarningsRateId | String | The unique identifier of the line-item's earnings rate | |
EarningsLine_RatePerUnit | Decimal | The line-item's wage per time unit (e.g. hours) | |
EarningsLine_NumberOfUnits | Double | The number of time units in this line-item (e.g. hours) | |
EarningsLine_FixedAmount | Decimal | The total earnings in this line-item. Only appears if the rate-type is FIXED. | |
EarningsLine_LumpSumETaxYear | String | The tax year that applies to these earnings. Only appears if the earnings type is LUMPSUME. | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip leave accrual line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipLeaveAccrual WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipLeaveAccrual WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
LeaveAccrualLine_LeaveTypeId | String | The unique identifier of the line-item's leave type | |
LeaveAccrualLine_NumberOfUnits | Double | How much leave time was added | |
LeaveAccrualLine_AutoCalculate | String | Whether or not to automatically calculate leave | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip leave earnings line-items for a Xero organisation.
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
LeaveEarningsLine_EarningsRateId | String | The unique identifier of the line-item's earnings rate | |
LeaveEarningsLine_RatePerUnit | Decimal | The line-item's hourly wage | |
LeaveEarningsLine_NumberOfUnits | Double | The number of hours in this line-item | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip reimbursements line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipReimbursements WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipReembursements WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
ReimbursementLine_ReimbursementTypeId | String | The unique identifier of the line-item's reimbursement type | |
ReimbursementLine_Description | String | A description of the reimbursement | |
ReimbursementLine_ExpenseAccount | String | The account the reimbursement is paid from | |
ReimbursementLine_Amount | Decimal | The amount of the reimbursement | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Create, update, and query payslips for a Xero organisation.
Name | Type | Filterable | Description |
PayRunID | String | The ID of the payrun that contains the payslips | |
PayslipID [KEY] | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
FirstName | String | The employee's first name. | |
LastName | String | The employee's first name. | |
EmployeeGroup | String | The name of the group the employee belongs to. | |
LastEdited | Datetime | When this payslip was last changed. | |
Wages | Decimal | The total wages included in the payslip. | |
Deductions | Decimal | The total deductions included in the payslip. | |
NetPay | Decimal | The total net pay included in the paysilp. | |
Tax | Decimal | The total tax paid included in the payslip. | |
Super | Decimal | The total superannuation included in the paysilp. | |
Reimbursements | Decimal | The total reimbursement included in the payslip. | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip superannuation line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipTaxeSuperannuations WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipSuperAnnuations WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
SuperannuationLine_SuperMembershipId | String | The unique identifier of the line-item's super fund. | |
SuperannuationLine_ContributionType | String | The super contribution type | |
SuperannuationLine_CalculationType | String | The super calculation type | |
SuperannuationLine_MinimumMonthlyEarnings | Decimal | The super minimum monthly earnings | |
SuperannuationLine_ExpenseAccountCode | String | The super expense account code | |
SuperannuationLine_LiabilityAccountCode | String | The super liability account code | |
SuperannuationLine_PaymentDateForThisPeriod | Date | The super payment date for this period | |
SuperannuationLine_Percentage | Double | The super percentage | |
SuperannuationLine_Amount | Decimal | The super amount | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip tax line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipTaxes WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipTaxes WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
TaxLine_TaxTypeName | String | The name of the line-item's tax type | |
TaxLine_Description | String | The description of the tax | |
TaxLine_Amount | Decimal | The tax's amount | |
TaxLine_LiabilityAccount | String | The liability account used to pay the tax | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query payslip timesheet-earnings line-items for a Xero organisation.
Equal operator
The equal operator is supported on the server side:SELECT * FROM PaySlipTimesheetEarnings WHERE PayslipID ='218db321-b72e-4aa1-9067-477d63705f95'
IN operator
The IN operator is supported on the client side:SELECT * FROM PaySlipTimesheetEarnings WHERE PayslipID IN ('1843c739-aa8b-4d58-956a-3e097708cd84','218db321-b72e-4aa1-9067-477d63705f95')
Name | Type | Filterable | Description |
Id [KEY] | String | The earnings line-item combined with the unique Xero identifier of the payslip | |
PayslipID | String | The unique identifier of the payslip. This field is assigned by Xero. | |
EmployeeID | String | The unique identifier of the payslip's employee. | |
TimesheetEarningsLine_EarningsRateId | String | The unique identifier of the line-item's earnings rate | |
TimesheetEarningsLine_RatePerUnit | Decimal | The line-item's rate per time unit (e.g. hours) | |
TimesheetEarningsLine_NumberOfUnits | Decimal | The number of time units (e.g. hours) | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query the list of organisations connected to your Xero account.
Name | Type | Filterable | Description |
TenantId [KEY] | String | A unique identifier for the tenant. | |
Name | String | The name of the organisation or practice | |
Type | String | Whether the tenant is an organisation or a practice | |
IsActive | Boolean | Whether the connection is currently using this tenant |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetLimitInfo | Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant. |
GetOAuthAccessToken | Obtains the OAuth access token to be used for authentication with Xero. |
GetOAuthAuthorizationURL | Obtains the OAuth authorization URL for authentication with various Xero services. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Xero services. |
RevokeTenant | Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant. |
SwitchTenant | Changes the tenant this connection fetches data from. |
Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.
If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:
SELECT * FROM PayRuns LIMIT 1
Name | Type | Description |
TenantId | String | ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one. |
AppRemaining | Integer | The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants. |
MinuteRemaining | Integer | The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant. |
DayRemaining | Integer | The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant. |
RetryAfter | Integer | The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant. |
LastModified | Datetime | When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time. |
Obtains the OAuth access token to be used for authentication with Xero.
Name | Type | Required | Description |
AuthMode | String | True | The type of authentication mode to use.
The allowed values are APP, WEB. The default value is WEB. |
AuthToken | String | False | The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
AuthKey | String | False | The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
CallbackURL | String | False | This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/'). |
Verifier | String | False | The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
PKCEVerifier | String | False | The PKCEVerifier returned by GetOAuthAuthorizationURL. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthAccessTokenSecret | String | The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Obtains the OAuth authorization URL for authentication with various Xero services.
Name | Type | Required | Description |
CallbackURL | String | False | This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal. |
Scope | String | False | The space-separated list of scopes you are requesting from Xero. By default all scopes are requested. |
State | String | False | A state token to include in the authorization URL. Not included by default. |
Name | Type | Description |
URL | String | The URL to complete user authentication. |
AuthToken | String | A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
AuthKey | String | A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
PKCEVerifier | String | A random value used as input for GetOAuthAccessToken in the PKCE flow. |
Refreshes the OAuth access token used for authentication with various Xero services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned from the original authorization code exchange. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to revoke, as given in the Tenants view |
Name | Type | Description |
Success | String | Whether the connection was successfully revoked |
Changes the tenant this connection fetches data from.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to attach to. If empty, the current tenant is not changed. |
Name | Type | Description |
ActiveTenant | String | The ID of the tenant that is currently active. |
The Jitterbit Connector for Xero models the Xero Files API as relational tables and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
Tables describes the available tables.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.
Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Associations | Create, query and delete Associations for a Xero organization. |
Files | Query, update and delete Files for a Xero organization. |
Folders | Create, query, update and delete Folders for a Xero organization. |
Create, query and delete Associations for a Xero organization.
To create an Association, everything but the ID and ObjectType must be provided:
INSERT INTO Associations(FileId, ObjectId, ObjectGroup) VALUES ('2bf7b85c-7ed8-47b4-8559-d83f8f5f9b9c', '4819be2c-b3b7-43a2-80fe-ef479ad25351', 'Invoice')
Associations cannot be changed once they have been inserted. If you need to update an Association you must delete it and recreate it.
Name | Type | ReadOnly | Description |
Id [KEY] | String | False |
A unique combination of the file and object being associated |
FileId | String | False |
The FileId of the file that is being associated |
ObjectId | String | False |
The identifier of the object that is being associated to |
ObjectGroup | String | False |
The Accounting entity to which the object should be attached |
ObjectType | String | True |
A more specific category that the object belongs to |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Query, update and delete Files for a Xero organization.
If you need to interact with file contents (either uploading or downloading them), please see the Files stored procedures.
Name | Type | ReadOnly | Description |
FileId [KEY] | String | True |
The unique Xero-generated ID of the file |
Name | String | False |
The name of the file |
FolderId | String | False |
The FolderId of the folder containing the file |
MimeType | String | True |
What type of data the file contains |
Size | Int | True |
The size of the file in bytes |
User_Id | String | True |
The unique ID of the user who uploaded the file |
User_Name | String | True |
The username of the user who uploaded the file |
User_FirstName | String | True |
The first name of the user who uploaded the file |
User_LastName | String | True |
The last name of the user who uploaded the file |
User_FullName | String | True |
The full name of the user who uploaded the file |
Created | Datetime | True |
When the file was first uploaded |
Updated | Datetime | True |
When the file metadata was last changed |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Create, query, update and delete Folders for a Xero organization.
A Name has to be provided when creating a Folder:
INSERT INTO Folders(Name) VALUE ('Legal Documents')
Name | Type | ReadOnly | Description |
FolderId [KEY] | String | True |
The unique Xero-generated ID of the folder |
Name | String | False |
The name of the folder |
FileCount | Integer | True |
How many files exist in the folder |
IsInbox | Boolean | True |
Whether this folder is the inbox. The inbox is automatically created by Xero and cannot be changed or deleted. |
String | True |
The email address that accepts files and uploads them to this folder. Only available for the Inbox. | |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Tenants | Query the list of organizations connected to your Xero account. |
Query the list of organizations connected to your Xero account.
Name | Type | Description |
TenantId [KEY] | String | A unique identifier for the tenant. |
Name | String | The name of the organization or practice |
Type | String | Whether the tenant is an organization or a practice |
IsActive | Boolean | Whether the connection is currently using this tenant |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
DownloadFile | Downloads a file from the Xero Files service |
GetLimitInfo | Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant. |
GetOAuthAccessToken | Obtains the OAuth access token to be used for authentication with Xero. |
GetOAuthAuthorizationURL | Obtains the OAuth authorization URL for authentication with various Xero services. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Xero services. |
RevokeTenant | Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant. |
SwitchTenant | Changes the tenant this connection fetches data from. |
UploadFile | Uploads a file to the Xero Files service |
Downloads a file from the Xero Files service
There are two different ways to download a file. If you provide a DownloadPath, then the file is downloaded and its contents are stored at that location:
EXECUTE DownloadFile @FileID = '...', @DownloadPath = 'c:/my/xero/files/form.docx'If you do not provide a DownloadPath, then the contents of the file are base64 encoded and returned in the FileData output:
EXECUTE DownloadFile @FileID = '...'
Name | Type | Required | Description |
FileId | String | True | The ID of the file to download |
DownloadPath | String | False | Where to write the downloaded file. If not provided, the file is returned as an output. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
FileData | String | If the DownloadPath is not provided, this contains the base64 encoded content of the file. |
Success | String | This parameter sets whether the operation was successful or not. |
Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.
If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:
SELECT * FROM Files LIMIT 1
Name | Type | Description |
TenantId | String | ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one. |
AppRemaining | Integer | The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants. |
MinuteRemaining | Integer | The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant. |
DayRemaining | Integer | The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant. |
RetryAfter | Integer | The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant. |
LastModified | Datetime | When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time. |
Obtains the OAuth access token to be used for authentication with Xero.
Name | Type | Required | Description |
AuthMode | String | True | The type of authentication mode to use.
The allowed values are APP, WEB. The default value is WEB. |
AuthToken | String | False | The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
AuthKey | String | False | The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
CallbackURL | String | False | This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/'). |
Verifier | String | False | The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
PKCEVerifier | String | False | The PKCEVerifier returned by GetOAuthAuthorizationURL. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthAccessTokenSecret | String | The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Obtains the OAuth authorization URL for authentication with various Xero services.
Name | Type | Required | Description |
CallbackURL | String | False | This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal. |
Scope | String | False | The space-separated list of scopes you are requesting from Xero. By default all scopes are requested. |
State | String | False | A state token to include in the authorization URL. Not included by default. |
Name | Type | Description |
URL | String | The URL to complete user authentication. |
AuthToken | String | A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
AuthKey | String | A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
PKCEVerifier | String | A random value used as input for GetOAuthAccessToken in the PKCE flow. |
Refreshes the OAuth access token used for authentication with various Xero services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned from the original authorization code exchange. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to revoke, as given in the Tenants view |
Name | Type | Description |
Success | String | Whether the connection was successfully revoked |
Changes the tenant this connection fetches data from.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to attach to. If empty, the current tenant is not changed. |
Name | Type | Description |
ActiveTenant | String | The ID of the tenant that is currently active. |
Uploads a file to the Xero Files service
There are a few different ways to upload a file:
1. Provide the full path to a local file. This will create a file with the same name and content in Xero:
EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx
2. Provide the full path to a local file as well as a Name. This will still upload the content in the file, but lets you change the name of the file in Xero:
EXECUTE UploadFile @Filename = 'c:/my/xero/files/form.docx', @Name = 'legal-form.docx'3. Provide the file's content in base64 and the name the file should receive in Xero:
EXECUTE UploadFile @Name = hello.txt', @FileData = 'SGVsbG8sIFdvcmxkIQo='
In addition, you can provide a ContentType to override what type Xero should use when classifying the file. Please note however that Xero only supports a limited number of MIME types, which are listed in the Xero Central article on managing the file library.
Name | Type | Required | Description |
Name | String | False | The name the file will receive when uploaded. If not given, it will be determined from Filename. |
ContentType | String | False | This parameter explicitly sets the MIME type for the document. If left empty, it will be determined automatically. |
FileData | String | False | Base-64 encoded data for the file. Required if Filename and FileStream are not defined. |
FileStream | String | False | Stream of input data. Required if Filename and FileData are not defined. |
Filename | String | False | The full path (including filename) of the file to be uploaded. Required if FileData and FileStream are not defined. |
TenantId | String | False | The ID of the tenant to query instead of the connection tenant |
Name | Type | Description |
Success | String | Whether the operation was successful or not. |
FileId | String | The unique Xero-generated ID of the file |
Name | String | The name of the uploaded file |
FolderId | String | The FolderId the file was uploaded to |
MimeType | String | What type of data the uploaded file contains |
Size | Int | The size of the uploaded file in bytes |
User_Id | String | The unique ID of the user who uploaded the file |
User_Name | String | The username of the user who uploaded the file |
User_FirstName | String | The first name of the user who uploaded the file |
User_LastName | String | The last name of the user who uploaded the file |
User_FullName | String | The full name of the user who uploaded the file |
Created | Datetime | When the file was first uploaded |
Updated | Datetime | When the file was last changed |
The Jitterbit Connector for Xero models the Xero Assets API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
Tables describes the available tables.
Views describes the available views.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.
Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Assets | Query, insert and update assets for a Xero organization. |
AssetTypes | Query, insert and update asset types for a Xero organization. |
Query, insert and update assets for a Xero organization.
An asset can be created with only an AssetName and an AssetNumber. This will create a draft asset with blank settings for purchase date and price, serial number and all other values.
INSERT INTO Assets(AssetName, AssetNumber) VALUES ('Corporate van', 'FA-0042')
UPDATE Assets SET BookDepreciationSetting_DepreciationRate = 0.12, BookDepreciationSetting_EffectiveLifeYears = null WHERE AssetId = '46c7f1f4-7cdb-47d3-8772-2796d0b83e87'
To move an asset from the draft to the registered state, you must assign these properties on it:
Then you can update the AssetStatus field to the value "Registered".
Disposing of assets is not supported through the API.
Name | Type | ReadOnly | Description |
AssetId [KEY] | String | True |
The unique Xero-generated ID of the asset |
AssetName | String | False |
The name of the asset |
AssetNumber | String | False |
A unique code for the asset |
AssetStatus | String | False |
Either DRAFT, REGISTERED or DISPOSED |
Description | String | False |
Free-form comments on the asset |
PurchaseDate | Date | False |
When the asset was purchased |
PurchasePrice | Decimal | False |
How much asset cost when it was purchased |
DisposalDate | Date | False |
When the asset was purchased |
DisposalPrice | Decimal | False |
What price the asset was disposed at |
WarrantyExpiryDate | Date | False |
When the asset's warranty expires |
SerialNumber | String | False |
The asset's serial number |
AssetTypeId | String | False |
The ID of the type of this asset |
BookDepreciationSetting_DepreciationMethod | String | False |
Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation |
BookDepreciationSetting_AveragingMethod | String | False |
Either ActualDays or FullMonth |
BookDepreciationSetting_DepreciationRate | Double | False |
The rate of depreciation in percent |
BookDepreciationSetting_EffectiveLifeYears | Int | False |
The effective life of the asset in years |
BookDepreciationSetting_DepreciationCalculationMethod | String | False |
Either Rate, Life or None |
BookDepreciationDetail_CurrentCapitalGain | Decimal | False |
Once the asset is disposed, this will be the sell price minus the purchase price |
BookDepreciationDetail_CurrentGainLoss | Decimal | False |
Once the asset is disposed, this will be the minimum of the sell and purchase price, minus the current book value |
BookDepreciationDetail_DepreciationStartDate | Date | False |
When the depreciation takes effect |
BookDepreciationDetail_CostLimit | Decimal | False |
The value of the asset to be depreciated, if this is less than the cost |
BookDepreciationDetail_ResidualValue | Decimal | False |
The value of the asset once it's been fully depreciated |
BookDepreciationDetail_PriorAccumDepreciationAmount | Decimal | False |
All depreciation prior to the current fiscal year |
BookDepreciationDetail_CurrentAccumDepreciationAmount | Decimal | False |
All depreciation occurring in the current financial year |
CanRollBack | Boolean | True |
Whether the asset's depreciation can be rolled back |
AccountingBookValue | Decimal | True |
The accounting value of the asset |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Query, insert and update asset types for a Xero organization.
An AssetType can be created with one of two methods of computing depreciation. The first is according to a fixed percentage (in this example, 0.15%):
INSERT INTO AssetTypes( AssetTypeName, FixedAssetAccountId, DepreciationExpenseAccountId, AccumulatedDepreciationAccountId, BookDepreciationSetting_DepreciationMethod, BookDepreciationSetting_AveragingMethod, BookDepreciationSetting_DepreciationRate ) VALUES ( 'Vehicles', '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac', '20b36596-3d1d-4789-9f08-50c92d19d522', '2a9bb066-3398-40e8-a953-caaabed2b7a7', 'StraightLine', 'FullMonth', 0.15 )
Alternatively, you could create an asset type that depreciates according to an effective lifetime:
INSERT INTO AssetTypes( AssetTypeName, FixedAssetAccountId, DepreciationExpenseAccountId, AccumulatedDepreciationAccountId, BookDepreciationSetting_DepreciationMethod, BookDepreciationSetting_AveragingMethod, BookDepreciationSetting_EffectiveLifeYears ) VALUES ( 'Vehicles', '8e968d1a-5e25-4dad-b6f9-c8f4d72c0cac', '20b36596-3d1d-4789-9f08-50c92d19d522', '2a9bb066-3398-40e8-a953-caaabed2b7a7', 'StraightLine', 'FullMonth', 12 )
UPDATE AssetTypes SET BookDepreciationSetting_DepreciationRate = 0.09, BookDepreciationSetting_EffectiveLifeYears = null WHERE AssetTypeId = 'bf28b6ca-3cc6-4591-ac5a-2c1115f04b4b'
Name | Type | ReadOnly | Description |
AssetTypeId [KEY] | String | True |
The unique Xero-generated ID of the asset type |
AssetTypeName | String | False |
The name of the asset type |
FixedAssetAccountId | String | False |
The asset account used for assets of this type |
DepreciationExpenseAccountId | String | False |
The expense account used when assets of this type depreciate |
AccumulatedDepreciationAccountId | String | False |
The account used for accumulated depreciation of assets of this type |
BookDepreciationSetting_DepreciationMethod | String | False |
Either NoDepreciation, StraightLine, DiminishingValue100, DiminishingValue150, DiminishingValue200 or FullDepreciation |
BookDepreciationSetting_AveragingMethod | String | False |
Either ActualDays or FullMonth |
BookDepreciationSetting_DepreciationRate | Decimal | False |
The rate of depreciation in percent |
BookDepreciationSetting_EffectiveLifeYears | Int | False |
The effective life of the asset in years |
BookDepreciationSetting_DepreciationCalculationMethod | String | True |
Either Rate, Life or None |
Locks | Int | True |
The number of assets using this asset type |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Settings | Query asset settings for a Xero organization. |
Tenants | Query the list of organizations connected to your Xero account. |
Query asset settings for a Xero organization.
Name | Type | Description |
AssetNumberPrefix | String | The prefix used for generating fixed asset numbers |
AssetNumberSequence | String | The next available asset number |
AssetStartDate | Date | The date Xero started calculating depreciation for fixed assets |
LastDepreciationDate | Date | The last depreciation date |
DefaultGainOnDisposalAccountId | String | The default account that gains are posted to |
DefaultLossOnDisposalAccountId | String | The default account that losses are posted to |
DefaultCapitalGainOnDisposalAccountId | String | The default account that capital gains are posted to |
OptInForTax | Boolean | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
Query the list of organizations connected to your Xero account.
Name | Type | Description |
TenantId [KEY] | String | A unique identifier for the tenant. |
Name | String | The name of the organization or practice |
Type | String | Whether the tenant is an organization or a practice |
IsActive | Boolean | Whether the connection is currently using this tenant |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetLimitInfo | Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant. |
GetOAuthAccessToken | Obtains the OAuth access token to be used for authentication with Xero. |
GetOAuthAuthorizationURL | Obtains the OAuth authorization URL for authentication with various Xero services. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Xero services. |
RevokeTenant | Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant. |
SwitchTenant | Changes the tenant this connection fetches data from. |
Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.
If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:
SELECT * FROM AssetTypes LIMIT 1
Name | Type | Description |
TenantId | String | ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one. |
AppRemaining | Integer | The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants. |
MinuteRemaining | Integer | The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant. |
DayRemaining | Integer | The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant. |
RetryAfter | Integer | The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant. |
LastModified | Datetime | When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time. |
Obtains the OAuth access token to be used for authentication with Xero.
Name | Type | Required | Description |
AuthMode | String | True | The type of authentication mode to use.
The allowed values are APP, WEB. The default value is WEB. |
AuthToken | String | False | The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
AuthKey | String | False | The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
CallbackURL | String | False | This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/'). |
Verifier | String | False | The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
PKCEVerifier | String | False | The PKCEVerifier returned by GetOAuthAuthorizationURL. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthAccessTokenSecret | String | The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Obtains the OAuth authorization URL for authentication with various Xero services.
Name | Type | Required | Description |
CallbackURL | String | False | This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal. |
Scope | String | False | The space-separated list of scopes you are requesting from Xero. By default all scopes are requested. |
State | String | False | A state token to include in the authorization URL. Not included by default. |
Name | Type | Description |
URL | String | The URL to complete user authentication. |
AuthToken | String | A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
AuthKey | String | A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
PKCEVerifier | String | A random value used as input for GetOAuthAccessToken in the PKCE flow. |
Refreshes the OAuth access token used for authentication with various Xero services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned from the original authorization code exchange. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to revoke, as given in the Tenants view |
Name | Type | Description |
Success | String | Whether the connection was successfully revoked |
Changes the tenant this connection fetches data from.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to attach to. If empty, the current tenant is not changed. |
Name | Type | Description |
ActiveTenant | String | The ID of the tenant that is currently active. |
The Jitterbit Connector for Xero models the Xero Projects API as relational tables, views and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
Tables describes the available tables.
Views describes the available views.
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The connector models the data in Xero into a list of tables that can be queried using standard SQL statements.
Generally, querying Xero tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Projects | Query, create and update projects for a Xero organisation. |
Tasks | Query, create update and delete tasks for a Xero project. |
Time | Query, create update and delete time entries for a Xero project. |
Query, create and update projects for a Xero organisation.
A Project must be created with a Name and ContactId. The ContactId comes from the ContactId column in the Contacts table of the Accounting API. An EstimateValue may also be provided to set the initial estimate for the project:
INSERT INTO Projects(Name, ContactId, Deadline, EstimateValue) VALUES ('Construct Parking Lot', '1c5ac798-1e31-4c09-82ac-4781261ff20a', '2025-01-01', 250000)
Once a project has been created, only its Deadline and EstimateValue may be updated:
UPDATE Projects SET Deadline = '2022-01-01', EstimateValue = 150000 WHERE ProjectId = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29'
Name | Type | ReadOnly | Description |
ProjectId [KEY] | String | True |
The unique Xero identifier of the project. |
ContactId | String | False |
The ID of the Contact the project was created for |
Name | String | False |
The name of the project. |
CurrencyCode | String | True |
The currency used within the project. |
MinutesLogged | Int | True |
The total minutes logged against all tasks in the project |
TotalTaskAmountValue | Decimal | True |
The total actuals amount of each project task |
TotalTaskAmountCurrency | String | True |
The currency of the TotalTaskAmountValue |
TotalExpenseAmountValue | Decimal | True |
The total actuals amount of each project expense |
TotalExpenseAmountCurrency | String | True |
The currency of the TotalExpenseAmountValue |
MinutesToBeInvoiced | Int | True |
The total minutes across all tasks which have not been invoiced |
TaskAmountToBeInvoicedValue | Decimal | True |
The total AmountToBeInvoicedValue for each task in the project |
TaskAmountToBeInvoicedCurrency | String | True |
The currency of the TaskAmountToBeInvoicedValue |
TaskAmountInvoicedValue | Decimal | True |
The total AmountInvoicedValue for each task in the project |
TaskAmountInvoicedCurrency | String | True |
The currency of the TaskAmountInvoicedValue |
ExpenseAmountToBeInvoicedValue | Decimal | True |
The total of all the expenses in the project which have not been invoiced |
ExpenseAmountToBeInvoicedCurrency | String | True |
The currency of the ExpenseAmountToBeInvoicedValue |
ExpenseAmountInvoicedValue | Decimal | True |
The total of all the expenses in the project which have been invoiced |
ExpenseAmountInvoicedCurrency | String | True |
The currency of the ExpenseAmountInvoicedValue |
ProjectAmountInvoicedValue | Decimal | True |
The total amount that has been invoiced across the whole project |
ProjectAmountInvoicedCurrency | String | True |
The currency of the ProjectAmountInvoicedValue |
DepositValue | Decimal | True |
The deposit for the project |
DepositCurrency | String | True |
The currency of the DepositValue |
DepositAppliedValue | Decimal | True |
The amount of the deposit which have been credited to project invoices. |
DepositAppliedCurrency | String | True |
The currency of the DepositAppliedValue |
CreditNoteValue | Decimal | True |
The total amount of credit notes in the project |
CreditNoteCurrency | String | True |
The currency of the CreditNoteValue |
Deadline | Date | False |
The currency of the CreditNoteValue |
TotalInvoicedValue | Decimal | True |
The total amount invoiced to the project across all categories |
TotalInvoicedCurrency | String | True |
The currency of the TotalInvoicedValue |
TotalToBeInvoicedValue | Decimal | True |
The total amount for the project that is yet to be invoiced, across all categories |
TotalToBeInvoicedCurrency | String | True |
The currency of the TotalToBeInvoicedValue |
EstimateValue | Decimal | False |
The estimate for the project |
EstimateCurrency | String | True |
The currency of the EstimateValue |
Status | String | True |
Either INPROGRESS or CLOSED |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Query, create update and delete tasks for a Xero project.
A Task must be created with a ProjectId, Name, RateCurrency, RateValue, and ChargeType. The ProjectId and RateCurrency must come from the project (ProjectId and CurrencyCode respectively). ChargeType determines how the RateValue is applied to the cost of the project:
The EstimateMinutes may also be provided to set the time estimate for the task:
INSERT INTO Tasks(ProjectId, Name, RateCurrency, RateValue, ChargeType, EstimateMinutes) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'Pave Sidewalks', 'USD', 25000, 'FIXED', 1000)
Once a Task has been created, its RateCurrency, RateValue, ChargeType or EstimateMinutes may be updated:
UPDATE Tasks SET RateValue = 250, ChargeType = 'TIME', EstimateMinutes = 2000 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'
Tasks may also be deleted by specifying their Id:
DELETE FROM Tasks WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'
Name | Type | ReadOnly | Description |
Id [KEY] | String | False |
A unique combination of the project and task identifiers |
TaskId | String | True |
The unique Xero identifier of the task. |
ProjectId | String | False |
The ID of the project |
Name | String | False |
The name of the task |
ChargeType | String | False |
One of TIME, FIXED or NON_CHARGEABLE |
RateValue | Decimal | False |
The per-hour rate billed for the task |
RateCurrency | String | False |
The currency of the RateValue |
EstimateMinutes | Int | False |
The estimated number of minutes to complete the task |
TotalMinutes | Int | True |
The total number of minutes logged against the task |
TotalAmountValue | Decimal | True |
The total value of the task, TotalMinutes multiplied by RateValue |
TotalAmountCurrency | String | True |
The currency of the TotalAmountValue |
MinutesToBeInvoiced | Int | True |
The minutes on this task that have not been invoiced |
MinutesInvoiced | Int | True |
The minutes on this task that have been invoiced |
NonChargeableMinutes | Int | True |
The minutes on this task that cannot be charged |
AmountToBeInvoicedValue | Decimal | True |
The total value of the task that has not been invoiced, MinutesToBeInvoiced multiplied by RateValue |
AmountToBeInvoicedCurrency | String | True |
The currency of the AmountToBeInvoicedValue |
AmountInvoicedValue | Decimal | True |
The total value of the task that has been invoiced, MinutesInvoiced multiplied by RateValue |
AmountInvoicedCurrency | String | True |
The currency of the AmountInvoicedValue |
Status | String | True |
One of ACTIVE, INVOICED or LOCKED |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Query, create update and delete time entries for a Xero project.
A Time must be created with a ProjectId, TaskId, UserId, Date and Duration. An optional description may also be provided:
INSERT INTO Time(ProjectId, TaskId, UserId, Date, Duration, Description) VALUES ('1de78bad-5a81-4cb8-ab53-5a1a3bc73b29', 'd15810a1-9324-4765-a357-80d160a0b87c', '312b124b-f33f-4cfe-a831-20eb290610d6', '2019-10-01', 250, 'Paved northwest section')
Once a Time has been created, its UserId, Date, Duration or Description may be updated:
UPDATE Time SET Date = '2019-11-01', Duration = 350 WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'
Time may also be deleted by specifying their Id:
DELETE FROM Time WHERE ID = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'
Name | Type | ReadOnly | Description |
Id [KEY] | String | False |
A unique combination of the project and time entry identifiers |
TimeEntryId | String | True |
The unique Xero identifier of the time entry. |
UserId | String | False |
The ID of the user who logged the time entry |
ProjectId | String | False |
The ID of the project the task belongs to |
TaskId | String | False |
The ID of the task the time is logged for |
Date | Date | False |
The day the time was logged for |
DateEntered | Datetime | True |
When the time entry was created |
Duration | Int | False |
When the time entry was created |
Description | String | False |
The description of the time entry |
Status | String | True |
One of ACTIVE, LOCKED or INVOICED |
TenantId | String | False |
The ID of the tenant to query instead of the connection tenant |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Tenants | Query the list of organizations connected to your Xero account. |
Users | Query project users for a Xero organisation. |
Query the list of organizations connected to your Xero account.
Name | Type | Description |
TenantId [KEY] | String | A unique identifier for the tenant. |
Name | String | The name of the organization or practice |
Type | String | Whether the tenant is an organization or a practice |
IsActive | Boolean | Whether the connection is currently using this tenant |
Query project users for a Xero organisation.
Name | Type | Description |
UserId [KEY] | String | The unique Xero identifier of the user. |
Name | String | The full name of the user. |
String | The email of the user. | |
TenantId | String | The ID of the tenant to query instead of the connection tenant |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetLimitInfo | Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant. |
GetOAuthAccessToken | Obtains the OAuth access token to be used for authentication with Xero. |
GetOAuthAuthorizationURL | Obtains the OAuth authorization URL for authentication with various Xero services. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Xero services. |
RevokeTenant | Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant. |
SwitchTenant | Changes the tenant this connection fetches data from. |
Retrieves limit information. Use this stored procedure* to monitor your API limits as you make calls against a tenant.
This procedure reports information that the connector caches after each API request. If no requests have been made to a tenant on a connection than this procedure will report 0 rows. Otherwise, the LastModified field reports the last time the information in the row was updated.
If the last modified time is too long ago for your application to use, then this table may be refreshed by executing a query. The query must read at least one row to ensure that an API request is triggered:
SELECT * FROM Projects LIMIT 1
Name | Type | Description |
TenantId | String | ID of the tenant that the limit applies to. The output contains a row for each tenant queried during the connection. Usually there is only one. |
AppRemaining | Integer | The number of API calls the current OAuth app has this minute (max 10000). This applies across all tenants. |
MinuteRemaining | Integer | The number of API calls the current OAuth app has this minute (max 60). This applies only to this tenant. |
DayRemaining | Integer | The number of API calls the current OAuth app has today (max 5000). This applies only to this tenant. |
RetryAfter | Integer | The suggested retry time, if one of the above limits was 0 on the most recent API call to the tenant. |
LastModified | Datetime | When this information was last updated. The values in this table are only refreshed when performing API calls, so this may be older than the current time. |
Obtains the OAuth access token to be used for authentication with Xero.
Name | Type | Required | Description |
AuthMode | String | True | The type of authentication mode to use.
The allowed values are APP, WEB. The default value is WEB. |
AuthToken | String | False | The authentication token returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
AuthKey | String | False | The AuthKey returned by GetOAuthAuthorizationUrl. Required for only the Web AuthMode. |
CallbackURL | String | False | This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the Developer Portal, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/'). |
Verifier | String | False | The verifier token returned by Xero after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
PKCEVerifier | String | False | The PKCEVerifier returned by GetOAuthAuthorizationURL. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthAccessTokenSecret | String | The authentication token secret returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Obtains the OAuth authorization URL for authentication with various Xero services.
Name | Type | Required | Description |
CallbackURL | String | False | This field determines where the response is sent. This value is only necessary if you have registered your own app. This URL must have the same domain as the OAuth callback domain specified in the Xero Developer Portal. |
Scope | String | False | The space-separated list of scopes you are requesting from Xero. By default all scopes are requested. |
State | String | False | A state token to include in the authorization URL. Not included by default. |
Name | Type | Description |
URL | String | The URL to complete user authentication. |
AuthToken | String | A token used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
AuthKey | String | A key used as input for the GetOAuthAccessToken stored procedure* to verify the request and get the OAuth Access Token. |
PKCEVerifier | String | A random value used as input for GetOAuthAccessToken in the PKCE flow. |
Refreshes the OAuth access token used for authentication with various Xero services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned from the original authorization code exchange. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Xero. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Disconnects the tenant from this application. The user must reauthorize the next time they connect this application to that tenant.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to revoke, as given in the Tenants view |
Name | Type | Description |
Success | String | Whether the connection was successfully revoked |
Changes the tenant this connection fetches data from.
Name | Type | Required | Description |
TenantId | String | False | The ID of the tenant to attach to. If empty, the current tenant is not changed. |
Name | Type | Description |
ActiveTenant | String | The ID of the tenant that is currently active. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Xero:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Contacts table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Contacts'
Name | Type | Description |
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure* parameters.
The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure* containing the parameter. |
ColumnName | String | The name of the stored procedure* parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys. The following query retrieves the primary key for the Contacts table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Contacts'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:xero:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the connector can offload to the data source.
When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | Whether COUNT function is supported. | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures* that can be used for generating schema files. |
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Name | Type | Description |
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
AuthScheme | The type of authentication to use when connecting to Xero. |
Schema | The Xero API you want to access as a database schema. |
Tenant | Sets the Xero organization to connect to. Can be a name or tenant ID. |
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
Scope | This determines the scopes that the OAuth application requests from Xero. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
AutoExpandDetails | Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts. |
IncludeArchived | Set this property to include archived records. |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RetryDailyLimit | Whether to wait when Xero reports that the provider has hit its daily limit. |
RetryWaitTime | How long to wait when a request hits the Xero API limit, in milliseconds. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
This section provides a complete list of authentication properties you can configure.
Property | Description |
AuthScheme | The type of authentication to use when connecting to Xero. |
Schema | The Xero API you want to access as a database schema. |
Tenant | Sets the Xero organization to connect to. Can be a name or tenant ID. |
The type of authentication to use when connecting to Xero.
string
"PKCE"
Current authentication methods supported for new apps:
The Xero API you want to access as a database schema.
string
"ACCOUNTING"
The connector supports the Accounting API, the Assets API, the Australian Payroll API, the Files API and the Projects API. Set the Schema connection property to Accounting, Assets, PayrollAUS, Files or Projects to connect to one of the corresponding APIs.
Sets the Xero organization to connect to. Can be a name or tenant ID.
string
""
Xero apps may be authorized by for multiple organizatins at once. By default the connector will connect to the first available tenant. To control which organization your are connected to, set this option.
This option can be set to either the name of your organization or to the tenant ID. If you have multiple organizations with the same name, then you must use the tenant ID. You can find the tenant ID by connecting with this option unset and then querying the Tenants view.
This section provides a complete list of OAuth properties you can configure.
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
Scope | This determines the scopes that the OAuth application requests from Xero. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
string
"OFF"
The following options are available:
The client ID assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
This determines the scopes that the OAuth application requests from Xero.
string
""
By default the connector will request that the user authorize all available scopes. If you want to override this, you can set this property to a space-separated list of OAuth scopes.
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
string
""
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
The verifier code returned from the OAuth authorization URL.
string
""
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
See to obtain the OAuthVerifier value.
Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set. To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.The OAuth refresh token for the corresponding OAuth access token.
string
""
The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.
The lifetime in seconds of the OAuth AccessToken.
string
""
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
The Unix epoch timestamp in milliseconds when the current Access Token was created.
string
""
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
This section provides a complete list of SSL properties you can configure.
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of schema properties you can configure.
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\Xero Data Provider\Schema"
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note: Given that this connector supports multiple schemas, the structure for Xero custom schema files is as follows:
If left unspecified, the default location is "%APPDATA%\\Xero Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:
Platform | %APPDATA% |
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
string
""
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
string
""
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
AutoExpandDetails | Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts. |
IncludeArchived | Set this property to include archived records. |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RetryDailyLimit | Whether to wait when Xero reports that the provider has hit its daily limit. |
RetryWaitTime | How long to wait when a request hits the Xero API limit, in milliseconds. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.
string
"False"
Possible values include True and False. When set to true, this makes some columns contain data that are otherwise NULL, but it will make reads from those tables slower than they are by default.
When set to false (the default), the only way to get extra details from those tables is to explicitly select single items:
SELECT * FROM ExpenseClaims WHERE ExpenseClaimId = '...'
Set this property to include archived records.
string
"False"
Possible values include True and False.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
int
-1
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
string
""
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Whether to wait when Xero reports that the provider has hit its daily limit.
bool
false
Use of this option is strongly discouraged. See Xero API Limits for more details.
By default the connector will fail if it hits the daily API limit to avoid excessive wait times. If this is enabled and the connector hits the daily API limit, it will instead delay and retry after whatever length of time Xero recommends. This can potentially lead to the connector delaying for several hours.
How long to wait when a request hits the Xero API limit, in milliseconds.
string
"10000"
When connector issues more requests then Xero allows, this option determines how long the connector will wait before issuing another request. Increasing this value is recommended when multiple driver connections are active, such as when using Power BI.
This value is in milliseconds, so to use a delay of 10 seconds this property must be set to 10000.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the connector throws an exception.