Thread: Help! Storewide Sale without Changing items already on sale???

I want to have a storewide sale of 10% off. BUT, I don't want to lose the markdown price that I've already got on several hundred titles. Also, if it is already marked down, I don't want the sale to apply to the markdown price. Is there any way to do this?

I could do a set sales discount percentage in the store-wide maintenance, but if I reset all default variants, I lose all the sale prices on the stuff already marked down. Any suggestions?? Thank you!

You could set a 'sale discount percentage' in the Store Wide Maintenance page, but it's gonna override your existing markdown prices, so this isn't the way to go. The other option you have is 'Customer Level'. Do any of your customer mapped to a customer level? Well you can create a customer level, say 'Store Wide Sale' with 10% level discount, then map your customers to that level. Once they will logged in and view every product in your site, they'll see similar image as shown below:



That particular product already has a markdown price (see below), but the level discount was applied in the product's 'Regular Price'. That's how customer level discounting works.



Hope this helps.

How about something like :-

UPDATE ProductVariant SET SalePrice = Price * 0.9 WHERE SalePrice = 0;

N.B. I have not tested this.



TTFN

BFG

If I sue the sale discount percentage in Store Wide Maintenance, is there a way to reset that without removing the marked down prices when completed?

As for the set variant, that sounds interesting but how would I remove the sale afterwards?

I allow customers to not register so the set customer levels would not work for everyone.

How about something like this :-

C#/VB.NET Code:

UPDATE ProductVariant SET ExtensionData5 = 'sale' WHERE SalePrice = 0;
UPDATE ProductVariant SET SalePrice = Price * 0.9 WHERE ExtensionData5 = 'sale'; 


& then to remove :-

C#/VB.NET Code:

UPDATE ProductVariant SET SalePrice = 0 WHERE ExtensionData5 = 'sale'; 


N.B. THIS IS NOT TESTED & ASSUMES YOU DON'T ALREADY USE ExtensionData5


TTFN

BFG

I tried this on my test server and got this error:

Exception=The data types ntext and varchar are incompatible in the equal to operator.

How about this :-

C#/VB.NET Code:

UPDATE ProductVariant SET ExtensionData5 = 123456 WHERE SalePrice = 0;
UPDATE ProductVariant SET SalePrice = Price * 0.9 WHERE ExtensionData5 = 123456; 


& then to remove :-

C#/VB.NET Code:

UPDATE ProductVariant SET SalePrice = 0 WHERE ExtensionData5 = 123456; 


Am I supposed to add anything? I pasted exactly what you put and got this:

Exception=Operand type clash: int is incompatible with ntext

Hmm..

Try this :-

C#/VB.NET Code:

UPDATE ProductVariant SET ExtensionData5 = 'sale' WHERE SalePrice = 0;
UPDATE ProductVariant SET SalePrice = Price * 0.9 WHERE ExtensionData5 LIKE 'sale'; 


& then to remove :-

C#/VB.NET Code:

UPDATE ProductVariant SET SalePrice = 0 WHERE ExtensionData5 LIKE 'sale'; 


It took that but it did not make any changes to the prices. Everything looks the same.

What was the message when you ran the script? i.e. (0 row(s) affected)

Maybe the SalePrice existing value is NULL not 0, that's why those scripts do not have any effect at all. Try this one instead:

Code:

UPDATE ProductVariant SET ExtensionData5 = 'sale' where SalePrice = 0 or SalePrice is NULL;
UPDATE ProductVariant SET SalePrice = Price * 0.9 WHERE ExtensionData5 like 'sale';

...then to remove:

Code:

UPDATE ProductVariant SET SalePrice = 0 WHERE ExtensionData5 LIKE 'sale';

MAKE A FULL BACKUP OF YOUR DB FIRST, just in case.

That's perfect! Thank you. One more question... is there any way to add a "where category is X"? type of line to limit it to one category?

C#/VB.NET Code:

UPDATE ProductVariant SET ExtensionData5 = 'sale' where ( SalePrice = 0 or SalePrice is NULL ) AND ProductID IN (Select ProductID FROM ProductCategory WHERE CategoryID = 123); 


This is for CategoryID 123 - obviously change it to whatever category you like.


TTFN

BFG