How to use QuickBooks Advanced Inventory Database?

As of the 2012 version of Intuit’s Enterprise Solutions, subscribers can turn on an additional inventory control feature from within the Enterprise Solutions program. This feature is called the QuickBooks Advanced Inventory Database and it is available as an add-on service. It provides unparalleled product tracking and allocation for up to 200 physical locations and integrates with a number of other outside applications.

This feature allows account managers to:

1. Track lot or serial numbers from within QuickBooks
2. Track inventory using average or FIFO methods and easily switch between the two  methods
3. Track and allocate inventory between warehouse, invoice, service orders, and purchase orders automatically without the need for additional programs.

This is a quick guide for business owners to help them get the best results from this powerful inventory control tool.

Using Advanced Inventory Database

In order to use this feature, the user must first subscribe to the service. There is an additional cost of $850 per year and this cost includes product support and a five-user license. Once you have subscribed to this add-on, it will be highlighted to indicate that it’s active.

You can then go into the ‘Items and Company Preferences’ selections and choose ‘Advanced Inventory Settings’ to select the features you need. It is from within this setting that you can choose the options to switch between inventory tracking methods and tracking criteria. This is a huge advantage over the QuickBooks standard tracking method, which required a separate program to enable FIFO tracking.

The tracking criteria options are a) tracking by lot number or b) tracking by serial number. But remember that although you can switch between these options, you cannot use both for any inventory item concurrently. On all invoices, bills, quantity, and other documents, there is a field to enter the lot number or serial number and that inventory item’s status will automatically be adjusted in each relevant account pertaining to that item.

Overcoming Database Issues

The main problems occurring within this program were related to database corruption when using the ‘Multiple locations’ feature of Advanced Inventory Database. This was due to the overwhelming expansion of the existing Enterprise solutions database, which is incompatible with the memory limitations from within Enterprise solutions.

Historically, QuickBooks has had issues with memory due its inability to recognize memory capacity in 64-bit systems. However, the fix for the issue of memory limits can be cured after installation of Enterprise Solutions and it calls for editing the Framework.xml from within the QuickBooks folder.

This is how it’s done:

  • Find and open the QuickBooks folder in your Windows directory
  • Right click the Framework.xml file
  • Select ‘edit’ from the options list
  • When the ‘read me’ notepad text file opens, find the text line ‘Min Requirements Memory1000MB/Memory’
  • Change the amount of memory from 1000MB to 0MB
  • Click save and exit

There is another issue with a ‘Checkpoint’ error occurring whenever a purchase order for any item that involves more than one location is edited. It causes verification issues and can only be fixed by finding and editing the transaction ID number from within the master link and transaction tables. If you’re denied access to these tables, the data can be sent to Intuit for retrieval and they’ll fix it for you for as long as the company provides a permanent solution for the same.

Following these guidelines will enable account managers and business owners to manage inventory within one accounting program in ways that were previously impossible for QuickBooks users. For a business with a large inventory housed in multiple locations, the ability to quickly locate and manage product is an invaluable feature that increases efficiency and reduces overall costs.

Bernard Roesch About Bernard Roesch

Bernard Roesch is co-founder and Managing Partner of Mission Consulting. Bernard’s background in the early years of his career was spent in the manufacturing sector, making his QuickBooks perspective a unique one – he understands the intricacies of a complex environment and then applies his strategic skills accordingly.