Xero Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8852 of the connector.

Get Started¶

Xero Version Support

The Xero connector models Xero Accounting, Australian Payroll, Files, Assets and Projects objects as a relational database.

Establish a Connection¶

Connect to Xero¶

The connector supports using PKCE applications and OAuth applications. Both PKCE and OAuth connections to Xero are authenticated using OAuth. When the connector starts, it opens a browser and requests your login information. The connector uses the credentials you provide to access your Xero data. These credentials are saved and automatically refreshed as needed.

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

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

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

For information about how to create a custom OAuth application, see Creating a Custom Authentication Application.

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

Desktop Applications¶

To authenticate with the credentials for a custom OAuth application, you must get and refresh the OAuth access token. After you do that, you are ready to connect.

Get and refresh the OAuth access token:

• InitiateOAuth = GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• OAuthClientId = the client ID assigned when you registered your application.
• OAuthClientSecret = the client secret that was assigned when you registered your application.
• CallbackURL = the redirect URI that was defined when you registered your application.

When you connect, the connector opens Xero's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector then completes the OAuth process:

1. The connector obtains an access token from Xero and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

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

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

Manual refresh of the OAuth access token:

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

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the GetOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId = the Client ID in your application settings.
   • OAuthClientSecret = the Client Secret in your application settings.
3. Call GetOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by GetOAuthAccessToken. This opens a new connection.

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

Connect to a Xero API¶

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

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

Specifying Permitted Organization Access

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

Create a Custom Authentication Application¶

Create a Custom Authentication Application¶

This section describes how to create custom authentication applications for use with PKCE, OAuth, and custom connections. Developers writing client side applications can use PKCE to test applications locally, because PKCE does not require a client secret.

OAuth¶

To create and register an OAuth application for desktop, web, or headless servers, and obtain the OAuthClientId and OAuthClientSecret:

1. Log in to the Xero developer portal.
2. Navigate to My Apps > Add Application.
3. Choose the application type Auth Code.
4. Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
5. Add a Redirect URI. The same URI should be set in CallbackURL.
6. Click Save. Xero displays the OAuth Client ID of the new application.
7. Create a new OAuth Client Secret.
8. Set OAuthClientId and OAuthClientSecret to the credentials you just obtained.

Custom Connections¶

Xero also supports server-to-server OAuth integrations using Custom Connections. Licenses for these connections must be purchased from Xero before they can be used in production organizations. They can also be linked to demo organizations for free.

To register a custom connection application in Xero:

1. Log in to the Xero developer portal.

2. Navigate to My Apps > Add Application.

3. Specify an application type of Custom Connection.

4. Select a list of scopes to which this application should have access. To avoid permission conflicts when connecting the connector, we recommend

   that you select all the available scopes. You can also select scopes individually, but if you do this you must update the Scope connection property to match.

5. If desired, choose a user to authorize the connection. If you do this, the application can only be linked to an organization that this user can access.

6. Click Save and Connect. Xero sends an email to the user you selected, which contains instructions on how to link the application to an organization.

7. Wait for the user to link the application.

8. Return to the developer portal and open the application settings.

9. Click the Configuration tab and scroll to the Client Id. The CLient ID is used to set the OAuthClientId application property.

10. Click Generate a Secret.

11. Copy the secret you just generated, and apply it to the OAuthClientSecret property.

Xero API Limits¶

Xero API Limits¶

The Xero API has usage limitations that may be encountered while using the Xero connector. 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 connector 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 connector will use to avoid hitting the limit again.

Uncertified Connectors Limit¶

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

Per-Day Limit¶

At most 5000 API calls per day may be made from the application to the same organization. Hitting this limit is rare but can happen with certain tables (such as history tables and some reports) or certain settings (such as AutoExpandDetails) are used, as they require the connector 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 connector 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.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Xero connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Xero and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views¶

The Xero connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Contacts WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The Xero connector models five Xero APIs as relational databases: the Accounting, Australian Payroll, Files, Fixed Assets and Projects APIs. Set the Schema property and use the information in each section to select the proper API based on your project needs.

