Thread: SQL Update Extended Pricing

The problem I am having i am not sure if it started when we switch to 9.1 or before but either way it seems like it should have an easy fix. So i am asking all of you for your help again! Sorry post is so long... Not good at explaining things

The store has 6 Customer levels, and we use extended pricing to set the prices for each level. I created a simple program in C#, that reads a csv, performs some calculations depending on what the level, and manufacturer code it has and then it puts it all into the store. It works and is placing all the values I want in the right spot.

The problem I am running into is when the lowest level gets no discount I want it to show list price. According to the store all you have to do is place a 0 into, and it will revert back to the list price. Well in the program I place a zero, but when i run the program then go into the store and look at the extended pricing on all six levels they are in a format like this below.

level1 - 0.0000
level2 - 9.4233
level3 - 8.2344
level4 - 7.2342
level5 - 4.1556
level6 - 3.2342

When it looks like this any parts in level 1 show as $0.00 when you add it to your shopping cart. On the page that shows me the discount levels their is a button that says UPDATE, if I click the update button without touching any of the values. it fixes the problem and switches to look like this below, and that makes it work fine in the store when adding it to the cart is shows the list price. I want to figure out what i need to do to avoid having to click that update button to get it to work.

level1 - 0
level2 - 9.4233
level3 - 8.2344
level4 - 7.2342
level5 - 4.1556
level6 - 3.2342

Is their something I am missing when i am placing them into store using sql? below is the sql I am using. This sample should take product 5, and place a different price into all six levels, but level 1 should show 0. Am I messing something up in my sql to cause it to not fully update?

Code:

// This maps 6 levels to product 5
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 1)
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 2)
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 3)
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 4)
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 5)
INSERT INTO dbo.ProductCustomerLevel (ProductID, CustomerLevelID) values (5, 6)

// this adds the prices to the levels that were just mapped
DELETE FROM dbo.ExtendedPrice WHERE VariantID=5;
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,1,0);
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,2,2000);
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,3,3000);
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,4,4000);
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,5,5000);
INSERT INTO dbo.ExtendedPrice (VariantID, CustomerLevelID, Price) values (5,6,6000);

Have you tried simply not inserting an extended price for those items that you don't want to discount? i.e. don't insert a zero - just leave it with no extended price.


TTFN

BFG

Originally Posted by BFG 9000

Have you tried simply not inserting an extended price for those items that you don't want to discount? i.e. don't insert a zero - just leave it with no extended price.


TTFN

BFG

omg, no i have not. I will give it a try and let you know.

Works great now. All I had to do was modify the c# code so that it skips the customer level if no discount was applied. If it skips it the store automatically places a 0 in it which made it work.

Once again BFG has helped me out. Thanks man I really appreciate it!