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

必須プロパティは［設定］タブにリストされています。［Advanced］タブには、通常は必要ない接続プロパティが表示されます。

Xero API への接続

Sync App は、以下のXero API をサポートしています。

• Accounting API：Schema 接続プロパティをACCOUNTING に設定。
• Australian Payroll API：Schema 接続プロパティをPAYROLLAUS に設定。
• Files API：Schema 接続プロパティをFILES に設定。
• Fixed Assets API：Schema 接続プロパティをASSETS に設定。
• Projects API：Schema 接続プロパティをPROJECTS に設定。

It is also recommended that you set the Tenant property, which can be the name or ID of a Xero organization. Xero allows you to authorize the Sync App to access multiple organizations. By default the Sync App will pick the first one, which may not be the one you expect if the Sync App is granted access to additional organizations.

If you need to confirm what organizations the Sync App has access to, you can connect without an Tenant and read the Tenants view. This will provide both the ID and the name of each connected organization. You should use the ID to set the Tenant property when possible since multiple organizations can have the same name.

Xero への認証

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

Xero API Limits

The Xero API has usage limitations that may be encountered while using the CData Sync App. Note that all of these apply on a per-application and per-organization basis. An application may exceed these limits if it is querying multiple organizations as long as it obeys the limits within each organization.

Concurrent Limit

At most 5 API calls from the application may be active against the same organization at once. Typically this limit is not an issue because the Sync App will automatically retry the current request if this occurs.

Per-Minute Limit

At most 60 API calls per minute may be made from the application to the same organization. This limit is also not an issue as Xero reports a recommended delay time that the Sync App will use to avoid hitting the limit again.

Per-Day Limit

At most 5000 API calls per day may be made from the application to the same organization. Hitting this limit is rare but can happen with certain tables (such as history tables and some reports) or certain settings (such as AutoExpandDetails) are used, as they require the Sync App to make a single API call for each invoice/contact/etc. that is read.

If this limit is hit frequently then the first step should be avoiding the tables or settings that trigger it. It is also possible to convert the queries into versions which read fewer rows at one time. This is usually done by replacing simple selects with subqueries that pick out small groups of rows, and then using multiple versions of these queries at different times:

/* Retreives history for about 1/16 of the invoices */
SELECT * FROM HistoryInvoices WHERE InvoiceId IN (
  SELECT InvoiceId FROM Invoices WHERE InvoiceId LIKE '0%'
) 

The RetryDailyLimit option is also available but its use is strongly discouraged, as in the worst cases the delays that Xero suggests can span several hours during which the Sync App will be unavailable. It is typically better to restructure how you request data to fit within the limits than to ignore the limits and execute queries that take multiple hours to run.

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

ユーザー定義ビュー

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

SSL の設定

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

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

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

クエリ処理

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

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

ログ

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

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

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

• プロキシベースのファイヤーウォールを使用するには、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.

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.

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 テーブルのクエリは、リレーショナルデータベースのテーブルのクエリと同じです。時には特別なケースもあります。例えば、テーブルの特定のカラムデータを取得するために特定のカラムをWHERE 句に含める必要がある場合などです。これは通常、特定のカラムを取得するために行ごとに個別のリクエストを行う必要がある場合に必要です。これらの特別な状況は、以下にリンクされているテーブルページの上部に明確に文書化されています。

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The following fields are required for inserts: Code, Name, and Type.

INSERT INTO Accounts (Name,Code,Type) VALUES ('John Doe','2000','BANK')

Columns

