Easy Populate & Products Attributes

[surfalot]

I am using Easy Populate 2.76h-MS2 and when updating existing products if there is a price drop for an product attribute (from 0 to -1 cost for example) it doesn't get update.

 

step 1: I upload a product with some attributes

step 2: I download the complete csv

step 3: I edit one of the options to a -1 value (not caring about tax or whatever)

step 4: I upload the file

 

After step 4 the negative value hasn't been applied, but any other changes have.

there was an issue with this quite some time ago, but has since been fixed. I can't reproduce the problem now. can you post a link to a sample of the CSV file what you are trying to insert (with the negative number? you can PM me the link if it is sensitive data. Please don't put the data in the post since the board will change it.

[GieltjE]

there was an issue with this quite some time ago, but has since been fixed. I can't reproduce the problem now. can you post a link to a sample of the CSV file what you are trying to insert (with the negative number? you can PM me the link if it is sensitive data. Please don't put the data in the post since the board will change it.

 

I just found the problem, if the export date + time isn't the same the attribute update will fail, but others still succeed (I generate them every time I rerun the export).

Should I rewrite my program to use a fixed date+time or is this a 'bug' in the easy populate?

 

P.s: Are you sure you release the file after the export? My browser keeps the file locked until I close/manually unlock it.

P.s2: sorry for the inconvenience.

Edited August 1, 2009 by GieltjE

[roback]

All, could you please look at this thread here:

http://www.oscommerce.com/forums/index.php?showtopic=341992

 

I am trying to insert unique values into my product_model column so that EP will work properly. Is there a SQL query that I could use to do this quickly?

 

Also, why does EP NOT extract the product ID number? You would think, since that is the unique value, it would be the one used to check products on import instead of the product_model. Seems like an oversight to me?

 

Any ideas about this?

[roback]

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

[surfalot]

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

what is the version and upload date of the EP you are using?

Edited August 2, 2009 by surfalot

[surfalot]

I just found the problem, if the export date + time isn't the same the attribute update will fail, but others still succeed (I generate them every time I rerun the export).

Should I rewrite my program to use a fixed date+time or is this a 'bug' in the easy populate?

 

P.s: Are you sure you release the file after the export? My browser keeps the file locked until I close/manually unlock it.

P.s2: sorry for the inconvenience.

 

export date + time? do you mean the date on the CSV file name or the v_date_added or v_date_avail columns?

[comp818]

Hello,

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

[surfalot]

Hello,

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

please look at the support section of the docs and include as much of the info as you can that it asks for.

[comp818]

Settings & Info

 

EP vers: 2.76h-MS2

osCommerce Online Merchant v2.2 RC2a

OS: Linux 2.6.18-92.1.10.el5PAE

HTTP: Apache

DB: MySQL 4.1.22-max-log

PHP: 5.2.8 (Zend: 2.2.0)

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

[surfalot]

that's only half the settings, how about the rest?

[surfalot]

Settings & Info

 

EP vers: 2.76h-MS2

osCommerce Online Merchant v2.2 RC2a

OS: Linux 2.6.18-92.1.10.el5PAE

HTTP: Apache

DB: MySQL 4.1.22-max-log

PHP: 5.2.8 (Zend: 2.2.0)

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

The install directions do ask you to go through all the settings at the top of the easypopulate.php before you start. :)

 

I think I know the problem. The latest version of 2.76h: "easypopulate.php 2.76h con PDF File Upload and Display - josequituc - 28 May 2009" has some other contribution settings enabled by default.

 

Check the settings section at the top of the easypopulate.php for this section: // *** Other Contributions Support ***

Make sure all those settings are false unless you have those contributions installed.

 

Note to josequituc, increase the version number when you create a new version please.

[GieltjE]

export date + time? do you mean the date on the CSV file name or the v_date_added or v_date_avail columns?

 

v_date_added, I don't even add a v_date_avail since that is not in the question.

[surfalot]

v_date_added, I don't even add a v_date_avail since that is not in the question.

 

I still can't replicate the issue.

 

I change the attrib price to 10 or 0,

export the data,

change the v_date_added and the attrib price to -1

everything updates fine.

 

There should never be a connection between the date and the attrib price. the data is manipulated distinctly separate from each other and I don't see any like variables being used or overwritten.

 

Can you give me a couple links to two sample csv files with the one product example? One before product data, and one that represents the change giving you problems?

[GieltjE]

I still can't replicate the issue.

 

I change the attrib price to 10 or 0,

export the data,

change the v_date_added and the attrib price to -1

everything updates fine.

 

There should never be a connection between the date and the attrib price. the data is manipulated distinctly separate from each other and I don't see any like variables being used or overwritten.

 

Can you give me a couple links to two sample csv files with the one product example? One before product data, and one that represents the change giving you problems?

 

Ok, just did some more testing, and as my export program allways gave "-0.8403" (tax value = 19% as stated in the export file) and never worked, my manual attempts with a fast "-1.0000" allways work.

 

I tested some more low value changes and all failed, by everything lower then -1.000 works.

Another strange thing I noticed (tax still 19%):

0.8403 * tax = doesn't work

1.0000 * tax = 1.19

1.5000 * tax = 1.19

 

see: http://www.speelmans.com/Example.rar

[roback]

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

 

Here are the full specs of my EP load:

 

Settings & Info

EP vers: 2.76h-MS2
osCommerce Online Merchant v2.2 RC2a
OS: Linux 2.6.28.9
HTTP: Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8i DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
DB: MySQL 5.1.30
PHP: 5.2.9 (Zend: 2.2.0)