Using the Accounting API

The Accounting API contains the company's financial information, such as contacts, invoices and reporting. It also contains history tables which records changes to most kinds of entities.

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

See Accounting Data Model for the available entities in the Accounting API.

Using the Australian Payroll API

The Australian Payroll API contains information on the company's employees, such as timesheets, leave and tax declarations.

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

See Australian Payroll Data Model 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 Files Data Model 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 Fixed Assets Data Model 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 Projects Data Model for the available entities in the Projects API.

Accounting Data Model¶

The Xero connector 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¶

Tables describes the available tables.

Views¶

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

Stored Procedures¶

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables¶

The connector models the data in Xero as a list of tables in a relational database that can be queried using standard SQL statements.

Xero Connector Tables¶

Accounts¶

Query, insert, update and delete accounts for a Xero organisation.

Table Specific Information¶

The Accounts table allows you to SELECT and INSERT accounts for a Xero organization.

SELECT

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

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

To insert a single row, set the required fields. A new table object is created as a single entry. The following fields are required for inserts: Code, Name, and Type.

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

Columns¶

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.

SELECT

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

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

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

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

1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the BankTransactions API for more details.

   INSERT INTO BankTransactions (Type, Contact_ContactName, BankAccount_Code, LineItemAggregate) VALUES (
     'RECEIVE',
     'John Doe',
     '200',
     '<LineItem>
           <Description>Monthly account fee</Description>
           <UnitAmount>49.90</UnitAmount>
           <TaxType>NONE</TaxType>
           <TaxAmount>0.00</TaxAmount>
           <LineAmount>49.90</LineAmount>
           <AccountCode>404</AccountCode>
           <Quantity>1.0000</Quantity>
      </LineItem>'
   )
   

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

   INSERT INTO BankTransactions (BankTransactionId, LineItem_Description, LineItem_Quantity) 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¶

BankTransfers¶

Usage information for the operation BankTransfers.rsd.

Table Specific Information¶

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

SELECT

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

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

To insert a single row, set the required fields. A new table object is created as a single entry. The following fields are required for inserts: FromBankAccount_Code, ToBankAccount_Code, and Amount

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

Columns¶

BatchPayments¶

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

To insert a single row, set the required fields. A new table object is created as a single entry. To create a new batch payment, the AccountId, Date and Reference are required in addition to at least one payment item; the Payment_InvoiceId and Payment_Amount fields are required to insert a payment item.

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

INSERT INTO BatchPayments (AccountId, Reference, Date, PaymentAggregate) VALUES (
  'AAAAA-AAAAA-AAAAA-AAAAA',
  '2019-03-01',
  '<Payment>
        <Invoice>
            <InvoiceID>5aa9451d-95d1-4f95-a966-bbab2573f71c</InvoiceID>
            <HasErrors>false</HasErrors>
            <IsDiscounted>false</IsDiscounted>
        </Invoice>
        <PaymentID>a22a64cb-364e-43fa-9a1f-bb2cd1f4adde</PaymentID>
        <Reference>ref/cheque</Reference>
        <Amount>913.55</Amount>
    </Payment>
    <Payment>
        ...
    </Payment>'
)

Columns¶

ContactGroups¶

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¶

Contacts¶

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

To insert a single row, set the required fields. A new table object is created as a single entry. The Name field is required to add a new contact.

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

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¶

CreditNotes¶

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

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

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

1. You can also insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the CreditNotes API for more details.

   INSERT INTO CreditNotes (Type, Contact_Name, LineItemAggregate) VALUES (
     'ACCPAYCREDIT',
     'John Doe',
     '<LineItem>
       <Description>LineItemDesc1</Description>
       <Quantity>1</Quantity>
     </LineItem>
     <LineItem>
       <Description>LineItemDesc2</Description>
       <Quantity>2</Quantity>
     </LineItem>'
   )
   

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

   INSERT INTO CreditNotes (CreditNoteId, LineItem_Description, LineItem_Quantity) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1)
   

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

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

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

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

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

