Sale on products entered into inventory before a certain date?

[laurelsstitchery]

I have a ton of old inventory that I would like to be rid of. I'd like to be able to set up a sale on all inventory entered before say, January 1, 2014. Is there any way to do this, preferable a quick and easy way? :)

[mc12345678]

Hmmm, thinking may be able to assign all such product to a second newly created category through a sql statement, then place that category on sale?

Not sure if would need to do something with the master category for each of the products (which could be another sql statement) and then if so changed, would also remove the old category reference for those old categories.

The interim table I'm thinking of is the products_to_categories table with the master_category located in the products table.

I forget the field name for the date criteria, but would use a comparison of less than the date of concern.

[Ajeh]

Make a backup of your database ...

Did you make a really good backup of your database?

NOTE: If you forget to make a backup of your database or really make a mess ... this should hopefully not be hard to fix if things go wrong ...

1 Create a New Category that you will be using to place ALL products in this New Category on Sale

2 Check to see what the new categories_id is for that category

Let's assume the new categories_id is 1155 ...

To get ALL Products that were added prior to January 1, 2014 to be in this new category that is categories_id 1155, you can run this SQL command:

insert into products_to_categories (products_id, categories_id) select products_id, 1155
from products 
where products_date_added < '2014-01-01 00:00:00' 
and products_id not in (select products_id from products_to_categories where categories_id = 1155);

So now all Products created before January 1, 2014 are in categories_id 1155 ...

However, we need to now get all of those Products to use this new categories_id 1155 to be their master_categories_id ...

Remember, to make this change will *cancel* any current SaleMaker sale prices on these Products ...

Use the SQL command:

UPDATE products SET master_categories_id = 1155 WHERE products_date_added < '2014-01-01 00:00:00';

Now, all of these Products added before January 1, 2014 are using the master_categories_id 1155 ...

Next, you need to add a SaleMaker sale on this category ...

Read through this slowly and think about what this is doing and if this is what you are trying to accomplish ...

If so, great, you are good to go ... if you have questions, ask them before you try this ...

[laurelsstitchery]

Just to double check the thought process here: This will take the items from their current category and plop them into the newly created category. Then I can create a sale for just that category? If so, then that's exactly what I want to do. I'm going to start on this tomorrow after I make a double backup of my database. THANK YOU SO MUCH!!

Make a backup of your database ...

Did you make a really good backup of your database?

NOTE: If you forget to make a backup of your database or really make a mess ... this should hopefully not be hard to fix if things go wrong ...

1 Create a New Category that you will be using to place ALL products in this New Category on Sale

2 Check to see what the new categories_id is for that category

Let's assume the new categories_id is 1155 ...

To get ALL Products that were added prior to January 1, 2014 to be in this new category that is categories_id 1155, you can run this SQL command:

insert into products_to_categories (products_id, categories_id) select products_id, 1155
from products 
where products_date_added < '2014-01-01 00:00:00' 
and products_id not in (select products_id from products_to_categories where categories_id = 1155);

So now all Products created before January 1, 2014 are in categories_id 1155 ...

However, we need to now get all of those Products to use this new categories_id 1155 to be their master_categories_id ...

Remember, to make this change will *cancel* any current SaleMaker sale prices on these Products ...

Use the SQL command:

UPDATE products SET master_categories_id = 1155 WHERE products_date_added < '2014-01-01 00:00:00';

Now, all of these Products added before January 1, 2014 are using the master_categories_id 1155 ...

Next, you need to add a SaleMaker sale on this category ...

Read through this slowly and think about what this is doing and if this is what you are trying to accomplish ...

If so, great, you are good to go ... if you have questions, ask them before you try this ...

[mc12345678]

Yep that's the formal coded way to do what I haphazardly put together from what I remembered ajeh previously suggesting. :)

But yep, with no doubt, that will specifically place all such items into a category where the category can then be put "on sale". If it is something you think you would want to routinely perform (annually) then it could be all formalized a bit into a sort of script to run each year, or it could even be something to have done "nightly" to keep product that is older than so long ago out of the recent product area and automatically go on sale.

[Ajeh]

That is correct ...

Note: these Products will still be Linked Products to other Categories, but will get the SaleMaker sale that you make. There are also ways to remove them from other Linked Categories, but the advantage is seeing these Products that were setup for SaleMaker in the new categories_id 1155 *and* the other Categories to help sell them all faster ... it is up to you if you really want them removed from the other categories ...

My personal though is leave them as Linked Products to the other Categories in addition to being in the new Sale/Clearance category 1155 ...

[laurelsstitchery]

Yes, having them linked to the previous category is a huge plus for me! Thanks again! :)

That is correct ...

Note: these Products will still be Linked Products to other Categories, but will get the SaleMaker sale that you make. There are also ways to remove them from other Linked Categories, but the advantage is seeing these Products that were setup for SaleMaker in the new categories_id 1155 *and* the other Categories to help sell them all faster ... it is up to you if you really want them removed from the other categories ...

My personal though is leave them as Linked Products to the other Categories in addition to being in the new Sale/Clearance category 1155 ...

[laurelsstitchery]

... if you have questions, ask them before you try this ...

One more question. As I was thinking about this process today, I realized there are quite a few items that have been "in stock" for a long time, but that's because I replenish and those are items I'm not going to be including in the sale. Is there a way to do this using the Product ID field? I think that's going to be the best way to get this figured out and done. Thanks so much! :)