Quickbooks Enterprise 13 and Above How To Increase Cache Size

These steps apply when the full program is installed on the hosting computer (server) and QuickBooks Enterprise Solutions is on R6 release or later.

  1. Go to the computer (server) hosting the company file.
  2. Go to the QBW.ini file in the Intuit folder:
    • Windows Vista and later: C:\ProgramData\Intuit
    • Windows XP or Windows Server 2003: C:\Document and Settings\All Users\Application Data\Intuit\
  3. Click the QBW file. Note: This file is different from the one you can access directly from the program.
    • Use the information below to add a [DBSERVEROPTIONS] section with your chosen values.
      [DBSERVEROPTIONS]
      InitialCache=256 (if set to 0, meaning “None”)
      MaxCache=512 (if set to 0, meaning ‘Unlimited’)
      Notes about InitialCache: The initial cache setting is used only until the first company file is opened. The initial cache adjusts at that time. Setting it to 0 or none should have no impact on performance.
      Notes about MaxCache: The max cache is the maximum amount of memory QuickBooks database manager can use to communicate with the Sybase database file (company file). Increasing this can improve performance. Setting this to 0 makes it "Unlimited". It is not truly unlimited but actually "Database managed" and can use more memory when needed.
  4. Save the file.
  5. Important! For the .ini file settings you entered to "take affect" you need to turn off Hosting from the file menu and turn it back on.  This will disconnect any user’s sessions that are running at the time.
  6. If you have a "server only install" you can do the steps below:
    1. From the command prompt, type: net stop QuickBooksDB23 (or QuickbooksDB24)
    2. From the command prompt, type: net stop QBCFMonitorService
    3. From the command prompt, type: net start QBCFMonitorService

How to improve Quickbooks Web Connector Speed

For years I have worked to improve the transfer speed of my AbleCommerce Web Connector for Quickbooks. But there’s only so much you can do in code. The rest is up to the internet connection, the speed of the Quickbooks PC and Quickbooks itself.

A client of mine found a document that details how to increase the cache memory allocation of the Quickbooks database engine. So he passed it along to me to try.

Why increase the cache memory you ask? Because the default is a whopping 512mb of RAM. Even if your PC hosting the quickbooks company file has 8gb of RAM, the database engine will only ever use 512mb. Way to keep up with the times Intuit.

Read the attached document and follow the steps. It will require a change to the registry. So know what you are doing, or find someone who does.

The reward is worth it. My larger QB connector clients were taking 2.5 hours to transfer 300+ orders in a single transfer session. Now it takes 20 minutes. Yeah. You read that right. 20 minutes. Same internet. Same PC. 20 minutes instead of 2.5 hours. That’s how much of a difference this can make for your QB installation.

Here’s the PDF.

How to force HTTPS/SSL in AbleCommerce Gold

Just like AbleCommerce 7.x, AbleCommerce Gold uses a specific configuration file to identify which pages/folders should force SSL.  Obviously key pages like the login page, the admin pages, checkout etc are all set to force SSL by default. 

But sometimes you need your own custom stuff to force SSL.  In my case, it’s my Quickbooks Connector for AbleCommerce.  The Intuit Web Connector program will only transfer to a public IP using HTTPS.  Any attempt to do so with straight HTTP will result in an Authentication Failed error response.

To force SSL connection on a specific page/file in your AbleCommerce Gold install, edit the /App_Data/ssl.config file and add the appropriate file name.  You’ll see the necessary syntax with the other entries in the file.

Make sure the file name is in all lower case in the ssl.config AND the actual file on your drive.    Able has had some issues with failing to recognize filenames when the case is not the same between what’s in the ssl.config and the actual file itself.  This was supposed to be corrected in Gold R3 however that has not been confirmed as of this writing.

New version released for QuickBooks Web Connector for AbleCommerce 7

Wow, what an great two months it’s been since I started this project.   So much progress, so many new features.  Few projects have been this enjoyable to bring to completion.

I’ve updated the product description for the QuickBooks module to reflect the new features added.  You can find it here:  http://www.ablemods.com/AbleCommerce-7-Web-Connector-for-QuickBooks-P1C1.aspx

The downloadable Demo version has also been updated.  You can find it easily by clicking the Downloads tab near the top on the home page of www.AbleMods.com

Quickbooks Web Connector Rewrite Features and Improvements

After 6 weeks of work, I have completed testing on the feature list for the new release of my Quickbooks module.  It’s quite a list of work accomplished:

  • Option to set default Sales Rep when creating new customer in Quickbooks.
  • Option to set default Customer Type when creating new customer in Quickbooks.
  • Option to automatically add order note to Notes/History when order is successfully transferred.
  • Ability to update inventory values from Quickbooks back into AbleCommerce.
  • Full support for sub-categorized lists in Quickbooks such as G/L accounts and Inventory Items.
  • Improved error handling.   Error messages now report what specific part of the transfer caused an error and more importantly, why.
  • Improved logging to AC7 Error Log since it already supports easy export for emailing it to tech support.
  • Improved configuration page Bank Account-to-Payment Method assignment.  Now dropdown lists are used so typos cannot cause a transfer error.
  • Improved configuration page layout so parameters are grouped in a more logical way.
  • Improved documentation.  Clarified key points of transfer.  Added new content for new features and configuration settings.  Added new upgrade instructions.
  • Improved efficiency of accessing configuration parameters during a transfer session.  This dramatically cuts down on database hits during transfers.
  • Improved Bill and Purchase Order routines to handle multiple shipments more accurately.
  • Improved Quickbooks list retrieval.  Quickbooks lists are now stored in the database instead of individual files in the /App_Data/ folder.
  • Eliminated second DLL from installation.  Now the entire module is deployable in a single DLL file.
  • Added support for Sales Orders (only available in Quickbooks Premier or Enterprise)
  • Fixed bug when searching by Order Number range in the SetExported page.