Name	Type	ReadOnly	Description
AccountId [KEY]	String	True	The unique, Xero-assigned identifier of the account.
Code	String	False	Customer-defined alphanumeric account code. For example, 200 or SALES.
Name	String	False	Name of the account.
Type	String	False	The account type. Valid values are BANK, CURRENT, CURRLIAB, DEPRECIATN, DIRECTCOSTS, EQUITY, EXPENSE, FIXED, LIABILITY, NONCURRENT, OTHERINCOME, OVERHEADS, PREPAYMENT, REVENUE, SALES, TERMLIAB, PAYGLIABILITY, SUPERANNUATIONEXPENSE, SUPERANNUATIONLIABILITY, and WAGESEXPENSE.
Description	String	False	Description of the account. All accounts except bank accounts return this element.
TaxType	String	False	The tax type. For example, INPUT or NONE.
EnablePaymentsToAccount	Boolean	False	Describes whether the account can have payments applied to it.
ShowInExpenseClaims	Boolean	False	Describes whether the account code is available for use with expense claims.
Class	String	True	The account class type. For example, ASSET or EQUITY.
Status	String	False	The account status code.
SystemAccount	String	True	Returned only for system accounts, such as DEBTORS, CREDITORS, etc.
BankAccountNumber	String	False	The bank account number associated with the account. BANK account types only.
CurrencyCode	String	False	The currency code associated with the account. BANK account types only.
ReportingCode	String	True	The reporting code. Only available for NZ organisations.
ReportingCodeName	String	True	The reporting code name. Only available for NZ organisations.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the user record.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
BankAccountType	String	False	Valid types are BANK, CREDITCARD, and PAYPAL
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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

Table Specific Information

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

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new bank transaction record, the following fields are required in addition to at least one line item: Type, Contact_ContactName or Contact_ContactId, and BankAccount_AccountId or BankAccount_Code. To create a new line item, the following fields are required: LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, and LineItem_AccountCode.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

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>LineItem1Desc</Description><Quantity>1</Quantity><AccountCode>100</AccountCode></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><AccountCode>100</AccountCode></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) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Delete

The Xero API has limited support for deleting any object. In order to delete a record from the BankTransactions table, you must update the Status field to an appropriate value.

Columns

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

Usage information for the operation BankTransfers.rsd.

Table Specific Information

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

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The following fields are required for inserts: FromBankAccount_Code, ToBankAccount_Code, and Amount

INSERT INTO BankTransfers (FromBankAccount_Code,ToBankAccount_Code,Amount) VALUES ('3d08fd48-434d-4c18-a57b-831663ab70d2','ceef65a5-a545-423b-9312-78a53caadbc4','1000.00')

Columns

Name	Type	ReadOnly	Description
BankTransferId [KEY]	String	True	The unique, Xero-assigned identifier for the bankTransfer.
FromBankAccount_Code	String	False	The bank account code that you will be sending from.
FromBankAccount_AccountId	String	False	The bank account account Id that you will be sending from.
FromBankAccount_Name	String	False	The bank account name that you will be sending from.
ToBankAccount_Code	String	False	The bank account code that you will be sending to.
ToBankAccount_AccountId	String	False	The bank account account Id that you will be sending to.
ToBankAccount_Name	String	False	The bank account name that you will be sending to.
Amount	Decimal	False	The amount of the money to transfer.
Date	Date	False	The date of the transfer YYYY-MM-DD.
CurrencyRate	Decimal	True	The currency rate.
FromBankTransactionID	String	True	The Bank Transaction ID for the source account.
ToBankTransactionID	String	True	The Bank Transaction ID for the destination account.
HasAttachments	Boolean	True	Boolean to indicate if a Bank Transfer has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Query and insert BatchPayments for a Xero organisation.

Table Specific Information

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

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new batch payment, the AccountId, Date and Reference are required in addition to at least one payment item; the Payment_InvoiceId and Payment_Amount fields are required to insert a payment item.

