Xero への接続を追加

Xero への接続を追加するには：

1. アプリケーションコンソールで、接続ページに移動します。
2. 接続の追加パネルで、追加したい接続のアイコンを選択します。
3. Xero アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからXero コネクタをダウンロードおよびインストールします。

必須プロパティについては、設定タブを参照してください。

通常必須ではない接続プロパティについては、高度な設定タブを参照してください。

Connecting to Xero

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

When the Sync App starts, it opens a browser and requests your login information. The Sync App uses the credentials you provide to access your Xero data. These credentials are saved and automatically refreshed as needed.

To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth, and you must create a custom OAuth application.

The following subsections describe how to authenticate to Xero from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping.
• Web: access to data via a shared website.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard.

For information about how to create a custom OAuth application, see カスタム認証アプリケーションの作成.

For a complete list of connection string properties available in Xero, see Connection.

Automatic refresh of the OAuth access token:

To have the Sync App automatically refresh the OAuth access token, do the following:

1. Before connecting to data for the first time, set the following connection parameters:
   • InitiateOAuth = REFRESH.
   • OAuthClientId = the client Id in your application settings.
   • OAuthClientSecret = the client secret in your application settings.
   • OAuthAccessToken = the access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation = the path where you want the Sync App to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the GetOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId = the Client Id in your application settings.
   • OAuthClientSecret = the Client Secret in your application settings.

3. Call GetOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by GetOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Connecting to a Xero API

The Sync App supports several Xero APIs. To connect to them, set the appropriate connection property:

• For the Accounting API, set Schema to ACCOUNTING.
• For the Australian Payroll API, set Schema to PAYROLLAUS.
• For the Files API, set Schema to FILES.
• For the Fixed Assets API set Schema to ASSETS.
• For the Projects API, set Schema to PROJECTS.

Specifying Permitted Organization Access

For any of the above APIs, we also recommend setting the Tenant property, which enables you to authorize the Sync App to access multiple organizations. Tenant is normally set to identify one or more organizations that the Sync App can access. Legal entries for Tenant can be either the name of the organization or its ID. (Since multiple organizations can have the same name, we recommend using the ID to set the Tenant property.)

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.

Uncertified Connectors Limit

Uncertified OAuth applications are limited to 25 connections. There is no limit on connections for certified apps.

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:

