QuickBooks Online Database and API – Part 2

by

The content of this article is related to two courses I'll be teaching at "Scaling New Heights 2017" regarding QuickBooks Online Troubleshooting and Data Utilities. 

In Part 1 of this two-part series, we examined QuickBooks Online Tables, Views and Stored Procedures relative to their accessibility via the QBO "Accounting" API. We looked at one example of a QuickBooks Online "list" table, the Account table. 

Here, we'll examine one example of a QuickBooks Online "transaction" by examining the Journal Entry (JournalEntry) and Journal Entry Line Item (JournalEntryLineItems) tables, along with applicable API related XML commands being issued to QuickBooks Online regarding creation of a Journal Entry.

Journal Entries are transactions in which at least one pair of transaction lines must be a debit and the other a credit. Under the API, these are referred to as distribution lines. Each of the line entries is associated with an "account" from the Chart of Accounts.

The total of the debit column must equal the total of the credit column.

Journal Entry objects are labeled JRNL by the QuickBooks Online GUI (graphical user interface) in the register(s) and as General Journal in reports listing transactions. Journal Entries associated with one or more Accounts Receivable type account must have an associated "Customer" in the Name Field. 

Journal Entries associated with one or more Accounts Payable type account must have an associated "Vendor" in the Name Field.

Unlike QuickBooks Desktop, QuickBooks Online does not have a restriction that prevents the use of either multiple Accounts Receivable, multiple Accounts Payable or a combination of Accounts Receivable and Accounts Payable accounts.

The JournalEntries Table

Journal Entries within QBO may be inserted, queried or updated via the JournalEntries or JournalEntryLineItems tables. JournalEntries may be deleted via the JournalEntries table:

More about the JournalEntries table fields

  • Id is the unique identifier for the JournalEntry object.
  • SyncToken is the version number of the object. It is used to lock an object for use by one application at a time. As soon as an application modifies the object, its SyncToken is incremented. QuickBooks Online only maintains the latest version of the object.That means an object cannot be modified when specifying a previous (older) SyncToken.
  • MetaData is descriptive information by the object – this field is read-only.
  • DocNumber is the reference number for the transaction. If not provided at the time the object is created, this field is populated based upon settings within Preferences:CustomTxnNumber.
  • TxnDate is the date entered by the user for the transaction, which impacts the financial statements as of this date. If this date is not supplied, the current date on the server will be used.
  • PrivateNote – An optional, user entered, organization-private note about the transaction.
  • Adjustment indicates the total amount of the transaction, including the total of all the charges, allowances and taxes. By default, this is recalculated by the system based on sub-items total and overridden.
  • Line{0…n] is individual line items of the transaction. There must be at least one pair of JournalEntryLine elements representing one debit and one credit of equal value. This pair is referred to as distribution lines within the API.
  • TxnTaxDetail is information regarding taxes charged on the transaction as a whole. This includes the sales tax calculation details for the transaction based on the tax codes referenced by the transaction.
  • TotalAMT is the total amount of the transaction, including all charges, allowances and taxes. This is a QuickBooks computed field, any value supplied by an app via the API will be over-written by QuickBooks logics.

The LineAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the JournalEntryLineItems table as # columns.

This means both JournalEntry and JournalEntryLineItems table entries are being created via the same insert. (The reverse also is possible.)

The following will insert a new JournalEntry with two Line Items:

INSERT INTO JournalEntries (LineAggregate)

VALUES ('<Line><Amount>0.02</Amount><DetailType>JournalEntryLineDetail</DetailType><JournalEntryLineDetail><PostingType>Debit</PostingType><Entity><Type>Customer</Type><EntityRef>24</EntityRef></Entity><AccountRef>33</AccountRef></JournalEntryLineDetail></Line>

<Line><Amount>0.02</Amount><DetailType>JournalEntryLineDetail</DetailType><JournalEntryLineDetail><PostingType>Credit</PostingType><AccountRef>14</AccountRef></JournalEntryLineDetail></Line>')

How the JournalEntry Looks

We can't actually look at the JournalEntry table within the QBO database. We can use a data utility to query and then retrieve the data from the table. 

In this case, the data is 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).

You will see that I have three (3) Journal Entries within my data.

The JournalEntryLineItems Table

To add a JournalEntry object, there must be at least one credit line and one debit line where the total of the debit and credit lines are equal to each other. 

In the graphic depiction illustrating the fields within the JournalEntryLineItems table below, columns denoted with a # are Line Item columns. They can be used for inserting multiple Line Items for a new JournalEntry transaction.

This means it's possible to create JournalEntryLineItems table entries and, at the exact same time, insert new entries into the JournalEntry table.

More about the JournalEntriesLineItems table fields

  • LineID is the ID of the line item of the Journal Entry. This is a [KEY] field.
  • JournalEntryID is the Id of the Journal Entry. This is a [KEY] field.
  • SyncToken is the version number of the object. The SyncToken must be specified when executing an update or delete.
  • Line_ID# specified the Id of the line item.
  • Line_Description# is the description of the line item that appears in the printed record.
  • Line_Amount# is the amount of the line item.
  • Line_DetailType# is the detail type of the line item. Different detail types indicate different types of line items.
  • Line_JournalEntryLineDetail_PostingType# is a field that indicates the posting type of the line item of the journal entry detail. The allowed values are Credit or Debit.
  • Line_JournalEntryLineDetail_Entity_Type# is the type of the entity to be referenced.
  • Line_JournalEntryLineDetail_Entry_EntityRef# is the Id of the entity to be referenced.
  • Line_JournalEntryLineDetail_Entity_EntityRef_Name# is the name of the entity to be referenced.
  • Line_JournalEntryLineDetail_AccountRef# is the Id of the account associated with the journal entry line item.
  • Line_JorunalEntryLineDetail_AccountRef_Name# is the name of the account associated with the journal entry line item.

The following XML commands issued to QuickBooks Online via the API will insert two line items into a new journal entry:

INSERT INTO JournalEntryLineItems (Line_DetailType#1, Line_JournalEntryLineDetail_PostingType#1, Line_JournalEntryLineDetail_Entity_Type#1, Line_JournalEntryLineDetail_Entity_EntityRef#1, Line_JournalEntryLineDetail_AccountRef#1, Line_Amount#1, Line_DetailType#2, Line_JournalEntryLineDetail_PostingType#2, Line_JournalEntryLineDetail_AccountRef#2, Line_Amount#2) VALUES ('JournalEntryLineDetail', 'Debit', 'Customer', '24', '33', 0.02, 'JournalEntryLineDetail', 'Credit', '14', 0.02)

Hopefully this introduction to the QuckBooks Database and API will have interested you enough to continue your studies. ProAdvisors wishing to support QuickBooks Online from a data perspective need a complete understanding of the structural layout of the data and API accessibility to the data.  

While a variety of tools and data utilities are available for accessing and manipulating QBO data, like QODBC and many others, the vast majority of these utilities are dependent upon the user having a sound working knowledge of these fundamentals.