Establishing a Connection

Create a connection to Xero by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Xero icon is not available, click the Add More icon to download and install the Xero connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to a Xero API

The Sync App supports the following Xero APIs:

Accounting API: Set the Schema connection property to ACCOUNTING
Australian Payroll API: Set the Schema connection property to PAYROLLAUS
Files API: Set the Schema connection property to FILES
Fixed Assets API: Set the Schema connection property to ASSETS
Projects API: Set the Schema connection property to PROJECTS

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 Sync App to access multiple organizations. By default the Sync App will pick the first one, which may not be the one you expect if the Sync App is granted access to additional organizations.

If you need to confirm what organizations the Sync App 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.

Authenticating to Xero

All connections to Xero are authenticated using OAuth. The Sync App supports using PKCE applications and OAuth applications.

Xero API Limits

The Xero API has usage limitations that may be encountered while using the CData Sync App. 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.

Concurrent Limit

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 Sync App will automatically retry the current request if this occurs.

Per-Minute Limit

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 Sync App will use to avoid hitting the limit again.

Per-Day Limit

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 Sync App 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 Sync App 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.

Advanced Features

This section details a selection of advanced features of the Xero Sync App.

User Defined Views

The Sync App 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.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Xero and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

By default, the Sync App 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.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
To tunnel the connection, set FirewallType to TUNNEL.
To authenticate, specify FirewallUser and FirewallPassword.
To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Data Model

The CData Sync App 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.

Using the Accounting API

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.

Using the Australian Payroll 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.

Using the Files 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.

Using the Fixed Assets 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.

Using the Projects 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.

Accounting Data Model

The CData Sync App 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

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The Sync App 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.

Xero Connector for CData Sync Tables


Name	Description
Accounts	Query, insert, update and delete accounts for a Xero organisation.
BankTransactions	Query, insert and update bank transactions for a Xero organisation.
BankTransfers	Usage information for the operation BankTransfers.rsd.
BatchPayments	Query and insert BatchPayments for a Xero organisation.
ContactGroups	Query, insert, update and delete contact groups for a Xero organisation.
Contacts	Query, insert and update contacts for a Xero organisation.
CreditNotes	Query, insert and update credit notes for a Xero organisation.
Employees	Query, insert and update employees for a Xero organisation.
ExpenseClaims	Query, insert and update expense claims for a Xero organisation.
HistoryBankTransactions	Query and insert BankTransaction history and notes for a Xero organization.
HistoryBatchPayments	Query and insert BatchPayment history and notes for a Xero organization.
HistoryContacts	Query and insert Contact history and notes for a Xero organization.
HistoryCreditNotes	Query and insert CreditNote history and notes for a Xero organization.
HistoryInvoices	Query and insert Invoice history and notes for a Xero organization.
HistoryPurchaseOrders	Query and insert PurchaseOrder history and notes for a Xero organization.
Invoices	Query, insert and update Invoices for a Xero organisation.
Items	Query, insert, update and delete items for a Xero organisation.
LinkedTransactions	Query, insert, update and delete LinkedTransactions for a Xero organisation.
ManualJournals	Query, insert and update manual journals for a Xero organisation.
Payments	Query, insert and delete payments for a Xero organisation.
PurchaseOrders	Query, insert and update purchase orders for a Xero organisation.
Receipts	Query, insert and update receipts for a Xero organisation.
TaxRates	Usage information for the operation TaxRates.rsd.

Accounts

Query, insert, update and delete accounts for a Xero organisation.

Table Specific Information

The Accounts table allows you to SELECT and INSERT accounts for a Xero organization.

SELECT

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.

INSERT

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')

Columns


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

BankTransactions

Query, insert and update bank transactions for a Xero organisation.

Table Specific Information

The BankTransactions table allows you to SELECT, INSERT, UPDATE, and DELETE bank transactions for a Xero organization.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the BankTransactions API for more details.

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>'
)

