My Favorite Newbies – QuickBooks 2019 Desktop (Part 1)

by

“Raindrops on roses and whiskers on kittens. Bright copper kettles and warm woolen mittens. Brown paper packages tied up with strings. These are a few of my favorite things”1

Each year, I begin my favorite newbies article with the excerpt from "My Favorite Things," which was composed for "The Sound of Music." The song emphasizes the simple things in life.

Many times, my favorite things have been ‘complex’ features found only in QuickBooks Enterprise (even Advanced Inventory), and yet other years they are some of the most simplistic things found even in Pro and Premier. Still other years they have been features geared solely toward accountants, bookkeepers and ProAdvisors and found only in the Accountant and Enterprise Accountant version of the software.

In past years a few people have been critical of why I didn’t provide more ‘rationale’ as to the selection of my favorites. So, here is one of my favorite "newbies" from the 2019 feature set of QuickBooks Desktop 2019 products, look for others in the days to come.

Data File Optimization (aka: Condense/Remove Audit Trail ‘to date’)

Finally, we have a way to condense a QuickBooks Company file without impacting the transactional history. In the past the built-in condense feature (at one time called ‘clean-up’) removed your transactions, sometime summarizing them properly (many times NOT), or you were required to manually enter opening balances as of the ending date for the period of transactions removed. 

Issues with un-intended open transactions, the last ‘payroll date’ (in relationship to the condense date), corrupted data, and file size itself could in fact limit the ability of the internal condense utility and might even prevent it from working at all. Sometimes a skilled ProAdvisor might get around these obstacles with a few ‘tricks of the trade’, but sometimes not.

One alternative to using the internal condense feature to resolve a file size issue was to start an entirely ‘new file’ that involved importing list information, and recreating transactions that were open as of some cut-off date, along with posting beginning balances.  In many cases the best approach to this method was to use the ‘condense utility’ to remove ALL transactions. But even that approach could potentially leave behind a lot of ‘hidden clutter.’ Worst of all, this typically meant that QuickBooks users were working in a file without any history, and then opening their historical file to see a customer’s last order, or to see what a vendor last charged them.

Of course, one of the best alternatives was to have a professional file service create a new file that contained some period of data (perhaps a year or two) for purposes of going forward, while your old file was retained for historical information. When my own firm did this work (we stopped doing them in the late Fall of 2015), we called it a ‘file-split’, but each of the reputable firms doing this work call it by ‘their own name’. Since these companies are working ‘outside of the QuickBooks internal write mechanisms’ (on ‘the dark side’ so to speak) using proprietary mechanisms based around the Sybase database itself, they do not have the limitations imposed by the QuickBooks application or database server.

One alternative to these forms of condense, also performed by professional data firms, has been to remove the Audit Trail to achieve some file size reduction; however, while making the file size smaller that didn’t always mean the file would perform any faster.  In discussing this new feature with Shannon Tucker, who owns a data file service called ‘QuickbooksUsers.com’, he remarked that his own attempts at removing just the audit trail from QuickBooks file in some cases “reduced file size by up to 50%, but that users still complained that ‘it didn’t help speed up their slow files’.”

The new ‘condense option’ found in QuickBooks Desktop (Pro, Premier, Accountant and Enterprise) 2019 gives you this ability to remove the Audit Trail that happens to be one of the largest single portions of the file. This form of condense has the potential to substantially reduce file size which should (emphasis added) result in enhanced performance.

In the tests I ran, the time needed to perform this new ‘Remove Audit Trail Only’ form of condense was significantly less than other forms of condense offered within the internal QuickBooks Utility. Out of my half-dozen different tests using files ranging from 300 MB to 1.2 GB, the file size was reduced between 31- and 49-percent.

I subsequently discussed with Intuit development team members that it appeared the more ‘edited’ a file was, the greater the file size reduction. They confirmed that should be true. In other words, if your QuickBooks file has a history of a lot of edits to transactions after they are initially created and saved, then you are more likely to see a larger file size reduction by using this new functionality. 

To understand this a little better, let’s create a transaction, and then edit that same transaction six times on different occasions. In so doing you have created a total of thirteen entries in the audit trail (there is a ‘before edit’ record and the ‘after edit’ record for each edit of the original transaction). When you purge this Audit Trail you remove thirteen entries, as opposed to a single entry (originally created) when a transaction has no history of edits. In the scheme of things thirteen entries may not seem like a lot of data, but if you have done this hundreds, or thousands of times, it significantly contributes to the size of the Audit Trail and overall file size.

Purging the Audit Trail isn’t the only action performed by this new condense option. Several other internal performance logs, which are stored as part of the Company file, are also cleaned-up during the process. Then an ‘unload/reload’ is performed to help ‘optimize’ the file so that it should perform more robustly than before. In my own testing I could see enhanced performance, but I was only able to test this (in both ‘single-user’ and ‘multi-user’ modes) without other users working on the file simultaneously. Going forward the new file should act just like any other QuickBooks file in terms of the way that information is queried and recorded. If I were a betting man, I would favor the odds on enhanced performance although the extent of such enhancement is still a ‘crap shoot.’

Now let’s turn our attention to ‘the old file’. During this new process QuickBooks appends your old (original file) with the date of the condense (optimize) and the word 'Copy' as you can see highlighted in the figure below.  This is your original file (an actual .QBW Company file) without any other changes (even though Intuit refers to it as a backup in the message displayed), and the ‘Audit Trail’ as it existed is still intact. This file also exists in the file directory where it was at the time you began the process, so it hasn't even been moved.

Whether some people will argue that the file was changed because the date was appended and that somehow limits the ‘forensic integrity’ of the file will probably be a question that only ‘some court somewhere’ will be forced to answer. Of course, that means that the decision will probably be appealed, and who knows where we go from there.    

I am anticipating that the IRS will accept a ‘copy’ or ‘backup’ of the original file (with the appended date) for any investigation, and probably most forensic examiners will likely do the same once they understand this new process. 

Now for wishful thinking on my part. I have one request, that this feature offer an ‘as of date’ for the removal of the Audit Trail so you could select ‘as of 12/31/2017’ and all entries as of 12/31/2017 and prior would be removed. If you could do that, then you could run this process annually once the books were closed, meaning that each year you would have all your saved data with Audit Trail intact for that one year, and your going forward file would still have your transactions and the new Audit Trail. Repeating the process would keep each file clean for the year. So, Intuit if you are listening (or reading) maybe you can do this for next year!

Let me address one other issue here, that being ‘will there still be a need for other forms of condense’? The answer is definitely!  While the Audit Trail removal could produce a substantial file size reduction when performed initially, you can not expect to see the same reductions in subsequent periods. 

Think of it this way…with a file of five years history and your audit trail is initially 40 percent of the file size, when you reduce the file by that 40 percent, the remaining 60 percent represents your actual data. If your file grows by 20 percent the following year the audit trail represents (most likely) only 40% (maximum) of the additional 20 percent, in other words 8% of the total file size. Running the process again at the end of the year would leave you with a file of approximately 92% the size you began the process with…that’s a minimal file size reduction overall. You are likely going to be looking for an alternative way to reduce file size if that is the only reason you are using this new process.

Still the same, this is definitely one of my ‘favorite newbies’ for 2019.

1-"My Favorite Things"; Rodgers and Hammerstein musical The Sound of Music (1959), Composer Richard Rodgers and lyricist Oscar Hammerstein.

Figit Friends Newbies (as shown in this articles' headline graphic) are the registered trademark of Mattel, Corporation.