BankTransactions

Queries, inserts, and updates bank transactions within Xero. Bank transactions include deposits, withdrawals, and other activities that affect the cash accounts of the organization.

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

Note: This method must be used for inserting multiple parent BankTransaction records in a single query.

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

INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount, LineItem_AccountCode, LineItem_LineAmount, Total, SubTotal) 
VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 1.00, 150, 1.00, 3.00, 3.00)

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

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

UPDATE

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

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

DELETE

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

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

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

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier for the row, combining the line item index with the ID of the bank transaction. This identifier is used to reference a specific bank transaction entry.
BankTransactionId	String	False	The unique identifier for the bank transaction. This ID links the transaction to the corresponding record in the system.
Type	String	False	The type of the transaction. Valid values include RECEIVE (money received), SPEND (money spent), RECEIVE-OVERPAYMENT, RECEIVE-PREPAYMENT, RECEIVE-TRANSFER, SPEND-OVERPAYMENT, SPEND-PREPAYMENT, and SPEND-TRANSFER, each representing different transaction types.
LineAmountTypes	String	False	Indicates how the line item values are calculated relative to tax. Possible values are 'Exclusive' (tax excluded from the amount), 'Inclusive' (tax included in the amount), or 'NoTax' (no tax applied).
Contact_ContactName	String	False	The name of the contact associated with the transaction. This identifies the party (for example, customer or vendor) involved in the transaction.
Contact_ContactId	String	False	The unique ID of the contact associated with the transaction. This links the transaction to a specific contact in the system.
BankAccount_AccountId	String	False	The unique ID of the bank account associated with the transaction. This ID links the transaction to the relevant account in the system.
BankAccount_AccountName	String	False	The name of the bank account associated with the transaction. This is the user-friendly label for the bank account.
BankAccount_Code	String	False	The code for the bank account, used for identifying the account in financial transactions or reports.
LineItem_LineItemId	String	True	The Xero-generated ID for the line item within the bank transaction. Each line item within a transaction has a unique ID.
LineItem_Description	String	False	A description for the line item in the bank transaction. This typically includes details such as the nature of the transaction, goods or services involved, or reference information.
LineItem_Quantity	Double	False	The quantity of items involved in the line item. This value must be greater than or equal to zero and represents the amount or number of items in the transaction.
LineItem_UnitAmount	Decimal	False	The unit amount for the line item. This is the price or cost per unit of the item, and must be a positive value.
LineItem_AccountCode	String	False	The code for the account referenced by the line item. Including this field is recommended when posting to ensure the correct account is used in the transaction.
LineItem_Item_ItemId	String	False	The unique ID of the item the line item refers to. This links the line item to a specific product or service in the system.
LineItem_Item_Name	String	False	The name of the item the line item refers to. This is the human-readable label for the product or service associated with the transaction.
LineItem_Item_Code	String	False	The code associated with the item referenced in the line item. This code can be used for identifying and categorizing items in the system.
LineItem_TaxType	String	False	The tax type for the transaction line item. This overrides the default tax code for the account, allowing customization of tax rules for specific transactions.
LineItem_TaxAmount	Decimal	False	The automatically calculated amount of tax applied to the line item, based on the tax rate and the line amount.
LineItem_LineAmount	Decimal	False	The total amount of the line item, including any applicable taxes. If either Quantity or UnitAmount is omitted, Xero calculates the missing value from this field.
LineItem_TrackingCategory1_CategoryId	String	False	The ID of the first tracking category associated with the line item. The API supports two optional tracking categories for organizing transactions.
LineItem_TrackingCategory1_CategoryName	String	False	The name of the first tracking category associated with the line item. This allows users to categorize transactions for reporting purposes.
LineItem_TrackingCategory1_OptionId	String	False	The ID of the first tracking category option selected for the line item. This allows for finer classification within the tracking category.
LineItem_TrackingCategory1_OptionName	String	False	The name of the option selected within the first tracking category. This value provides additional classification for the line item.
LineItem_TrackingCategory1_Option_Stat	String	False	The status of the option selected within the first tracking category. This field indicates whether the tracking category option is active or inactive.
LineItem_TrackingCategory2_CategoryId	String	False	The ID of the second tracking category associated with the line item. The API allows two optional tracking categories to be defined for more detailed categorization.
LineItem_TrackingCategory2_CategoryName	String	False	The name of the second tracking category associated with the line item. This provides additional classification options for the transaction.
LineItem_TrackingCategory2_OptionId	String	False	The ID of the second tracking category option selected for the line item. This enables the classification of transactions using a second category.
LineItem_TrackingCategory2_OptionName	String	False	The name of the option selected within the second tracking category. This allows the user to apply additional categorization for reporting.
LineItem_TrackingCategory2_Option_Stat	String	False	The status of the option selected within the second tracking category. This field indicates whether the second tracking category option is active or inactive.
LineItemAggregate	String	False	Used to define line item rows using XML values. This field should only be provided during an INSERT operation to define how the line items should be structured.
IsReconciled	Boolean	False	Indicates whether the transaction has been reconciled with the bank statement. A value of 'true' means the transaction has been matched with a bank statement entry, and 'false' indicates it has not been reconciled.
Date	Date	False	The date when the transaction took place. This is the date the transaction was recorded in the system, marking the event.
Reference	String	False	An internal reference for the transaction within Xero. This could be a unique identifier or code used for tracking or linking transactions.
CurrencyRate	Decimal	False	The exchange rate applied to the transaction when it is not in the base currency. For transactions in a foreign currency, this field records the rate used to convert the amount to the base currency.
URL	String	False	A link to a source document related to the transaction, such as an invoice or receipt, providing further context or supporting documentation.
Status	String	False	The status of the bank transaction. Possible values are 'AUTHORISED' (transaction approved and processed) and 'DELETED' (transaction removed or voided).
SubTotal	Decimal	False	The total of the transaction before tax is applied. This is the sum of all line items, excluding taxes.
TotalTax	Decimal	False	The total tax applied to the transaction. This amount reflects the sum of taxes calculated based on the transaction's tax rules.
Total	Decimal	False	The total amount of the transaction, including taxes. This is the final amount after adding the taxes to the subtotal.
PrepaymentId	String	False	The unique identifier generated by Xero for a prepayment. This is used when a transaction involves a prepayment, such as in 'SPEND-PREPAYMENT' or 'RECEIVE-PREPAYMENT' types.
OverpaymentId	String	False	The unique identifier generated by Xero for an overpayment. This is used when a transaction involves an overpayment, such as in 'SPEND-OVERPAYMENT' or 'RECEIVE-OVERPAYMENT' types.
UpdatedDateUTC	Datetime	True	The date and time when the transaction was last updated, provided in UTC format.
FullyPaidOnDate	Date	False	The date on which the transaction was fully paid, marking the completion of the payment process.
TenantId	String	False	The unique identifier of the tenant, allowing queries to access data specific to a particular tenant instead of using the default connection tenant.

ODBC Driver for Xero