You can also insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing bank transaction:
```
INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

BankTransfers

Usage information for the operation BankTransfers.rsd.

Table Specific Information

The BankTransfers table allows you to SELECT and INSERT bank transfers for a Xero organization.

SELECT

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.

INSERT

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')

Columns


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

BatchPayments

Query and insert BatchPayments for a Xero organisation.

Table Specific Information

The BatchPayments table allows you to SELECT and INSERT batch payments for a Xero organization. The Id column is generated by the Sync App this value combines the index of the individual payment within the batch with the unique, Xero-generated BatchPaymentId

SELECT

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.

INSERT

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, Date and Reference 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. Please refer to the Xero API documentation for more details on what elements are supported within each payment entry.

INSERT INTO BatchPayments(AccountId, Reference, Date, PaymentAggregate) VALUES (
  'AAAAA-AAAAA-AAAAA-AAAAA',
  '2019-03-01',
  '<Payment><Invoice><InvoiceID>BBBBB-...</InvoiceID></Invoice><Amount>50.21</Amount></Payment> <Payment><Invoice><InvoiceID>CCCCC-...</InvoiceID></Invoice><Amount>12.96</Amount></Payment>'
)

Columns


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

ContactGroups

Query, insert, update and delete contact groups for a Xero organisation.

Table Specific Information

The ContactGroups table allows you to manage both contact groups as well as the members of those contact groups.

SELECT

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.

INSERT

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>')

UPDATE

When updating a contact group, the only fields that can be changed are the Name and Status field. Changing the Status field to DELETED will delete the contact group.

-- 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

Deleting entries from a contact group will remove individual contacts from the group.

DELETE FROM ContactGroups WHERE Id = '3|11111-11111-11111-11111'

Columns


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

Contacts

Query, insert and update contacts for a Xero organisation.

Table Specific Information

The Contacts table allows you to SELECT, INSERT, and UPDATE contacts for a Xero organization.

SELECT

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.

INSERT

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')

Update

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.

Columns


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

CreditNotes

Query, insert and update credit notes for a Xero organisation.

Table Specific Information

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 Sync App; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.

SELECT

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.

INSERT

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.

You can also insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the CreditNotes API for more details.
```
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>'
)
```
You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing credit note:
```
INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

Employees

Query, insert and update employees for a Xero organisation.

Table Specific Information

The Employees table allows you to SELECT, INSERT, and UPDATE employees for a Xero organization.

SELECT

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.

INSERT

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.

Update

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.

Columns


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

ExpenseClaims

Query, insert and update expense claims for a Xero organisation.

Table Specific Information

The ExpenseClaims table allows you to SELECT, INSERT, and UPDATE expense claims for a Xero organization.

SELECT

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.

INSERT

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>'
)

Update

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.

Columns


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

HistoryBankTransactions

Query and insert BankTransaction history and notes for a Xero organization.

Table Specific Information

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'),

Columns


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

HistoryBatchPayments

Query and insert BatchPayment history and notes for a Xero organization.

Table Specific Information

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')

Columns


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

HistoryContacts

Query and insert Contact history and notes for a Xero organization.

Table Specific Information

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'),

Columns


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

HistoryCreditNotes

Query and insert CreditNote history and notes for a Xero organization.

Table Specific Information

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'),

Columns


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

HistoryInvoices

Query and insert Invoice history and notes for a Xero organization.

Table Specific Information

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'),

Columns


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

HistoryPurchaseOrders

Query and insert PurchaseOrder history and notes for a Xero organization.

Table Specific Information

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'),

Columns


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

Invoices

Query, insert and update Invoices for a Xero organisation.

Table Specific Information

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 Sync App this value combines the index of the line item with the unique, Xero-generated InvoiceId.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Invoices API for more details.

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>'
)

You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, adding a line item to an existing invoice:
```
INSERT INTO Invoices (InvoiceId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 10)
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Discount Amounts, Discount Rates and Line Amounts

Invoices in Xero may be discounted either via a fixed amount using LineItem_DiscountAmount or with a percentage using LineItem_DiscountRate. These two methods are not compatible because discount amounts are more precise than discount rates. For example, a line item with the amount 2000.00 and a discount amount of 99.99 cannot be represented using the discount rate 4.999 because Xero allows only two digits of precision on discount rates. In addition, setting any form of discount means that LineItem_LineAmount becomes computed by Xero.

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 Sync App 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 Sync App would remove any discount values attached to that Invoice.

Columns


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

Items

Query, insert, update and delete items for a Xero organisation.

Table Specific Information

The Items table allows you to SELECT, INSERT, and UPDATE items for a Xero organization.

SELECT

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.

INSERT

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.

Update

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.

Columns


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

LinkedTransactions

Query, insert, update and delete LinkedTransactions for a Xero organisation.

Columns


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

ManualJournals

Query, insert and update manual journals for a Xero organisation.

Table Specific Information

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 Sync App; it combines the index of the line item with the unique, Xero-generated ManualJournalId.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using an XML aggregate. The elements supported here are the same as the JournalLine columns without the "JournalLine_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the ManualJournals API for more details.
```
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>'
)
```
You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:
```
INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
```

Note: Inserting into existing records 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.

Update

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.

Columns


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

Payments

Query, insert and delete payments for a Xero organisation.

Table Specific Information

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.

SELECT

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.

INSERT

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.

Columns


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

PurchaseOrders

Query, insert and update purchase orders for a Xero organisation.

Columns


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

Receipts

Query, insert and update receipts for a Xero organisation.

Table Specific Information

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 Sync App; 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.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Receipts API for more details.

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>'
)

You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:
```
INSERT INTO Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

