Quickbooks Import QBI

[tj1]

Is there an easy way to match and import attributes?

I only have one attribute, but I'm having a lot of trouble. Maybe I'm missing some important step that might make this easier.

The attribute is for engravable text.

I have about 15,000 items on my site and about 1000 of them have this attribute.
I can't do matching manually for this many items.

At the matching page, QBI lists items like
EngravableItem1:Text
EngravableItem2:Text
EngravableItem3:Text
...etc

In QuickBooks I just have one service item called EngraveService.

Is it possible to just import the Text attribute as a separate item matched to the EngraveService item in QB?

The way this is importing right now is QBI is automatically creating hundreds of sub-items in quickbooks, this is creating a big mess.

Or if not possible to import the attribute as a separate item, can QBI just match to the item number without creating a sub-item?
For example, currently QBI does something like this
Engravable1:Text
Is imported in QB as
Engravable1
- variable subitem with text input


More appropriate behavior would be to just match Engravable1:Text to Engravable1. No creating sub-items, just include the attribute as the item description.


Thanks.

[adam5532]

Hi,

Is there an easy way to match and import attributes?

I only have one attribute, but I'm having a lot of trouble. Maybe I'm missing some important step that might make this easier.

The attribute is for engravable text. ...

All the features you mention for handling engraved or personalized items are available in the paid version of QBI, but not in the free version.

Thanks,
Adam

[jwitt98]

Hi all,
Sorry if this has been posted somewhere already, but I've spent most of the morning looking for an answer and cannot find anything specific to this issue.

I've followed the procedures on installing qbi_v3_21b_zen and I am now at the point of setting up the initial configuration options. After configuring all the option and clicking on "update" to submit the form, I get the following message:

Notice: Query failed: Incorrect integer value: '' for column 'tax_lookup' at row 1 SQL: UPDATE qbi_config SET qbi_qb_iso2='US',qbi_qb_subver='1',qbi_qb_addr='0',qbi_output_fmt='iif',qbi_file_date='1',qbi_dl_file='1',qbi_prod_rows='5',prods_sort='0',item_width='48',qbi_log='1',orders_status_import='2',qbi_status_update='1',qbi_cc_status_select='2',qbi_mo_status_select='1',qbi_email_send='1',qbi_cc_clear='0',orders_docnum='%I',orders_ponum='%I',cust_nameb='%C10W-%I',cust_namer='%L10W-%I',cust_limit='0',cust_type='',cust_state='1',cust_country='0',cust_compcon='1',cust_phone='1',cust_default='0',cust_default_name='',invoice_acct='Accounts Receivable',invoice_salesacct='Undeposited Funds',invoice_toprint='1',invoice_pmt='1',invoice_termscc='PayPal',invoice_terms='Check',invoice_rep='',invoice_fob='',invoice_class='',invoice_comments='1',invoice_message='Thank you very much!',invoice_memo='',item_acct='Sales',item_asset_acct='Inventory Asset',item_class='',item_cog_acct='Cost of Goods Sold',item_cart_lang='0',item_match_i in C:\apache\htdocs\store\ADMIN_NAME_REMOVED\includes\classes\qbi_mysql.php on line 119

Additional Info:
* Apache/2.2.9 (Win32) PHP/5.2.6
* MySQL client version: 5.0.51a
* PHP extension: mysql

Does anyone have any pointers on where to start looking?

Thanks

[jwitt98]

Hi all,
Sorry if this has been posted somewhere already, but I've spent most of the morning looking for an answer and cannot find anything specific to this issue.

I've followed the procedures on installing qbi_v3_21b_zen and I am now at the point of setting up the initial configuration options. After configuring all the option and clicking on "update" to submit the form, I get the following message:

Notice: Query failed: Incorrect integer value: '' for column 'tax_lookup' at row 1 SQL: UPDATE qbi_config SET qbi_qb_iso2='US',qbi_qb_subver='1',qbi_qb_addr='0',qbi_output_fmt='iif',qbi_file_date='1',qbi_dl_file='1',qbi_prod_rows='5',prods_sort='0',item_width='48',qbi_log='1',orders_status_import='2',qbi_status_update='1',qbi_cc_status_select='2',qbi_mo_status_select='1',qbi_email_send='1',qbi_cc_clear='0',orders_docnum='%I',orders_ponum='%I',cust_nameb='%C10W-%I',cust_namer='%L10W-%I',cust_limit='0',cust_type='',cust_state='1',cust_country='0',cust_compcon='1',cust_phone='1',cust_default='0',cust_default_name='',invoice_acct='Accounts Receivable',invoice_salesacct='Undeposited Funds',invoice_toprint='1',invoice_pmt='1',invoice_termscc='PayPal',invoice_terms='Check',invoice_rep='',invoice_fob='',invoice_class='',invoice_comments='1',invoice_message='Thank you very much!',invoice_memo='',item_acct='Sales',item_asset_acct='Inventory Asset',item_class='',item_cog_acct='Cost of Goods Sold',item_cart_lang='0',item_match_i in C:\apache\htdocs\store\ADMIN_NAME_REMOVED\includes\classes\qbi_mysql.php on line 119

