We’ve moved our site! Please visit the new QuickBooks® Resource Center site! You will find all of our previous blog posts as well as information about the QuickBooks® Resource Center, our services, and how We Make QuickBooks® Work For You!
We’ve Moved!
September 27, 2013Teaching at Scaling New Heights 2013
June 25, 2013Earlier 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!
Constant Contact and QuickBooks
March 15, 2013Our 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.
Update QuickBooks Parent Name Utility
January 24, 2013Our 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.
Connect a New QuickBooks® File to Your ACCTivate! Database
May 11, 2012Do 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!
Custom Advanced Reporting in QuickBooks® Enterprise and Microsoft Products
February 22, 2012We 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.
Increase QuickBooks® Speed and Performance
September 19, 2011Update: Intuit has now posted detailed instructions for all versions of QuickBooks®. You can view these instructions here, but please free to continue to ask us any questions you might have.
********************************************************************************
We’ve moved our site! Please visit the new QuickBooks® Resource Center Site… You will find our previous blog posts (such as this one) as well as all updates and more information about how we help with all your QuickBooks needs and questions.
********************************************************************************
Update: It has been brought to my 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 quickbooks@acsi-az.com.
Oasis CRM Purchas Order and On Order Issues
March 24, 2011Some time ago we identified an issue with Purchase Orders and the quantity that Oasis was reporting as the “On Order” amount for items. There were problems when a PO was created, and left in the Purchase Request status. When the “Remove” link was used the system would subtract the quantity on that order from the total On Order number for that item. The problem with this is that the quantity doesn’t get added to the On Order number for an item until the PO is put into a Submitted status. So, what was happening is a user would create a PO and add several line items to the order and leave it as a Purchase Request. Then another user would review the order and determine that a particular item was not needed so they would remove it. The behind the scenes work that was going on was as follows: the items were added and the On Order numbers would not increase (let’s say there zero on order), then when the user removed them the system would reduce the On Order quantity by the amount on the order (let’s say we had 10 items on the order). So, in the end the system would indicate that we had -10 On Order.
That problem has been corrected, however there is no real way to know what you’re On Order numbers should really be at this point. Depending on the quantities that you order you could be showing that you have 1000 items On Order; but because of this issue you might actually have 1500. At some point though you had removed 500 from an order and that made your number 1000. To correct this issue we have created a Crystal Report (but you could use the reporting tool in Oasis) showing all the items we have on order, groups them by warehouse, and provides totals for each warehouse and item. Once we have that information we can then go into Oasis, open the item in Edit mode and update the On Order quantities in the Stock tab.
This is a problem that could go unnoticed for quite some time, especially if the numbers don’t go into the negative. There are some companies that rely more on the On Order numbers than others but no matter what we always want to ensure that the data is as accurate as possible. So, if you think you might have run into this problem and would like assistance in cleaning it up please contact us today.
Moving Memorized Transactions in QuickBooks
March 22, 2011We recently had a situation where we needed to combine three QuickBooks company files into one single file. Overall this wasn’t extremely difficult and we had a plan in place to take care of all the tasks at hand; that was until we got a look at the list of memorized transactions that the client wanted to include.
QuickBooks allows you to export most of the available lists, but the Memorized Transactions list is not one of them. Fortunately there are a couple ways to get your Memorized Transactions from one file to another.
The first way that you can accomplish this is to remove all transactional data from a copy of your file and once complete use this as the base for your new file. The second way to handle this issue is to run all your memorized transactions with a date in the future. Then use a tool such as the Data Transfer Utility by Karl Irvin to transfer those transactions to your new file. Once they have been copied you can then memorize them and you will now have a new Memorized Transactions list.
If neither of these works for you then you have one option left, and that is to manually recreate all of your memorized transactions. If you would like to use one of the methods above and need assistance please feel free to contact us, we’d be happy to help.
Can’t Pay an Employee? Could Be a QuickBooks Set-Up Issue…
March 9, 2011Question: We just added a new employee into QuickBooks last week and I am trying to run payroll but the new employee isn’t showing up. I have verified all the employee’s data and I can’t see why they aren’t showing up. The hire date is 2/22, their earnings are filled in and the taxes are all setup, but I still can’t run 2/28 payroll for this one employee. Why?
Answer: I began to review an employee record and scan for a field that might just cause such a problem. As I completed the question, “What do you have in as the Payroll Schedule”, the customer stated that she fixed it. I asked what the problem was and she said, “I didn’t have a Payroll Schedule entered.”
In this case it really wasn’t a problem with the software it was a simple oversight by the user. But how often is that the case? We often overlook something during set up no matter how hard we try to be vigilant. This post is more of a reminder to always take your time and go slow whenever setting up anything within QuickBooks. The problems that improper setup can cause are not always as noticeable as “I can’t pay my employee”. If you setup an item incorrectly you could cause activity in incorrect accounts which could go unnoticed for some time. Once you’ve realized there is a problem it could take a while to determine the source of the issue, and in the mean time you could be having problems at all sorts of levels.
So the lesson here is take your time, fill in all the data, then double check, and triple check your information.
Alembic Computer Services, Inc. 