Columns¶

Employees¶

Query, insert and update employees for a Xero organisation.

Table Specific Information¶

The Employees table allows you to SELECT, INSERT, and UPDATE employees for a Xero organization.

SELECT

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

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

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

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¶

ExpenseClaims¶

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¶

HistoryBankTransactions¶

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¶

HistoryBatchPayments¶

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¶

HistoryContacts¶

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¶

HistoryCreditNotes¶

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¶

HistoryInvoices¶

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¶

HistoryPurchaseOrders¶

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¶

Invoices¶

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

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

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

1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Invoices API for more details.

   INSERT INTO Invoices (Type, ContactName, LineItemAggregate) VALUES (
     'ACCPAY',
     'Boom FM',
     '<LineItem>
       <ItemCode>12</ItemCode>
       <Description>Onsite project management </Description>
       <Quantity>1.0000</Quantity>
       <UnitAmount>1800.00</UnitAmount>
       <TaxType>OUTPUT</TaxType>
       <TaxAmount>225.00</TaxAmount>
       <LineAmount>1800.00</LineAmount>
       <AccountCode>200</AccountCode>
       <AccountId>4f2a3169-8454-4012-a642-05a88ef32982</AccountId>
       <Tracking>
           <TrackingCategory>
               <Name>Activity/Workstream</Name>
               <Option>Onsite consultancy</Option>
           </TrackingCategory>
       </Tracking>
       <LineItemID>52208ff9-528a-4985-a9ad-b2b1d4210e38</LineItemID>
      </LineItem>
      <LineItem>
       ...
      </LineItem>'
   )
   

2. You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, adding a line item to an existing invoice:

   INSERT INTO Invoices (InvoiceId, LineItem_Description, LineItem_Quantity, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'New Line Item Desc', 1, 10)
   

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

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

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

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

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

Discount Amounts, Discount Rates and Line Amounts

Invoices in Xero may be discounted either via a fixed amount using LineItem_DiscountAmount or with a percentage using LineItem_DiscountRate. These two methods are not compatible because discount amounts are more precise than discount rates. For example, a line item with the amount 2000.00 and a discount amount of 99.99 cannot be represented using the discount rate 4.999 because Xero allows only two digits of precision on discount rates. In addition, setting any form of discount means that LineItem_LineAmount becomes computed by Xero. This means that, out the three fields LIneItem_DiscountAmount, LineItem_DiscountAmount and LineItem_DiscountRate, only one can be used as part of an INSERT or UDPATE query. For example, neither of the below queries are valid:

INSERT INTO Invoices (Invoice, LineItem_LineAmount, LineItem_DiscountRate) VALUES ('2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e', 4999.99, 6.75)
UPDATE Invoices SET LineItem_DiscountRate = 12.50, LineItem_DiscountAmount = 99.99 WHERE ID = '1|2bf5f07b-edf0-4b05-9200-cc2cd6b89b1e'

It also means that the connector 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 connector would remove any discount values attached to that Invoice.

Columns¶

Items¶

Query, insert, update and delete items for a Xero organisation.

Table Specific Information¶

The Items table allows you to SELECT, INSERT, and UPDATE items for a Xero organization.

SELECT

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

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

To insert a single row, set the required fields. A new table object is created as a single entry. To insert a new item record, the Code field must be defined.

UPDATE

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

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

Columns¶

LinkedTransactions¶

Query, insert, update and delete LinkedTransactions for a Xero organisation.

Columns¶

ManualJournals¶

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

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

1. You can insert a new object with multiple line items using an XML aggregate. The elements supported here are the same as the JournalLine columns without the "JournalLine_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the ManualJournals API for more details.

   INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES (
     'Accrued expenses',
     '<JournalLine>
           <Description>Coded incorrectly Office Equipment should be Computer Equipment</Description>
           <TaxType>NONE</TaxType>
           <LineAmount>-2569</LineAmount>
           <AccountCode>720</AccountCode>
       </JournalLine>
       <JournalLine>
           ...
       </JournalLine>'
   )
   

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

   INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')
   

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

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

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