Additional Info:
* Apache/2.2.9 (Win32) PHP/5.2.6
* MySQL client version: 5.0.51a
* PHP extension: mysql

Does anyone have any pointers on where to start looking?

Thanks

I finally found the solution to this problem in a post by Adam here:
http://www.qbisoftware.com/forum/vie...ax_lookup#p323

Once I remove the 'tax_lookup' column from the array, it worked fine.

[srturner47]

Thanks for the helpful response adam5532! I did a little digging through the code and figured out how to charge different sales tax rates with qbi. It takes a little coding, but if you are at all handy with php, its really fairly straight forward. While I know nothing about the .iif format, you can easily tell what variables need to be changed due to adam5532's careful and deliberate coding! (Wish my coding was that organized!)

The function you'll want to edit is in admin/includes/classes/qbi_classes_out_iif.php. You'll want to find where the function tax_us($cust,$order) is. Everything is pretty straight forward once you've figured out that this is the function that does the tax magic! If you need to do any SQL queries to determine which tax rate to use, $order['orders_id'] is the order id you'll need to use after the "WHERE" part of the SQL query.

Thanks for your great work on this adam5532! I'm impressed with how you kept the main code very sparse and relied almost entirely on functions. You're a very neat and tidy coder! I'll definitely be purchasing the new version with the tax codes when you release it -- it will make changing tax codes a cinch in the future, so it will be well worth it. In the meantime, my little hack will suffice! :)

[tj1]

Is the function clean_iif_data applied to the product model?

The reason I ask is because I have many products that have an underscore in the model and I'm looking for a way to remove that when imported into quickbooks.
I figure if the clean_iif_data is being applied to the product model, I can simply add a line there to remove the underscores.

Thanks.

[adam5532]

Is the function clean_iif_data applied to the product model?

The reason I ask is because I have many products that have an underscore in the model and I'm looking for a way to remove that when imported into quickbooks.
I figure if the clean_iif_data is being applied to the product model, I can simply add a line there to remove the underscores.

Thanks.

clean_iif_data is in the qbi_functions.php file, and calls the following other functions:
remove_returns_and_tabs_array
strip_tags_array
convert_quotes_array

Each of these processes all the data going to the iif file. So, you could add a new function to it (see the others as a model), but it would remove underscores from everything, not just the model.

Otherwise, you could modify the code in the guts, but I'm not going to step you through that.

Or, you could put your function in the qbi_classes_out_iif.php file, although design-wise that's just an output formatting file and code that manipulates the data shouldn't go there.

The last and best way of course would be for you to just run an SQL statement (REPLACE) on your database and remove all the underscores from your model numbers in Zen Cart, so they match QB.

Adam

[tj1]

Thanks for the clarification, adding the function to remove underscore will work just fine.
The only place I have underscore is at the end or beginning of some product models and nowhere else. These are duplicate products, limited quantity on sale, so I use the underscore to easily distinguish that.

Thanks again.

[adam5532]

Thanks for the clarification, adding the function to remove underscore will work just fine.
The only place I have underscore is at the end or beginning of some product models and nowhere else. These are duplicate products, limited quantity on sale, so I use the underscore to easily distinguish that.

Thanks again.

Then this should work:

Add to function clean_iif_data:

$var=remove_underscore_array($var);

Add function:

function remove_underscore_array(&$var) {
	if (is_array($var)) {
		array_walk($var,'remove_underscore_array');
	} else {
		$var=str_replace('_','',$var);
	}
	return $var;
}

Thanks,
Adam

[tj1]

Wow, thanks for taking the time to write that extra function, although I had already implemented that before my last post.

Thanks for the great support and great contribution. I've been able to find workarounds for some of the limitations by making very minor changes or commenting something out.
This has allowed me to import as estimates, which can then be used to create sales orders.
Also, attributes are now imported only as part of the product description, without creating endless subitems in Quickbooks.
If anyone needs this let me know and I'll post what I did.


Now all I need is to figure out how to import orders paid with GoogleCheckout.