VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-ASP Shopping Cart Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Problems and bugs
 convertproducts does not remove old category data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

44 Posts

Posted - February 18 2011 :  09:46:34  Show Profile  Reply with Quote
I'm a big fan of VPASP and the combination of using the level3 field within a product record to define multiple categories and then convertproducts.asp to assign products to those categories works fine for new products.


If you regularly use .csv files to update your product data - including changes in categories - then convertproducts.asp only ADDS records it does not check or delete outdated records. This means the cateogry assignments on your website can be wrong.

e.g. you import products that are in a category called 'SPECIAL OFFERS' and the products are assigned to that category. If you subsequently import data where some of those products have been removed from the special offers category, you'll find that they still appear on your website within special offers as there is still a row within the prodcategories table that assigns that product catalogid to the category categoryID.

I think this is a shortcoming.

The BigYellowZone category fixing tools are very helpful but I don't believe cover this situation.

Is there any way to extend the functionality of convertproducts to do the following:

amend the entries within the prodcategories table based on the values held in the level3 field e.g.

(1) If there is a product within prodcategories, but the row within prodcategories shows it as assigned to a categoryID that IS NOT in the level3 field for that product, delete the row.

(2) If there is a product within prodcategories, but the row within prodcategories shows a categoryID that IS in the level3 field for that product, add a row if it doesn’t already exist.

Thank you

Starting Member

15 Posts

Posted - July 16 2011 :  11:31:40  Show Profile  Visit magnumtools.com's Homepage  Reply with Quote
I would greatly appreciate more functionality in this regard as well. We are approaching 7,000 products in our store and are struggling to maintain the database as the vast majority of our products are listed in multiple categories.

The only efficient manner in which to bulk update our products as of now is by way of editing in excel and exporting to csv, uploading and running convertproducts. But, as stated above, that will only add products to categories and not full update to show a category removal. I am DYING for the functionality to be built in for the ability to remove products from categories in bulk.

It would be an immense time saver if we were able to simply change the values in the level3 field in excel and have the database accurately mirror the changes.

We would be able to save HUNDREDS of man hours if this functionality were available. Any ideas if this is on the table for upcoming patches?
Go to Top of Page

Senior Member

United Kingdom
1898 Posts

Posted - July 17 2011 :  02:03:11  Show Profile  Visit devshb's Homepage  Reply with Quote
Firstly, before anyone reading this does any changes suggested in this message to their data/processes, please always make sure you take backups/exports of your data first!

Assuming that your level3 field (ie the field you've configured to hold the category id csv list in for the product) is the definitive source of what categories you want the products to be in (ie that you don't add categories to products manually in the admin screen, you always just import and use level3 to add the categories to products), then the easiest way to achieve that would simply be to delete all the prodcategories records before you run the convertproducts screen.

ie just do this in raw sql (or via something like BYZ097; advanced sql execution) before you run convertproducts -

delete from prodcategories

That's not a perfect solution, and you'd definitely want to export/backup your prodcategories table before you do the delete, but it should do what's needed, as deleting prodcategories at the start and then running convertproducts for all products has the same end-result as doing what neilt above specified (**but only if you've been maintaining your category list in level3 and not manually adding them via admin etc**)

If, however, someone's reading this and thinking "level3 doesn't hold my definitive list of categories; I add mine manually, but I also want to take products out of a given category in bulk" then BYZ089 (bulk product update) will do that (it'll also let you add categories to products in bulk)

So, some of these 3 aspects may be relevant to some people reading this topic:

Bulk Product Updater for VPASP - BYZ089:
(ie for people who don't necessarily use imports, but who want to do things like "take all products that are in category x out of category y", or "add all products that are in category x to category y", or "add all products that have ccodes starting with 'a' to category y, or "reduce the price of everything that's in category x by 10%" etc etc)

Product/Category Fixing/Reporting Utils for VP-ASP - BYZ055:
(for everyone, regardless of what kind of processes you have; helps maintain a clean set of category and prodcategory data)

Advanced SQL Execution Tool for VP-ASP - BYZ097:
(for anyone who needs to run a raw sql command, such as an insert/update/delete or column-creation etc, and wants to run it from vpasp admin)

However, having said all that, if you have a lot of products that you want to maintain/source from outside vpasp (eg from supplier's excel files, or from your own excel files), and if that ultimate source is quite a lot different to the products table structure etc, then there's really no substitute for a custom-built/custom-tweaked import process.

Sometimes a quick-fix (like doing the delete from prodcategories first, either in raw sql, or via byz097, or via a minor tweak to the vpasp import/convertproduct scripts) is all that's needed, but sometimes it saves time/money/errors to just have your own import process built.

For example, someone came to us the other day and asked if we could explain/document the precise process needed to tweak their data after they'd done their default vpasp import (eg the explanation/documentation would have been along the lines of "turn your excel file into a vpasp products structure by doing this...this...this, then save it as a csv file, then import it, then run convertproducts, then do this...etc etc), and that process would have been quite complex and been prone to errors, so instead we simply said "why don't we just import your existing excel file directly into vpasp via a custom-coded import process? The excel file doesn't fit the precise vpasp format, but the logic to "translate" it to vpasp format can be automated, so we just setup an import script, you upload the file and press a button to do all that "translation" for you."
So we did that, and now all they need to do is maintain their products excel file in *their* chosen format, and when they want to do an import they login to admin, press a button to upload the excel file, then press another button to import it, and the import process uses *their* rules for when to change values and when not to, and uses *their* rules for how to pick which categories the products go into.

A classic example of this kind of custom-built import process would be something like qty-discounts, where you might want to specify qty-discounts in your products excel file, and import that into the quantity discounts table as part of the import process; all this kind of stuff can be custom-coded, either as tweaks to the default vpasp import, or as a totally new/separate import process.

It's all a balancing act of budgets and the efficiency of the various aspects etc I guess; sometimes it's better to bend/customise vpasp to suit what you need, rather than changing your processes to suit vpasp.

Simon Barnaby
[email protected]
Follow us on Twitter: http://twitter.com/bigyellowzone
Web Design, Online Marketing and VPASP addons

Edited by - devshb on July 17 2011 03:13:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000