Set Up Case Study

Set Up Case Study

 

With this case study, you will see the process that was followed, along with how to do it better in the future.  There are several add-ons available to aid in the process.  These will be highlighted as well.

Background:

The file had become too large to manage in QuickBooks 2003.  It was impossible to condense because the software would simply close when the rebuild feature was used.  The client did not want to upgrade to version 2005, they prefer to wait until 2006 when Intuit will sunset version 2003.  The client had spent some time on the telephone with technical support and with several consultants and had accepted the fact that a new file was needed.  The 3,000+ customers, 2,000+ memorized transactions and 1,000+ customers with outstanding balance was the problem.  The file also included transactions since the end of the fiscal year.  To further complicate matters, they are a cash basis company so the ability to preserve the report integrity needed to be addressed.

Alternative: The file could not be condensed in version 2003 because the process stated that the file needed to be rebuilt, but each time towards the end of the rebuild process, the software would crash.

Alternative: Upon upgrading to version 2005 without a problem, the file size actually decreased by about 25%.  The condense process was not tested since the client did not want to upgrade.

Alternative: Based on the existing file size and the large number of transactions, it is quite possible that the best solution for this client would be to upgrade to the Enterprise Solution product. This product is designed to handle larger file size (250 MB is OK where 100 MB is the recommended max for the other desktop products) so, in addition to reducing the file size upon conversion and possible further reduction upon condense, this product is designed to provide improved speed during daily use based on the large number of transactions that will be processed through the file each month.

Solution:

Create a new file in the most automated way possible due to the large number of transactions.  While the solution was easy, making it happen was a little more challenging.

Step 1: Get the old data file

In order to extract the information from the old file, we needed to physically obtain it.  The decision was made that the file would be transferred late Thursday and the new file would be returned Monday morning.  At over 500 MB, the file was obviously way too large to send as an e-mail attachment.  As an alternative, RASwhich can usually work magic in any situation) was tried but the client's computer kept timing out during the transfer.  The last alternative was to burn a CD and coordinate delivery of that.

Just as an aside: The CD was received but without the password.  Due to how late it was at night, the decision was made to crack the password.  The first try was with the Advanced Intuit Password Recovery Tool which is usually a little easier since it will remove the long passwords on the newer versions (plus it is a one time purchase with future upgrades available at no charge) but it timed out trying to crack such a large file.  The back up plan of the other password recovery product we use was tried.  QuickBooks Key churned a little but then was able to replace the passwords.  The Admin password was copied, the QuickBooks data file was opened by pasting the password, and then the password was removed to make working with the file easier.

Step 2: Create the data file

This is probably the easiest part of the entire project.  The only trick to creating a new data file in this case is to choose <None> for the chart of accounts since this information will be imported from the old file.

Step 3: Transfer the lists

In this case, it was a two step process. 

First all of the lists except for the customers could be exported from the old file.  This *.iif file could then be simply imported into the new file.  The result of the list transfer is that the vendors, chart of accounts, items, etc. are now in the new file.

The second part of this process was MUCH more difficult.  In fact, this next step took 75% of the time to create the new file.  The decision was made that since the memorized transactions cannot be transferred from one file to the other, and the task of re-entering such a huge volume was so daunting, an invoice duplicator add-on was a logical solution.  The problem was that the customers were invoiced different amounts each month.  A custom field or customer type field was needed to permit using the add-on to invoice the customers correctly.

  1. The process started as above with exporting the customer list. 
  2. This list was then opened in Excel.
  3. The list was sorted by hidden Y or N with a secondary sort by name.  All of the Y (i.e. inactive customers) were then deleted so only the active customers remained.
  4. The memorized transaction list was exported to Excel and sorted by name (luckily most of the memorized invoices actually had the customer name as the name on this list)
  5. The memorized transactions were then copied into the blank columns to the right of the exported customer list information to make matching the names easier.  (Note: at this point someone with more extensive Excel experience probably could have made the process more efficient by using pivotal tables). 
  6. The memorized transactions were lined up in the same row as the appropriate customer. (Note: there were several transactions that were associated with inactive customers so these were transferred to a new Excel sheet to be provided to the client for further investigation)
  7. Once all the memorized transactions were lined up with the appropriate customer, the column with the dollar amount was copied into the column for the CUSTFLD1 (i.e. custom field 1) and the column heading was replaced where it need to be to make the import work correctly. 
  8. This file was then re-saved as an *.iif file.
  9. The new file was imported into the new QuickBooks data file.  The result was the customer contact information, the new custom field, and any notes from the old file were all transferred at once.

Step 4: Transfer of Beginning Balances

The original plan was to use the Beginning Balance Transfer Tool but the problem came on Saturday afternoon when it was discovered that version 4 of the tool only works with versions 2004 and 2005.  For version 2003, version 3 of the tool was required.  The correct software was purchased, but unfortunately, the key code was not available until the developer's office opened again on Monday (yes, the same time the file needed to be to the client).

