Post by pmchardy » Fri Oct 14, 2011 9:17 pm

Dear Forum,

I am integrating Linnworks with my opencart store. All is going well. However, every product and variant needs to have a unique sku. So I installed optionsplusredux. This then gives every variant a filed for a unique sku.

However after installing the unique skus are empty. I am looking for a sql script that will take product name and add it to product variant and place this in the variant sku field. Have over 2500 prducts and dont want to have to do this manually?

so if product sku is "1234"
and option value name is "Red"
you want product sku+" "+option value name
"1234 Red"


Can any one help?

Thanks

Peter

Newbie

Posts

Joined
Fri May 20, 2011 4:56 am

Post by uksitebuilder » Fri Oct 14, 2011 9:22 pm

You will also need to contact Linnworks to see if their script can handle this

I know I had a similar issue with them with OSCommerce a while back because OSC options (attributes) dont have a sku field either

After some coding on my part and discussion with them, they coded a refined script for their end to match what I had done in OSC

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by pmchardy » Fri Oct 14, 2011 9:26 pm

the linn works side is fine, when I manually update the product variant skus, but i was hoping for a sql wizard to guide me through a script to do this. I just did not want to have to manually fill in over 2500 skus, that would make me grumpy

Peter

Newbie

Posts

Joined
Fri May 20, 2011 4:56 am

Post by uksitebuilder » Fri Oct 14, 2011 9:31 pm

When you say the Linnworks side is fine, I think you mean that you can enter the variant skus in the actual program

However their script file which you upload to OpenCart will not know what to look for or how to deal with the variant skus added to your options

Some intervention by Linnworks will be needed to grab the new skus from your options in OpenCart

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by pmchardy » Fri Oct 14, 2011 9:38 pm

it does, when I change the quantity in linnworks then hit synchronise linnworks has updated the quantity of the variant in my opencart store, so I am happy that lin works is able to associated the opencart product variant where a matching sku exists. But if no sku exists then it is not able to match the products and their variants.

Newbie

Posts

Joined
Fri May 20, 2011 4:56 am

Post by Xsecrets » Fri Oct 14, 2011 9:42 pm

in very general terms (because I'm not sure of the structure of that plugin) you would be looking for something like

Code: Select all

UPDATE tablename SET sku = CONCAT(field1, ' ', field2);

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by pmchardy » Fri Oct 14, 2011 9:59 pm

OK I have just backed up the database and now I will give the following code a try


UPDATE ocproduct_option_value SET sku = CONCAT(sku FROM ocproduct , ' ', name FROM product_option_value_description);



that throws a syntax error

Newbie

Posts

Joined
Fri May 20, 2011 4:56 am

Post by Xsecrets » Fri Oct 14, 2011 10:34 pm

that won't work.

maybe try something like

Code: Select all

UPDATE ocproduct_option_value pov LEFT JOIN ocproduct p ON p.product_id = pov.product_id LEFT JOIN  ocproduct_option_value_description povd ON povd.product_option_value_id = pov.product_option_value_id SET pov.sku = CONCAT(p.sku , ' ', povd.name);
of course that's untested code, and partial speculation from what you posted since product_option_value_description does not exist in the default install I'm guessing it was added by the mod.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US
Who is online

Users browsing this forum: No registered users and 50 guests