TaxRates

Usage information for the operation TaxRates.rsd.

Table Specific Information

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 Sync App this value combines the index of the line item with the unique, Xero-generated TaxType.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the TaxComponent columns without the "TaxComponent_" prefix.

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>'
)

You can also insert a new tax component on an existing object by specifiying the Xero-generated value for the existing record. For example, adding a tax component to an existing tax rate:
```
INSERT INTO TaxRates (TaxType, TaxComponent_Name, TaxComponent_Rate, TaxComponent_IsCompound) VALUES ('TAX001', 'customTax2', '2.0000', 'False')
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

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.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Xero Connector for CData Sync Views


Name	Description
AgedPayablesByContact	Query Aged Payables for a Contact.
AgedReceivablesByContact	Query Aged Receivables for a Contact.
BalanceSheet	Query a monthly Balance Sheet.
BankStatement	Query the transactions listed on the account.
BankSummary	Query the balances and cash movements for each bank account.
BrandingThemes	Query 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	Query a monthly Budget Summary.
ContactCISSettings	Query CIS settings for contacts in a Xero organisation.
CreditNoteAllocations	Query credit note allocations for a Xero organisation.
Currencies	Query the Currencies for a Xero organisation.
ExecutiveSummary	Query a summary of changes between a month and the previous month
ExpenseClaimPayments	Query payments for a Xero organisation.
ExpenseClaimReceipts	Query claimed receipts for a Xero organisation.
HistoryBankTransfers	Query BankTransfer history and notes for a Xero organization.
HistoryExpenseClaims	Query ExpenseClaim history and notes for a Xero organization.
HistoryItems	Query Item history and notes for a Xero organization.
HistoryOverpayments	Query Overpayment history and notes for a Xero organization.
HistoryPayments	Query Payment history and notes for a Xero organization.
HistoryPrepayments	Query Prepayment history and notes for a Xero organization.
HistoryReceipts	Query Receipt history and notes for a Xero organization.
HistoryRepeatingInvoices	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	Query Overpayments for a Xero organisation.
PrepaymentAllocations	Query prepayment allocations for a Xero organisation.
Prepayments	Query Prepayments for a Xero organisation.
ProfitAndLoss	Query 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	Query a monthly Trial Balance.
Users	Query users for a Xero organisation.

AgedPayablesByContact

Query Aged Payables for a Contact.

Table-Specific Information

Select

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns


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-Columns

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.

AgedReceivablesByContact

Query Aged Receivables for a Contact.

Table-Specific Information

Select

If the ContactId is not provided, the Aged Payables report for all contacts will be returned, one after the other.

Columns


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-Columns

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.

BalanceSheet

Query a monthly Balance Sheet.

Columns


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-Columns

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.

BankStatement

Query the transactions listed on the account.

Table Specific Information

Select

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.

Columns


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-Columns

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.

BankSummary

Query the balances and cash movements for each bank account.

Columns


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-Columns

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.

BrandingThemes

Query the Branding Themes defined for the Xero organisation.

Table Specific Information

The BrandingThemes view allows you to SELECT branding themes for a Xero organization.

SELECT

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.

Columns


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

BudgetLines

Query Budget line items for a Xero organisation

Columns


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-Columns

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.

Budgets

Query Budgets for a Xero organisation

Columns


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

BudgetSummary

Query a monthly Budget Summary.

Columns


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-Columns

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).

ContactCISSettings

Query CIS settings for contacts in a Xero organisation.

Columns


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

CreditNoteAllocations

Query credit note allocations for a Xero organisation.

Columns


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

Currencies

Query the Currencies for a Xero organisation.

Table Specific Information

The Currencies table allows you to SELECT currencies for a Xero organization.

SELECT

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.

Columns


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

ExecutiveSummary

Query a summary of changes between a month and the previous month

Table Specific Information

Select

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.

Columns


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-Columns

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.

ExpenseClaimPayments

Query payments for a Xero organisation.

View Specific Information

The ExpenseClaimPayments view allows you to SELECT payments to different expense claims for a Xero organization. The Id column is generated by the Sync App; 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.

SELECT

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.

Columns


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

ExpenseClaimReceipts

Query claimed receipts for a Xero organisation.

View Specific Information

The ExpenseClaimReceipts view allows you to SELECT claimed receipt line items for a Xero organization. The Id column is generated by the Sync App; 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.

SELECT

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.

Columns


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

HistoryBankTransfers

Query BankTransfer history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryExpenseClaims

Query ExpenseClaim history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryItems

Query Item history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryOverpayments

Query Overpayment history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryPayments

Query Payment history and notes for a Xero organization.

Columns


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

HistoryPrepayments

Query Prepayment history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryReceipts

Query Receipt history and notes for a Xero organization.

View Specific Information

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.

Columns


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

HistoryRepeatingInvoices

Query RepeatingInvoice history and notes for a Xero organization.

View Specific Information

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.

Columns


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

Journals

Query the line items in journals for a Xero organisation.

Table Specific Information

The Journals view allows you to SELECT the journal lines in journals for a Xero organization. The Id column is generated by the Sync App; this value combines the index of the line item with the unique, Xero-generated JournalId.

SELECT

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.

Columns


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-Columns

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.

Organisation

Query organisation data for a Xero organisation.

Table Specific Information

The organization view allows you to SELECT organization data for a Xero organization.

SELECT

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.

Columns


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

OrganisationActions

Query organisation data for a Xero organisation.

Columns


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

OverpaymentAllocations

Query overpayment allocations for a Xero organisation.

Columns


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

Overpayments

Query Overpayments for a Xero organisation.

Columns


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

PrepaymentAllocations

Query prepayment allocations for a Xero organisation.

Columns


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

Prepayments

Query Prepayments for a Xero organisation.

Columns


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

ProfitAndLoss

Query a Profit and Loss report over a date range.

Table-Specific Information

Select

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'

Columns


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-Columns

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.

Quotes

Query quotes for a Xero organization

Columns


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

RepeatingInvoices

Query Repeating Invoice templates.

Table Specific Information

The RepeatingInvoice view allows you to SELECT the line items in repeating invoices for a Xero organization. The Id column is generated by the Sync App; this value combines the index of the line item with the unique, Xero-generated RepeatingInvoiceId.

SELECT

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.

Columns


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

Tenants

Query the list of organizations connected to your Xero account.

Columns


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

TrackingCategories

Query Tracking Categories for a Xero organisation.

Table Specific Information

The TrackingCategories view allows you to SELECT tracking categories for a Xero organization. The Id column is generated by the Sync App; it combines the index of the line item with the unique, Xero-generated TrackingCategoryId.

SELECT

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.

Columns


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

TrialBalance

Query a monthly Trial Balance.

Columns


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-Columns

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.

Users

Query users for a Xero organisation.

Table Specific Information

The Users table allows you to SELECT users for a Xero organization.

SELECT

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.

Columns


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.
EmailAddress	String	Email address 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

Australian Payroll Data Model

The CData Sync App 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

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The Sync App 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.

Xero Connector for CData Sync Tables


Name	Description
Employees	Query, insert and update employees for a Xero organisation.
LeaveApplications	Query, insert and update Leave Applications for a Xero organisation.
PayItemsDeductions	Query, insert and update PayItems for a Xero organisation.
PayItemsEarnings	Query, insert and update PayItems for a Xero organisation.
PayItemsLeave	Query, insert and update PayItems for a Xero organisation.
PayItemsReimbursements	Query, insert and update PayItems for a Xero organisation.
PayrollCalendars	Query, insert and update Payroll Calendars for a Xero organisation.
PayRuns	Query, insert and update payruns for a Xero organisation.
SuperFunds	Query, insert and update add and update Payroll Super Funds in a Xero organisation.
Timesheets	Query, insert and update Timesheets for a Xero organisation.

Employees

Query, insert and update employees for a Xero organisation.

Columns


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.
Email	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

LeaveApplications

Query, insert and update Leave Applications for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT and INSERT leave applications for a Xero organization. The Id column is generated by the Sync App; the value of this field combines the index of the line item with the unique, Xero-generated LeaveApplicationID.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the LeavePeriods_LeavePeriod_ prefix removed.

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>'
)

You can insert a new period on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a period to an existing leave application:
```
INSERT INTO LeaveApplications(LeaveApplicationID, LeavePeriods_LeavePeriod_NumberOfUnits) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 32)
```

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

PayItemsDeductions

Query, insert and update PayItems for a Xero organisation.

Columns


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

PayItemsEarnings

Query, insert and update PayItems for a Xero organisation.

Columns


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

PayItemsLeave

Query, insert and update PayItems for a Xero organisation.

Columns


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

PayItemsReimbursements

Query, insert and update PayItems for a Xero organisation.

Columns


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

PayrollCalendars

Query, insert and update Payroll Calendars for a Xero organisation.

Columns


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

PayRuns

Query, insert and update payruns for a Xero organisation.

Columns


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

SuperFunds

Query, insert and update add and update Payroll Super Funds in a Xero organisation.

Columns


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

Timesheets

Query, insert and update Timesheets for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT, INSERT and UPDATE timesheet lines for a Xero organization. The Id column is generated by the Sync App; the value of this field combines the index of the line item with the unique, Xero-generated TimesheetID.

SELECT

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.

INSERT

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.

You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the Line_ prefix removed, except when providing numbers of units. See the Xero documentation for the Timesheets API endpoint for more details.

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>'
)

You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing timesheet:

INSERT INTO Timesheets(TimesheetID, Line_EarningsRateID, Line_NumberOfUnits_NumberOfUnit1, Line_NumberOfUnits_NumberOfUnit2, ...) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 8, 7, ...)

Note: Inserting into existing records 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.

Update

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.

Delete

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.

Columns


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

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.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Xero Connector for CData Sync Views


Name	Description
EmployeeBankAccounts	Query the bank accounts for an Employee in a Xero organisation.
EmployeePayTemplateDeductions	Query the pay template deduction lines for an Employee in a Xero organisation.
EmployeePayTemplateEarnings	Query the pay template earnings lines for an Employee in a Xero organisation.
EmployeePayTemplateLeave	Query the pay template leave lines for an Employee in a Xero organisation.
EmployeePayTemplateReimbursements	Query the pay template reimbursement lines for an Employee in a Xero organisation.
EmployeePayTemplateSupers	Query the pay template super lines for an Employee in a Xero organisation.
LeaveBalances	Query 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	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.

EmployeeBankAccounts

Query the bank accounts for an Employee in a Xero organisation.

Columns


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

EmployeePayTemplateDeductions

Query the pay template deduction lines for an Employee in a Xero organisation.

Columns


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

EmployeePayTemplateEarnings

Query the pay template earnings lines for an Employee in a Xero organisation.

Columns


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

EmployeePayTemplateLeave

Query the pay template leave lines for an Employee in a Xero organisation.

Columns


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

EmployeePayTemplateReimbursements

Query the pay template reimbursement lines for an Employee in a Xero organisation.

Columns


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

EmployeePayTemplateSupers

Query the pay template super lines for an Employee in a Xero organisation.

Columns


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

LeaveBalances

Query the Leave Balance for an Employee in a Xero organisation.

Columns


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

PaySlipDeductions

Query payslip deduction line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlipEarnings

Query payslip earning line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlipLeaveAccrual

Query payslip leave accrual line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlipLeaveEarnings

Query payslip leave earnings line-items for a Xero organisation.

Columns


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

PaySlipReimbursements

Query payslip reimbursements line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlips

Query payslips for a Xero organisation.

Columns


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

PaySlipSuperannuations

Query payslip superannuation line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlipTaxes

Query payslip tax line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

PaySlipTimesheetEarnings

Query payslip timesheet-earnings line-items for a Xero organisation.

Table Specific Information

Queries

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')

Columns


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

Tenants

Query the list of organisations connected to your Xero account.

Columns


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

Files Data Model

The CData Sync App 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

Tables describes the available tables.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The Sync App 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.

Xero Connector for CData Sync Tables


Name	Description
Associations	Query, insert and delete Associations for a Xero organization.
Files	Query, update and delete Files for a Xero organization.
Folders	Query, insert, update and delete Folders for a Xero organization.

Associations

Query, insert and delete Associations for a Xero organization.

Table-Specific Information

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.

Columns


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

Files

Query, update and delete Files for a Xero organization.

Table-Specific Information

If you need to interact with file contents (either uploading or downloading them), please see the Files stored procedures.

Columns


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

Folders

Query, insert, update and delete Folders for a Xero organization.

Table-Specific Information

A Name has to be provided when creating a Folder:

INSERT INTO Folders(Name) VALUE ('Legal Documents')

Columns


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.
Email	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

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.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Xero Connector for CData Sync Views


Name	Description
Tenants	Query the list of organizations connected to your Xero account.

Tenants

Query the list of organizations connected to your Xero account.

Columns


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

Fixed Assets Data Model

The CData Sync App 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

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The Sync App 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.

Xero Connector for CData Sync Tables


Name	Description
Assets	Query, insert and update assets for a Xero organization.
AssetTypes	Query, insert and update asset types for a Xero organization.

Assets

Query, insert and update assets for a Xero organization.

Table-Specific Information

Creating Assets

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')

Updating Assets

Like asset types, an asset's value can be measured with either a depreciation rate or an estimated lifetime. Once these values have been set, you can only change between the two by setting the other to null:

UPDATE Assets
SET BookDepreciationSetting_DepreciationRate = 0.12,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetId = '46c7f1f4-7cdb-47d3-8772-2796d0b83e87'

Registering Assets

To move an asset from the draft to the registered state, you must assign these properties on it:

AssetTypeId
PurchaseDate
PurchasePrice
BookDepreciationSetting_DepreciationStartDate
BookDepreciationSetting_AveragingMethod
Either a BookDepreciationSetting_DepreciationRate or a BookDepreciationSetting_EffectiveLifeYears

Then you can update the AssetStatus field to the value "Registered".

Disposing Assets

Disposing of assets is not supported through the API.

Columns


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

AssetTypes

Query, insert and update asset types for a Xero organization.

Table-Specific Information

Creating Asset Types

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
)

Updating Asset Types

Since an AssetType must be either using a depreciation rate or an effective lifetime, you must set one field to NULL if you want to change the AssetType to use the other. For example, to change from an effective lifetime to a depreciation rate:

UPDATE AssetTypes
SET BookDepreciationSetting_DepreciationRate = 0.09,
    BookDepreciationSetting_EffectiveLifeYears = null
WHERE AssetTypeId = 'bf28b6ca-3cc6-4591-ac5a-2c1115f04b4b'

Columns


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

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.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Xero Connector for CData Sync Views


Name	Description
Settings	Query asset settings for a Xero organization.
Tenants	Query the list of organizations connected to your Xero account.

Settings

Query asset settings for a Xero organization.

Columns


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

Tenants

Query the list of organizations connected to your Xero account.

Columns


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

Projects Data Model

The CData Sync App 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

Tables describes the available tables.

Views

Views describes the available views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables

The Sync App 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.

Xero Connector for CData Sync Tables


Name	Description
Projects	Query, insert and update projects for a Xero organisation.
Tasks	Query, insert, update and delete tasks for a Xero project.
Time	Query, insert, update and delete time entries for a Xero project.

Projects

Query, insert and update projects for a Xero organisation.

Table-Specific Information

Creating Projects

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)

Updating Projects

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'

Columns


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

Tasks

Query, insert, update and delete tasks for a Xero project.

Table-Specific Information

Creating Tasks

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:

TIME charges the project the amount in RateValue every hour.
FIXED charges the project the amount in RateValue once.
NON_CHARGEABLE does not charge the project. RateValue must be set to 0.

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)

Updating Tasks

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'

Deleting Tasks

Tasks may also be deleted by specifying their Id:

DELETE FROM Tasks WHERE Id = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/d15810a1-9324-4765-a357-80d160a0b87c'

Columns


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

Time

Query, insert, update and delete time entries for a Xero project.

Table-Specific Information

Creating Time

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')

Updating Time

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'

Deleting Time

Time may also be deleted by specifying their Id:

DELETE FROM Time WHERE Id = '1de78bad-5a81-4cb8-ab53-5a1a3bc73b29/cb53829a-31ca-4760-a2a7-5a2a46e67cfb'

Columns


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

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.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Xero Connector for CData Sync Views


Name	Description
Tenants	Query the list of organizations connected to your Xero account.
Users	Query project users for a Xero organisation.

Tenants

Query the list of organizations connected to your Xero account.

Columns


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

Users

Query project users for a Xero organisation.

Columns


Name	Type	Description
UserId [KEY]	String	The unique Xero identifier of the user.
Name	String	The full name of the user.
Email	String	The email of the user.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Connection String Options

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

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.

OAuth

Property	Description
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.
OAuthAccessToken	The access token for connecting using OAuth.

SSL

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

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.

Miscellaneous

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.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

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.

AuthScheme

The type of authentication to use when connecting to Xero.

Remarks

Current authentication methods supported for new apps:

OAuth: Authenticates you using OAuth with a client ID and secret. Should be used with server-side applications.
PKCE: Authenticates you using OAuth with only a client ID. Should be used with client-side applications.
OAuthClient: Authenticates you using client credentials with a client ID and secret. Can only be used if you have purchased a Custom Connection license from Xero.
Auto: Uses the value of the XeroAppAuthentication property to determine the authentication mode. XeroAppAuthentication is included only for backwards compatibility and all new connections should use a different AuthScheme property.

Schema

The Xero API you want to access as a database schema.

Remarks

The Sync App 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.

Tenant

Sets the Xero organization to connect to. Can be a name or tenant ID.

Remarks

Xero apps may be authorized by for multiple organizatins at once. By default the Sync App 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.

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
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.
OAuthAccessToken	The access token for connecting using OAuth.

OAuthClientId

The client Id assigned when you register your application with an OAuth authorization server.

Remarks

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.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Remarks

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.

OAuthAccessToken

The access token for connecting using OAuth.

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

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.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Firewall

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to Xero and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

Proxy

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

ProxyAutoDetect

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

ProxyPassword

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

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.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.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 Sync App supports multiple schemas, the structure for Xero custom schema files is as follows:

Each schema is given a folder corresponding to that schema name.
These schema folders are contained in a parent folder.
The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\\CData\\Xero Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

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.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

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.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

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.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

AutoExpandDetails

Set this property to true automatically include extra details on BankTransfers, ContactGroups, ExpenseClaims, ExpenseClaimPayments, ExpenseClaimReceipts and Receipts.

Remarks

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 = '...'

IncludeArchived

Set this property to include archived records.

Remarks

Possible values include True and False.

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.

Remarks

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.

Remarks

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.

Integration and Formatting


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.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

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, "*=*".

RetryDailyLimit

Whether to wait when Xero reports that the provider has hit its daily limit.

Remarks

Use of this option is strongly discouraged. See Xero API Limits for more details.

By default the Sync App will fail if it hits the daily API limit to avoid excessive wait times. If this is enabled and the Sync App hits the daily API limit, it will instead delay and retry after whatever length of time Xero recommends. This can potentially lead to the Sync App delaying for several hours.

RetryWaitTime

How long to wait when a request hits the Xero API limit, in milliseconds.

Remarks

When Sync App issues more requests then Xero allows, this option determines how long the Sync App 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.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

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 Sync App throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App 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 Sync App.

This User Defined View configuration file is formatted as follows:

Each root element defines the name of a view.
Each root element contains a child element, called query, which contains the custom SQL query for the view.

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"

Xero Connector for CData Sync

Xero Connector for CData Sync

Overview

Establishing a Connection

Connecting to a Xero API

Authenticating to Xero

Xero API Limits

Xero API Limits

Concurrent Limit

Per-Minute Limit

Per-Day Limit

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Using the Accounting API

Using the Australian Payroll API

Using the Files API

Using the Fixed Assets API

Using the Projects API

Accounting Data Model

Tables

Views

Stored Procedures

Tables

Xero Connector for CData Sync Tables

Accounts

Table Specific Information

SELECT

INSERT

Columns

BankTransactions

Table Specific Information

SELECT

INSERT

Update

Delete

Columns

BankTransfers

Table Specific Information

SELECT

INSERT

Columns

BatchPayments

Table Specific Information

SELECT

INSERT

Columns

ContactGroups

Table Specific Information

SELECT

INSERT

UPDATE

DELETE

Columns

Contacts

Table Specific Information

SELECT

INSERT

Update

Columns

CreditNotes

Table Specific Information

SELECT

INSERT

Update

Delete

Columns

Employees

Table Specific Information

SELECT