1 of 6
QB Databaser Table
QuickBooks Database Concepts
2 of 6
Check-1
A QuickBooks check contains a minimum of two records, a Master Record (the check) which records the source account (the bank) and other source data (date and payee).
3 of 6
Check-2
Each line of a QuickBooks check-stub represents a unique Target Record, these lines record the 'target data' including target account (or item). Additional data can also be recorded (customer and class, etc.)
4 of 6
Check Detail Relationship Table
This slide illustrates the type of relationships (and database nomenclature used) between check-detail "item" line fields.
5 of 6
Check-3
In this check example, the Master Record results in a credit for $605.00 to the source bank account. The Target Record (detail line) results in a debit of $605.00 to the target expense account "Materials".
6 of 6
Sources and Targets
This report illustrates the source data (associated with the Master record) and the target data (associated with the Target record). In QuickBooks Detail level reports, such as Transaction Detail, or Journal, the top line always reflects the 'source' account and the subsequent lines reflect the 'target' account(s).
Have you ever encountered a QuickBooks’ “Verify Target” error, or wondered why you can't find certain information about a transaction in a report when all the data you need is contained in your transactions? The key to understanding these situations is found in the QuickBooks concept of Master and Target records, and their related Source and Target data.
How QuickBooks Data is Organized
QuickBooks transactions are recorded in multiple tables within the database. Each QuickBooks transaction consists of at least two records stored in different tables. Three of the tables we will refer to in this article are the: Check, Check-Expense Lines, and Check-Item Lines, tables.
For example, a Check consists of a Master record, sometimes called the “source” (or header) transaction. This is what we think of as the actual ‘check’. There is at least one Target record for each expense or item line on your check, also called the ‘transaction details’.(See Slide 1)
In our check example, the master record contains the payee name, check date and so forth, it also contains the ‘source account’ which is the bank account upon which the check is written. But the target records for the check are the individual detail lines listing expense accounts or items on the ‘stub’. For every detail line a unique relationship is created between the Master record (and source account) and the Target record (and ‘target account’). (See Slide 2)
When you include other information on the Check Expense-line, it is also linked to additional QuickBooks lists. If you post a ‘Class’ to an expense-line, you immediately create a relationship between the Check Expense-line and the Class List. If you post a Customer to an expense-line, you also create a relationship between the Check Expense-line transaction and Customer. These relationships are recorded in the database using the RefListID for each appropriate list. So now the Master record in the Check transaction table is linked to not only the expense account, but the other details recorded in the Check Expense-line transaction table. (See Slide 3)
If we write a check we can get a better understanding of the Source and Target ‘relationships’ within QuickBooks for that check; the Master record contains the ‘source account.’ Sources and Targets are a way of representing transactions in a different manner than ‘financial accounting’. The sources and targets determine how the components of each transaction fit into database structure. You should not confuse Sources and Targets with the accounting concepts of Debits and Credits, they are NOT the same. For any given transaction the source can be either a debit or credit, and similarly the target can be either a debit or credit.
For example, if our check was for $605.00, the Bank account upon which it is written is the source account for the transaction, and a credit will be posted for the $605.00. If we expense this check against the Materials account, that account represents the target account, and will be debited for the $605.00. (See Slide 4)
If we viewed the transaction journal (or a detail report) in QuickBooks for this same check, the report shows the source account on the first line of the transaction. The subsequent line(s) for each transaction contain all of the target data, including the target accounts. If our check had more than one target record on the 'stub', each additional line would be listed on these reports. (See Slide 5)
Sometimes additional target information can be displayed by selecting additional columns of data to display on the report. QuickBooks reports frequently display more data than just the Source and Target information. The Journal Entry exception to these Transaction Journal (transaction detail) report principles will be discussed later in this article.
For some transactions, certain “Source” transaction data is also copied into fields associated with the Target records. For example, the “Source name” is copied to the Target record as the “Source name”. This is why it may be necessary to display not only the ‘Name” column but the “Source Name” column in some reports.
While there are no absolutes in the QuickBooks in terms of source and target accounts, the following principles usually apply:
- The bank account is almost always the source account for transactions that contain a bank account; this includes the five types of QuickBooks checks (Checks, Bill Payment Checks, Paychecks, Liability Checks, and Sales Tax Checks).
- The bank account is the source account for Deposit transactions.
- Accounts Receivable is typically the source account for transactions that post to A/R; however, an exception is a Payment Item on transactions like an Invoice, in such cases the bank account becomes the source and A/R is the target.
- For A/P transactions, Accounts Payable is almost always the source account; but an exception is associated with Bill Payment Checks, because, since they are checks, the bank account is the source and Accounts Payable is the target.
How QuickBooks ‘relates’ Master and Target records
Without going into too much detail, when a user enters a transaction QuickBooks assigns the Master record an internal transaction number; each Target record is also assigned an internal number in sequence. (This article's feature image shows an excerpt from a typical QuickBooks data table.) However, because transactions can be later modified, and detail lines (corresponding to new target records) can be added or deleted, these changes are assigned the next available sequential record number based on the last transaction entered in the company file. Since QuickBooks allows users to insert line items (targets) between existing lines within a transaction, the target transaction numbers may no longer be consecutive. Even with such variations, this internal identification arrangement permits the Database Server to assign a unique “index” to associate each Master with each Target record.
Unfortunately one side effect of this identification methodology between the various Master and Target records is the possibility for corruptions that disassociate the related records. This can be one of the causes of some of the “Verify Target” errors reported within the QBWin.log.
Because I mentioned ‘index’ a couple of paragraphs ago, I will explain that indexes are pointers used to provide rapid access to data within database tables. When you query QuickBooks for specific information, the Database Server finds the matching data using these indexes without having to verify each piece of data in every table. It is similar to using the index of a book to go directly to the page on which the information is located, so you don’t have to thumb through the entire book to find what you are looking for.
When Source & Target Principles May Not Apply
Before leaving the topics covered in this article, I want to discuss the most noted irregularity to everything we have learned about sources and targets…..the Journal Entry.
In most cases the first line of a Journal Entry contains the source account, and all other lines are targets. But the Journal Entry is the one QuickBooks transaction that can have absolutely ‘no’ source account, if you leave the first line completely blank. Furthermore, the sum of the target accounts of a journal entry need not equal the value of the source account, this is because target accounts can off-set each other as well as the source account.
Journal Entries are also the one transaction type in which the first line of a Transaction Journal (or Transaction Detail) Report does not necessarily display the Source account. When an existing Journal Entry is modified to insert a new line into the first line of the transaction, the new first line identifies the new source account for the transaction; however, in Transaction Journal (or Transaction Detail) Reports, this new line will actually be shown as the last line for the transaction in the report because these reports are sorted by the internal transaction number discussed in the prior section of this article.
While leaving the first line of some Journal Entries blank has certain uses, it also causes other issues including the inability to zoom directly to such transactions from QuickBooks reports.
Future articles in this series will apply these concepts to both reporting, and data corruption issues.