You can also provide multiple payments using an XML aggregate which contains the data for each payment in the batch. Please refer to the Xero API documentation for more details on what elements are supported within each payment entry.

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The batch payment index combined with the Id of the batch payment.
BatchPaymentId	String	True	The Id of the invoice. This field is assigned by Xero.
AccountId	String	False	The ID of the account used to make the payment. It must either be a BANK account or have payments enabled.
Particulars	String	False	(NZ only) The particulars that will appear on the statement.
Code	String	False	(NZ only) The transaction code that will appear on the statement.
Reference	String	False	(NZ only) The transaction reference that will appear on the statement.
Details	String	False	(Non-NZ only) The details sent to the organization's bank as a reference for the bank transaction.
Narrative	String	False	(UK only) A description that is only visible in the Xero statement line
Date	Date	False	The date the payment is being made.
Payment_PaymentId	String	True	The ID of the Payment. This field is assigned by Xero.
Payment_InvoiceId	String	False	The ID of the Invoice the payment was made against
Payment_BankAccountNumber	String	False	The account number of the bank the payment is being made to.
Payment_Particulars	String	False	(NZ only) The particulars that will appear on the statement.
Payment_Code	String	False	(NZ only) The transaction code that will appear on the supplier's bank account.
Payment_Reference	String	False	(NZ only) The transaction reference that will appear on the supplier's bank account.
Payment_Details	String	False	The details sent to the supplier's bank as a reference for the bank transaction.
Payment_Amount	Decimal	False	The amount being paid.
PaymentAggregate	String	False	Used to define Payment rows using XML values. Should be provided on INSERT only.
Type	String	True	Either PAYBATCH for bill payments, or RECBATCH for sales payments.
Status	String	True	Either AUTHORISED or DELETED
TotalAmount	Decimal	True	The total of all the payments included in the batch
IsReconciled	Boolean	True	Whether the batch payment has been reconciled
UpdatedDateUTC	Datetime	True	The date when the batch payment was last updated.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. The Name field is required to add a new contact.

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

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns

