Routinely users will want to clean up their set of products within SMS, in some cases over years of working with SMS it is not uncommon for there to be 200+ products in the system. Users often want to clean up their products by removing all old products and importing a new set.

While this sounds ideal there are some drawbacks to totally deleting all your products:

  • All historical orders will no longer be linked to those products since they no longer exist, this cuts down on the ability for you to clone old orders.
  • All currently open orders will no longer be linked to the products you just removed, thus when you edit or change the order in any way you'll be prompted to reassign all the line items to a new set of products. This can be a lot of work considering some open order lists can exceed 100+ orders easily.
  • Your new product set that you are going to import doesn't have some products that you really need to keep from your existing set.

Our recommendation based on our experience in working with users over the years is to clean up the product set in this manner.

  • Delete all products that are not linked to any order or estimate regardless of status.
  • Rename all products that are linked to any order or estimate to include an underscore _ at the front of the product name. This allows a user to easily distinguish the old products versus the new products you are going to import.
  • Set all the products to inactive that are not linked to an open order or estimate. This helps to reduce the amount of old products that are no longer intended to be used.
  • Delete any product categories that no longer have any products in them.
  • Import the new product set.
  • Finally, as the user finds old products that need to be added back to the “master” set simply edit the product name and remove the underscore _ from the product name. This effectively advises all users that this product is now part of the new set that everyone should use going forward.
  • At a later date, perhaps 3-6 months you can then set any other remaining old products “ones with underscores _ at front of product name” to inactive. After which you will then have a product list that only consists of the products that you wish to use on an ongoing basis.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

// Pulls a list OF ALL products that are linked TO orders.
SELECT DISTINCT productid FROM "order details database"
WHERE productid > 0
; tmp1 FALSE
// Adds TO the list any other products that are linked TO estimates.
INSERT INTO "tmp1"
SELECT DISTINCT productid FROM "estimate details database"
WHERE productid > 0 AND productid NOT IN (SELECT productid FROM "tmp1")
;
// Deletes ALL products NOT linked TO any estimates OR orders.
DELETE FROM "product database"
WHERE productid NOT IN (SELECT productid FROM "tmp1") AND productid > 0
;
DELETE FROM "pricing database"
WHERE productid NOT IN (SELECT productid FROM "product database")
;
DELETE FROM "avi to product database"
WHERE productid NOT IN (SELECT productid FROM "product database")
;
// Updates ALL product names TO include an underscore _
// This helps TO distinguish OLD products versus newly imported ones.
UPDATE "product database"
SET productcode = '_' + productcode
WHERE productid > 0
;
// Pulls a list OF ALL products linked TO OPEN orders
SELECT DISTINCT productid FROM "order details database"
WHERE orderid IN (SELECT orderid FROM "order database" WHERE STATUS IN ('WIP','Built','On Hold','Picked-Up'))
;tmp2 FALSE
// Adds TO the list any other products that are linked TO estimates
INSERT INTO "tmp2"
SELECT DISTINCT productid FROM "estimate details database"
WHERE productid > 0 AND productid NOT IN (SELECT productid FROM "tmp2")
;
// Sets ALL products NOT linked TO OPEN orders OR estimates TO inactive.
UPDATE "product database"
SET productactive = 'FALSE'
WHERE productid NOT IN (SELECT productid FROM "tmp2")
;
// Deletes any product categories WITHOUT products
DELETE FROM "product category database"
WHERE productcategoryid NOT IN (SELECT productcategoryid FROM "product database")
;
// Final step IS TO actually run the import.
  • Entered : 08/26/2011, Brandon
You could leave a comment if you were logged in.