Just a quick post as I was having this problem when creating a custom program for a client using the QuickBooks SDK and I couldn’t find much help on this when I searched. I was trying to update the class field on transactions using a program, and everything was working fine until I got to Transfers. Transfers were added to the QuickBooks SDK in version 12. When I first started my program I used version 9, which of course would generate an error as that feature wasn’t supported in version 9.
After switching the project to version 12, I started getting the error “3231 – Status unprocessed” with the error message “The request has not been processed.” There was little to no information about how to resolve this error that I could find. After spending time researching I was about to give up, but on a whim, I decided to try installing the latest patch for my QuickBooks 2013 software (the R12 patch). Luckily, after installing the patch, the program worked fine and I no longer received the 3231 error code.
Even though I tell all my clients that they should install updates when QuickBooks prompts them, I was not following my own advice. The lesson has been learned and I will now start to practice what I preach!
QuickBooks Statement Writer is an Excel Add-in and runs within Excel. The Statement Writer Pane is where you make changes to your financial report.
In the Statement Writer pane, you can do the following:
- Open or close the five sections of the pane.
- Change specific properties within each section of the pane.
- Move or re-size the pane.
- Display the pane by clicking the Show Statement Writer Pane icon to restore it.
- Change report date or basis
- Choose a header/footer setup
- Refresh from QuickBooks
- Edit the report appearance
- Change the statement title
- Add or remove a Class Filter
- Add or remove a Job Filter
- Set display options for zero-balance accounts
- Change the Balance Sheet Round-off Account
- Refresh from QuickBooks
- Format the statement date
- Delete the statement from the report
- Change the row type and name
- Manage accounts in the row – combine, add, separate, zero-balance settings, and reverse sign.
- Insert rows into the statement
- Delete rows from the statement
- Customize a formula
- Change the column type and heading
- Insert columns into the statement
- Delete columns from the statement
- Override a value on a temporary basis
- Override dates
- Reset date overrides
- Insert fields – accountant and client information, basis, and dates
I was at the Intuit Reseller Conference a little over a week ago and as has been the case over the last few years, all roads led to the cloud. (A short sidebar: Can I tell you how much I hate the word cloud when used in technology circles. At the conference I began counting how many times the speakers used the word “cloud” and had to stop at 100 after only two hours. Not to mention that my 73-year-old mother, who now has a smart phone, keeps referring to her cloud. As if she walks around with one like a balloon.)
So why are we talking about this you ask. Well, if you Google “QuickBooks in the Cloud” the first two QuickBooks sites that come up show two very different services. And quite frankly I spend a lot of time with new business owners explaining the difference so I’m thinking people are a bit confused.
So what is this QuickBooks in the cloud? Let’s look at these two Intuit sites. One is for QuickBooks Online, the other QuickBooks Hosting Services?
We’ll start with QuickBooks Online. QBO (Intuit loves their acronyms) is a web-based application. According to the ever-informative Wikipedia a web app runs in a web browser or is created in a browser supported programing language and relies on a common web browser to render the application. My non-technical translation of this is it’s a program that runs like a website. You use the web page to enter information, the back arrows to go back to the page before etc.
Onward to hosting. It was hard for me to find a simple definition for this service so please bare with me on this one. Cory Janseen of techopedia.com uses the phrase “hosted desktop” to define this idea. A hosted desktop is a virtual machine that hosts the operating system, applications, data and other system configurations of a physical desktop. A hosted desktop provides similar functionality and capabilities as a physical desktop. Yikes! Basically you open up a web page and what you see is something that looks just like your computer. It has QuickBooks Desktop Edition loaded on it; MS Office, whatever, and you can use that application from anywhere. From your office desk, your home, while on vacation, okay hopefully not while you are on vacation.
So what have we learned? One, that Intuit needs to figure out which cloud balloon to carry around and if not that, two, that there are actually two different QuickBooks in the Cloud. Wow that was a mouthful of cloud.
Look for my follow up post in a few days on QuickBooks Online. Why I think you should look at it again.
Workflow and reporting
We’ve discussed in the past the different ways to get reports out of QuickBooks that are not part of their out the box list. There are needs and quirks to each company that differentiate what they are looking for and having the options to pull data and compose it in a manner that makes sense to them and saves them time is extremely valuable.
With that said, reporting on data can always be garbage in garbage out. Without ensuring certain workflows are followed within your database, any report you generate will be useless. Setting up the proper workflow is as important as getting the right report which involves many variables. How is responsible for entering the data? What is the type of data which is being entered? Are there checks and balances? How do we reconcile the reports that are being generated?
The first step to ensuring you are able to get the data is finding out what the end result should be. Often we run into situations where the end-user wants something, but they are unsure exactly what it is they want. Establishing the end result will help you configure the proper processes to get you to that point. Once that is settled, figuring out who does what is equally as important. Flowcharts and organizational charts with users and roles are a good starting point and documenting every task will get you in the right direction.
An interesting project we are currently working on involves all of this. Not only are we writing complex reports that will give the end-user valuable information at the click of a button, we are also putting together the workflow to get us to that point. In the next few posts we will discuss what steps we are taking to make sure our client gets what they need.
One of my clients recently started using Sales Orders in QuickBooks to help manage their inventory levels. All new orders were placed in QuickBooks as a Sales Order, and as the order got shipped out, they would invoice for the portion that was mailed. This allowed them to easily see what was still left to ship, and they were able to invoice for the goods delivered to the customer, which is good accounting practice. However, when looking at the invoices, they did have some questions about the zero lines that get produced when doing a partial invoice.
For those of you who don’t use Sales Orders, or don’t use QuickBooks, if the order has multiple product lines, you are able to select quantities of each of the goods to invoice. This means that if the customer ordered 10 widgets, but you only shipped 6 of them, you can invoice for just the 6. If the Sales Order also had 5 sprockets listed on it, you could invoice for 0 sprockets. When you select this option in QuickBooks, the invoice will show a line for 6 widgets and a line for 0 sprockets. Because the client was also printing packing slips from QuickBooks, they though it would be best to not include the items that didn’t get shipped. Luckily, there’s a way to do this in QuickBooks.
Tucked away in the preferences screen, QuickBooks has an option to not print zero quantity items on the invoices. You access this preference by going to Edit > Preferences, and then selecting Sales & Customers on the left side, and finally selecting the Company Preferences tab. Because this is a company preference, you will need to be logged in as the Admin user and also in Single User Mode. Along the right side there is a checkbox that says, “Don’t print items with zero amounts when converting to invoice”. This will prevent these line items from printing on the invoice. They will still show on the screen, but your printed/emailed version will not show them. Keep in mind that this ONLY works for invoices that were generated from a Sales Order. If you manually created an invoice with a zero line amount, it will always get printed.
Use the Report Designer to set up and customize a report with the statements and documents of your choice, then save a statement or the entire report as a template that you can re-use. Below you will find all the information you need to get around in the Report Designer.
1 – Report Content.
On this screen, you will be building a “bundle,” or a collection of documents and statements that will be included in the financial report.
In the Report Content screen, simply use the basic features and navigation to:
- Choose everything you want to include (title page, balance sheet, etc) in the report; and then
- Organize your selections in the order you want.
- Then, on the following screens, you’ll be able to customize each document and statement you chose, and you’ll also have an opportunity to save a statement, or the entire report, as a template that you can use to create a similar report later on.
2 – Columns.
Use these key features to customize columns for each statement in the report.
- Add a column: In the Drag/Drop Columns list, select a column, then drag-and-drop it into the statement. You can also right-click a column in the statement to display a menu, then make a selection.
- Edit column header or dates: In the statement, right-click a column, then click “Format Column Heading” or “Edit Column Dates,” or now use the new tool bar buttons for these functions.
- Move a column: In the statement, select a column, then drag it left or right.
- Delete a column: In the statement, right-click the column, then select “Delete Column.”
3 – Rows / Accounts.
Use the following features to customize rows and accounts for each statement in the report.
- Customize Rows…
- Add a row: In the Drag/Drop Rows list, select a row, then drag-and-drop it between the desired rows in the statement. You can also right-click a row in the statement to display a menu, then make a selection.
- Assign a row for a missing account: In the Accounts toolbox, select a missing account, then drag-and-drop it between the desired rows in the statement.
- Move a row: In the statement, select a row, then drag it up or down.
- Delete a row: In the statement, right-click the row, then select “Delete Row.”
- Customize total row types: There are four new total row types, all of which are customizable in Excel. To change the formula for a total row in Excel, simply go to the Row Properties area of the Statement Writer pane. Please note that you can change only the SUM formula on this Rows/Accounts screen in the Report Designer – simply double-click it to make changes.
- Tip: To see specific definitions of all row types, including the totals, please see the new “bubble help” which is available by placing your mouse pointer over the blue text at the top of the Rows/Accounts screen.
- Roll up or Separate Accounts…
- Roll up accounts: In the statement, press CTRL while selecting the rows you want to combine, right-click to display the menu, then select “Roll up Selected Accounts.”
- Roll up missing accounts: In the Accounts toolbox, select the missing account you want to add, then drag-and-drop it directly onto the desired row in the statement.
- Separate accounts: In the statement, pointing to a row with a Roll up indicator will show a list of accounts in the row. To separate them, just right-click the row, then click “Separate Accounts.”
4 – Headers & Formatting.
Insert Headers and/or Footers…
Use the key features below to customize headers and footers on each document in the report. Later, in Excel, you will have another chance to edit headers and footers by clicking the Statement Writer button, and then selecting Edit Header/Footer
- Insert header/footer fields: In the Drag/Drop fields list, select a field, then drag-and-drop it into the desired header or footer area at the top or bottom of the statement. You can also right-click a header or footer area in the statement to display a menu, then make a selection
- Use copy/paste: You can perform basic copy and paste functions via the right-click menu.
- Type your own custom text: In the statement, place your cursor in the desired header or footer area, then simply begin typing.
- Add Formatting…
- Use the features below to customize the format of your documents and statements in the report.
- Apply Standard formatting: There are basic formatting buttons (font, bold, italics, etc.) on the tool bar, and we recently added several new ones for improved customization. Just select a cell, then click the style you want on the tool bar. Please note that when you select a cell and make a format change, you are changing the format for the style in the cell, so the change will then be reflected across ALL cells with this style. To change the formatting for one single cell, you can do so in Excel.
5 – Review & Finish.
Use the features below to review the report information, save a statement or entire report as a template, and then create the report.
Note: Because you will not be able to print the report from this screen, you must create the report and then later save the report as a PDF in Excel before you can print it.
- Review Statement Information: This tab displays the report information before loading data into Quickbooks. This allows you to see any issues and make any changes needed before creating the report.
- Memorize statements as templates: Use this feature to memorize statements to use again (even across clients). To memorize the current statement, click on the tool bar. To memorize all statements and documents as a report group, click . Then, save time by using your custom templates to create reports for new clients with a similar chart of accounts.
- Launch report in QSW/Excel: To see the actual data, customize formulas, and print, you must launch the report in QSW/Excel by clicking the Create Report button (shown here).
The reporting functionality in QuickBooks is one of the better reporting implementations I’ve seen. The amount of customization that you can do in the reports, and the way you can create reports is one of the strengths of QuickBooks. There are some times, however when you need information that you just can’t get out of QuickBooks. One of my clients recently was going through an audit. They wanted a report that would show all of their payments from a customer and show which invoices they were applied to. They also wanted to see a list of deposits and which payments were included on the deposit. Showing ‘linked’ transactions is not something that can be reported on in QuickBooks. There is the option to view a transaction history, but you have to be in QuickBooks and you can only view one transaction at a time.
I was able to create an external report that pulls the data from QuickBooks to show this information. The report allows you to filter for a specific date range, and also allows you to filter for different transaction types (Deposits, Payments, Credit Memos, or Invoices). Here’s a sample of what the report looks like:
If you’re interested in getting this report for yourself, feel free to contact us using the Contact Us link at the top of the page!
We have some clients who have switched to using QuickBooks that used to use Peachtree (now called Sage 50) software. The transition is usually smooth, though there always seems to be one hiccup along the way. After importing the open AR transactions, the clients will run the A/R Aging Detail report from both software and compare and they will always notice that the QuickBooks version of the report looks different than the Sage 50 version. The numbers are the same, but the Sage 50 report shows the detailed transactions, as well as the 0-30, 31-60, 61-90, etc. columns. They always ask to get the same style report out of QuickBooks. The detailed report in QuickBooks gives you the transactions, but only gives you one column for the number of days aging.
Ever since Intuit has opened their data to third party programs, we here at Presti & Naegele have been working to get as much useability out of the software, and bringing in third party programs if QuickBooks wasn’t able to do everything a client needs. We have been working with Crystal Reports, and have now been able to generate an A/R Aging Detail Report from QuickBooks that mimics the Sage 50 style. It include the details of each transaction, totaled by customer, an breaks out each invoice open balance into separate columns based on how far past due the invoice is. It even works if the customer has an outstanding credit on their account. Take a look at a sample:
Now even if you’re used to looking at the Sage style report, you can still utilize QuickBooks and no longer need to export the data out to Excel for manual formatting! If you’re interested in getting this report for yourself, feel free to contact us using the Contact Us link at the top of the page!
The QuickBooks Statement Writer is a tool we use fairly often with our clients and allows us to create reports we normally would not be able to generate using the stock QuickBooks reports. In the next few posts we’ll discuss some tips and tricks that we use when working with the Statement Writer.
To access the QuickBooks Statement Writer go to Reports>
Once selected you will get to the main window for the Statement Writer:
From here you can set your preferences which we will get into later, open reports and design new reports.
Designing new reports or Financial Statements is initially done through the Financial Statement Designer. The Financial Statement Designer gives you a large selection of customizable financial statements from which to choose. It includes multiple balance sheets, income statements, statements of cash flows, as well as compilation and review letters. With this program you can create a standard set of financial statements that are in accordance with Generally Accepted Accounting Principles (GAAP).
In our next post we will walk through the steps in creating a new financial statement.
A lot of times in my job, I have to troubleshoot problems people have with QuickBooks. I’ve been doing this for many years, so I typically have an idea what the question is about, and what the common error messages say. There are times however, when I get an email or a phone call from a client who needs my help with something I’ve never seen or heard about before. Or at least I don’t recognize the problem. This can be due to some new feature or bug in the QuickBooks software, but it’s more often because the customer doesn’t give me all the information, or gives me incorrect information. So today, I’m going to post some pointers about how to answer and ask questions to get good support.
One of the most important things to keep in mind when getting support from someone is that they are not mind readers. They only have the information that you give them. This means that your description should have details about the problem you are having. For example, if you take your car to the mechanic and say, “My car doesn’t work. Fix it”, they’ll have no idea what to do (and will probably just do random things to charge you). Giving details about the problem will allow the person to narrow down their focus and ask the questions to help get to the root cause.
With that in mind, if you get an error message, either have it on the screen, or have a screenshot or printout of the error. There are many errors that have very similar wordings, but mean completely different things. In QuickBooks, when opening a file, you can get many errors where the only difference is the error number. Error 6000 is very different than error 6005.
Keep in mind that troubleshooting is really just eliminating possibilities. This can sometimes be easier if there’s a specific error message, but even with the message, finding the root cause may take some testing to eliminate the possibility. Let’s say you get an error message that there’s no paper in the printer. You have double checked that there is paper, but still get the error. There’s a reason the printer is reporting that there’s no paper, but to find out the real reason, you have to eliminate some possibilities. Taking the paper and and putting it back in, turning the printer off and turning it back on, ensuring you have selected the correct printer, etc. All of these steps are designed to eliminate reasons why the printer would think there isn’t any paper in the tray.
The person that you are working with to fix an issue may have a lot of knowledge, but many things (especially computers) can have multiple configurations and setups. Even if you purchased the exact same computer, software installed could have an effect on what you are doing, even if your neighbor doesn’t have the problem. Even though my co-workers have much of the same software as me, and our computers are the same, my computer behaves differently.