Name	Type	ReadOnly	Description
ContactId [KEY]	String	True	The unique Xero identifier for the contact.
Name	String	False	The contact name.
AccountNumber	String	False	The account number associated with the contact.
ContactNumber	String	False	An identifier from an external system. Although this field is read-only on the Xero contact screen, it can be updated via the API.
ContactStatus	String	False	The status of the contact. Available status types are ACTIVE or ARCHIVED.
FirstName	String	False	The first name of the contact. The maximum length of this field is 255 characters.
LastName	String	False	The last name of the contact. The maximum length of this field is 255 characters.
EmailAddress	String	False	The email address of the contact. The maximum length of this field is 500 characters.
SkypeUserName	String	False	The Skype username of the contact.
ContactPerson1_FirstName	String	False	First name of contact person.
ContactPerson1_LastName	String	False	Last name of contact person.
ContactPerson1_EmailAddress	String	False	Email address name of contact person.
ContactPerson1_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson2_FirstName	String	False	First name of contact person.
ContactPerson2_LastName	String	False	Last name of contact person.
ContactPerson2_EmailAddress	String	False	Email address name of contact person.
ContactPerson2_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson3_FirstName	String	False	First name of contact person.
ContactPerson3_LastName	String	False	Last name of contact person.
ContactPerson3_EmailAddress	String	False	Email address name of contact person.
ContactPerson3_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
ContactPerson4_FirstName	String	False	First name of contact person.
ContactPerson4_LastName	String	False	Last name of contact person.
ContactPerson4_EmailAddress	String	False	Email address name of contact person.
ContactPerson4_IncludeInEmails	Boolean	False	Whether to include contact person in emails.
BankAccountDetails	String	False	The bank account number of the contact.
TaxNumber	String	False	The region-specific tax number of the contact: the ABN (Australia), GST (New Zealand), VAT (UK), or Tax Id Number (US and global).
AccountsReceivableTaxType	String	False	The default tax type used for the contact on accounts-receivable invoices.
AccountsPayableTaxType	String	False	The default tax type used for the contact on accounts-payable invoices.
Street_AddressLine1	String	False	Line 1 of the address.
Street_AddressLine2	String	False	Line 2 of the address.
Street_AddressLine3	String	False	Line 3 of the address.
Street_AddressLine4	String	False	Line 4 of the address.
Street_City	String	False	The city.
Street_Region	String	False	The region.
Street_PostalCode	String	False	The postal code.
Street_Country	String	False	The country.
Street_AttentionTo	String	False	The attention-to line of the address.
POBox_AddressLine1	String	False	Line 1 of the address.
POBox_AddressLine2	String	False	Line 2 of the address.
POBox_AddressLine3	String	False	Line 3 of the address.
POBox_AddressLine4	String	False	Line 4 of the address.
POBox_City	String	False	The city.
POBox_Region	String	False	The region.
POBox_PostalCode	String	False	The postal code.
POBox_Country	String	False	The country.
POBox_AttentionTo	String	False	The attention-to line of the address.
DDI_PhoneNumber	String	False	The phone number without area or country code.
DDI_PhoneAreaCode	String	False	The area code.
DDI_PhoneCountryCode	String	False	The country code.
Default_PhoneNumber	String	False	The phone number without area or country code.
Default_PhoneAreaCode	String	False	The area code.
Default_PhoneCountryCode	String	False	The country code.
Fax_PhoneNumber	String	False	The phone number without area or country code.
Fax_PhoneAreaCode	String	False	The area code.
Fax_PhoneCountryCode	String	False	The country code.
Mobile_PhoneNumber	String	False	The phone number without area or country code.
Mobile_PhoneAreaCode	String	False	The area code.
Mobile_PhoneCountryCode	String	False	The country code.
UpdatedDateUTC	Datetime	True	The timestamp of when the contact was last updated.
ContactGroupId	String	True	Displays which contact groups a contact is included in.
IsSupplier	Boolean	True	This field shows whether a contact has any accounts-payable (AP) invoices. This field is set automatically when an AP invoice is generated for a contact.
IsCustomer	Boolean	True	This field shows whether a contact has any accounts-receivable (AR) invoices. This field is set automatically when an AR invoice is generated for a contact.
DefaultCurrency	String	False	The default currency for the contact.
Website	String	True	The website of the contact.
BrandingThemeId	String	True	The Id of the branding theme.
BrandingThemeName	String	True	The Name of the branding theme.
PurchasesDefaultAccountCode	String	False	The default purchases account code for contacts.
SalesDefaultAccountCode	String	False	The default sales account code for contacts.
BatchPayments_BankAccountNumber	String	True	Batch payment bank account number for the contact.
BatchPayments_BankAccountName	String	True	Batch payment bank account name for the contact.
BatchPayments_Details	String	True	Batch payment details for the contact.
Discount	Decimal	True	The default discount rate for the contact.
Balances_AccountsReceivable_Outstanding	Decimal	True	The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsReceivable_Overdue	Decimal	True	The outstanding and overdue amounts for bills (accounts payable).
Balances_AccountsPayable_Outstanding	Decimal	True	The outstanding and overdue amounts for sales invoices (accounts receivable).
Balances_AccountsPayable_Overdue	Decimal	True	The outstanding and overdue amounts for bills (accounts payable).
PaymentTerms_Bills_Day	Int	True	The default payment terms for the contact.
PaymentTerms_Bills_Type	String	True	The default payment terms for the contact.
PaymentTerms_Sales_Day	Int	True	The default payment terms for the contact.
PaymentTerms_Sales_Type	String	True	The default payment terms for the contact.
SalesTrackingCategory1_Name	String	False	The default sales tracking category name for contacts.
SalesTrackingOption1_Name	String	False	The default sales tracking option name for contacts.
SalesTrackingCategory2_Name	String	False	The default sales tracking category name for contacts.
SalesTrackingOption2_Name	String	False	The default sales tracking option name for contacts.
PurchaseTrackingCategory1_Name	String	False	The default purchase tracking category name for contacts.
PurchaseTrackingOption1_Name	String	False	The default purchase tracking option name for contacts.
PurchaseTrackingCategory2_Name	String	False	The default purchase tracking category name for contacts.
PurchaseTrackingOption2_Name	String	False	The default purchase tracking option name for contacts.
HasAttachments	Boolean	True	This field indicates whether the invoice has an attachment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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

Table Specific Information

