Donovan has begun writing a series of articles for the Intuitive Accountant. His first article, “QuickBooks Custom Reporting – A Series: Introduction” can be found here. Donovan will be following up this introduction with future articles on Custom Reporting. Have a look and let us know what you think, then keep an eye out for the follow ups.
As you may or may not know ACCTivate! integrates with a handful of shopping carts, but there are so many choices and sites out there that it just can’t have a direct integration for them all. We were contacted by a rather new ACCTivate! user that is currently utilizing one of the unsupported sites; however, they may be moving to a supported site in the next 6 to 9 months.
The Problem: The customer didn’t want to keep hand keying in their Web Orders while on their current system; but they also didn’t want to spend too much on a solution to eliminate manual entry because of the plan to migrate to a supported site down the road.
How Did We Help: After some discussion we learned that the customer could either download a poorly formatted text file or a nicely laid out XML file. We suggested writing a small custom program that would take the XML file and convert it into a comma separated value (.csv) file that could be used with the Sales Order Import feature in ACCTivate!. The customer chose to go with the custom program option so we moved forward with that plan. Now the customer has a custom program that reads an XML file into the program and outputs a perfectly formatted .csv file which they use to import their Web Orders into ACCTivate! via the Sales Order Import function. This has done away with the manual entry work they were doing and has saved time as well as eliminated human error. It is not as automated as the Web Order module that ACCTivate! has, but it was a quick and rather inexpensive interim solution while they decide if and when they will make a shopping cart change.
UPDATE: See some of our Acctivate reports on our Acctivate Custom Reporting Page!
We have been supporting ACCTivate! for some time now and we have been doing quite a bit of custom reporting work for users all over the country. ACCTivate! comes with quite a few canned reports but of course they don’t have it all. Different businesses have different needs and even in the same industry people want to see their data differently.
We have created custom sales reports, product re-order reports, special order fulfillment reports, a wide range of inventory reports and more. We have even had opportunities to create custom applications that interact with the ACCTivate! database. We plan on adding “How We Helped” posts here on our blog when we complete projects that others may benefit from. These posts will include a brief overview of the “problem” and our solution.
We will also be adding “How We Helped” posts for QuickBooks® related projects that you may find since QuickBooks® is one of the products that takes care of all of the accounting for ACCTivate!
My how time flies. Seems like it was just a couple of weeks ago that I was in Florida teaching a handful of Scaling New Heights attendees a bit of what I know about QuickBooks custom reporting. You may have noticed I said a “bit”. Here at Alembic we have learned so much about how QuickBooks data is stored and how to report on it. We get requests from all types of businesses looking for all types of reports and to try to cram in all that we know into an 8 hour workshop is just not possible.
In our workshop we discussed the QuickBooks Custom Advanced Reporting Tool and covered the basics of how it works. Here at Alembic this is our preferred method of reporting on QuickBooks data when our customer is running QuickBooks Enterprise v11 or newer. With this tool Intuit has given us access to views of the “raw” QuickBooks data. We are still limited to the data that Intuit gives us access to, but we love the control we have over the data and the speed of the reports is typically super fast. Another benefit is that the only additional cost to the customer is our development time.
The other tool we covered in the workshop was QQube. QQube is a third party product that brings your data into its own database that is formatted for reporting. I won’t go into what all this really means but trust me when I say it makes it much easier for a user to learn to report on the QuickBooks data; it’s easier to report on a database formatted for reading than one designed for data writing and storage. While we absolutely love the Custom Advanced Reporting Tool it does take a certain level of technical knowledge and skill to master.
While most users find QQube easier to understand and create their own reports with, we find ourselves choosing the reporting method that is going to meet the customers needs best. If you are in need of a custom report or would like help learning how to use one of these tools please contact us today.
Earlier this year Donovan was personally contacted by QuickBooks® expert Joe Woodard, of Woodard Consulting, to present a workshop on Crystal Reports using QuickBooks® Data and QQube by Clearify at this year’s Scaling New Heights Conference at the Walt Disney World Resort in Orlando, Florida. In 2012 Joe was recognized by Accounting Today as one of the Top 100 influencers within the accounting profession. We were honored to receive the invitation from him and the opportunity to present a workshop at the Scaling New Heights Advanced Training Conference.
In order to prepare for the workshop Donovan became a Certified QQube Professional and Alembic is now a Certified QQube Solution Provider. We are excited about this new certification and look forward to working with you to determine how QQube can help you with your reporting needs.
When Donovan returns he looks forward to telling you all about it!
Our friends over at AZ Retail recently asked us to create a report for them that would help update their Constant Contact mailing list more efficiently.
AZ Retail specializes in all things retail including QuickBooks® POS supplies, tagging guns and fasteners, price marking guns and labels, as well as a wide range of other store supplies. When a customer places an order, AZ Retail sends out a “thank you” email via Constant Contact. Generating the list of customers that made a purchase in the last week was becoming a rather time consuming process, and this is why they asked us for a little help.
To help with this problem we created an Excel file that reads their QuickBooks® Enterprise invoices and generates the contact list they can upload directly into Constant Contact. The Excel file has a date range that is used to define the range of invoices they want to report on. When the date is changed, the file automatically looks at invoices and creates a distinct list of customers who have made a purchase within that range; displaying the main e-mail address and company name.*
Now that AZ Retail has this new method of updating their Constant Contact list they can make updates in a matter of minutes so that there is no need to go into QuickBooks® to repeatedly run reports, export them to Excel and modify the data until they get a usable list.
If you are using QuickBooks® Enterprise 11 or higher and want a quick and easy way to update your Constant Contact list call or e-mail us today!
If you are using a different version of QuickBooks® and would like a quicker, faster way of updating your Constant Contact lists please e-mail us so that we can create a solution just for you.
* We are currently reporting the top level customer and not at the job level.
Our utility allows you to update or add a parent name to your QuickBooks® items.
When you created your items list in QuickBooks® did you ever think you would want those items categorized by using parent items? If your answer was “no” but now you think it would be the a great idea but don’t want to go through your 5,000+ items one at a time and check the “Sub Item of” box and select the parent item, then don’t. We have created a utility that will update the parent items for you, whether there’s one currently assigned or not.
Our Update QuickBooks® Parent Name Utility will add your parent items for you then assign them to your items based on criteria you provide.
The utility cost is $595*.
Contact us today for more information on this utility. If you have a specific need that you would like us to help solved please send us a message today.
*This utility is customized to meet your specific needs. For highly complex needs additional costs may apply.
Do you have a QuickBooks®/ACCTivate setup that you need to change the QuickBooks® file in but you’ve been told it’s impossible? Well, the “impossible” has been made POSSIBLE (in many cases).
It is not practical to simply change the QuickBooks® file connected to an ACCTivate database just because you don’t like the one that is currently being used, but when the core data is the same there are times when it is appropriate to make this change. An example could be when your QuickBooks® file is “split” to make it smaller.
We recently worked with a new customer that operates in a QuickBooks®/ACCTivate environment, during the original set up it was decided that an old QuickBooks® file would be used for the division of the company that was using ACCTivate; meaning the company would operate using two separate QuickBooks® files. We decided that the best course of action would be to combine the data from the two QuickBooks® files and create one accurate and usable file that we could connect to ACCTivate. After changing the QuickBooks® file our test sync failed miserably. We quickly learned that what we wanted to do was deemed “impossible”; being new to ACCTivate we had been unaware of this. This was not acceptable! Should our customer “lose” historical ACCTivate data because of improper initial setup? We didn’t think so. So we set out to solve the “impossible”.
We were told that the issue with changing the QuickBooks® file is that ACCTivate stores the GUID of the QuickBooks® transactions that the ACCTivate record is associated with and when a new QuickBooks® file is put into place this link is broken. To us this was merely a “data” issue and in our mind was something we could work with.
The end result is that we solved the problem and have created a utility that allows a user to change the QuickBooks® file used with a particular ACCTivate database. Please note this is not something that should be done haphazardly. Careful thought and consideration must be put into why and how this process should be done, but know that if and when the need truly arises it can be done.
If you have need of, or might be interested in, such a utility please feel free to contact us for further information. After an initial review we will determine whether your file is one that can be re-synchronized. At this time we consider our utility to be in a beta phase and are currently offering this ability to change QuickBooks® files as a service. Upon agreement, we will work closely with you to synchronize your database to a different QuickBooks® file. In time we plan to have a utility available that will enable users to make this change on their own.
Although we were told what we wanted/needed to do was not possible, we were determined to develop a solution and in the end made the “impossible”… possible!
We were recently asked by several different users how the QuickBooks® Custom Reporting Tool interacts with Microsoft Excel and Access. We have found the Custom Reporting Tool to be very helpful and the interaction between it and Crystal Reports is great. However, we have noticed that there is an “issue” in the way that the product works with Access and Excel, which is what we want to talk about today.
The QuickBooks® Custom Reporting Tool works off of a File Based DSN. This means that the connection info is stored in a file which is located in the same folder as the QuickBooks® file. This file contains the “name” of the database that is being referenced. The “issue” is that the database name changes every time QuickBooks® is closed and reopened. For a product like Crystal Reports this isn’t an issue, because Crystal goes out to the file DSN and reads the database name every time it establishes a connection to the data. The Microsoft products work a little differently though.
When you connect Excel or Access to QuickBooks® via the Custom Reporting Tool instead of going out and reading the database name every time, it stores the name in the application the first time you establish the connection. The problem with this is that Intuit is changing that “name” every time you close and reopen the QuickBooks® file. This means you have to “re-link” the data connection every time you want to use the Excel/Access file you created. For example, in Access you have to go to Database Tools > Linked Table Manager and refresh the linked table information. While this is not overly difficult, it is a pain.
If Intuit didn’t change the database name OR if Microsoft didn’t hardcode the database name in the connection then we wouldn’t have a problem… BUT the “issue” is that they do. We have been in touch with the developer at Intuit several times about this issue and at this point they have not made any changes. They have informed us that they are looking into the issue, but we don’t know if or when they will make any changes to the way they handle the database name.
Hope this clears things up a bit on why the “issue” occurs, but unfortunately at this time there is no “good” way to make the two products play nicely with one another.
Update: Intuit has now posted detailed instructions for all versions of QuickBooks®. You can view these instructions here, but please feel free to continue to ask us any questions you might have.
Update: It has been brought to our attention that in the latest release of QBES 12 and all versions of 13 Intuit has possibly implamented a change that would affect the memory allocation discussed in this artical. I have not been able to test this at this time but I will post a new article once I have some results. The change in question could reset the RAM allocation everytime QuickBooks® starts up, which would cause an issue with the changes discussed in this post. Again more info will be posted soon.
QuickBooks® speed and performance is something that we all deal with. As our QuickBooks® files get bigger the worse performance becomes. When you are dealing with Enterprise the typical file begins to demonstrate performance issues at around 500MB while Pro and Premier files typically show degradation around 256MB. There are two reasons for this, first the number of transactions and list sizes create more data for the software to work through. The second reason is what we want to discuss in depth with this post and seems to be something most people don’t know about.
In any system the more data you have in the database the more of an effect you will see on the overall performance of the system; however in a SQL or Oracle type environment you don’t see these types of performance issues with this amount of data. I don’t have a lot of background in how all the various systems utilize memory and other system resources but I have learned quite a bit about how QuickBooks® seems to work, at least in the area of RAM usage.
When QuickBooks® is installed there is a setting in the registry that tells the system how much of the system’s RAM to utilize initially and the maximum amount it is allowed to use. In Pro and Premier these numbers are 128MB and 256MB, in Enterprise they are 256MB and 512MB. What does this mean to us? Well, the amount of RAM set is going to be how much of your QuickBooks® file is loaded into cache. It also means that initially it will load the amount of data equal to the lower number, and as the file is used more data will be loaded into cache. For example, if you have a large file and you open it, 256MB of the data will be loaded and then if you run an A/R Aging report more data will be loaded and so on.
Let’s look at this in relation to a “large” QuickBooks® file. I like to use our first introduction to a “large” file as an example for all of this. We were contacted by a customer with a QuickBooks® file that was 1.2GB in size and it contained about a year and a half worth of data. The client was completely against starting a new file, like most users they wanted to be able to see history when viewing reports and such. We started with basic troubleshooting, things like network setup and such. Nothing we tried was helping the fact that a 10 line invoice took 3 minutes to save, and that the user would often surf the web between entering invoices.
We had to find a way to resolve this issue because honestly the customer was close to abandoning QuickBooks® altogether. One day while speaking with an Intuit employee something was said about this registry setting that could possibly help the situation. After a little research we found the setting and applied the change on the clients system. We changed the setting to allow for 2GB of RAM to be dedicated to QuickBooks® to allow for file growth. Invoice processing went from 3 minutes to 3 seconds, and we had a very happy customer. Since then we have had a customer that has a 3.8GB file and a 40 line invoice took about 6 minutes to save and now it takes about a minute.
Here are some things to understand about this registry change. First off, the numbers mentioned above are the defaults; however these may not be the settings your system is using. I have seen situations where the maximum setting was 32MB! This means users began to see performance issue after the file reached only 32MB in size, and in an Enterprise file that isn’t very much data. The only thing I can come up with on this one is that the system did not have the typical 512MB of RAM free at the time of install so QuickBooks® determined that 32MB was the “safe” amount of RAM it could use without causing major issues for the system. With that being said, I like to check any system I get my hands on to verify that it is at least using the default amount of RAM. Also, the system information screen (F2) always shows the default numbers at the bottom of the window no matter what the settings are, so don’t go by those.
Something else to keep in mind is that the registry setting will reset when an update is installed, so you will have to make the change again after you update the system. Please note that this is on the system that is hosting the QuickBooks® file (the “server”) and not on the client.
I could continue to talk about this for some time, but I think the basics have been covered here. The only other thing I would like to cover is when multiple files are being used at the same time. You will need to ensure that you have enough RAM allocated to accommodate the total of all the files that are being accessed at the same time by all users.
The final thing I would like to add is that we are changing the system registry and that it is always recommended to make a backup before any modifications are made.
This is not a change that will allow for unlimited file growth, but it will help you get more out of your QuickBooks® file before having to split your file or start a new one. I hope this has been an educating and helpful piece of information for everyone that reads it and even more importantly I hope it helps make for happier QuickBooks® users!
If you would like to discuss this further, have questions or would like to have detailed instructions on how to make the registry change please feel free to contact us at firstname.lastname@example.org.