AbleCommerce Quickbooks module rewrite nearly complete

Testing has gone very well.  What a project.  Wound up rewriting like 90% of the entire module.  Added several new features, vastly improved error handling and laid a solid foundation for more enhancements.

Intuit has done a decent job of working out the kinks found in the older versions of their QBWC SDK.

The final improvement will be the support for Sales Orders.  Not entirely sure what I’ll do with the payment side of an order being transferred into a Sales Order, but we’ll cross that bridge once I reach it.  I’m waiting now on a QB Enterprise trial from Intuit so I can test.  Buying a full copy of Enterprise just for testing purposes seems wasteful.

I eliminated support for AbleCommerce 7 versions prior to release 7.0.2.  If you’re not on 7.0.2 or later, you should be.

The manual has already been updated.  As soon as I can test Sales Order transfer, I’ll release the new build to the general public.  Then it’s on to the next project !

QBWC sales tax are double taxed

If you are using QBWC aka QuickBooks Web Connector to import invoices or sales receipts to your company file, you may see the sales getting double taxed.

This is caused by having sales tax calculated by the original source of the data (in my case AbleCommerce 7) and then again in QuickBooks.

The solution is to change the default sales tax setting in the QuickBooks company file.  QuickBooks likes to set the default taxable status of a customer to “Taxable”.  However in situations where the data is being imported from another app, it’s quite possible that taxes are already computed.

The setting is easy to find, once you know where it is.   First, load QB and open your desired company file.

Then choose the Edit menu and select ‘Preferences’

When the preferences load, select the Sales Tax category as shown here.   image

Now select the Company Preferences tab in the main window.  You’ll see a dropdown choice for “Your Most Common Sales Tax Item”.  Change that from whatever taxable tax code is listed to a non-taxable tax code.

image

 

If you have a non-taxable sales tax item, just click the Add Sales Tax Item… button to create a new one.

Quickbooks for AbleCommerce 7 rewrite in progress

Well I’ve been working hard for over three weeks now doing a complete rewrite of my Quickbooks Connector that transfers AbleCommerce 7 data electronically into Quickbooks using ASP.Net web services.

The first challenge was getting the entire Quickbooks API written into a strongly-typed ASP.Net class library.  I originally wrote some very basic classes that would serialize.  However it was a hack job as I was very new to XML, serialization etc.  Boy was it confusing. 

Plus none of the Quickbooks response messages were de-serialized back into strongly typed data classes.  I was actually doing a manual XML search on the response text to find any data needed from the responses.  Ugly doesn’t even begin to describe the code.  I never want to see another XML node search again.

Having such clumsy access to the response and query data meant the code was brutal to debug when something didn’t work right.

So I sat down and bit the bullet.  I built 26 strongly-typed objects that all correctly serialize and de-serialize the Quickbooks SDK requests and responses.  These are written exactly to the Intuit SDK specifications.  Testing serialization and de-serialization created some long nights but it was well worth it.

Now any response from the QB web connector can be quickly de-serialized into usable strongly-typed classes.  Responses are even typed correctly so dollar amounts are (double) and logical values are (bool).  Wow does this make things easier to program.

So now the sky’s the limit.  I’ve already implemented full support for sub-grouped GL accounts and sub-grouped inventory items.  This is becoming increasingly more popular with businesses using Quickbooks.

I’ve also vastly improved error reporting.  Troubleshooting has always been a nightmare with the QB module.  No longer though, I’ve got a solid handle on it now that I have strongly-typed responses.

Next I’m going fix another common problem: Inventory.  Many companies use Quickbooks inventory to track their stock levels.  Getting these values back into AbleCommerce is a manual process.  I am going to make this new version of the Quickbooks Web Connector for AbleCommerce 7 synchronize inventory stock levels from QuickBooks into AbleCommerce.

I’m also going to implement another inventory problem, selling in bulk.  Often an AC7 site will sell a case of something but inventory it indivdually in QuickBooks.  That poses a problem with the transfer because the quantity sold will be 1 yet the case contained 250 units.

The new version of the module will allow a "multiplier" value to be set to each product in AbleCommerce 7 so the Quantity 1 sold becomes Quantity 250 when it hits QuickBooks.

That’s all for now, more to follow.  As always, check out my http://www.Solunar.com site for all your solunar times and solunar table needs plus camping, hunting, marine equipment and yes, even boat anchors.

2011 Projects and Ideas for AbleCommerce 7 Customization

Well it’s been a while since I did any blog work, so I’m taking a few minutes to update what’s on the horizon.

Projects in the works:

Reports – there has always been a shortage of reports in AbleCommerce 7.  Specifically reports that subgroup on logical data like State or Country.  Look for some new reports coming to the store very soon.

Quickbooks module – this module will be getting some much-needed love this spring.  First I’d like to see it handle sub-groups in QB.  Currently a General Ledger setup that uses sub-grouping like Sales:Merchandise and Sales:Clothing etc isn’t compatible with my module.  That will soon change, along with some better handling of foreign characters that like to mess up the transfer.  I’d love to map up products so a two-way sync could be done, but every time I look at the idea the effort involved completely turns me away.

Loyalty Points module – I’d love to write a new module that handles loyalty points (aka reward points) for your AC7 customers.  If you have any ideas on how it should work, drop me a note on my Contact Us page – I’d love to have your input on the design.

Returns Manager module – this module saw great success in 2010 and I want to continue that with some improvements in 2011.  First I need to add support for automatic re-ordering.  Then I want to add automatic credits with choices like A) To Original Payment Method B) Gift Certificate C) Manual Check etc.