The CreditNotes table allows you to SELECT, INSERT, UPDATE, and DELETE credit note line items for a Xero organization. The Id column is generated by the Sync App; the value of this field combines the index of the line item with the unique, Xero-generated CreditNoteId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new credit note, the Contact_Name field is required. For a credit note to be approved, the following fields must be defined in addition to at least one line item: Contact_Name, Date, and LineAmountTypes.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new employee record, the following fields are required: FirstName and LastName.

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns

Name	Type	ReadOnly	Description
EmployeeId [KEY]	String	True	The unique identifier of the employee. This field is assigned by Xero.
Status	String	False	The status of the employee. Valid values are ACTIVE and DELETED.
FirstName	String	False	The first name of the employee.
LastName	String	False	The last name of the employee.
ExternalLink_URL	String	False	A URL to an external resource.
ExternalLink_Description	String	False	A description for the external link.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the employee record.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new invoice, the Type and ContactName fields must be set in addition to at least one line item. Note that also at least the LineItem_LineAmount or LineItem_UnitAmount are also required fields for the LineItem aggregate.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

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><Description>LineItem1Desc</Description><Quantity>1</Quantity><UnitAmount>30</UnitAmount></LineItem> <LineItem><Description>LineItem2Desc</Description><Quantity>2</Quantity><UnitAmount>50</UnitAmount></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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new item record, the Code field must be defined.

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns

Name	Type	ReadOnly	Description
ItemId [KEY]	String	True	The unique, Xero-assigned identifier for the item.
Code	String	False	A user-defined code that identifies the item.
InventoryAssetAccountCode	String	False	The inventory asset account for the item. The account must be of type INVENTORY. The COGSAccountCode in PurchaseDetails is also required to create a tracked item.
Name	String	False	The name of the item.
PurchaseDescription	String	False	The purchase description of the item.
Description	String	False	A description of the item.
PurchaseDetails_UnitPrice	String	False	The unit Price of the item.
PurchaseDetails_COGSAccountCode	String	False	Cost of goods sold account. Only applicable to the purchase details of tracked items.
PurchaseDetails_AccountCode	String	False	The code of the account to be used for the purchased item.
PurchaseDetails_TaxType	String	False	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
SalesDetails_UnitPrice	String	False	The unit price of the sold item.
SalesDetails_COGSAccountCode	String	False	Cost of goods sold account. Only applicable to the purchase details of tracked items.
SalesDetails_AccountCode	String	False	The code of the account to be used for the sold item.
SalesDetails_TaxType	String	False	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, TAX003, etc.
IsTrackedAsInventory	Boolean	False	True for items that are tracked as inventory. An item will be tracked as inventory if the InventoryAssetAccountCode and COGSAccountCode are set.
TotalCostPool	Decimal	True	The value of the item on hand. Calculated using average cost accounting.
QuantityOnHand	Double	True	The quantity of the item on hand.
IsSold	Boolean	False	Boolean value, defaults to true. When IsSold is true the item will be available on sales transactions in the Xero UI. If IsSold is updated to false then Description and SalesDetails values will be nulled.
IsPurchased	Boolean	False	Boolean value, defaults to true. When IsPurchased is true the item is available for purchase transactions in the Xero UI. If IsPurchased is updated to false then PurchaseDescription and PurchaseDetails values will be nulled.
UpdatedDateUTC	Datetime	True	Timestamp of the last change to the item record.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new manual journal record, the Narration field is required in addition to at least two line items. A journal record must contain at least one credit and one debit, so that the record as a whole is balanced.

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><LineAmount>55.00</LineAmount><AccountCode>200</AccountCode></JournalLine> <JournalLine><LineAmount>-55.00</LineAmount><AccountCode>201</AccountCode></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.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new payment record, the InvoiceNumber and AccountCode fields are required.

Columns

