Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Quickbooks Iff Export From 4.3.3 Rate Topic   - - - - -

 
  • mattcaswell
  • Junior Member
  • Members
  • Join Date: 06-Oct 09
  • 79 posts

Posted 19 July 2015 - 07:55 PM #1

Using Ulitmate 4.3.3 and in 4.2, when I would export a sale in Quickbooks IFF export, I only had to go in and change some tax values from N to Y and add some other stuff.. (CS-Cart never fixed it from over a year ago.. I guess they don't really care)

Now that I've updated to 4.3.x, I have to do the following to be able to get an export from CS-Cart that Quickbooks will accept. I have to open the order in Excel and do the following:
  • INSERT BLANK LINE ABOVE THE TAX LINE
  • MOVE SHIPPING ABOVE TAX TO THE BLANK LINE
  • CHANGE TAXABLE TO Y FOR EACH ITEM
  • ADD AUTOSTAX TO COLUMN N FOR TAX LINE.
After doing these things I can import the iff into quickbooks and the taxes are correctly entered.

I have NEVER been able to do a clean import into Quickbooks from CS-Cart IFF export if there's sales tax involved.

Since nobody else is having this issue.. haven't seen it listed in any search returns, I'm going to assume that I must have some settings for my taxes in CS-Cart wrong. I've read over all the directions on how to do it and still can't get it to work for export/import.

If anyone else is using the Quickbooks export IFF feature and it's doing clean imports for you, I would love to talk to you to compare your settings. I would be willing to compensate you for your time.

Thanks,
matt caswell
store. dryheatembroidery.com

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 23 July 2015 - 01:29 AM #2

I've import IIF files since 2009. I'm still using CS-Cart v3 for my live site and will have to get back to work on the export script for v4. I did some of what needed to be done, but there's more work to do. The big problem with the v4 process is that CS-Cart developers moved the whole process to a PHP script and kept all the old problems. I don't know why they even bothered. None of the old problems were fixed, and the method for v3 was easier to edit (and fix the mistakes).
I import state sales tax too.
It does not go in as a line item when it's properly included in the IIF file, it goes where QB expects it be. and then it is auto calculated into your sales tax reports. If I recall correctly, sales tax MUST be the last item in the record to import properly, and yes each item that IS taxable needs to be Y for taxable, even if you are not charging sales tax. And I had to include AUTOTAX in my version for v3.
Everything else is a line item, including Postage, Reward Points, Discounts.

I just export the data, and import it into QB without even looking at it - it's that clean. I haven't had any errors since v3 came out.

I'll get back to work on this in about 3 weeks. It must be finalized. I'll share the work. What version of QB are you running?

This is an example of an order WITH Sales Tax, Shipping, Reward Points and a Discount:
TRNS INVOICE 01/02/2015 Accounts Receivable Fabrice, Joe Website Sale 136.95 3136 Website Order:  Joe Fabrice 1313 Mockingbird Ln  "Perrysburg, OH 43551" United States  Y USPS Priority Joe Fabrice 1313 Mockingbird Ln  "Perrysburg, OH 43551" United States  N
SPL INVOICE 01/02/2015 Sales Fabrice, Joe Website Sale -20 3136 Yellow Balls | 7/8 Inch 20 -1 30696 Y Tax
SPL INVOICE 01/02/2015 Sales Fabrice, Joe Website Sale -36 3136 Blue Balls | 1 1/8 Inch 36 -1 30695 Y Tax
SPL INVOICE 01/02/2015 Sales Fabrice, Joe Website Sale -120 3136 Blue Balls | 2 5/8 Inch 30 -4 30702 Y Tax
SPL INVOICE 01/02/2015 Sales Discounts:Reward Points Discounts Fabrice, Joe Website Sale 45.9 3136 Reward Points Redeemed -45.9 -1 POINTS USED N
SPL INVOICE 01/02/2015 Sales Discounts Fabrice, Joe Website Sale 8.8 3136 Order Discount -8.8 -1 DISCOUNT N
SPL INVOICE 01/02/2015 Shipping and Delivery Income Fabrice, Joe Website Sale -5.95 3136 USPS Priority with Tracking 5.95 -1 SHIPPING N
SPL INVOICE 01/02/2015 Sales Tax Payable Ohio Sales Tax Website Sale -9.7 3136 Ohio Sales Tax 8% -1 Ohio Sales Tax N  AUTOSTAX
ENDTRNS

This is what the last line of an order without sales tax looks like:
SPL INVOICE 01/02/2015 Sales Tax Payable Ohio Sales Tax Website Sale -0 3137  0 -1 Out of State N  AUTOSTAX

Every order needs to have its sales tax declared. I have a taxable value set up as Out of State in QB. All the other custom values in this export for Ohio and Points and Discounts were fixes I had to add to the v3 process.

CS-Cart Ultimate ver 4.3.5


 
  • imac
  • Head of Product
  • CS-Cart Architects
  • Join Date: 22-Nov 05
  • 2073 posts

Posted 24 July 2015 - 01:20 PM #3

Using Ulitmate 4.3.3 and in 4.2, when I would export a sale in Quickbooks IFF export, I only had to go in and change some tax values from N to Y and add some other stuff.. (CS-Cart never fixed it from over a year ago.. I guess they don't really care)

Now that I've updated to 4.3.x, I have to do the following to be able to get an export from CS-Cart that Quickbooks will accept. I have to open the order in Excel and do the following:

  • INSERT BLANK LINE ABOVE THE TAX LINE
  • MOVE SHIPPING ABOVE TAX TO THE BLANK LINE
  • CHANGE TAXABLE TO Y FOR EACH ITEM
  • ADD AUTOSTAX TO COLUMN N FOR TAX LINE.
After doing these things I can import the iff into quickbooks and the taxes are correctly entered.

I have NEVER been able to do a clean import into Quickbooks from CS-Cart IFF export if there's sales tax involved.

Since nobody else is having this issue.. haven't seen it listed in any search returns, I'm going to assume that I must have some settings for my taxes in CS-Cart wrong. I've read over all the directions on how to do it and still can't get it to work for export/import.

If anyone else is using the Quickbooks export IFF feature and it's doing clean imports for you, I would love to talk to you to compare your settings. I would be willing to compensate you for your time.

Thanks,
matt caswell
store. dryheatembroidery.com


hi,
Matt, sorry to hear that. But this actually true, mainly because Quickbooks is not very popular.

Can you please provide me with two files, one default CS-Cart iif file, and another one already corrected by you. I will check if we can fix this in default CS-Cart.
Also please let me know what version/edition of Quickbooks are you using.
Ilya Makarov,
CS-Cart Architect Team
Suggest and vote for new features | Report a bug

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 24 July 2015 - 03:55 PM #4

imac, I did all of that, and more, with Sergey Minukevich back in March 2013. Support ticket 100260360.
I provided all kinds of details for solutions to the erroneous formatting of the IIF export. Nothing changed.
The last dialog included this:
"Also, Quickbooks will only import Sales Taxes totaled. If you attempt to import Sales Taxes as line items, they will not be calculated properly for Quickbook's Sales Tax Agency reporting. In an order import, they must be the last item imported, and include the proper information for them to appear in the correct field in Quickbooks."
His response was:
"As far as I understand the "Sales Taxes" section should be commented out so we can add an option to the add-on settings for it."
I'm still not sure what that means, but it didn't matter as nothing has changed with the IIF format.
What DID change is the export process that builds the IIF file was converted to a PHP script (\app\addons\quickbooks\controllers\backend\quickbooks_export.php) that is FAR MORE difficult to edit than the "addons/quickbooks/views/orders/components/export_to_iif.tpl" template that was included in CS-Cart thru version 3.
Weirdly, "\design\backend\templates\addons\quickbooks\views\orders\components\export_to_iif.tpl" exists in v4, but is not used.

CS-Cart Ultimate ver 4.3.5


 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11302 posts

Posted 24 July 2015 - 07:24 PM #5

Matt, sorry to hear that. But this actually true, mainly because Quickbooks is not very popular.

Are you kidding? In the US (a significant portion of your customer base) quickbooks is VERY popular and would be considered the defacto standard for small business accounting. The only reason you don't get more complaints is because it's assumed that it doesn't work (hasn't since V2 anyway) and if IFF is not done perfectly, it will corrupt QB (which would be a disaster).

It shouldn't matter how "popular" a feature is. if it's implemented then you need to support it and correct defects within it.

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 24 July 2015 - 08:16 PM #6

I agree Tony.
Native support for Quickbooks was one of the primary reasons I selected CS-Cart as my shopping cart in 2009 (v2).
I had to teach myself how to create IIF files so that I could use the addon though - the process right out of the CS-Cart box is full of problems. BUT the logic is still built into CS-Cart, so all I had to do is to was format the output to IIF file properly.
Without any Quickbooks support, I would have selected a different cart.

CS-Cart Ultimate ver 4.3.5


 
  • mattcaswell
  • Junior Member
  • Members
  • Join Date: 06-Oct 09
  • 79 posts

Posted 26 July 2015 - 12:19 AM #7

I'm using Quickbooks Pro 2013 on Windows 7. I was going to switch get 2015 but not sure if it's worth the new features for something that every time I add an order that has tax, I have to manually edit the order in Excel.

I have also uploaded a bad file and a good file in the past and nothing ever came of it. Imac, if you're serious, I'll do it again with the most recent files, 1 bad and the same file with my corrections.

Magpie Don: I sincerely appreciate your assistance and insight. As I stated in my first email, I'm willing to provide compensation for assistance. I'm not rich, but I'll do what I can if necessary for a good fix.

By the way... I learned the hard way not to open a ticket because when I did, and I provided the files and the fix for the ticket since I wasn't getting any help from CS-Cart Tech, they charged me credits.. and it was THEIR software that had the issue. I still have a negative credit balance from that experience.

Thanks for all the responses,
Matt Caswell
Store.dryheatembroidery.com

 
  • mattcaswell
  • Junior Member
  • Members
  • Join Date: 06-Oct 09
  • 79 posts

Posted 26 July 2015 - 12:22 AM #8

Magpie Don,
So are you saying that I need to setup a ZERO sales tax in CS and QB for everything other than my home state of Arizona? Or is it just QB that you set it up in?
Thanks for your time,
Matt

 
  • clips
  • Aged Resident Loon
  • Members
  • Join Date: 14-Jan 07
  • 1650 posts

Posted 26 July 2015 - 04:45 PM #9

Matt, sorry to hear that. But this actually true, mainly because Quickbooks is not very popular.


Oh my, is CS-Cart seriously getting that far out of touch? We still use QBs but have drifted away from using the export from CS because among other things it kept zeroing out all of our "cost" within QBs. We had to end up using a 3rd party software to import orders so we didn't have to manually edit each time we did an import.
Regards,
Jim

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 27 July 2015 - 02:15 PM #10

Magpie Don,
So are you saying that I need to setup a ZERO sales tax in CS and QB for everything other than my home state of Arizona? Or is it just QB that you set it up in?
Thanks for your time,
Matt

Hi Matt,
I set up a Non-Taxable value for Sales Tax in QB and I used some logic so the last line of each imported order includes the proper sales tax value (Non-taxable or Taxable). Basically, when you create an invoice in QB you should be defining if the sale is taxable or not. You also define if the customer is taxable or not. My Ohio customers are Taxable, all others are Non-Taxable.
In CS-Cart, you can't do this natively with the forms that CS-Cart provides for the QB addon - it needs to be added to the script with a new variable. As soon as I can get back to work on the PHP script in v4, I'll share my work with this thread.

Oh my, is CS-Cart seriously getting that far out of touch? We still use QBs but have drifted away from using the export from CS because among other things it kept zeroing out all of our "cost" within QBs. We had to end up using a 3rd party software to import orders so we didn't have to manually edit each time we did an import.

You can prevent CS-Cart's QB export from including Products. Or if you must include the Products during importing, you do not need to import the COST field. I completely removed the importing of Products from the CS-Cart script. All my products are ALREADY set up in QB, so there is no need to import them again. Over two years ago, I suggested to CS-Cart that they remove the Export of Products from the Export of Orders and maybe make it a separate process for those who need it.

CS-Cart Ultimate ver 4.3.5


 
  • jackyboy
  • Junior Member
  • Members
  • Join Date: 31-Dec 10
  • 49 posts

Posted 27 July 2015 - 03:12 PM #11

We had to end up using a 3rd party software to import orders so we didn't have to manually edit each time we did an import

What 3rd party software are you using may I ask?

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 27 July 2015 - 03:13 PM #12

Here's a screen shot of the Customer's Tax options on a QB invoice:
Posted Image
The Non value is included in the CS-Cart import of customers (simple logic based upon IF their Billing State is equal to "OH" (or "AZ", in your case). Each customer is imported (or updated) when the sales are imported.


Here's a screen shot of the Invoice Tax options:
Posted Image

The Out of State value is used for all sales that are to customers who are out of the state.
The Non value is used for sales that are IN Ohio, but are for Tax Exempt inventory, or Tax Exempt customers.

CS-Cart Ultimate ver 4.3.5


 
  • imac
  • Head of Product
  • CS-Cart Architects
  • Join Date: 22-Nov 05
  • 2073 posts

Posted 27 July 2015 - 03:48 PM #13

I have also uploaded a bad file and a good file in the past and nothing ever came of it. Imac, if you're serious, I'll do it again with the most recent files, 1 bad and the same file with my corrections.


Matt, yes please post it to Help Desk. I will take it under control.
I can not guarantee that we will fix this fast, but we will.

If anyone else using some tricks exporting orders to Quickbooks please share it.
Ilya Makarov,
CS-Cart Architect Team
Suggest and vote for new features | Report a bug

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 27 July 2015 - 06:40 PM #14

Matt, yes please post it to Help Desk. I will take it under control.
I can not guarantee that we will fix this fast, but we will.

If anyone else using some tricks exporting orders to Quickbooks please share it.


You could start by taking this entire function and removing it from the Orders Export:

/** Export Products START
    function fn_quickbooks_export_products($orders, $order_products, &$export)
{
    $export[] = "!INVITEM\tNAME\tINVITEMTYPE\tDESC\tPURCHASEDESC\tACCNT\tASSETACCNT\tCOGSACCNT\tPRICE\tCOST\tTAXABLE";
    $invitem = "INVITEM\t%s\tINVENTORY\t%s%s\t%s%s\t%s\t%s\t%s\t%01.2f\t0\tN";
    $accnt_product = Registry::get('addons.quickbooks.accnt_product');
    $accnt_asset = Registry::get('addons.quickbooks.accnt_asset');
    $accnt_cogs = Registry::get('addons.quickbooks.accnt_cogs');
    foreach ($order_products as $product) {
	    $product_select_options = !empty($product['selected_options']) ? $product['selected_options'] : '';
	    $product_name = !empty($product['product_code']) ? $product['product_code'] : $product['product_id'];
	    $export[] = sprintf($invitem, $product_name, $product['product'], $product_select_options, $product['product'],
		    $product_select_options, $accnt_product, $accnt_asset, $accnt_cogs, $product['price']
	    );
    }
    fn_set_hook('quickbooks_export_items', $orders, $invitem, $export);
    $export[] = '';
    return true;
}
Export Products END **/

Using the QB Orders Export process should depend upon the Products ALREADY existing as an Item in Quickbooks. IF you want to build a Product Export function that will export all the products to Quickbooks, you already have most of the logic done in that code above. Except that you unnecessarily export Cost (which doesn't exist in CS-Cart) and hard code the cost to zero.

CS-Cart Ultimate ver 4.3.5


 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 27 July 2015 - 07:06 PM #15

Matt, yes please post it to Help Desk. I will take it under control.
I can not guarantee that we will fix this fast, but we will.

If anyone else using some tricks exporting orders to Quickbooks please share it.


I wish you would pull the values from Shipping > Taxes for the Tax Rate Value so that the rate for each order is included (you already fetch the Tax Name value). I've had to create a variable for this in every version, and hard code it into the script or template.

(BTW, Quickbooks doesn't support taxes on individual line items, taxes are one lump sum for the entire order, including all taxable items and non-taxable items in the order. Example: You could have 1 taxable items, with $2.00 in tax charged, and 1 non-taxable items with $0 in tax charged, the export of the order then includes one record of $2.00 in tax on the entire order.)

CS-Cart Ultimate ver 4.3.5


 
  • mattcaswell
  • Junior Member
  • Members
  • Join Date: 06-Oct 09
  • 79 posts

Posted 28 July 2015 - 03:35 PM #16

Matt, yes please post it to Help Desk. I will take it under control.
I can not guarantee that we will fix this fast, but we will.

If anyone else using some tricks exporting orders to Quickbooks please share it.

Here you go. and if you going to try and charge me credits again, forget it!

Here's the number: Ticket ID: #101326561 uploaded to the help desk area

 
  • imac
  • Head of Product
  • CS-Cart Architects
  • Join Date: 22-Nov 05
  • 2073 posts

Posted 29 July 2015 - 08:26 AM #17

Here you go. and if you going to try and charge me credits again, forget it!

Here's the number: Ticket ID: #101326561 uploaded to the help desk area


Matt, not this time:)
Thank you for the ticket.
Ilya Makarov,
CS-Cart Architect Team
Suggest and vote for new features | Report a bug

 
  • imac
  • Head of Product
  • CS-Cart Architects
  • Join Date: 22-Nov 05
  • 2073 posts

Posted 29 July 2015 - 08:51 AM #18

Ups, looks like Intuit plans to discontinue IIF files support: https://developer.in...t_oriented/faqs
I tried online version and did not find an IIF file import feature.

Do you guys use downloadable version? What edition do you use?
Ilya Makarov,
CS-Cart Architect Team
Suggest and vote for new features | Report a bug

 
  • Magpie Don
  • Senior Member
  • Members
  • Join Date: 01-Apr 09
  • 813 posts

Posted 29 July 2015 - 12:24 PM #19

Yes, Intuit has been saying for years that they are moving to XML for imports, and moving away from the IIF file format.
I keep hoping that CS-Cart is paying attention to that and will develop the new format- but it sounds like you just discovered the fact...

CS-Cart's promise to provide QB support was a significant reason for choosing the CS-Cart software. I'm using Quickbooks Premier edition for Manufacturing and Wholesale version 2010 and version 2013. (about to retire v2010) Each upgrade, about every 2 years for me, has cost about $200. I have about $600 invested in QB over the last 6 years.

I will not be upgrading to any new version of Quickbooks that does NOT support IIF files for import until CS-Cart upgrades the export process to their new specified format. At this point, the limitations of the Sales Channel (CS-Cart) is determining the accounting software that I can use.

Could you please indicate ANY method that is designed to Export sales transactions that include Customer, Order and Item data, & Payment data defined by a range of orders like the IIF Export process does. I realize I can Export ALL my orders to a CSV file, but that doesn't do me any good on a daily basis.
How does CS-Cart intend for clients to integrate the sales channel into ANY accounting software?

CS-Cart Ultimate ver 4.3.5


 
  • mattcaswell
  • Junior Member
  • Members
  • Join Date: 06-Oct 09
  • 79 posts

Posted 29 July 2015 - 02:50 PM #20

I"m using Quickbooks Pro 2013 on a PC. Not using Quickbooks Online.