The size of your QuickBooks file and the number of DB File Fragments determines the health of your QuickBooks company data file. Most people don’t periodically check the health of their QuickBooks file to help prevent data corruption – mainly because they don’t realize that they should or even how to go about it.
Monday’s blog post – Basic File Maintenance Before & After You Upgrade – talked about checking your current file size and health before before you upgrade to a new version of QuickBooks. In reality, you should periodically check this information just to see how healthy your file is or isn’t.
To check the size of your own QuickBooks file – with QuickBooks open press the CTRL and the 1 keys at the same time OR press the F2 key at the top of your keyboard – this will open a Product Information window; similar to that shown below, paying close attention to the circled areas – file location, file size and DB (database) file fragments:
Right click on the image to enlarge it
The screenshot above provides the following information about our own company data file:
- File Size 180932 K or roughly 180 megs which is a fairly large file; this is confirmed by the fact that there are 17583 total transactions in the file
- DB File Fragments 3 – anything under 20 DB file fragments means that your file is pretty healthy, while anything over 20 means your file is sick!
There are some things that you can do to try and repair you QuickBooks file yourself:
- Rebuild you company data file and check the Product Information Window after to see if the number of DB file fragments has been reduced
- Create and restore a Portable company file {watch our YouTube video} and check your results
- Seek the help of a professional to repair your data file – such as QuickBooksUsers.com or QB or not QB
Keeping your QuickBooks data file healthy means that it will perform better!
We hope you found this tip to be helpful, if so please take a moment to leave a comment or share it on your favorite social network.
A QuickBooks tip for entering Job-to-data costs and billing data at the item level when converting or archiving.
Use this procedure to enter Job-to-date (“JTD”) costs and JTD billing so the Job Profitability Detail Report is accurate at the Item level after converting your existing Quickbooks file to a new database file, or after archiving old QuickBooks transactions. In both cases, you are bringing over beginning balances as of the conversion date into your new data file, and this procedure will prove useful for reporting on jobs that are in progress as of the conversion date.
From your old data file:
Run your Job Profitability Detail Reports for each job in progress to obtain the JTD cost and revenue figures as of the conversion date. In the date fields, leave the first field blank, enter the conversion date in the second date field. You will use the figures on this report for your data entry in the new data file.
In the new data file:
- Create a fake Vendor called “Opening Balance”. You will make this inactive after completing this procedure.
- Create an Other Expense account in the Chart of Accounts called “9999-Conversions-JTD Costs”. You will make this inactive after completing this procedure.
- Create an Other Income account in the Chart of Accounts called “9998 · Conversion – JTD billing”. You will make this inactive after completing this procedure.
- Create a Service Item called “Opening balance JTD Costs” and link it to “9999 · Conversion – JTD Costs”. You will make this inactive after completing this procedure.
- Print your Item List. (Reports>List>Item Listing) Modify the report to display columns for Account and COGS Account; remove price, cost, tax code, etc.
- Look at your Item list and determine which Service Items appear on your Job Profitability Detail Reports. These are the items to edit in step 9. These are active items.
- In the Item List window, make all other Service Items temporarily inactive. Making them inactive will keep them from appearing in the Add/Edit Multiple Items screen allowing you to quickly copy down the account edits you will perform in step 9.
- Create a backup before performing step 9!
- Using the Add/Edit Multiple Items screen (available in QB 2010 and later) ,edit “all active service items” and point the COGS/Expense account to account 9999 and point the Account/Income account to 9998. Change the top item, and quickly copy down the account edits to the other items. Click Save. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
- When you are done with the following procedures, you will edit these same Items again, and change the COGS/Expense and Account/Income accounts back to what they were. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
To enter the JTD Costs for each item on each job:
Enter a zero dollar Bill to the “Opening Balance” vendor for the JTD cost. On the item tab enter each Item and the costs on the job profitability detail report and the appropriate Customer/Job. Keep the Billable box checked. On the last line, enter a negative number to the “Opening balance JTD Costs” Item with no customer:job.
This results in no AP balance, leaves no effect on GL, but leaves the correct JTD cost by Item in your Job Cost reports.
Create a new zero-dollar Bill for each active job as of the conversion date.
To enter the JTD Billing for each job:
Enter an Invoice for the Customer:Job for the total Billing-to-date on the job. The Add Time/Costs box will pop up, select the Item tab and bring over all Items to the invoice. You now only have to edit the dollar amounts to agree with your JTD Billing on your Job Profitability Detail report.
Enter a Journal Entry for the total amount of the JTD Billing on the job. The debit is to the “9998 · Conversion – JTD billing” account, leave the Name field blank. The credit is to Accounts Receivable. Select the Customer/Job name in the Name field.
In Receive Payments, enter the Customer/Job, and you will find the above Journal Entry is available as a credit against the Invoice you created above. Apply the Journal Entry to the Invoice.
The net effect on GL is zero, the invoice is paid, and the correct Job-To-Date Billing as of the conversion date will be reflected in your Job Cost reports.
Create a new Invoice, Journal Entry and Receive Payment entry for each active job as of the conversion date.
Run your Job Profitability Detail Reports on the new data file and compare with the same report on the old data file. They should agree. If not, you’ll need to double check your data entry.
I hope you find this QuickBooks tip to be helpful. If so, please leave a comment or share it on your favorite social media platform by using the buttons below.
This QuickBooks Tip provides instructions for how to rebuild your QuickBooks data file. The rebuild function will help maintain the overall health of your file.
Is your QuickBooks file very large? Is it running slow? Are you in a multi-user environment where 2 or more people are inputting a lot of information on a daily basis? If you answered yes to any of these questions you would probably benefit from rebuilding your data file on a regular basis.
The QuickBooks Rebuild Data function is like a cleaning lady. When you access the rebuild function it looks at all of the information in your file and makes sure that it has been filed correctly. Think of your desk, covered in piles of papers that need to be filed. Before you actually put all of the papers in the filing cabinet, you sort and separate the various piles into organized files. The rebuild function performs the same task.
How to Rebuild Your Data File:
You’ll need to be logged into QuickBooks as the Administrator. If you have a large file, this could take quite awhile – so plan to run this process at lunch time, or gear it up just before you leave for the day.
- From the File menu
- Choose Utilities
- Rebuild Data
The first thing that the Rebuild will require you to do is to make a backup of your data file, go ahead and do that.
As soon as the backup has completed, the rebuild function will automatically start. This tool will take two passes through your file, looking for problems, organizing your data, and trying to fix any problems with your data that it finds. If you sit an watch the process, don’t be concerned if QuickBooks seems to stop responding or stop working at 99% on the first pass and 50% on the second. This just means that it’s found things that are wrong, is attempting to fix them, and then going back to check that everything it found on the first pass has been fixed.
There are times when the Rebuild function will fail, this usually means that there is data damage within your file that the rebuild cannot fix (it does have limited abilities). In a situation like this, you’ll need additional assistance to fix the issues. If you run into this situation, I would recommend that you investigate the services of Accounting Users, Inc., QB or Not QB, or The Bottom Line.
Our own QuickBooks file contains 11 years of data and I rebuild it on a monthly basis and have for many years.
If you found this article helpful, please leave a comment or share it with others.
A QuickBooks for contractors tip about issuing joint checks to a subcontractor and a lower tier subcontractor or material supplier for payment for work completed on a construction project.
Quite frequently, in the construction industry, a contractor needs to issue a joint or two-party check to a subcontractor and a lower-tier subcontractor or material supplier for work completed and/or materials delivered to the construction project’s job site.
Unlike many high-end construction accounting packages, QuickBooks doesn’t have a way to handle this automatically — or easily.
This QuickBooks for contractors tip provides what we consider to be a best practice when a situation like this arises.
Problem:
Joe’s General Contracting, Inc. needs to issue a $10,000.00 joint check to Sam Subcontractor AND O’Fallon’s Gravel for sand, gravel, and crushed rock which was delivered to a jobsite. Sam Subcontractor is one of Joe’s regular subcontractors and is already in the QuickBooks Vendor List; O’Fallon’s Gravel is not a normal supplier and is not setup in the Vendor list. Sarah, Joe’s bookkeeper isn’t sure how she should handle a joint check.
Solution:
When Sarah is ready to write the joint or two-party check, she should follow these steps:
- Edit Sam Subcontractors Vendor record in QuickBooks and in the Print on Check as field, she should add AND O’Fallon Gravel. She should also edit the Billed From Address so that it to displays Sam Subcontractor AND O’Fallon Gravel. Clicking the OK button to save her changes.
Right click on the image to enlarge it
- From the Write Checks window, select Sam Subcontractor, enter the dollar amount. In the Memo field type in Joint Check issued to Sam Subcontractor AND O’Fallon Gravel and then job cost as usual.
Right click on the image to enlarge it
- Print just this check.
- Immediately go back to Sam Subcontractors Vendor record and delete all references to O’Fallon Gravel, click OK to save the changes.
- Customize the columns to display in the Vendor Center to include the memo field.
Right click on the image to enlarge it
Make sure that you also have the proper Lien Waive and Release forms.
We hope you found this QuickBooks tip to be useful — if so please take a moment to leave a comment, share it on your favorite social media site or click the +1 button below.
A QuickBooks tip for creating a pre-set or memorized Estimate with a standard set of items that will automatically display whenever you create an estimate.
Some contractors, and many other types of businesses as well, frequently prepare an Estimate, Schedule of Values, Proposal, or Quote for different customers that use the exact same items – or groups of items, just with different pricing. Manually creating this same bid over and over by pulling the items into the form is a repetitive and time-consuming task that can easily be automated in QuickBooks by memorizing it.
Follow these simple steps to create a pre-set estimate with standard items and memorize it in QuickBooks
- Pull up a blank estimate form
- Build your bid using Items (cost codes) from your Items List
- Do NOT add any dollar amounts
- Review the basic proposal carefully to make sure that you’ve included all the standard items
- Press the CTRL and “M” keys at the same time (this tells QuickBooks that you want to Memorize the transaction on your screen)
- QuickBooks may present you with a message window that states: When you memorize this estimate, QuickBooks removes the customer:job so that the estimate can be recalled for any customer. Click the OK button.
- Next, a Memorize Transaction window will appear. Use the Name field to hold a description for this quote (Site Work, etc.), select the radio button next to Do Not Remind Me and click the OK button. You can also close the Create Estimate window.
You’ve now created a pre-set Estimate containing standard items that your company uses for bidding.
Using or recalling the memorized Estimate
The next time that you need to create a bid, follow these simple steps:
- Click the Estimates icon from the Home Page OR from the Customers menu choose Estimates OR From the Lists menu -> choose Memorized Transactions OR press the CTRL and the “T” keys at the same time
- Click on the transaction name -> and click the Enter Transaction button
- Select the Customer:Job, review, add any additional/special items for this project, enter pricing, print, and save.
NOTE: Make sure that you always carefully review the information on the form before you print it to make sure that all of the required items, specific to this job have been included. You can add or remove items from the base group that you initially memorized.
I hope that you have found this article to be helpful, if so please take a moment to leave a comment.