Name	Type	ReadOnly	Description
PaymentId [KEY]	String	True	The Id of the payment.
Date	Date	False	The date the payment is being made.
Amount	Decimal	False	The amount of the payment. This value must be less than or equal to the outstanding amount owing on the invoice.
CurrencyRate	Decimal	False	The exchange rate when the payment is received. This field is only used for non-base-currency invoices and credit notes.
Reference	String	False	A optional description for the payment.
PaymentType	String	False	The payment type.
Status	String	False	The status of the payment.
UpdatedDateUTC	Datetime	False	The date when the payment was last updated.
IsReconciled	Boolean	False	This field indicates whether the payment has been reconciled. Only available when querying for one payment at a time.
BatchPaymentId	String	True	If the payment was submitted through BatchPayments, this is the ID of the batch it was provided with.
InvoiceId	String	False	The Id of the invoice. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
InvoiceNumber	String	False	The invoice number of the invoice or credit note. When applying a payment, specify either the InvoiceId or the InvoiceNumber.
CreditNoteId	String	False	The Id of the credit note. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
CreditNoteNumber	String	False	The credit note number. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.
AccountId	String	False	The Id of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.
AccountCode	String	False	The account code of the account used to make the payment.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

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

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The line item index combined with the Id of the purchase order.
PurchaseOrderId	String	False	The Id of the purchase order.
PurchaseOrderNumber	String	False	A unique identifier for the purchase order that identifies it to the user.
Reference	String	False	An optional field to store a reference.
Type	String	False	The purchase order type. The valid values are ACCPAYCREDIT and ACCRECCREDIT.
Contact_ContactId	String	False	The Id of the contact.
Contact_Name	String	False	The name of the contact.
DeliveryDate	Date	False	The date the purchase order is to be delivered.
ExpectedArrivalDate	Date	False	The date the purchase is expected to arrive.
DeliveryAddress	String	False	The address the goods are to be delivered to.
AttentionTo	String	False	The person the delivery is going to.
Telephone	String	False	The phone number of the person accepting the delivery.
DeliveryInstructions	String	False	Free-form field for information needed to complete the delivery.
Date	Date	False	The date the purchase order was created.
Status	String	False	The status of the purchase order.
LineAmountTypes	String	False	The line amount type, which determines whether line amounts include tax (default). The valid values are Exclusive, Inclusive, and NoTax. Purchase orders cannot be approved without this field being defined.
LineItem_LineItemId	String	False	The Xero generated identifier for a LineItem. If LineItemIDs are not included with line items in an update request then the line items are deleted and recreated.
LineItem_Description	String	False	The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_Quantity	Double	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_UnitAmount	Decimal	False	The unit amount of the subject of the line item. Invoices cannot be approved without these fields.
LineItem_ItemCode	String	False	The user-defined item code.
LineItem_AccountCode	String	False	The account code. The account code must be active for the organisation. Invoices cannot be approved without these fields.
LineItem_TaxType	String	False	The description for the line item of the purchase order. This value must be at least one character. Invoices cannot be approved without these fields.
LineItem_TaxAmount	Decimal	False	The quantity of the subject of the line item. This value must be greater than or equal to 0. Invoices cannot be approved without these fields.
LineItem_LineAmount	Decimal	False	The total of the Quantity field multiplied by the UnitAmount field with discounts applied.
LineItem_DiscountRate	Double	False	The discount percentage being applied to the line item. Discounts are only supported on ACCREC-type purchase orders.
LineItemAggregate	String	False	Used to define LineItem rows using XML values. Should be provided on INSERT only.
SubTotal	Decimal	False	The subtotal of the purchase order, excluding taxes.
TotalTax	Decimal	False	The total tax on the purchase order.
Total	Decimal	False	The total of the purchase order (the subtotal plus the total tax).
LineItem_TrackingCategory1_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	False	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	False	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	False	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	False	The status of a tracking category. The API allows two optional tracking categories to be defined.
UpdatedDateUTC	Datetime	True	The date when the purchase order was last updated.
HasAttachments	Boolean	True	Indicates if the PurchaseOrder has a file attached.
CurrencyCode	String	False	The currency used for the purchase order.
BrandingThemeId	String	False	The Id of the branding theme.
CurrencyRate	Decimal	False	Exchange rate to base currency when money is spent or received. For example, 0.7500. This field is used for only bank transactions not in the base currency. If this is not specified for non-base-currency accounts then either the user-defined rate or the XE.com daily rate will be used.
TenantId	String	False	The ID of the tenant to query instead of the connection tenant