Columns¶

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.

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¶

PurchaseOrders¶

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

Columns¶

Quotes¶

Query quotes for a Xero organization

Table Specific Information¶

The Quotes table allows you to SELECT, INSERT, and UPDATE Quote lines for a Xero organization. The ID column is generated by the connector; it combines the index of the line item with the unique, Xero-generated QuoteId.

SELECT

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

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

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

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

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

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

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

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

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

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

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

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

Columns¶

Receipts¶

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 connector; it combines the index of the line item with the unique, Xero-generated ReceiptId.

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

SELECT

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

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

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

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

1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Receipts API for more details.

   INSERT INTO Receipts (Date, Contact_Name, User_UserId, LineItemAggregate) VALUES (
     '4/19/2006 9:16:48 AM',
     'John Doe',
     'c81045b2-5740-4aea-bf8a-3956941af387',
     '<LineItem>
       <Description>Coffee with client</Description>
       <UnitAmount>13.00</UnitAmount>
       <AccountCode>200</AccountCode>
      </LineItem>
      <LineItem>
       <Description>Bagel</Description>
       <UnitAmount>2.00</UnitAmount>
       <AccountCode>200</AccountCode>
      </LineItem>'
   )
   

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

   INSERT INTO Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)
   

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

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

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

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

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

Columns¶

TaxRates¶

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 connector this value combines the index of the line item with the unique, Xero-generated TaxType.

SELECT

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

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

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

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

1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the TaxComponent columns without the "TaxComponent_" prefix.

   INSERT INTO TaxRates (Name, TaxComponentAggregate) VALUES (
     'My Tax Rate',
     '<TaxComponent>
       <Name>customTax1</Name>
       <Rate>1.0000</Rate>
       <IsCompound>false</IsCompound>
      </TaxComponent>
      <TaxComponent>
       <Name>customTax2</Name>
       <Rate>2.0000</Rate>
       <IsCompound>false</IsCompound>
      </TaxComponent>'
   )
   

2. You can also insert a new tax component on an existing object by specifiying the Xero-generated value for the existing record. For example, adding a tax component to an existing tax rate:

   INSERT INTO TaxRates (TaxType, TaxComponent_Name, TaxComponent_Rate, TaxComponent_IsCompound) VALUES ('TAX001', 'customTax2', '2.0000', 'False')
   

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

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

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

Note: You cannot update predefined tax rates through the Xero API. DELETE

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

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

Columns¶

Views¶

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Xero Connector Views¶

AgedPayablesByContact¶

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¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AgedReceivablesByContact¶

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¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BalanceSheet¶

Query a monthly Balance Sheet.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BankStatement¶

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¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BankSummary¶

Query the balances and cash movements for each bank account.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BrandingThemes¶

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¶

BudgetLines¶

Query Budget line items for a Xero organisation

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Budgets¶

Query Budgets for a Xero organisation

Columns¶

BudgetSummary¶

Query a monthly Budget Summary.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ContactCISSettings¶

Query CIS settings for contacts in a Xero organisation.

Columns¶

CreditNoteAllocations¶

Query credit note allocations for a Xero organisation.

Columns¶

Currencies¶

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¶

ExecutiveSummary¶

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¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ExpenseClaimPayments¶

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 connector; 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¶

ExpenseClaimReceipts¶

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 connector; 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¶

HistoryBankTransfers¶

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¶

HistoryExpenseClaims¶

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¶

HistoryItems¶

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¶

HistoryOverpayments¶

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¶

HistoryPayments¶

Query Payment history and notes for a Xero organization.

Columns¶

HistoryPrepayments¶

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¶

HistoryReceipts¶

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¶

HistoryRepeatingInvoices¶

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¶

Journals¶

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 connector; 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¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Organisation¶

Query organisation data for a Xero organisation.

Table Specific Information¶

The organization view allows you to SELECT organization data 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¶