A couple of years ago, Intuit introduced a new subscription based add-on to QuickBooks Enterprise Solutions, Advanced Inventory. In its initial year this new feature provided “multi-site” capabilities, allowing users to track inventory quantities for each item by specific physical locations. This gave users the ability to create up to 200 Inventory Sites such as Warehouse locations, Shipping/Receiving facilities and Drop-ship. All of this sounds very good if you need “multi-site tracking”, right?
Update 10/25/2012: A fix for the data corruption issue outlined here has been found, see our followup article at http://www.sleeter.com/blog/2012/10/quickbooks-advanced-inventory-error-identified-repair-now-possible/
As more users have ‘turned-on’ this feature, we have seen significant changes in the database that appear to be overburdening the capabilities of the QuickBooks database, and which may pose additional risks of database corruption. Let’s look at some of the problems that I’m seeing.
With the release of QuickBooks Enterprise 2012, users of Advanced Inventory were treated to a group of additional features they could turn on. One of these was the ability to track serial or lot numbers. Intuit advertises this feature as
“Enter serial numbers and lots at the time of purchase, transfer, or sale for more reliable tracking. You can track defective lots through assemblies and subassemblies, in stock on shelves, and customer purchases with new reports.”
If you need to track “serial or lot numbers” it sounds like the perfect option to preclude you from needing to add a 3rd-party inventory management solution.
While not specifically an Advanced Inventory function, another new capability released in QuickBooks Enterprise for 2012 was Enhanced Inventory Receiving. This effectively separates the receiving (voucher) function from the vendor bill function. The result is creation of both a voucher and a bill, rather than the voucher simply being converted to the bill by use of a feature flag as in other QuickBooks versions.
All of these new features share several RISKS in common.
- They significantly expand the size of the QuickBooks database.
- They pose, and in fact have caused, a variety of new forms of data corruption associated with their complex data structure.
- They stress the abilities of the QuickBooks Database Server to perform within the RAM limitations Intuit has posed.
As future releases of Advanced Inventory features continue to expand capabilities, these problems can only become worse if not addressed by Intuit.
Understanding the QuickBooks Database Engine
Everyone is aware that the performance of QuickBooks becomes worse as the database increases in size. Usually Pro/Premier files show significant declines as file size approaches 250MB, and Enterprise shows degradation starting around 500MB. All of the various table sizes, including the internal link table, impact this performance, but the built-in RAM limitations of QuickBooks also contribute greatly to this.
The more new features, tables and data created within the database, the greater the potential for performance degradation due to the limitations Intuit has posed in the use of RAM. In order to understand the effects of these Advanced Inventory features on your QuickBooks, we need to take a closer look at how Intuit’s RAM limitations impact this.
Databases such as Microsoft SQL or Oracle, and even Sybase, allow for reallocation of RAM by increasing cache size as the database grows. Even though Intuit uses the Sybase database for QuickBooks they have not chosen not to take this approach; rather they limit the use of RAM by setting specific cache sizes for their two levels of product, Pro/Premier and Enterprise. Settings within the products limit how much of the computer’s RAM is used. For Pro/Premier the maximum is 256MB, and for Enterprise the maximum is 512MB. While these numbers are ‘the maximums’ by default there are situations in which QuickBooks will make use of significantly less RAM.
In order to make your data rapidly accessible, each computer application uses “cache”, which stores the data temporarily in the computer’s RAM memory. In the case of Enterprise the database server will begin loading file data into cache until it reaches the maximum permitted, when data needs to be accessed that is not already in cache, cached data must be written back to the disk, and data on the disk must then be loaded into cache. The more a QuickBooks file exceeds the RAM use limitations, the greater the effect of these limitations upon performance.
Because of the extra amount of data created by all these new Advanced Inventory features and the RAM limitations of QuickBooks, we can start to see a significant impact on performance as you start to use the features.
While some ProAdvisors and Computer Professionals have learned ways to adjust system settings so that QuickBooks can use more RAM than the limit imposed by Intuit’s restrictions, these “tricks of the trade” would not be necessary if QuickBooks simply allocated RAM on the basis of total file size.
How Advanced Inventory Affects Database Size
Let’s examine the multi-site feature of Advanced Inventory and how this feature may be impacting your QuickBooks database.
Adding a few fields to a database generally does not represent a significant increase in size, but when features are added that create entirely new tables, especially tables that act as multipliers of existing tables like the Item Site table, the potential for phenomenal growth of the database becomes a distinct probability.
When multi-site is enabled you enter your various “sites” into a new table, the Inventory Site table. Data such as site name, site location, and other generic information is captured. Currently (up through Enterprise V12) there is a limitation of 200 sites that Enterprise supports, so this is not a significant change in the database by itself.
An additional table, the Item Site table, records the allocation of your inventory items to every site. Every quantity transaction for every item for every site is recorded to a site. Not only is the transaction recorded to the appropriate transaction but it is also written to the linked record within the Item Site table, and the Item Site table is linked to the appropriate item in the Item table.
To illustrate this, I set-up a brand new company file and created 10 inventory items, as shown below.
I enabled Advanced Inventory and set-up an “Initial” site to which the feature assigned all of the existing inventory. This feature also by default creates a “Drop Ship” site. I then added 4 sites (Sites A through D), making a total of 6 sites in the Inventory Site list, shown below.
The following Quantity on Hand by Site report shows all of my Inventory items assigned to the “Initial site” since I hadn’t yet transferred any inventory to any other site.
If you examine the List Information window in the Product Information screen (press F2), the Inventory Sites and Item Site entries are listed, as shown below. Note that there are 6 Inventory Sites and 10 Item Site listings already.
I then entered a single ‘inventory transfer’ to take 1/5th the total quantity for each of the ten items and allocate that quantity to “Site-A” as shown.
If we again look at the List Information window, you can now see that the number of entries in the Item Site list has grown from 10 to 20.
To continue this process I entered three additional inventory transfers to allocate 1/5 of the total inventory to each of the other three sites I had set up (Site-B, C and D). The Quantity on Hand by Site report below shows the results.
So let’s examine the effect upon the Inventory Site table shown in the List Information. There are 50 site entries. Fifty records have been entered in this Advanced Inventory table to allocate 10 items to each of 5 sites.
Now instead of my extremely limited example, imagine if you had 1,000 inventory items. As the Multi-site feature begins to work, it immediately posts 1,000 entries to the Item Site Table allocating existing item inventory to your default site location. If you then transfer that inventory to each of your sites, so that items are recorded to just 20 (of the 200 possible) sites, the number of records in the Item Site table has grown to 20,000 records. Just 20 Inventory transfer transactions have created 20,000 records within a single database table.
If we take this a step further and assume you have 10,000 inventory items, and just 50 sites, you are talking about 500,000 records created within this table. Of course on the ‘back-end’ the database server is also creating data-objects to track the relationships between the various site, inventory site and item tables as well as the transactions related to each. All of this means that the Advanced Inventory feature is expanding you QuickBooks file size very quickly, so that less and less of the file can be loaded in cache because of the RAM limitations Intuit has established.
Multi-site Related Corruption
As if the growth in file size created by the Advanced Inventory Item Site table is not enough of a compromise on the ability of QuickBooks to perform, a whole new set of database corruptions have come to light associated with the Multi-site feature. Below is an excerpt from a QBWin.log file showing several examples of one such error.
The error mixed sites in targets which verify/rebuild cannot fix is exclusively associated with this Advanced Inventory feature. These errors compromise the Verify utility, and prevent QuickBooks from backing-up unless you don’t select full verification.
What is worse, from my perspective, these errors seem to be getting more common. In my own QuickBooks ProAdvisor practice I have encountered this error six or more times in the last 20 months since the Advanced Inventory feature was first offered. Twice in the 2011 version of Enterprise; and several more times in the 2012 version. Neither myself, nor Shannon Tucker (of AccountingUsers, Inc.) were able to resolve these issues.
Even though this type of error clearly reads “Please contact support”, I am aware of at least one client who subsequently sent their file to Intuit only to be told days later that there was no fix for this type of error. They were advised that the best solution would be to start a new file with an inception date subsequent to the error. It therefore appears that some Advanced Inventory corruptions cannot be resolved even by Intuit.
Editors Note: We are working with Intuit staff to investigate this error further.
How Other Tables are Over-Growing Your QuickBooks
The Advanced Inventory function also offers the ability to track serial numbers for your inventory items. This feature adds a significant number of new tables. Each type of transaction you would expect to see serial numbers recorded on (invoices, vendor bills, inventory transfers, assembly builds, etc.) has new companion tables to track the serial numbers. Since the serial numbers are stored in fields within each of these new tables, rather than the actual transaction tables, a field recording the ‘target’ (related) transaction is also posted to this table, so that the serial numbers can be linked back to the record with which they are associated.
If you are adding hundreds of serial numbers for each of thousands of inventory items, which are used on purchasing, inventory control and sales type transactions, the number of records will grow substantially over time. That growth will significantly increase the overall size of your QuickBooks file and tax the ability of the database server to provide timely access.
You can implement lot numbers instead of serial numbers, and that will have a similar impact (although the volume may be smaller).
Enhanced Inventory Receiving
Prior to this feature, QuickBooks recorded Item receipts and Vendor bills for those items using one set of forms, the “Bill Header” [the Master Record] and the “Bill Detail” [the Target Record(s)] transactions. Essentially a flag within the record differentiated it as either an item receipt or a vendor bill. Because they are one transaction a number of issues were common with respect to inventory items recorded in this manner.
For years, many problems associated with QuickBooks average cost have been tracked back to negative inventory resulting from sales occurring between the time that inventory was received and when the bill was subsequently posted. Upon receipt of inventory and the posting of an item receipt the inventory quantity was increased as of the receiving date. Later, when the bill was posted, the date of the item receipt (now a bill) changed and the previously received inventory was post-dated. If intervening sales relied on the previously received inventory to have positive inventory on hand, those inventory sales were subsequently turned into transactions which caused inventory to ‘go negative’.
In order to give users, with significant inventory, the option of segregating the receiving function from the vendor bill function, QuickBooks introduced the Enhanced Inventory Receiving (EIR) feature. Now the receiving voucher is recorded as an entirely unique transaction impacting on-hand inventory, separate from the bill. In order to accomplish this two new tables are added to the database in order to record the header and details of each of these transactions. This in effect doubles the total number of records required to record both receiving voucher and vendor bill.
An additional “behind the scenes” issue should be mentioned here. Since either the Purchase Order and Receiving Voucher, or Receiving Voucher and Vendor Bill, are ‘linked’ to each other, the database server appends this information to the QuickBooks link table. This is one of the largest tables in the database. Because most QuickBooks users with “large files” were advised to not to turn on the Enhanced Inventory Receiving feature unless they started new Company files, I have not been able to determine how significantly this feature maybe increasing the size of the link-table.
Lastly, because both site information (if that option is turned on) and serial or lot numbers information (if that option has been chosen) are recorded on inventory item transactions, the QuickBooks file can grow quickly as a result of the extra records created with the EIR feature.
There have been several articles about the drawbacks of Enhanced Inventory Receiving in this blog. One in particular relates to the problems I’m outlining here, QuickBooks Enhanced Inventory Receiving – Bill Linking Problems. To summarize, in cases where purchase orders have been created, those purchase orders are subsequently “linked” to the receiving voucher. However, in those cases the receiving voucher is NOT linked to the vendor bill when posted. In transactions not involving purchase orders, links are created between the receiving voucher and vendor bill. This approach can cause confusion at the very least.
What about the Future of Advanced Inventory?
There are many third party add-on products that provide features like multiple-preferred vendors and prices, duplicate parts (differentiated by class or some other characteristics or attribute), barcode enabling scanner interfaces, rack/shelf/bin locations for pick/pack/ship control, and many more. Because of the growing popularity of the Advanced Inventory features, there is every reason to believe that Intuit will be expanding this to include similar features and options.
While there are advantages to having an “all-in-one” solution, as opposed to having to use a companion product that takes over parts of what QuickBooks normally does, there can be substantial disadvantages as well. When a 3rd party product is performing the complex tasks for inventory, an entirely separate database and database server is being used. The developers of those products have designed their applications to maximize available RAM for greater performance, particularly when compared to how Intuit has implemented the QuickBooks database.
When everything for effective inventory control is dumped into the QuickBooks company file database it significantly adds to the number of tables, substantially growing those tables as data is appended to each. This greatly increases the size of the overall QuickBooks database. Given the RAM limitations Intuit has imposed on the Enterprise product, and the new forms of database corruption arising out of these new features, one can only ask, “Is Advanced Inventory Crippling QuickBooks?”