Query, insert and update receipts for a Xero organisation.

Table Specific Information

The Receipts table allows you to SELECT, INSERT, UPDATE, and DELETE receipt line items for a Xero organization. The Id column is generated by the Sync App; it combines the index of the line item with the unique, Xero-generated ReceiptId.

Note that this table only includes draft receipts - to retrieve receipts that have already been claimed, you will need to read from the ExpenseClaimReceipts view.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a receipt record, the following fields are required in addition to at least one line item: Date, Contact_Name, and User_UserId. To add a new line item, the following fields are required: LineItem_Description, LineItem_UnitAmount, and LineItem_AccountCode.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

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

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To create a new tax rate, the Name field must be set in addition to at least one tax component; the TaxComponent_Name, TaxComponent_Rate, and TaxComponent_IsCompound fields are required to insert a new line item.

In addition to inserting a single row, this table offers two additional ways to insert into a table.

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

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

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.
Quotes	Query quotes for a Xero organization
RepeatingInvoices	Query Repeating Invoice templates.
Tenants	Query the list of organizations connected to your Xero account.
TrackingCategories	Query Tracking Categories for a Xero organisation.
TrialBalance	Query a monthly Trial Balance.
Users	Query users for a Xero organisation.

Query Aged Payables for a Contact.

Table-Specific Information

Select

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

Columns

Name	Type	Description
ID [KEY]	String	The row number, combined with the contact ID.
ContactId	String	The ID of the contact this is reporting on.
Date	Datetime	The Date the bill is created.
Reference	String	Purchase order reference for this bill.
DueDate	Datetime	Date the amount is due.
Overdue	Decimal	Amount due passed due date.
Currency	String	The currency used for all fields but DueLocal, or empty if the default currency is used
Total	Decimal	Total amount due.
Paid	Decimal	Amount paid.
Credited	Decimal	Amount credited.
Due	Decimal	Amount due.
DueLocal	Decimal	Amount due in the default organisation currency.
InvoiceId	String	The invoice for this bill.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

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 Payables report for all contacts will be returned, one after the other.

Columns

Name	Type	Description
ID [KEY]	String	The row number, combined with the contact ID.
ContactId	String	The ID of the contact this is reporting on.
Date	Datetime	The Date the invoice is created.
Reference	String	Sale order reference for this bill.
DueDate	Datetime	Date the amount is due.
Overdue	Decimal	Amount due passed due date.
Currency	String	The currency used for all fields but DueLocal. Is empty if multi-currency is not in use.
Total	Decimal	Total amount due.
Paid	Decimal	Amount paid.
Credited	Decimal	Amount credited.
Due	Decimal	Amount due.
DueLocal	Decimal	Amount due in the default organisation currency.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

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.

Columns

Name	Type	Description
BankAccountId	String	The ID of the bank account this is reporting on
Date	Date	The date when the transaction occurred
Description	String	The transaction's description
Reference	String	The transaction's reference value
Reconciled	String	Whether the transaction has been reconciled ('Yes' or 'No')
Source	String	Where the transaction came from
Amount	Float	The transaction's value
Balance	Float	The statement's running total
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

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

Query ExpenseClaim history and notes for a Xero organization.

View Specific Information

The HistoryExpenseClaims view allows you to read the notes created on a expense claim, and the history of changes to that expense claim. If a ExpenseClaimId is not provided, the history of all ExpenseClaims will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ExpenseClaimId	String	The ID of the expense claim the history item belongs to.
Changes	String	What type of change happened on the expense claim
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query Item history and notes for a Xero organization.

View Specific Information

The HistoryItems view allows you to read the notes created on a item, and the history of changes to that item. If a ItemId is not provided, the history of all Items will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ItemId	String	The ID of the item the history item belongs to.
Changes	String	What type of change happened on the item
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query Overpayment history and notes for a Xero organization.

View Specific Information