/* Retrieves 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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

See the pages for individual テーブル and ビュー for their full list of optimized filters.

このセクションでは、Xero Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App はユーザー定義ビューの使用をサポートします。これは事前設定されたユーザー定義クエリによって内容が決定される仮想テーブルです。 このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。 カスタムビューの作成と設定の概要については、ユーザー定義ビュー を参照してください。

SSL の設定

SSL の設定 を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。詳しくは、「接続文字列オプション」にあるSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシ に合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、Xero にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくは、クエリ処理 を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログ を参照してください。基本的なロギングに必要なのは2つの接続プロパティだけですが、より詳細なロギングをサポートする多くの機能があり、LogModules 接続プロパティを使用してロギングする情報のサブセットを指定することができます。

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーとのTLS のネゴシエーションを試みます。サーバー証明書は、デフォルトのシステム信頼済み証明書ストアで検証されます。SSLServerCert 接続プロパティを使用して、証明書の検証方法をオーバーライドできます。

別の証明書を指定するには、SSLServerCert 接続プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

HTTP プロキシへの認証には、以下のように設定します。

• ProxyServer：HTTP トラフィックをルートするプロキシサーバーのホストネームもしくはIP アドレス。
• ProxyPort：プロキシサーバーが起動しているTCP ポート。
• ProxyAuthScheme：プロキシサーバーに対して認証する際にSync App が使用する認証方法。
• ProxyUser：プロキシサーバーに登録されているユーザーアカウントのユーザー名。
• ProxyPassword：ProxyUser に紐付けられたパスワード。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

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.

INSERTs to specific tables which model line items in the Accounting API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See 会計データモデル 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.

INSERTs to specific tables which model line items in the Australian Payroll API have batch support enabled. The recommended setting of BatchSize for maximum performance is 50.

See オーストラリア給与データモデル 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 ファイルデータモデル 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 固定資産データモデル 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 プロジェクトデータモデル for the available entities in the Projects API.

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

テーブル describes the available tables.

Views

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

Stored Procedures

ストアドプロシージャ are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Sync App はXero のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Xero Connector for CData Sync テーブル

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.
Quotes	Query quotes for a Xero organization
Receipts	Query, insert and update receipts for a Xero organisation.
TaxRates	Usage information for the operation TaxRates.rsd.

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

To insert a single row, set the required fields. A new table object is 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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Xero API does not list a high volume threshold for the BankTransactions table. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Type
  • Status
  • Date
  • Contact.ContactId
• Optimized ORDER BY columns:
  • BankTransactionID
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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.

1. 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>Monthly account fee</Description>
   		<UnitAmount>49.90</UnitAmount>
   		<TaxType>NONE</TaxType>
   		<TaxAmount>0.00</TaxAmount>
   		<LineAmount>49.90</LineAmount>
   		<AccountCode>404</AccountCode>
   		<Quantity>1.0000</Quantity>
      </LineItem>'
   )

2. 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, LineItem_UnitAmount, LineItem_AccountCode, LineItem_LineAmount, Total, SubTotal) 
   VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 1.00, 150, 1.00, 3.00, 3.00)

Note: The 'Total' and 'Subtotal' columns are for the entire record. As such, they need to be set to the total and subtotal for all line items attached to the record, including those that already exist prior to INSERT execution.

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.

In order to delete a record from the BankTransactions table, the BankTransactionId column should be set:

DELETE FROM BankTransactions WHERE BankTransactionId='2355f1d6-91f3-4a4b-b7e2-d13b1f58c531'

Note: This is equivalent to updating the status column to 'DELETED'. Additionally, delete functionality is not available for BankTransactions of type RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, SPEND-OVERPAYMENT, or SPEND-PREPAYMENT.

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	True	The Xero-generated ID of the bank transaction line item
LineItem_Description	String	False	A description for the line item in the bank transaction.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. This amount must be positive.
LineItem_AccountCode	String	False	The code for the referenced account. Including this field is recommended when posting.
LineItem_Item_ItemId	String	False	The ID of the item the lineitem refers to.
LineItem_Item_Name	String	False	The name of the item the lineitem refers to.
LineItem_Item_Code	String	False	The code of the item the lineitem refers to.
LineItem_TaxType	String	False	The tax code for the transaction. This field is used as an override for the default tax code for the selected account.
LineItem_TaxAmount	Decimal	False	The automatically calculated percentage of tax for the line amount, based on the tax rate.
LineItem_LineAmount	Decimal	False	The amount of the line item. If either Quantity or UnitAmount are omitted, Xero will calculate the missing value from LineAmount.
LineItem_TrackingCategory1_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
IsReconciled	Boolean	False	This field shows whether this transaction is reconciled.
Date	Date	False	The date of the transaction.
Reference	String	False	An internal Xero reference for the transaction.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
URL	String	False	A link to a source document.
Status	String	False	The bank transaction status code. The valid values are AUTHORISED and DELETED.
SubTotal	Decimal	False	The total of the transaction, excluding taxes.
TotalTax	Decimal	False	The tax on the bank transaction.
Total	Decimal	False	The total of the transaction, including tax.
PrepaymentId	String	False	Xero generated unique identifier for a Prepayment. This will be returned on BankTransactions with a Type of SPEND-PREPAYMENT or RECEIVE-PREPAYMENT.
OverpaymentId	String	False	Xero generated unique identifier for an Overpayment. This will be returned on BankTransactions with a Type of SPEND-OVERPAYMENT or RECEIVE-OVERPAYMENT.
UpdatedDateUTC	Datetime	True	The date when the transaction was last updated.
FullyPaidOnDate	Date	False	The date when the transaction was fully paid.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Usage information for the operation BankTransfers.rsd.

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

To insert a single row, set the required fields. A new table object is 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

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

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Xero API does not list a high volume threshold for the BatchPayments table. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Type
  • Account.AccountId
  • Status
• Optimized ORDER BY columns:
  • BatchPaymentID
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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>5aa9451d-95d1-4f95-a966-bbab2573f71c</InvoiceID>
			<HasErrors>false</HasErrors>
			<IsDiscounted>false</IsDiscounted>
		</Invoice>
		<PaymentID>a22a64cb-364e-43fa-9a1f-bb2cd1f4adde</PaymentID>
		<Reference>ref/cheque</Reference>
		<Amount>913.55</Amount>
	</Payment>
	<Payment>
		...
	</Payment>'
)

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.

In order to delete a record from the BatchPayments table, the BatchPaymentId column should be set:

DELETE FROM BatchPayments WHERE BatchPaymentId='79f60299-7138-4d67-ae3c-50f1d4231dde'

Note: This will only set the 'Status' column to 'DELETED' for the given record. The record will still be visible in an unfiltered result.

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

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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Xero API does not list a high volume threshold for the Contacts table. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Name
  • EmailAddress
  • AccountNumber
• Optimized ORDER BY columns:
  • ContactID
  • UpdatedDateUTC
  • Name

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 insert a single row, set the required fields. A new table object is created as a single entry. The Name field is required to add a new contact.

INSERT INTO Contacts (Name) VALUES ('John Doe')

The Contacts table also supports bulk inserts, simply specify multiple records to be added in the VALUES clause:

INSERT INTO Contacts (Name) VALUES ('John Doe'), ('Johnny')

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.
CompanyNumber	String	False	The registration number of the company. The maximum length of this field is 50 characters.
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.
MergedToContactID	String	True	This field displays the destination ID of a contact that has been merged.
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.
PurchasesDefaultLineAmountType	String	True	The default purchases line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
SalesDefaultAccountCode	String	False	The default sales account code for contacts.
SalesDefaultLineAmountType	String	True	The default sales line amount type for contacts. Possible values are: INCLUSIVE, EXCLUSIVE, or NONE.
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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Xero API does not list a high volume threshold for the CreditNotes table. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Status
  • Date
  • Reference
  • Contact.ContactID
  • Contact.Name
  • Contact.ContactNumber
  • Type
• Optimized ORDER BY columns:
  • CreditNoteID
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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.

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

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

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

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new employee record, the following fields are required: FirstName and LastName.

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe')

Employees can also be added in bulk by specifying multiple records in the VALUES clause.

INSERT INTO Employees (FirstName, LastName) 
	VALUES 
	('John', 'Doe'),
	('Jane', '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
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

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

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

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

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

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

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

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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Invoices table has a high volume threshold of 500,000. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Status
  • Contact.ContactID
  • Contact.Name
  • Contact.ContactNumber
  • Reference
  • Date
  • Type
  • AmountDue
  • AmountPaid
  • DueDate
  • SearchTerm
• Optimized ORDER BY columns:
  • InvoiceId
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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.

1. 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>
   	<ItemCode>12</ItemCode>
   	<Description>Onsite project management </Description>
   	<Quantity>1.0000</Quantity>
   	<UnitAmount>1800.00</UnitAmount>
   	<TaxType>OUTPUT</TaxType>
   	<TaxAmount>225.00</TaxAmount>
   	<LineAmount>1800.00</LineAmount>
   	<AccountCode>200</AccountCode>
   	<AccountId>4f2a3169-8454-4012-a642-05a88ef32982</AccountId>
   	<Tracking>
   		<TrackingCategory>
   			<Name>Activity/Workstream</Name>
   			<Option>Onsite consultancy</Option>
   		</TrackingCategory>
   	</Tracking>
   	<LineItemID>52208ff9-528a-4985-a9ad-b2b1d4210e38</LineItemID>
      </LineItem>
      <LineItem>
   	...
      </LineItem>'
   )

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

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

To insert a single row, set the required fields. A new table object is 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

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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Xero API does not list a high volume threshold for the ManualJournals table. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Narration
• Optimized ORDER BY columns:
  • ManualJournalId
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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.

1. 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>
   		<Description>Coded incorrectly Office Equipment should be Computer Equipment</Description>
   		<TaxType>NONE</TaxType>
   		<LineAmount>-2569</LineAmount>
   		<AccountCode>720</AccountCode>
   	</JournalLine>
   	<JournalLine>
   		...
   	</JournalLine>'
   )

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

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.

Optimized Filters and High Volume Thresholds

The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.

To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.

Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.

The Payments table has a high volume threshold of 100,000. The Xero API lists the following optimized filters for this table:

• Optimized columns (when used in WHERE clauses):
  • Optimized for standalone use in WHERE clause or in an AND clause:
    • PaymentType
    • Status
    • Date
    • Invoice.InvoiceId
    • Reference
  • Optimized for use in OR clause:
    • Invoice.InvoiceId
    • PaymentID
• Optimized ORDER BY columns:
  • PaymentID
  • UpdatedDateUTC
  • Date

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 insert a single row, set the required fields. A new table object is 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

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

Table Specific Information

The PurchaseOrders table allows you to SELECT, INSERT, and UPDATE PurchaseOrder 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 PurchaseOrderId.

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 insert a single row, set the required fields. A new table object is created as a single entry. To insert a new Purchase Order, the ContactId and Date fields are required in addition to at least one line item. A PurchaseOrder line item must at minimum contain a description (LineItem_Description).

1. You can insert a new Purchase Order with multiple line items using an XML aggregate. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix. Please refer to the Xero documentation of the Purchase Order API for more details.
   

   INSERT INTO PurchaseOrders (Contact_ContactId, LineItemAggregate) VALUES (
     '883628bc-7f22-4234-8a4c-81571816a8a1',
     '<LineItem>
   		<Description>Example Description</Description>
      </LineItem>
      <LineItem>
   		...
      </LineItem>'
   )

2. You can also create multiple Purchase Orders in a single query by using a bulk insert. To do this, you should not include a PurchaseOrderId in your query, then specify the values for the first line item of each PurchaseOrder to add.
   

   INSERT INTO PurchaseOrders (Contact_ContactId, LineItem_Description)
     VALUES
   ('37ec4d02-dc3f-4a4c-a943-e8ae8c7db93a', 'Example1'),
   ('abcd1234-1234-1b2c-a123-abcde12345ab', 'Example2')

3. You can also insert new line items on an existing object by specifying the Xero-generated value of the existing record. For example, to add 2 new line items to an existing Purchase Order:
   

   INSERT INTO PurchaseOrders (PurchaseOrderId, LineItem_Description)
     VALUES
   ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'Example1'),
   ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'Example2')

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

Query quotes for a Xero organization

Table Specific Information

The Quotes table allows you to SELECT, INSERT, and UPDATE Quote 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 QuoteId.

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 insert a single row, set the required fields. A new table object is created as a single entry. To insert a new Quote, the ContactId and Date fields are required in addition to at least one line item. A Quote line item must at minimum contain a description (LineItem_Description).

1. You can insert a new Quote with multiple line items using an XML aggregate. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix. Please refer to the Xero documentation of the Quotes API for more details.
   

   INSERT INTO Quotes (ContactId, Date, LineItemAggregate) VALUES (
     '883628bc-7f22-4234-8a4c-81571816a8a1',
     '2023-11-21',
     '<LineItem>
   		<Description>Example Description</Description>
   	</LineItem>'
   )

2. You can also create multiple Quotes in a single query by using a bulk insert. To do this, you should not include a QuoteId in your query, then specify the values for the first line item of each Quote to add.
   

   INSERT INTO Quotes (ContactId, Date, LineItem_Description)
     VALUES
   ('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description'),
   ('883628bc-7f22-4234-8a4c-81571816a8a1', '2023-11-21', 'Example Description')

3. You can also insert new line items on an existing object by specifying the Xero-generated value of the existing record. For example, to add 2 new line items to an existing Quote:
   

   INSERT INTO Quotes (QuoteId, LineItem_Description)
     VALUES
   ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem1'),
   ('fbb38812-dcc4-4e07-9ebd-2c38e1409b18', 'newItem2')

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	False	The line item index combined with the Id of the Quote.
QuoteId	String	False	The Id of the Quote. This field is assigned by Xero.
ContactId	String	False	The Id of the contact.
ContactName	String	False	The name of the contact.
Date	Date	False	The date the quote was issued.
ExpiryDate	Date	False	The date the quote expires.
Status	String	False	The status of the quote.
LineItem_LineItemId	String	False	The Xero generated identifier for a line item.
LineItem_Description	String	False	The description of the line item.
LineItem_Quantity	Double	False	The quantity of the associated item in the line item.
LineItem_UnitAmount	Decimal	False	The unit amount of the associated item in the line item.
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.
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_DiscountRate	Double	False	The discount percentage being applied to the line item.
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_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.
QuoteNumber	String	False	The unique, alphanumeric code identifying the quote.
LineAmountTypes	String	False	This field specifies whether line amounts include tax. The valid values are Exclusive, Inclusive, and NoTax.
Reference	String	False	An additional reference number for ACCREC-type invoices.
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.
SubTotal	Decimal	False	The subtotal for the invoice, which excludes taxes.
TotalTax	Decimal	False	The total tax for the invoice.
Total	Decimal	False	The total for the invoice, including tax -- the sum of SubTotal and TotalTax.
TotalDiscount	Decimal	False	Total of discounts applied on the invoice line items.
BrandingThemeId	String	False	The Id of the branding theme.
Terms	String	False	The terms of the quote.
UpdatedDateUTC	Datetime	False	The date when the invoice was last updated.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant
LineItemAggregate	String	False	Used to define Quote LineItem rows using XML values. Should be provided on INSERT only.

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

To insert a single row, set the required fields. A new table object is 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.

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

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

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

To insert a single row, set the required fields. A new table object is 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.

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

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

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Xero Connector for CData Sync ビュー

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

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. Please note that this will generate one operation against the Xero API per contact in the organization. Consider filtering to a subset of contacts or adding a LIMIT clause to avoid exceeding API limits. See Xero API の制限 for more information.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

Query Aged Receivables for a Contact.

Table-Specific Information

SELECT

If the ContactId is not provided, the Aged Receivables report for all contacts will be returned, one after the other. Please note that this will generate one operation against the Xero API per contact in the organization. Consider filtering to a subset of contacts or adding a LIMIT clause to avoid exceeding API limits. See Xero API の制限 for more information.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

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.

Note: To access bank statements, you must complete additional security requirements:

1. Sign an addendum to Xero's developer terms and conditions.
2. Request the accounting.reports.bankstatement.read scope in your custom OAuth app.

For more information and to complete these steps, reach out directly to Xero API support.

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	Decimal	The transaction's value
Balance	Decimal	The statement's running total
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
toDate	Datetime	Starting date.
fromDate	Datetime	Closing date.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.

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

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
DateFrom	Datetime	Includes budget periods starting from this date.
DateTo	Datetime	Includes budget periods until this date.

Query Budgets for a Xero organisation

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

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
Date	Datetime	As at date.
Periods	Integer	The number of periods to compare (integer between 1 and 12).
Timeframe	Integer	The period size to compare to: Specify 1 for month, 3 for quarter, or 12 for year).

Query CIS settings for contacts in a Xero organisation.

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

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

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

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
date	Date	The month to compare with the previous month.

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

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

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