Looking Inside Xero - Part 2

by

General Ledger

Well, it has been 3 weeks since part 1 of this mini-series, but we had two holidays and a lot of football games intervening, and that has kept me from getting back into the swing of this 'techy stuff.'  Now it’s time to go back to work so here is Part-2 of our ‘look inside’ Xero. And NO, this isn't the final part of this mini-series either.

In our last article we examined some fundamentals of the Xero Accounting API which exposes Tables, Views and Stored Procedures associated with Xero (organization) files.  We found out that Xero has tables ranging from Accounts to TaxRates. 

If you are taking notes and doing comparisons between Xero’s internal structure and that of QuickBooks Online, you realize that Xero streamlines their structure by having far fewer tables. This is accomplished by using a single table to contain all information of a single-type as opposed to using several linked tables.

In Part 1 we looked at the Xero Accounts Table, which is a typical example of a ‘list’ type table, but I thought today we would start-off by looking at one of Xero’s ‘transaction’ type tables.

The Invoices Table

The Invoices table allows you to SELECT, INSERT, UPDATE, and DELETE invoice line items for your Xero (organization) file. The Xero application generates a unique InvoiceId.

When using the Xero Accounting API, the SELECT features allows you to define ‘where’ and ‘order by’ clauses within your query in order to filter and order the results. 

To execute an INSERT 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; the LineItem_Description field is required to insert a new line item. 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.

The example below shows how to insert a single object with multiple line items; in this case execute a query to insert a new invoice with two line items:

You can UPDATE any field that is not read-only within a Xero table but update operations count as two operations within the Xero API since once operation is required to retrieve the existing records, and another operation is associated with the actual record update.

The Xero API has limited DELETE support. Applicable values for a specific table field by state-dependent, and you must update their status to an appropriate value. For example, invoices can only have their status set to DELETED when the invoice status is in DRAFT or SUBMITTED, not after the invoice status is AUTHORIZED.

The following graphic describes the Invoices table. We recognize that this table is ‘very small’ and ‘long’.  So, we will cover some of the major fields.

So if you don't have a like 1000-power magnification overlay for your computer screen, then I realize this table is both small and long. Here is some more detailed information on 'some' (NOT ALL) of the fields within the above table:

  • ID [Key] – The line item index combined with the ID of the Invoice.
  • InvoiceID – The Xero assigned ID of the Invoice.
  • Type – The invoice type, either ACCPAY or ACCREC.
  • ContactID – the ID of the contact.
  • ContactName – This required value (when inserting) is the name of the contact.
  • LineItem_LineItemID# - The Xero generated identifier for a line item.
  • LineItem_Description# - The line item description
  • LineItem_Quantity# - The quantity of the associated line item. (Zero or positive values are required).
  • LineItem_UnitAmount# - This required value is the unit amount of the associated line item.
  • LineItem_ItemCode# - The associated line item code.
  • LineItem_AccountCode# - This required value represents the account code.
  • LineItem_TaxType# - The tax type, maybe used to override AccountCode default tax code.
  • LineItem_TaxAmount# - The total value for the line item in which the quantity is multiplied by the unit amount (any discounts must/will be applied).
  • Date – the transaction date.
  • DueDate – the invoice due date.
  • InvoiceNumber – a unique alphanumeric code identifying the invoice.

I think it's always beneficial to see how some of the fields fit into the GUI (graphical user interface) so here is an example of a Xero Invoice.

You can see fields like the ContactName appearing in the To field above, the Date and DueDate in their respective fields, the InvoiceNumber in the Invoice # field, etc.

But tables, either list or transaction, are not the only data types within the Xero file structure, nor the only types of data supported by the Xero Accounting API.  In the remainder of today's article we will look at another type of data within Xero.

Views

Views are similar to tables in the way that data is represented; however, views do not support updates. Data types represented as views within Xero are typically read-only data sources.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar to what you would expect from a table. In some cases a stored procedure is available to update the data if such functionality is applicable to the data source.

The following table illustrates the various 'views' available within Xero via the API.

As an example, let's take a closer look at the information associated with the Users view which contains information about the users associated with a specific User (organization) file.

UserID [KEY]  - returns the string of data that uniquely identifies the Xero user.

FirstName - returns the string of data that identifies the user's first name.

LastName - returns the string of data that identifies the user's last name.

UpdatedDateUTC - a datetime stamp of the last change to the specific user record.

IsSubscriber - boolean indicating if the user is also the subscriber.

OrganizationRole - returns a string of data describing the user's role within the organization. Values include (but are not limited to): READONLY, INVOICEONLY, STANDARD, and FINANCIALADVISOR .

We haven't finished peeking into the inside of Xero, we have more of both the accounting data and payroll data to look at. So, join us for Part 3 in the not too distant future (now that the 'happy holidays' are over).