The HistoryOverpayments view allows you to read the notes created on a overpayment, and the history of changes to that overpayment. If a OverpaymentId is not provided, the history of all Overpayments will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
OverpaymentId	String	The ID of the overpayment the history item belongs to.
Changes	String	What type of change happened on the overpayment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query Payment history and notes for a Xero organization.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
PaymentId	String	The ID of the payment the history item belongs to.
Changes	String	What type of change happened on the payment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query Prepayment history and notes for a Xero organization.

View Specific Information

The HistoryPrepayments view allows you to read the notes created on a prepayment, and the history of changes to that prepayment. If a PrepaymentId is not provided, the history of all Prepayments will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
PrepaymentId	String	The ID of the prepayment the history item belongs to.
Changes	String	What type of change happened on the prepayment
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query Receipt history and notes for a Xero organization.

View Specific Information

The HistoryReceipts view allows you to read the notes created on a receipt, and the history of changes to that receipt. If a ReceiptId is not provided, the history of all Receipts will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
ReceiptId	String	The ID of the receipt the history item belongs to.
Changes	String	What type of change happened on the receipt
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query RepeatingInvoice history and notes for a Xero organization.

View Specific Information

The HistoryRepeatingInvoices view allows you to read the notes created on a repeating invoice, and the history of changes to that repeating invoice. If a RepeatingInvoiceId is not provided, the history of all RepeatingInvoices will be retrieved.

Columns

Name	Type	Description
Id [KEY]	String	A combination of the position of the history item and when it occurred.
RepeatingInvoiceId	String	The ID of the repeating invoice the history item belongs to.
Changes	String	What type of change happened on the repeating invoice
Date	Datetime	When the change occurred
User	String	The name of the user that made the change
Details	String	The change that was made
TenantId	String	The ID of the tenant to query instead of the connection tenant

Query the line items in journals for a Xero organisation.

Table Specific Information

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

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

Columns

Name	Type	Description
Id [KEY]	String	The journal line index combined with the unique Xero identifier of the journal.
JournalId	String	The unique Xero identifier of the journal.
JournalDate	Date	The date the journal was posted.
JournalNumber	Integer	The journal number, assigned by Xero.
CreatedDateUTC	Datetime	The date the journal was entered in the system.
Reference	String	A reference value.
SourceId	String	The identifier for the source transaction (e.g. InvoiceId).
SourceType	String	The journal source type. The type of transaction that created the journal. (e.g ACCREC, ACCPAY, ACCRECCREDIT)
JournalLine_JournalLineId	String	The unique Xero identifier of the journal line item.
JournalLine_Description	String	The description of the journal line item.
JournalLine_AccountId	String	The account associated with the line item in the journal.
JournalLine_AccountCode	String	Customer-defined alphanumeric account code; e.g, 200 or SALES.
JournalLine_AccountType	String	The type of the account; e.g., BANK, CURRENT, or CRRLIAB.
JournalLine_AccountName	String	The name of the account.
JournalLine_NetAmount	Decimal	The net amount of the line item. This value is positive for debits and negative for credits.
JournalLine_GrossAmount	Decimal	The net amount plus the tax amount.
JournalLine_TaxAmount	Decimal	The total tax on a journal line.
JournalLine_TaxType	String	The type of the tax rate, which varies by country. User-defined tax rates have a TaxType of TAX001, TAX002, etc.
JournalLine_TaxName	String	A name that identifies the tax type.
LineItem_TrackingCategory1_CategoryId	String	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionId	String	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory1_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryId	String	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_CategoryName	String	The name of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionId	String	The Id of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_OptionName	String	The option of a tracking category. The API allows two optional tracking categories to be defined.
LineItem_TrackingCategory2_Option_Stat	String	The status of a tracking category. The API allows two optional tracking categories to be defined.
TenantId	String	The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

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

Name	Type	Description
ModifiedAfter	String	If set, only journals created or modified since this timestamp will be returned e.g. 2009-11-12T00:00:00.
PaymentsOnly	Boolean	Set this to true to get cash transactions only.
Offset	Int	If provided, only journals with a higher JournalNumber will be returned.