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.
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.
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.