QuickBooks Online Database and API

by

Two of the courses I'll be teaching this year at "Scaling New Heights" deal with the QuickBooks Online Database in one way or another. One class – "Troubleshooting QuickBooks Online" – is a revision of last year's QuickBooks Online Errors course. The second is on "Using Data Utilities for QuickBooks Online." 

Both of these courses require ProAdvisors to have a fundamental understanding of the QuickBooks Online Database and related accessibility of that data via the QBO API (Application Programming Interface).  

This article serves as a brief introduction to some of the content that will be presented in regard to the structure of QuickBooks Online data.

QuickBooks Online is both an application and a flat file database that stores your data in a proprietary file format on Intuit’s cloud servers. Applications that wish to connect to QBO must communicate directly with Intuit’s cloud-based servers for purposes of file access authentication and data query.

The QuickBooks Online API actually is two APIs, or domains, the Accounting API and the Payments API.

The QuickBooks Online Accounting API can perform a variety of tasks and operations. Queries take the form of navigational XML commands issued to QuickBooks Online, which then returns record sets that qualify for the query results. Single requests send a single request to QuickBooks Online and process the return individually.

Batch operations actually batch operations into a single request, compared to multiple single requests a batch request can improve on applications performance by decreasing round trips and increasing throughput. 

The API supports changed data capture operations, which return a list of objects that have changed since a specified time. This is beneficial in polling QuickBooks data to refresh a local copy of the data.

The QuickBooks Online API has tables, views and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

Unfortunately, QuickBooks Online does not make all areas of the accounting data available, because Intuit has chosen to restrict certain data, or has yet to develop and authorize accessibility to some data via the API.

QuickBooks Tables

The QuickBooks Online API makes the following tables of data available for retrieval and updates of their data. The tables are normalized and contain an Id column, which is the primary key. This behavior is consistent across all tables within QuickBooks Online. The following table lists and describes the various QBO Data Tables:

QuickBooks Online Views

Data sources that are represented as views typically are read-only data sources. Views are composed of columns and pseudo columns.

Queries can be executed against a view as if it were a normal table. The data that comes back is similar in that regard. 

While views are like tables in the way that data is represented; views do not support direct updating via the API. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

The following table lists and describes the various QBO Views:

QuickBooks Online Stored Procedures

Stored Procedures are available via the API since it is sometimes necessary to update data available from a view using a stored procedure. The view data does not provide for direct, table-like, two-way updates.

In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure.

Stored procedures take a list of parameters and return a data-set that constitutes the response.

The following table lists and describes the various QBO Stored Procedures:

While the names of the various tables, views and stored procedures may be more than the typical user cares to know, it's really only a start for the QuickBooks ProAdvisor intending to work with QBO Data via Data Utilities or complex apps, especially apps used for Reporting or Business Intelligence.

It's important for ProAdvisors wanting to support QBO at the database level to know the fundamental table structures as well. We could invest in an entire series of articles on each of the various data objects, but here, we're going to look at a single QuickBooks Online table, "Accounts."

The QBO Account Table

The Account table contains Account objects that are a component of the QuickBooks Online Chart of Accounts used in the General Ledger. As such, Accounts are used to record a total monetary amount against a specific use.

QBO Accounts are one of five basic types: asset, liability, equity, income (revenue) or expense.

The fields associated with the Account Table are depicted in the following table along with their attributes and description:

Note: Additional table fields may exist within country-specific versions of QuickBooks Online. For example, the TxnLocationType field only exists within the French QBO version.

More about the Account table fields

  • Id is the unique identifier for the object within the table.
  • SyncToken is the version number of the object. It is used to lock an object for use by one app at a time. As soon as an application modifies an object, its SyncToken is incremented. Attempts to modify an object specifying an older SyncToken fails. Only the latest version of the object is maintained by QuickBooks Online.
  • MetaData is descriptive information about the object and the data is read-only.
  • SubAccount specifies whether this object represents a parent (false) or sub-account (true).
  • ParentRef specifies the Parent AccountId if this object is a SubAccount.
  • Description is the user entered description for the account.
  • FullyQualifiedName is derived from the Name and ParentRef. The fully qualified name prepends the top most parent, followed by each sub-account separated by colons. This nomenclature is limited to five levels within QBO. An example would be Parent:Account1:SubAccount1:SubAccount2.
  • Active specifies whether the account is active or inactive. When inactive, the object is hidden from most displays and may not be posted to. Deletion of an object is achieved by setting the Active attribute to false in an object update request; thus, making it inactive. In this type of delete, the record is not permanently deleted, but is hidden for display purposes. References to inactive objects are left intact. 
  • Classification is derived from AccountType and Account Subtype. Valid values include: Asset, Equity, Expense, Liability, Revenue.AccountType is a account classification that specifies the use of this account, based upon the Classification.
  • AccountSubType is the account sub-type based upon the AccountType.
  • AccountNum is a user-defined number to help in identification of the account within the Chart-of-accounts. (Note: Account numbers must not contain a colon.)

How the Account Data Looks

We can't take a peak into the actual QBO database. While Intuit doesn't permit us to do that, we can use a data utility to query and then retrieve the data from the table. 

In this case, the data is going to be displayed within Excel, but it's laid out in the exact order from left to right as the fields of the table (elaborated above from top to bottom).

We don't have room to show all the columns (fields) of data. If we did, the graphic above would be so small that you couldn't read it. 

In the above depiction, you can see the Id, SyncToken, Metadata_CreateTime, Metadata_LastUpdatedTime, Name, SubAccount, ParentRef, ParentRef_Name and FullyQualifiedName fields.

In our continuation of this miniseries, we'll examine one of the Transaction tables in QuickBooks Online.