Donovan has posted his second article at the Intuitive Accountant, “QuickBooks Custom Reporting – A Series: More about Reporting Tools.” This article covers Xpanded Reports, QQube, QODBC and the Custom Advanced Reporting (CAR) tool. To learn more about these tools have a look at the article, and let us know what you think. Make sure that you’re watching for follow up articles in the series.
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.
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.
We are very excited about one of the new features in QuickBooks® Enterprise 2011! We haven’t really seen anything written about it yet so we thought we might as well take the opportunity to do so. The Intuit Press Release “ QuickBooks® 2011 Saves Small Businesses Time in the Back Office” mentions most of the new additions to QuickBooks® Enterprise, however, one thing is not mentioned, the new Open DataBase Connection (ODBC) Driver.
This is not a replacement to the QODBC driver that has been available for some time now from FLEXquarters. It is an additional tool that has been made available by the developers at Intuit. This tool provides direct access to the QuickBooks® database via table views. Alembic has been working with the Intuit development team since early in the development stages of this great tool and we have found it to be a very nice addition. This driver provides strictly read only access to the database, unlike the advanced driver available by FLEXquarters which provides both read and write access. However, when it comes to custom reporting read access is all we need and this driver is FAST! Unlike the QODBC driver that uses the QuickBooks® SDK to build a secondary database to work from, this new driver works directly with the data. We were able to develop a report that pulled thousands of records in just a few seconds.
In addition to being fast we also have access to data that is unavailable via the QODBC driver, due to limitations imposed by the SDK. An example of this improved access is that we can now report on budget information contained within the QuickBooks® file. In the past if we wanted to report on budget data we had to export the info into another program, such as MS Access, pull additional data in, and then develop our report off of that. It was a very tedious and time consuming process. With this new tool this will no longer be an issue!
Since this is a new tool it does take a little bit of getting used to. You must create a reporting user within QuickBooks®, the driver is a File DSN as opposed to a System DSN, and the QuickBooks® file must be opened when accessing the data. We were fortunate to work directly with the development team at Intuit, learning first-hand how tables are related to one another and what relationships are needed to get to the data correctly. It was a great jump start for us.
We look forward to using this great new addition to QuickBooks® Enterprise 2011, which will be available for purchase September 27, 2010. Contact us today to learn how you can purchase this #1 Rated financial solution.
One of our services we have recently received questions on is our Database Maintenance Service. What exactly does that process consists of? While we don’t want to give away all of our secrets, here is a basic overview…
The process begins either by filling out our online form or giving us a call. Once we have some basic information and a basic idea of the problems you are facing, we will then provide you with our initial assessment. Depending upon the severity of any database problems, we will point out where your weaknesses are and how we and/or you could fix them.
Once the more immediate concerns are addressed, we will then make recommendations on what can be done in order to get things with your QuickBooks® database running with the speed and efficiency you would expect.
Upgrades can be a key solution that you might be in need of in order to get your file moving faster and more efficiently for you. As an Intuit Solution Provider, we have the ability to purchase Intuit software at better rates than most individuals.
We do have other processes we can use to help you, which we will recommend on an as-needed basis for each individual client.
Finally, if necessary, we will work with you to get your Database split, which will allow you to start a new file with as much data in it as you would like; however, we have not had to turn to this measure in order to help a client get moving more effectively as of yet.
In a nutshell, that is what Database Maintenance is. If you have any further questions, feel free to contact us.
One of the questions we received last week was from a client asking how to setup the Firebird ODBC driver to work with Fishbowl Inventory. This is something we found to be a bit of a challenge about 10 months ago, the first time we tried to set it up.
The first thing discovered right off the bat was that you must have the Fishbowl Server application installed on the machine you are setting up the ODBC driver on. Our client did not realize this either, it was not in the video they had watched. Please note that you will never need to open the server application on this system, you simply need the files installed.
The other part of the setup process that seems to pose a bit of a challenge is defining the actual path to the database. If you are attempting to set up the ODBC driver on the same machine where the Fishbowl file resides then simply use the Browse button and point to the database. However, if the actual database resides on another system, for example on a company server, then you can’t use the Browse button and you certainly can’t use a mapped drive. You must actually specify the destination system IP address followed by a colon (:) then enter the actual path to the database file. Here is an example… 10.0.0.1:C:\ProgramFiles\Fishbowl\database\data\TestComapny.FDB
After our client used the above information they were able to successfully test the database connection and move forward with their testing. If you have any questions about this setup process, please feel free to contact us.