Temp Directory:
/home/spogbz/public_html/temp/
Temp Dir is Writable
Magic Quotes is: off
register_globals is: on
Split files on: 3000 records
Model Num Size: 12
Price with tax: false
Calc Precision: 2
Replace quotes: false
Field seperator: comma
Excel safe output: true
Preserve tab/cr/lf: false
Category depth: 4
Enable attributes: false
SEF Froogle URLS: false
More Pics: false
Unknown Pics: false
HTC: false
SPPC: false
Extra Fields: false

 

Now again, I get the posted error when trying to upload and the only thing I can think of is that it doesn't like the product_size custom field.

 

Any help?

[surfalot]

Ok, just did some more testing, and as my export program allways gave "-0.8403" (tax value = 19% as stated in the export file) and never worked, my manual attempts with a fast "-1.0000" allways work.

 

I tested some more low value changes and all failed, by everything lower then -1.000 works.

Another strange thing I noticed (tax still 19%):

0.8403 * tax = doesn't work

1.0000 * tax = 1.19

1.5000 * tax = 1.19

 

see: http://www.speelmans.com/Example.rar

it really would help if you tell be exactly what you are doing. The nonsense about dates, taxes, and changing 0 to -1 has nothing to do with what you are actually doing. Which of course, all worked for me.

 

The actual issue here was decimals. The fact that you are trying to import decimals into the attributes. Yes, there seems to be a problem with that.

 

look for this:

if ( $$v_attribute_values_price_var < 0 ) $$v_attribute_values_price_var = strval(-((int)$$v_attribute_values_price_var));

change to this:

if ( $$v_attribute_values_price_var < 0 ) $$v_attribute_values_price_var = strval(-((float)$$v_attribute_values_price_var));

 

please note there is a precision setting in EP. Make sure you are using a precision of 4 not the default of 2.

[surfalot]

Here are the full specs of my EP load:

 

Now again, I get the posted error when trying to upload and the only thing I can think of is that it doesn't like the product_size custom field.

 

Any help?

 

and it won't like the product_uom either. did you add custom fields to the products table custom fields settings in EP ?

 

Did you create those fields in the database also ?

[roback]

and it won't like the product_uom either. did you add custom fields to the products table custom fields settings in EP ?

 

Did you create those fields in the database also ?

 

 

Okay not sure about the first one - add custom fields to the products table custom fields settings in EP? (Is this an oversight by me on the installation instructions?)

 

The fields are in the database yes. Not in the products table, but another one for extra fields (This was the Extra Fields Contrib). Both of these show up in EP export check box in admin.

 

Thanks

 

Rob

[surfalot]

Okay not sure about the first one - add custom fields to the products table custom fields settings in EP? (Is this an oversight by me on the installation instructions?)

 

The fields are in the database yes. Not in the products table, but another one for extra fields (This was the Extra Fields Contrib). Both of these show up in EP export check box in admin.

 

Thanks

 

Rob

you say you are using 2.76h. But neither "product_size" nor "product_uom" are anywhere in that version. So you must have added it somewhere. I would suggest going back to the version I uploaded and starting over. Only change the setting you are sure you understand. If there is any settings I can clarify, ask first.

[mbuswell]

I know I am not the first to ask, and I know that it isn't yet available, however it would be great if SPECIALS could be included in EasyPopulate :D

 

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

 

thanks

[surfalot]

I know I am not the first to ask, and I know that it isn't yet available, however it would be great if SPECIALS could be included in EasyPopulate :D

 

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

sounds like you know what you are doing! This would be a great project for you to contribute! :D

[surfalot]

+++++++++++++++++++++++++++++++++++++++++++

Easy Populate v2.76i was released on this date at this time.

+++++++++++++++++++++++++++++++++++++++++++

[surfalot]

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

the product id was used in a version of EP for the master products contribution. You will find that functionality by looking around the downloads.

[roback]

you say you are using 2.76h. But neither "product_size" nor "product_uom" are anywhere in that version. So you must have added it somewhere. I would suggest going back to the version I uploaded and starting over. Only change the setting you are sure you understand. If there is any settings I can clarify, ask first.

 

Okay, so I looked in PHPmyAdmin to find where product_size and product_uom are located.

 

They are declared in the table: products_extra_fields, where product_extra_fields_id is the unique index for the various extra fields.

 

Then the values for this are stored in: products_to_products_extra_fields table where they ref the product_extra_fields_id.

 

So for example size and uom are declared in products_extra_fields with product_extra_fields_id of 6 and 7 respectively. Then in table products_to_products_extra_fields is has products_id, product_extra_field_id (6 and 7 mentioned earlier) and product_extra_field_value where the values are stored.

 

Now, All of that has my head spinning when I go through the instructions and get to this point:

 

// BEGIN Define Custom Fields for your products database

 

The instructions indicate that only works in tables products and products_description.

 

Now I have ammended this and I am going to guess that this does nothing but create these as options to click on the export.

 

 $custom_fields[TABLE_PRODUCTS_DESCRIPTION] = array('product_size' => 'Products Size', 'product_uom' => 'U.O.M.' );

 

So at that point I am lost. the instructions are leading me to believe that I cannot access that table and subsequently its date.

 

So... please help... :-)

[surfalot]

Okay, so I looked in PHPmyAdmin to find where product_size and product_uom are located.

 

They are declared in the table: products_extra_fields, where product_extra_fields_id is the unique index for the various extra fields.

 

The extra fields contribution has nothing to do with the Custom Fields settings for the products and products_description tables. If you have the extra fields contribution installed you need to look at the instructions in the docs for the extra fields contribution.

 

You would use the Define Custom Fields area of the settings if you have added fields to the products or products_description tables only.

Edited August 4, 2009 by surfalot