Excel Add-In for Xero

Build 24.0.9062

Payments

Query, insert and delete payments for a Xero organisation.

Table Specific Information

The Payments table allows you to SELECT and INSERT payments for a Xero organization.

Note that this tables does not include payments on expense claims - to retrieve those, you will need to read from the ExpenseClaimPayments view.

Optimized Filters and High Volume Thresholds

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

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

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

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

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

SELECT

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

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

INSERT

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new payment record, the InvoiceNumber and AccountCode fields are required.

Columns

Name Type ReadOnly Description
PaymentId [KEY] String True

The Id of the payment.

Date Date False

The date the payment is being made.

Amount Decimal False

The amount of the payment. This value must be less than or equal to the outstanding amount owing on the invoice.

CurrencyRate Decimal False

The exchange rate when the payment is received. This field is only used for non-base-currency invoices and credit notes.

Reference String False

A optional description for the payment.

PaymentType String False

The payment type.

Status String False

The status of the payment.

UpdatedDateUTC Datetime False

The date when the payment was last updated.

IsReconciled Boolean False

This field indicates whether the payment has been reconciled. Only available when querying for one payment at a time.

BatchPaymentId String True

If the payment was submitted through BatchPayments, this is the ID of the batch it was provided with.

InvoiceId String False

The Id of the invoice. When applying a payment, specify either the InvoiceId or the InvoiceNumber.

InvoiceNumber String False

The invoice number of the invoice or credit note. When applying a payment, specify either the InvoiceId or the InvoiceNumber.

CreditNoteId String False

The Id of the credit note. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.

CreditNoteNumber String False

The credit note number. When applying a payment, specify either the CreditNoteId or the CreditNoteNumber.

AccountId String False

The Id of the account used to make the payment. The account type must be BANK or payments to the account must be enabled.

AccountCode String False

The account code of the account used to make the payment.

TenantId String False

The ID of the tenant to query instead of the connection tenant

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062