1 of 2
Sources and Targets
2 of 2
Sources and Targets
One of the problems for people who come from 'the old school' is that many times QB reports do not reflect 'debits' and 'credits' in what they would consider the normal order, this is due to the fact that QB posts transaction data on the basis of source (first) and target(s) (subsequent) regardless of their status as either 'debit' or 'credit'.
Every QB transaction has a source account that is associated with the 'header' portion of the document. For example, the Source account of a Check is always the 'Bank account' upon which it is written. The Source account of a Vendor Bill is always the Accounts Payable account, and the Source account for an Invoice is the Accounts Receivable account. Regardless of the 'debit' or 'credit' status of the transaction the header always is associated with the source account. (Note: Since Journal Entries in QB have 'no header' the first line of the Journal Entry is 'always' the Source account, be that a credit or a debit.)
The transaction lines (regardless of being either Items or Accounts) always point to the Target Account(s); again their status as either a 'debit' or 'credit' is only bounded by keeping the overall 'accounting equation' in balance.
We frequently tell people to use a 'zero dollar' check for 'this or that', in which the source account (even though it will end up zero still posts as a 'credit' to that bank account), and one of the transaction lines on the stub points to a target account as a 'debit' and then we enter an offsetting transaction line on the stub (another target) as a negative amount (which posts as a 'credit'), the net result being zero.
So when you look at the 'details' of any transaction, such as a transaction journal report (as shown in the headline illustration of this article) in QuickBooks, just remember the first line of a transaction on those reports is 'always' the source account and the subsequent lines are the target accounts regardless of the order of the displayed 'debits' and 'credits'.
QuickBooks reports are queries of the database produced with specific sets of instructions including filters that define how the Source and Target data will be presented within query. If a transaction corresponds to the selected filters it is included in the report, if the transaction does not correspond to the query filters it is excluded from the report. Understanding this concept is essential to optimizing your use of the QuickBooks reporting engine.
There are three fundamental types of filters used to define queries; these are ‘embedded filters’, ‘default filters’ and ‘applied filters’. Embedded filters are defined within the basic query programming and can’t be changed. An example of report containing an embedded filter is the Inventory Item QuickReport, the query filters for ‘Inventory Items’, users can not modify this filter.
Default filters are those filters applied within the query, if you do not modify a report prior to actually compiling it, these filters will be applied. Users can modify these filters in the Modify Report window; by default QuickBooks compiles a report when it is opened; however, you can change reporting preferences to allow you to Modify reports before the report compiles. One of the most common of all default filters is posting status of ‘posting’. Applied filters are those chosen by the user either in addition to, or in lieu of, the default filters of a specified report.
There are two important principles that must also be considered regarding filters, these are ‘conflicting filters’ and ‘overlapping filters’. If you attempt to run a report with conflicting filters you will most likely produce a report containing results other that what you are expecting. The most significant example of this is a report that contains ‘no data’ at all. Think about it this way; if you include a filter that ‘includes’ certain data, but then you include another filter that ‘excludes’ the same data, your query results will be null.
Overlapping filters can include or exclude information otherwise filtered for or out of your reports. An example of this would be a report of cash basis Invoices that have been paid. In order to achieve this report you would set the report basis to cash, and then set the Paid Status to Closed. The Paid Status is an overlapping filter in this query because only Invoices that have been paid will show up in a cash basis report regardless of their paid status being closed.
Let’s look at an example of how to apply all of these principles to produce a report that is not included in the standard set of reports within any version of QuickBooks. Re-purposing the missing and duplicate Check Number report can help us understand Sources, Targets and Filters.
QuickBooks includes a missing and duplicate Check number report that can be used to identify missing and/or duplicate numbers for other types of transactions, such as Invoices. One of the ‘embedded filters’ for this report is to identify ‘missing and duplicate enumerators’, you cannot change this embedded filter. In the event of some type of forensic investigation, you find such a list of invoices which been skipped or duplicated beneficial.
To create the report, open the Missing Checks report from the Banking sub-menu of the Reports menu. When QuickBooks displays the dialog box asking you to specify an account (which is a bank account by default), select your Accounts Receivable account in lieu of a bank Account. You have just modified one of the ‘default filters’.
QuickBooks creates a report of your Invoice transactions arranged in numerical order. But why are only Invoices shown on this report? Well the answer isn’t what you might think. If you are telling yourself that the Query includes a ‘filter by transaction type’ for Invoices, you would be wrong; there isn’t any transaction type filter applied in this report at all. Even the missing check report doesn’t contain a transaction type of ‘check.’
The reason only Invoices appear in the re-purposed ‘missing number’ report stems from the fact that there is a ‘default filter’ of amount >= (greater than or equal to) 0.00’, this prevents ‘credits’ from being included in the report. If you wished to display both Invoices and Credits Memos, if you use a common numbering schema, then you could set the amount filter to ‘all’, in this case ‘all’ is no filter ‘at all’ so the amount filter is then removed from the ‘applied filters’ list shown under current filter choices on the filters tab of the Modify Report dialog box.
Once upon a time, Woody Adams, QuickBooks Specialist, Educator and Trainer based at Intuit’s Plano campus recorded an overview of how QuickBooks uses sources, targets and filters in reporting. Many of you know Woody as one of the co-hosts of Radio Free QuickBooks. “Seeing and ‘hearing’ is believing”; so you should really take time to watch this video presentation.
As Woody has just shown you in his video, understanding how QuickBooks source and target data as well as report filters work are essential to your ability to properly use the QuickBooks report engine and produce reports you need for your business documentation. While the QuickBooks report engine is limited in scope, especially since it can only apply sort data on single level, and can also only apply sorts to a single total by column, you can still obtain most of the fundamental information within the QuickBooks database as you understand the basics set forth in this article.
Editor’s note: Thanks to Woody Adams of Intuit, Inc. for his re-purposed YouTube video.