How to Plan a Data Migration in 6 Easy Steps
From time to time, you may find that you need to replace your QuickBooks company data file. There are a variety of reasons this might happen:
- File damage
- File’s running slow
- Stepping down to a simpler edition of QuickBooks
- Wanting a fresh start.
Whatever the reason, there are probably a few things you’ll want to take care of in your existing file before starting the new file.
These tips can apply whether you’re migrating some years of transaction data to a new file, or just starting one with lists, balances, and open transactions. Some of the tips do assume this is from QuickBooks to QuickBooks in some form. If you’re coming from another software, the principles of what you should prep for still hold, but how you do so may differ based on your source software.
Step 1: Get Lists in Order
One of the first steps you can do to start getting ready for a new file is to re-sort your lists. This will put them back to an alphanumeric sort order, and lets QuickBooks make sure its indexing behind the scenes is correct, so it can properly find list elements as needed. List issues not addressed before a migration could cause issues in the new file via imported lists, so it’s important to take care of this now. We also start with re-sorting lists because it’s a step best done before we check the file for damage, leaving fewer nooks and crannies for bugs to hide in.
If you need to merge any list elements to clean up the lists, it’s usually best to do this now. You can do it in the new file, post-migration, but ensuring that the old and new file match may be simpler if you merge list elements before any exports. You should not merge or otherwise edit any list elements while in the process of migrating to a new file. Generally, you’ll have a cutoff date where you’ll stop working in the old file, and any changes not made before that time should be made in the new file.
If there are any list elements you won’t need in the new file, you’ll want to make sure they’ve had no activity during the time in question and make them inactive. For items, make sure that, if you are making them inactive, they do not have a quantity on hand or value. If so, they will need to be adjusted to zero before inactivating them.
Step 2: Check for Damage
With your lists cleared up, you’ll want to run the verify utility to check for any damage. This is best done logged into your QuickBooks file as the Admin user, in single-user mode. This gives the verify the best conditions to catch any damage.
You can run this by:
- Selecting File in the top menu
- Selecting Utilities.
- Selecting Verify Data.
Depending on the size of the file, and if it’s damaged, this may take some time. Once the verify completes, it will either tell you there were no problems with your data, or it will tell you about any issues it found. For most damage, QuickBooks recommends a rebuild, and this will often fix the issues.
In newer versions, you may have the option to view the errors QuickBooks found. If you see any errors listed that it says the rebuild won’t fix, reach out for assistance. You’ll usually want to get those fixed manually before rebuilding anything. Otherwise, if it lists all errors as fixable by the rebuild, you can try running it. QuickBooks will prompt you to create a backup before the rebuild begins, and it’s very important to do so. Depending on the damage present, QuickBooks may repair something it sees as an error only for this to cause an issue in the books, or it may fill in missing or damaged data with generic filler information that throws off reports. If things look off after running the rebuild, and you can’t account for why, you may want to restore the pre-rebuild backup and get assistance checking the issues manually.
If you rebuild your data, run another verify afterward to make sure all the damage is clear. Occasionally, one error won’t show until another is fixed, and there are times the rebuild thinks it can fix an error but then isn’t able to. A verify after the fact will help you be sure a rebuild has worked.
Step 3: Close Things Out
If your data file is in good shape, the next prep work you’ll want to do is to make sure that your only open transactions in QuickBooks are the ones that are still outstanding “in the real world”. Whether you are going to bring just lists, balances, and open transactions into your new file, or you’re importing a few years of history, you will certainly want to close any transactions that should be closed first for a cleaner result.
Look at reports like:
- Unpaid Bills Detail
- Open Invoices
- A/R Aging and A/P Aging reports
If you see bills or invoices listed as open that have already been paid, or payments that aren’t attached to anything, you’ll want to close them. Sometimes you may still need to enter the other transaction of the pair, sometimes it’s been entered but just not linked together.
Use the reports to figure out which, and if both an invoice and its payment are present, you can edit the payment to link it to the correct transaction. You should be able to tell this by seeing both transactions on the report canceling out to a zero balance, unless the customer or vendor has other open transactions. If a Check transaction should pay a bill, you can go to the Pay Bills screen and select the check as a credit to offset the bill, tying them together.
Step 4: Dive into Your Inventory
If you’re going to be tracking inventory in the new file, there are some important things to check for. Negative inventory can lead to issues in the file, most visibly rapidly fluctuating average costs on items. Make sure that no items are currently negative, and that none of the items you plan to take to the new file have been negative at any point during the period your new file will cover, if bringing transaction data.
The Inventory Valuation Detail report can help quite a bit with this.
- We can go to Reports at the top menu
- Then, go to Inventory.
- For the most accurate picture, check this for all dates.
- Focus on is the “On Hand” column.
You’ll see negatives elsewhere, such as in the Qty column when items are removed from inventory as they’re sold, but it’s the On Hand we don’t want to see going negative, as this means you’ve recorded selling or otherwise losing more of this item total than you’ve received total at that point.
You can customize the report using
- The customize button in the upper left corner.
- The Fonts & Numbers tab option to show negative numbers in bright red.
This can help you quickly scan the report and find points where item on-hand quantities may have gone negative. You could also export the report to Excel and find negatives several ways there, if you prefer. When you find the points where the item quantities go negative, investigate that item to see why it’s gone negative.
Sometimes it’s simply an input error on a transaction, like an incorrect date causing your books to show inventory as received later than it really was or sold sooner, or mistype details such as only receiving 10 units on the bill when you really received 100 units or a very similar item name. If you’re not finding anything like this that appears to be the culprit, then you may need to enter any missing bills, item receipts, or inventory adjustments to correct this.
This is also a good time to do a physical inventory count and adjust your data to reflect this count, prior to any exports.
Step 5: Review Your Books
Take a good look over all your standard financial reports, your bank statements, and anything else you need to get a clear picture of how QuickBooks is comparing to real-world numbers. There are a lot of things to look for:
- If your balance sheet is out of balance
- If your sales tax payable is wildly too high
- If you have dozens of transactions hitting an Uncategorized Expense, or so on.
If you’re seeing any issues where the file may be free of damage, but the numbers still aren’t right, then you’ll need to make sure these accounting or bookkeeping issues are resolved before moving onto the new file, or you may never have accurate information to match the new file to the old.
Step 6: Assess Your Workflow
People use QuickBooks in many ways to track many different things. What works well for one business may be incompatible with the way things need to work for another business. It’s important then, when preparing for a data migration, to take stock of each step of your workflow, why it happens, and whether there’s any flexibility for change in each step.
- The way you use QuickBooks
- The custom fields you may be using in it
- Any outside applications that you connect with QuickBooks.
If the way your business runs means that being able to link an invoice with its original sales order is vital, for example, that’s a very important thing to note because non-posting transactions like Sales Orders are typically not imported if they’re closed. More detail will always be more helpful than not, because each data migration is, on some level, as unique as your business.
If you’re not sure you’ve taken note of everything, that’s okay. Get the information you’re sure about and give us a call at (888) 682-8666. Our experts will help you get things sorted out and make sure you get all the right questions and answers to get started on a successful data migration project.