BankTransactions
Query, insert and update bank transactions for a Xero organisation.
Table Specific Information
The BankTransactions table allows you to SELECT, INSERT, UPDATE, and DELETE bank transactions for a Xero organization.
Optimized Filters and High Volume Thresholds
The Xero API defines a list of optimized filters for certain tables and views. If any query filter not included in this list is applied to any such table or view, and the number of records available in that table or view exceeds a certain value (what Xero calls a "high volume threshold"), the Xero API will block the query.
To avoid the rejection of your queries by the Xero API, if the the number of records in your table or view exceed the high volume threshold, make sure to use filters from the list of optimized filters.
Note that the high volume threshold, as well as the exact list of optimized filters, is subject to change as Xero updates its API.
The Xero API does not list a high volume threshold for the BankTransactions table. The Xero API lists the following optimized filters for this table:
- Optimized columns (when used in WHERE clauses):
- Type
- Status
- Date
- Contact.ContactId
- Optimized ORDER BY columns:
- BankTransactionID
- UpdatedDateUTC
- Date
SELECT
The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.
Note: The Xero API does not support filtering on line items or journal lines.
INSERT
To insert a single row, set the required fields. A new table object is created as a single entry. To create a new bank transaction record, the following fields are required in addition to at least one line item: Type, Contact_ContactName or Contact_ContactId, and BankAccount_AccountId or BankAccount_Code. To create a new line item, the following fields are required: LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, and LineItem_AccountCode.
In addition to inserting a single row, line item tables offer two additional ways to insert into a table.
- You can insert a new object with multiple line items using XML aggregates.
The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the
Xero documentation of the BankTransactions API for more details.
INSERT INTO BankTransactions (Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES ( 'RECEIVE', 'John Doe', '200', '<LineItem> <Description>Monthly account fee</Description> <UnitAmount>49.90</UnitAmount> <TaxType>NONE</TaxType> <TaxAmount>0.00</TaxAmount> <LineAmount>49.90</LineAmount> <AccountCode>404</AccountCode> <Quantity>1.0000</Quantity> </LineItem>' )
- 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 objects, you must update their Status to an appropriate value.
Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.
In order to delete a record from the BankTransactions table, the BankTransactionId column should be set:
DELETE FROM BankTransactions WHERE BankTransactionId='2355f1d6-91f3-4a4b-b7e2-d13b1f58c531'
Note: This is equivalent to updating the status column to 'DELETED'. Additionally, delete functionality is not available for BankTransactions of type RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, SPEND-OVERPAYMENT, or SPEND-PREPAYMENT.
Columns
Name | Type | ReadOnly | Description |
Id [KEY] | String | True |
The line item index combined with the Id of the bank transaction. |
BankTransactionId | String | False |
The Id of the bank transaction. |
Type | String | False |
The transaction type. Valid values are RECEIVE, SPEND, RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, RECEIVE-TRANSFER, SPEND-OVERPAYMENT, SPEND-PREPAYMENT, and SPEND-TRANSFER. |
LineAmountTypes | String | False |
How line item values are provided relative to tax. Can be either Exclusive, Inclusive or NoTax |
Contact_ContactName | String | False |
The name of the contact associated with the transaction. |
Contact_ContactId | String | False |
The Id of the contact associated with the transaction. |
BankAccount_AccountId | String | False |
The Id for the associated bank account. |
BankAccount_AccountName | String | False |
The Name for the associated bank account. |
BankAccount_Code | String | False |
The code for the associated bank account. |
LineItem_LineItemId | String | 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 |