As an alternative, the List Importer was purchased and upon checking out a 7 day temporary key code was available (and actually the permanent one arrived within a couple of hours).  This tool is an Excel AddIn so it works with versions 99-2005 QuickBooks Pro and higher products. 

  1. The AR Aging Summary report was used as the basis for the beginning balances.  (Note: if the detail report is used each line for the same customer will be imported as a new customer (i.e. ABC Co, ABC Co {1}, etc)
  2. The report was then exported to Excel.
  3. The report was sorted by the balance column to permit removing any zero balance amounts and the total line to make the report more manageable.
  4. The customer heading of name was added and the total heading was changed to balance.  Customer was added to each line (although the instructions say it is optional, it did not work for me without it).
  5. The tool was used to create the *.iif file
  6. TRICK: The *.iif was opened in Excel and "Opening Bal Equity" was replaced with an income account to solve the cash basis issue going forward.  The file was then saved again.  That change is not available with the qbXML tools.
  7. In QuickBooks, the file was imported.  The notes remained, but all the other customer information was replaced with blank information.  I.e. the customer contact information and the infamous custom fields were all removed.
  8. The file that was created above was re-imported to replace the missing information and all was well with the world again.
  9. The same process was followed for the chart of accounts.
    1. Create a trial balance report in QuickBooks
    2. Export the report to Excel
    3. Add the account type column
    4. Save the file
    5. Import into QuickBooks
    6. Double check all beginning balances and then make two entries: 1. Reclassify the beginning AR coded to the income account rather than opening bal equity and 2. Close opening bal equity into retained earnings.
  10. This client did not use the Accounts Payable feature so there were not any vendor balances to transfer.

Note: had the decision been made at the beginning to use this tool instead, all of the necessary information would have been included on the report (with only changes potentially to the column headings) for each type of balance to create one step instead of two.

Note: In this case the bank accounts were not reconciled through QuickBooks so the bank account was transferred as the balance at the conversion date.  Typically, however, once the beginning balances are transferred, the beginning balance for the bank and credit card accounts should be changed to the balance per the last statement and the outstanding transactions need to arrive at the ending balance per the old data file should be entered.

Step 5: Transfer of new transactional activity

There are several different tools available to transfer transactional data since version 2003 or higher is being used (i.e. the developers have an SDK from Intuit to permit transfer via qbXML technology).

The QuickBooks Transaction Copier alternative from Big Red Consulting is our choice if an older version of QuickBooks is used, or if both files are not located on the same computer.  It is easy to use and relatively trouble free.  The drawback is that since it uses an iif file rather than XML there is no error checking and the individual transactions are imported individually (i.e. they do not retain the links between transactions).

Due to the linking issue (and the desire to have error checking with such a large number of transactions) the decision was made to use the Data Transfer Utility instead.  Due to the size of the file and the quality control issue for each type of transaction, the transfer was accomplished in 4 "stages:"

  1. Transfer of checks, credit card charges and credit card credits
  2. Transfer of invoices and credit memos (Note: make sure the sales tax preference has been turned on if the previous file was turned on, otherwise all the transactions will be rejected and the process has to start again for this type of transaction)
  3. Transfer of payments received from customers (Note: Any payments received against the beginning balance was not transferred, but was indicated as an error.  The reason is that the computer could not match the date and invoice number for those transactions. Within the tool, open the transfer report then choose Reports > Receive Payment Detail.  To print to a file, this report can be printed using the snapshot viewer that can be downloaded for free from q2q.us, or if a PDF creation software is available, change the default printer then print the report.  These errors will need to be addressed prior to processing the deposits)
  4. Transfer of deposits (Note: There are two issues, the first is that all receive payments need to be in the file prior to processing the deposits. If a single receive payment is missing, the entire deposit will not transfer.  The second issue requires an entry between a new account the tool creates on the chart of account "undeposited transfers" and undeposited funds."  The SDK does not permit deposits to be coded to undeposited funds via the add-on.  There are clearly written instructions included in the tool).

If the previous file has other transaction types such as bills, bill payments, estimates, etc that need to be transferred the process would need to be repeated for additional stages.

Once the transactions have been transferred, confirm that the list information remains intact.  For example, the customer address and/or custom field were not replaced with blank information as part of the process.  If they were, simply import the list files created previously again.  The list information will not affect the transactional information.

Step 6: Export Memorized Reports

In this case there was no need to transfer memorized reports.  If there were, the individual reports need to be exported from the old file and imported into the new file.

Step 8: Set Up of Online Banking and Merchant Services in new file

This process has two components that need to be addressed.

The first is actually setting up the merchant services and online banking to work from within the new file.  This can be achieved through working with the free support from the merchant services.  The only problem experienced in this area was with a Windows XP machine.  When a Windows 2000 machine was used, the process went smoothly.

The second deals with importing the credit card information.  Within QuickBooks there is not a report with the credit card numbers on it.  However, the QTableGrabber will permit extracting the information into an Excel spreadsheet.  (Note: To eliminate the process of setting up the query, after the tool has been installed and the old file in QuickBooks is open, download and open cc query.xls then choose QTableGrabber > Refresh Data.  Select the query to refresh and the credit card information should populate the spreadsheet.)  The spreadsheet can then be converted to an import file by Flexquarters Solutions for about $150.  This automates the process of getting the information in to the file.  Unfortunately, in this situation, this was not possible since the technology is only supported for version 2004 and higher.  Instead, the old file was converted to version 2005, the QTableGrabber was used to extract the credit card information which was then provided to the client to cut and paste the information into the new file.

More information on this topic

Building New Data Files

QuickBooks Transaction Copiers

2006 Update Book